Forum Discussion

VickyDev18's avatar
VickyDev18
Icon for Advocate II rankAdvocate II
2 years ago

Optimizing a query that seems optimal already

First off, do want to be clarify that I am not looking for a specific solution but more of a theoritcal answer. 

 

I have a simple star schema model  with Financial data transactions and dimension tables tied to them.

The size of the model is ~250MB. 

 

To show a simple P&L view I've done the following:-

  • Simple measures to calculate Revenue, Expense, EBITDA and EBITDA% for Actuals and Plan.
  • Disconnected table with the metrics above with a metric that uses SWITCH to help display these in a P&L statement view.
  • Variance and Variance% measures
  • Calculation group with items for MTD, YTD measures using standard DATESMTD, DATESYTD functions.  

The measures are all pretty straightforward and this specific query takes about 500 ms to run. 

My question is... Is it possible to get it to run any faster e.g. down to 100 ms or 200 ms or is this the best it could run given all the measures are pretty straightfoward and there is no scope to optimize them further? 


Note: I already tried increasing Fabric capacity from F2 to F4 and even F64 and that made no difference.   

 

 

DEFINE
    ---- MODEL MEASURES BEGIN ----
    MEASURE __Metrics[Amount] = SUM ( Financials[Accounting Currency Amount Signed] )
    
    // ACTUALS METRICS
    MEASURE __Metrics[Amount_AC] = CALCULATE ( [Amount], KEEPFILTERS ( Financials[Scenario Code] = "FY24 ACT" ) )    
    MEASURE __Metrics[Revenue_AC] = CALCULATE ([Amount_AC],KEEPFILTERS ( MainAccount[Main Account Type] = "Revenue" ))
    MEASURE __Metrics[Expense_AC] = CALCULATE ([Amount_AC],KEEPFILTERS ( MainAccount[Main Account Type] = "Expense" ))
    MEASURE __Metrics[EBITDA_AC] = [Revenue_AC] - [Expense_AC]
    MEASURE __Metrics[EBITDA%_AC] = DIVIDE ( [EBITDA_AC], [Revenue_AC] )
   
	// PLAN METRICS   
    MEASURE __Metrics[Amount_PL] = CALCULATE ( [Amount], KEEPFILTERS ( Financials[Scenario Code] = "FY24 BUD" ) )
    MEASURE __Metrics[Revenue_PL] = CALCULATE ([Amount_PL], KEEPFILTERS ( MainAccount[Main Account Type] = "Revenue" ))
    MEASURE __Metrics[Expense_PL] = CALCULATE ([Amount_PL],KEEPFILTERS ( MainAccount[Main Account Type] = "Expense" ))
    MEASURE __Metrics[EBITDA_PL] = [Revenue_PL] - [Expense_PL] 
    MEASURE __Metrics[EBITDA%_PL] = DIVIDE ( [EBITDA_PL], [Revenue_PL] )
    
    // Metric to show as an income statement
    MEASURE __IncomeStatement[IncomeStatement_AC] =
        VAR __Metric = SELECTEDVALUE ( '__IncomeStatement'[Metric Short Name] )
        VAR __Result =
            SWITCH (
                __Metric,
                "REV", [Revenue_AC],
                "EXP", [Expense_AC],
                "EBITDA", [EBITDA_AC],
                "EBITDA%", [EBITDA%_AC],
                BLANK ()
            )
        RETURN
            __Result
            
    MEASURE __IncomeStatement[IncomeStatement_PL] =
        VAR __Metric = SELECTEDVALUE ( '__IncomeStatement'[Metric Short Name] )
        VAR __Result =
            SWITCH (
                __Metric,
                "REV", [Revenue_PL],
                "EXP", [Expense_PL],
                "EBITDA", [EBITDA_PL],
                "EBITDA%", [EBITDA%_PL],
                BLANK ()
            )
        RETURN
            __Result
    
    MEASURE __IncomeStatement[IncomeStatement_AC-PL] = [IncomeStatement_AC] - [IncomeStatement_PL]
    
    MEASURE __IncomeStatement[IncomeStatement_AC-PL%] = IF (
	    CONTAINSSTRING ( SELECTEDVALUE ( '__IncomeStatement'[Metric Short Name] ), "%" ),
	    BLANK (),
	    DIVIDE ( [IncomeStatement_AC-PL], __IncomeStatement[IncomeStatement_PL] )
	)

---- MODEL MEASURES END ----
    VAR __DS0Core =
        SUMMARIZECOLUMNS (
            '__IncomeStatement'[Metric Name],
            '__IncomeStatement'[Metric ID],
            '__CG_Period'[Period],
            '__CG_Period'[Ordinal],
            "AC", '__IncomeStatement'[IncomeStatement_AC],
            "PL", '__IncomeStatement'[IncomeStatement_PL],
            "Var", '__IncomeStatement'[IncomeStatement_AC-PL],
            "Var%", __IncomeStatement[IncomeStatement_AC-PL%]
        )
    VAR __DS0PrimaryWindowed =
        TOPN (
            101,
            SUMMARIZE (
                __DS0Core,
                '__IncomeStatement'[Metric Name],
                '__IncomeStatement'[Metric ID]
            ),
            '__IncomeStatement'[Metric ID], 1,
            '__IncomeStatement'[Metric Name], 1
        )
    VAR __DS0SecondaryBase =
        SUMMARIZE ( __DS0Core, '__CG_Period'[Period], '__CG_Period'[Ordinal] )
    VAR __DS0Secondary =
        TOPN (
            102,
            __DS0SecondaryBase,
            '__CG_Period'[Ordinal], 1,
            '__CG_Period'[Period], 1
        )
    VAR __DS0BodyLimited =
        NATURALLEFTOUTERJOIN (
            __DS0PrimaryWindowed,
            SUBSTITUTEWITHINDEX (
                __DS0Core,
                "ColumnIndex", __DS0Secondary,
                '__CG_Period'[Ordinal], ASC,
                '__CG_Period'[Period], ASC
            )
        )

EVALUATE
__DS0BodyLimited
ORDER BY
    '__IncomeStatement'[Metric ID],
    '__IncomeStatement'[Metric Name]

 

 

Don't see anything obviously wrong or bad in DAX Studio as well. The Formula engine% is high though but not sure what else can be done to reduce that in this case. 

 

Would appreciate any inputs so I know if there is scope to improve this further or is this the best that can be done. 

1 Reply

  • Look at the query plan.  Especially check for high numbers of records. Refactor your code to do aggregations earlier to reduce the cardinality as soon as possible (but not sooner)

     

    Check for RI errors.  Substitute SUMMARIZE with SUMMARIZECOLUMNS.  Eliminate CallbackDataIDs.