Modeling for SCD2 type fact table

[Anonymous]

I'm dealing with a model that has a fact table more closely modeled to an SCD 2.

InstalledServiceID	ServiceLocationID	ServiceID	Service	AccountID	AddressID	GeoCode	InstalledFeatureID	Qty	Cost	Price	TotalNet	MonthlyInstallment	ARPU	Installed	ServiceStart	ServiceEnd	FeatureStart	FeatureEnd	RecordStart	RecordEnd
112438	0	257	9783234324	16072	979117	2501737000	452999	1	0.0000	0.0000	0.0000	0.0000	0.00000000	1	2005-07-19 11:33:05.000	9999-09-09 00:00:00.000	2005-07-19 00:00:00.000	9999-09-09 00:00:00.000	2005-07-19 11:33:05.000	NULL​

This table is essentially a service history table down to the SKU level tracking Monthly Recurring Revenue.  Customers can change quantities of features within a contracted service over time, so these records capture each change.

In PowerBI I created the following DAX measure:

Base MRC = 
VAR MAXDATE =
    MAX ( ___DateDimension[Date] )
VAR MINDATE =
    MIN ( ___DateDimension[Date] )
VAR CurrentFeatureService =
    DATE ( 9999, 9, 9 )
VAR Historic =
    SELECTEDVALUE ( __Filters[Show Historic?], FALSE () )
VAR EndDates =
    ADDCOLUMNS (
        ALL ( InstalledItems[InstalledFeatureID] ),
        "Last Date", CALCULATE (
            MAX ( InstalledItems[RecordStart] ),
            KEEPFILTERS(FILTER (
                InstalledItems,
                InstalledItems[RecordStart] <= MAXDATE
                    && InstalledItems[FeatureStart] <= MAXDATE
                    && (
                        InstalledItems[RecordEnd] = BLANK ()
                            || InstalledItems[RecordEnd] > MINDATE
                    )
                    && InstalledItems[FeatureEnd] >= MINDATE
            ))
        )
    )
VAR FilteredDates =
    TREATAS (
        EndDates,
        InstalledItems[InstalledFeatureID],
        InstalledItems[RecordStart]
    )
RETURN
    IF (
        Historic,
        CALCULATE (
            [Base Revenue],
            FilteredDates,
            ProductCatalog[RecurringPeriod] = "M"
        ),
        CALCULATE (
            [Base Revenue],
            FilteredDates,
            ProductCatalog[RecurringPeriod] = "M",
            KEEPFILTERS(
                FILTER(
                    InstalledItems,
                    InstalledItems[FeatureEnd] = CurrentFeatureService 
                        && InstalledItems[RecordEnd] = BLANK ()
                )
            )
        )
    )

This works fine on the summary table to show month over month what the given MRR would have been by product.
When I implement drillthrough it tanks.

I'm trying to figure out a better way to model this that won't result in a petabyte of data.  I need to be able to filter on a given date range and get any service that was active in that time period.  If there are multiple records for that service due to changes, I would need the latest record.

[amitchandak]

@Anonymous ,

Refer if these can help

https://powerpivotpro.com/2019/06/how-can-i-get-a-lookup-table-from-a-slowly-changing-dimension-scd/

http://supun-biz.blogspot.com/2018/02/power-bi-and-slowly-changing-dimension.html

[Anonymous]

I'm familiar with these articles, and I have a DAX measure to get me where I need.  The problem is it's on a fact table (not a Dimension) so the table is huge (millions of records).  When I try to drill through on a given summary, it tanks.  Even in desktop I get the "exceeds available resources" and that's using filters down to a very small recordset.

[Anonymous]

Hi @Anonymous ,

This issue occurs when a visual has attempted to query too much data with the available resources. As a workaround, you can try filtering the visual to reduce the amount of data in the result. And try removing details fields display on visuals.  If you need that certain detail, consider reducing and adding a drill through chart to give you more information. Hope the following documentation can help you.

https://blog.crossjoin.co.uk/2020/01/20/visual-has-exceeded-the-available-resources-error-power-bi/

