Star Schema gives "Exceeded Resources" Error whereas Flat Table doesn't?

DAX DAX Calculations
1

  • The enclosed file has two tabs, FLAT using a “Flat Table” and STAR a Star Schema.

  • Both are the same datasets.

  • The measures are under _PayablesMeasures

  • The measures which start with * are adjusted for for STAR, the ones without are for FLAT.

  • I had to adjust some filters for the * measures, but I don’t think that will have affected performance.

For example:

FLAT OverdueAmount

_A0_ OverdueAmount = 

VAR SelectedColumn = SELECTEDVALUE(SwitchLocalCurrency[CurrencyID])

Var InvoiceValue =
         IF( 
            ISBLANK([Days Left]), 
                BLANK(), 
                SUMX(
                        FILTER(_FACT_BSEG_AP_FLAT,
                        [Days Left] < 0
                        &&  _FACT_BSEG_AP_FLAT[H_BLART_DocumentType]<>"KZ"),
                        [_A0_ OutstandingAmount]
                        )
        )

RETURN

InvoiceValue

STAR OverdueAmount

*_A0_ OverdueAmount = 

VAR SelectedColumn = SELECTEDVALUE(SwitchLocalCurrency[CurrencyID])

Var InvoiceValue =
         IF( 
            ISBLANK([*Days Left]), 
                BLANK(), 
                SUMX(
                        FILTER(ALL(DIM_H_BLART_DocumentType[H_BLART_DocumentType]),
                        [*Days Left] < 0
                        &&  [H_BLART_DocumentType]<>"KZ"),
                        [*_A0_ OutstandingAmount]
                        )
        )

RETURN

InvoiceValue
  • I have added the same columns to the tables in both tabs.

The Problem:

Why does the Star Diagram give a “Query has Exceeded the available resources” error, whereas the Flat Table does not?

Note:

I realise there are probably some things wrong with my measures, something which I am still in the process of figuring out. But I don’t think that can be the cause this error.

Demo File:
StarVSFlat_Demo.pbix (1.4 MB)

2

try using Data Mentor to help you solve your issues.

thanks
Keith

3

Thanks, I did already put some measures through the code corrector modules. However, that doesn’t solve my issue. Not entirely sure it is measure related in the first place, and the Data Mentor can’t understand that context, unless I am missing a feature.

4

Have you tried getting rid of everything with flat? Its likely getting confused. You should always try using star schema concepts, plus it looks like you don’t have the EDNA Date table. (which you should have)
Here is the link to creating the date table within the forum that @Melissa created: Extended Date Table (Power Query M function) - Power Query / M Code Showcase - Enterprise DNA Forum

Thanks
Keith

5

I have been using Melissa’s table since 2022 :slight_smile: I just have it stored in the Power BI Service/Datamart.

I have two fact tables in the file I included to make the comparison. The Star has everything that can be a dimension in a dimension. The Flat has it all together. Slimming down the Flat Table defeats the purpose of what I am trying to illustrate.: The Star Diagram should not get an exceeded resources error before the Flat Table does.