Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Modeling for SCD2 type fact table

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 Pr...
Need Help
Anonymous's avatar
Anonymous
Not applicable
6 years ago

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's avatar
Anonymous
Not applicable
6 years ago

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's avatar
    Anonymous
    Not applicable
    6 years ago

    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.