https://www.pbiusergroup.com/communities/community-home/digestviewer/viewthread?MessageKey=f569a508-9459-4ef2-a4a9-83b1d3fbad15&CommunityKey=b35c8468-2fd8-4e1a-8429-322c39fe7110&tab=digestviewer

Best Regards

Rena

[Anonymous]

Thank you @Anonymous 

As I stated in my previous post, I'm already filtering to just a few records.  I've actually tried filtering to just a single record and it fails.  I'm not using many details at all.  The only difference is that it's not summarized when you drill through but it's using the measure to control filtering.

I'm thinking there is a better way to model it, but I cant think of how.

[Anonymous]

@Anonymous @amitchandak 

This is what I get from running the DAX implementing tools from all of the links you've provided.

// DAX Query
DEFINE
  VAR __DS0FilterTable = 
    TREATAS({FALSE,
      BLANK()}, '__Filters'[Show Historic?])

  VAR __DS0FilterTable2 = 
    FILTER(
      KEEPFILTERS(VALUES('___DateDimension'[Date])),
      AND(
        '___DateDimension'[Date] >= DATE(2019, 3, 1),
        '___DateDimension'[Date] < DATE(2020, 3, 1)
      )
    )

  VAR __DS0FilterTable3 = 
    TREATAS({"Contracted (Actual)"}, 'LifeCycle_Status'[Status])

  VAR __DS0FilterTable4 = 
    TREATAS({"Install"}, 'ActivityTypes'[ActivityType])

  VAR __DS0FilterTable5 = 
    TREATAS({FALSE,
      BLANK()}, 'CustomerAccounts'[IsTestCustomer])

  VAR __DS0FilterTable6 = 
    TREATAS({"MSx"}, 'ProductCatalog'[Line Of Business])

  VAR __DS0Core = 
    SUMMARIZECOLUMNS(
      ROLLUPADDISSUBTOTAL('ProductCatalog'[Product], "IsGrandTotalRowTotal"),
      ROLLUPADDISSUBTOTAL(
        ROLLUPGROUP('___DateDimension'[Year, Qtr], '___DateDimension'[QTRKEY]), "IsGrandTotalColumnTotal"
      ),
      __DS0FilterTable,
      __DS0FilterTable2,
      __DS0FilterTable3,
      __DS0FilterTable4,
      __DS0FilterTable5,
      __DS0FilterTable6,
      "Base_MRC", 'Sales Measures'[Base MRC]
    )

  VAR __DS0Primary = 
    TOPN(
      102,
      SUMMARIZE(__DS0Core, 'ProductCatalog'[Product], [IsGrandTotalRowTotal]),
      [IsGrandTotalRowTotal],
      0,
      'ProductCatalog'[Product],
      1
    )

  VAR __DS0Secondary = 
    TOPN(
      102,
      SUMMARIZE(
        __DS0Core,
        '___DateDimension'[Year, Qtr],
        '___DateDimension'[QTRKEY],
        [IsGrandTotalColumnTotal]
      ),
      [IsGrandTotalColumnTotal],
      1,
      '___DateDimension'[QTRKEY],
      1,
      '___DateDimension'[Year, Qtr],
      1
    )

EVALUATE
  __DS0Secondary

ORDER BY
  [IsGrandTotalColumnTotal],
  '___DateDimension'[QTRKEY],
  '___DateDimension'[Year, Qtr]

EVALUATE
  NATURALLEFTOUTERJOIN(
    __DS0Primary,
    SUBSTITUTEWITHINDEX(
      __DS0Core,
      "ColumnIndex",
      __DS0Secondary,
      [IsGrandTotalColumnTotal],
      ASC,
      '___DateDimension'[QTRKEY],
      ASC,
      '___DateDimension'[Year, Qtr],
      ASC
    )
  )

ORDER BY
  [IsGrandTotalRowTotal] DESC, 'ProductCatalog'[Product], [ColumnIndex]

This runs 137ms on the Storage Engine and 100,455ms on the Formula Engine.  FE is single threaded and very slow.
The point I'm trying to get to is that all DAX methods that I have tried to reconcile multiple records for the same service over time heavily rely on the Formula Engine which uses up too much memory when you drill down on the details.

I'm trying to find a solution that doesn't result in the same problem of high FE usage.