topic Re: Performance semantic model measure vs custom measure in DAX Commands and Tips

Performance semantic model measure vs custom measure

Credential — Tue, 30 Nov 2021 14:18:59 GMT

Hello together,
I have a question about custom measures. I have created a measure in my semantic model which is quite slow.
The measure takes about 15 seconds. If I define the same measure as a custom measure in Power BI or DAX Studio, the measure takes only 1 second. Can anyone tell me what is the reason for this?

Thanks

Re: Performance semantic model measure vs custom measure

AlexisOlson — Tue, 30 Nov 2021 18:07:16 GMT

What semantic model are you referring to?

Re: Performance semantic model measure vs custom measure

Credential — Tue, 30 Nov 2021 19:53:56 GMT

Sorry, it's a ssas tabular model 1500

Re: Performance semantic model measure vs custom measure

AlexisOlson — Tue, 30 Nov 2021 21:07:46 GMT

Power BI is fundamentally pretty similar to SSAS under the hood but is updated more frequently. In the last couple of years, there have been some optimizations that could potentially make a large difference.

As an example, the May 2020 update improved performance issues I explained here. The October 2021 update also included some optimizations.

If I understand correctly, the latest major version of SSAS is for SQL Server 2019, although there have been "cumulative updates" within the last couple of months.

This is all largely speculation since I don't actually know what your measures are and what versions of things you're running (and even if I did, I'd have to do some deep digging to isolate differences).

Re: Performance semantic model measure vs custom measure

m3tr01d — Wed, 01 Dec 2021 00:27:59 GMT

Credential
When you say, the measure takes 15 seconds. How did you come up with that number?
Is it when you use the measure inside the report?

Re: Performance semantic model measure vs custom measure

Credential — Wed, 01 Dec 2021 09:26:50 GMT

I don't think it has anything to do with Power BI. I think it's more because of how the Query Optimizer optimizes the measures.

Here is the query with a running time of 15 seconds:

-- Semantic Model // DAX Query DEFINE VAR __DS0FilterTable = FILTER( KEEPFILTERS(VALUES('DIM.BI_Datum'[Datum])), 'DIM.BI_Datum'[Datum] < DATE(2021, 11, 16) ) VAR __DS0FilterTable2 = TREATAS({"19144"}, 'DIM.SAP_Personal_Daten__Besitzer'[Personalnummer_Master]) EVALUATE SUMMARIZECOLUMNS( __DS0FilterTable, __DS0FilterTable2, "Neukunde_DIV_SUM", IGNORE('FACT.Measure'[Neukunde_DIV_SUM]) )

Here is the query with a running time of 1 seconds:

-- Custom Measure // DAX Query DEFINE MEASURE 'CUSTOM.Measure'[CM_Neukunde_DIV_SUM] = (/* USER DAX BEGIN */ VAR ST = LASTDATE('DIM.BI_Datum'[Datum]) RETURN CALCULATE( SUMX( SUMMARIZE( FILTER( 'FACT.TJ_Rechnungsausgangsjournal' , RELATED('DIM.TJ_Auftrag'[Erstellt]) >= CALCULATE('FACT.Measure'[Datum_VM_Relevant_Seit], 'DIM.BI_Datum'[Datum] <= ST) && RELATED('DIM.TJ_Rechnungsausgangsjournal'[Ist_Fakturiert]) = "J" ) ,'DIM.TJ_Auftrag'[Erstellt] ,'DIM.TJ_Unternehmen'[Nummer] ,'DIM.BI_Kostenstelle'[DIV_Kurzname] ,"IstNeukunde" ,IF( CALCULATE( 'FACT.Measure'[TJ_Leistungsnachweis_Umsatz_per_VM_SUM] ,ALLEXCEPT( 'FACT.TJ_Rechnungsausgangsjournal' ,'DIM.TJ_Unternehmen'[Nummer] ,'DIM.BI_Kostenstelle'[DIV_Kurzname] ) ,FILTER(DATUM_ALL, 'DIM.BI_Datum'[Datum] < 'DIM.TJ_Auftrag'[Erstellt]) ,KEEPFILTERS('FILTER.VM_Relevant'[Ist_VM_Relevant] = "N") ,KEEPFILTERS('FILTER.VM_Relevant_STO_GES_DIV'[Ist_VM_Relevant_STO_GES_DIV] = "N") ,KEEPFILTERS('FILTER.VM_Relevant_KST'[Ist_VM_Relevant_KST] = "N") ) = 0 && NOT CALCULATE( 'FACT.Measure'[TJ_Leistungsnachweis_Umsatz_per_VM_SUM] ,ALLEXCEPT( 'FACT.TJ_Rechnungsausgangsjournal' ,'DIM.TJ_Unternehmen'[Nummer] ) ,FILTER(DATUM_ALL, 'DIM.BI_Datum'[Datum] < 'DIM.TJ_Auftrag'[Erstellt]) ,KEEPFILTERS('FILTER.VM_Relevant'[Ist_VM_Relevant] = "N") ,KEEPFILTERS('FILTER.VM_Relevant_STO_GES_DIV'[Ist_VM_Relevant_STO_GES_DIV] = "N") ,KEEPFILTERS('FILTER.VM_Relevant_KST'[Ist_VM_Relevant_KST] = "N") ) = 0 , 1 ,BLANK() ) ) ,[IstNeukunde] ) ,USERELATIONSHIP('DIM.TJ_Auftrag'[Erstellt], 'DIM.BI_Datum'[Datum]) ) /* USER DAX END */) VAR __DS0FilterTable = FILTER( KEEPFILTERS(VALUES('DIM.BI_Datum'[Datum])), 'DIM.BI_Datum'[Datum] < DATE(2021, 11, 16) ) VAR __DS0FilterTable2 = TREATAS({"19144"}, 'DIM.SAP_Personal_Daten__Besitzer'[Personalnummer_Master]) EVALUATE SUMMARIZECOLUMNS( __DS0FilterTable, __DS0FilterTable2, "CM_Neukunde_DIV_SUM", IGNORE('CUSTOM.Measure'[CM_Neukunde_DIV_SUM]) )

'CUSTOM.Measure'[CM_Neukunde_DIV_SUM] is exact the same as 'FACT.Measure'[Neukunde_DIV_SUM]

Re: Performance semantic model measure vs custom measure

AlexisOlson — Wed, 01 Dec 2021 17:01:39 GMT

Credential wrote:

I don't think it has anything to do with Power BI. I think it's more because of how the Query Optimizer optimizes the measures.

I was suggesting that it likely does have to do with the query optimizer but this is, in fact, related to Power BI updates. When you run a query in DAX Studio, it connects to the Power BI tabular analysis server, either a .pbix you have open locally or a dataset in the service (it doesn't have its own DAX engine).

Re: Performance semantic model measure vs custom measure

Credential — Thu, 02 Dec 2021 11:47:27 GMT

But we only use SSAS tabular 1500 on premise. What does this have to do with Power BI updates?
The measure is defined once in the tabular model itself and once in the query itself. Both queries are sent to SSAS tabular 1500 and evaluated there via the FE and SE, right?

What I do not understand?

Re: Performance semantic model measure vs custom measure

AlexisOlson — Thu, 02 Dec 2021 15:31:48 GMT

OK. I may not have fully understood the specifics of your question. Let me make sure we're on the same page. Is the following accurate?

You are using Power BI with a live connection to an SSAS tabular model (not Import or DirectQuery). You have a measure that performs better as a Report Level Measure (as defined here) than the same measure defined within the SSAS model.

Re: Performance semantic model measure vs custom measure

Credential — Thu, 02 Dec 2021 16:16:04 GMT

Yes, that's correct.

Re: Performance semantic model measure vs custom measure

AlexisOlson — Thu, 02 Dec 2021 20:56:54 GMT

That is indeed quite strange. I can't think of a good reason for a Report Level Measure to be faster than the same measure defined in the tabular model. Can you verify that it's not related to caching?

Re: Performance semantic model measure vs custom measure

Credential — Fri, 03 Dec 2021 13:30:42 GMT

I know and don't understand either.
We always execute the queries in DAX Studio with a cleared cache. I have no idea why the one time 17 queries and the other time 35 queries are executed on SE.