cancel
Showing results for 
Search instead for 
Did you mean: 

Suboptimal queries on Azure SSAS Tabular in live connection mode

We use Power BI in direct query mode with Azure SSAS Tabular. The queries generated by Power BI seem to be slower than necessary. 

 

For example, I have a table in Power BI that displays 1 dimension and 3 measures. The query generated runs for 10.2 seconds in DAX Studio:

 

 

DEFINE
    MEASURE 'TRANSACTION'[Rank on Revenue Is Lower then Other Customers] = ( /* USER DAX BEGIN */
    [Product Name Clock Rank on Revenue Other Customers]
        - [Product Name Clock Rank on Revenue] )
		
    VAR __DS0FilterTable =
        FILTER (
            KEEPFILTERS ( VALUES ( 'CUSTOMER'[Customer] ) ),
            SEARCH ( "<customer name>", 'CUSTOMER'[Customer], 1, 0 ) >= 1
        )
    VAR __DS0FilterTable2 =
        FILTER (
            KEEPFILTERS ( VALUES ( 'TRANSACTION TYPE'[Transaction Level 1] ) ),
            OR (
                'TRANSACTION TYPE'[Transaction Level 1] = "<level 1 desc 1>",
                'TRANSACTION TYPE'[Transaction Level 1] = "<level 1 desc 2>"
            )
        )
    VAR __DS0FilterTable3 =
        FILTER (
            KEEPFILTERS ( VALUES ( 'PRODUCT'[Product Level 4] ) ),
            'PRODUCT'[Product Level 4] = "<Product Level 4 Name>"
        )
    VAR __DS0FilterTable4 =
        FILTER (
            KEEPFILTERS ( VALUES ( 'CALENDAR'[In Last 6 Years] ) ),
            'CALENDAR'[In Last 6 Years] = TRUE
        )
    VAR __DS0FilterTable5 =
        FILTER (
            KEEPFILTERS ( VALUES ( 'PRODUCT'[FPC] ) ),
            'PRODUCT'[FPC] = "<FPC Name>"
        )
    VAR __DS0FilterTable6 =
        FILTER ( KEEPFILTERS ( VALUES ( 'CALENDAR'[Year] ) ), 'CALENDAR'[Year] = <Year> )
		
    VAR __ApplyFilterSQDS0 =
        TOPN (
            50,
            SUMMARIZECOLUMNS (
                'PRODUCT'[Product Name Clock],
                __DS0FilterTable,
                __DS0FilterTable2,
                __DS0FilterTable3,
                __DS0FilterTable4,
                __DS0FilterTable5,
                __DS0FilterTable6,
                "Product_Name_Clock_Rank_on_Revenue", 'TRANSACTION'[Product Name Clock Rank on Revenue]
            ),
            [Product_Name_Clock_Rank_on_Revenue], 1
        )
		
    VAR __ValueFilterDM0 =
        FILTER (
            KEEPFILTERS (
                SUMMARIZECOLUMNS (
                    'PRODUCT'[Product Name Clock],
                    __DS0FilterTable,
                    __DS0FilterTable2,
                    __DS0FilterTable3,
                    __DS0FilterTable4,
                    __DS0FilterTable5,
                    __DS0FilterTable6,
                    __ApplyFilterSQDS0,
                    "Product_Name_Clock_Rank_on_Revenue", 'TRANSACTION'[Product Name Clock Rank on Revenue],
                    "Product_Name_Clock_Rank_on_Revenue_Other_Customers", 'TRANSACTION'[Product Name Clock Rank on Revenue Other Customers]
                )
            ),
            NOT ( ISBLANK ( [Product_Name_Clock_Rank_on_Revenue] ) )
        )
        
EVALUATE
TOPN (
    501,
    SUMMARIZECOLUMNS (
        'PRODUCT'[Product Name Clock],
        __DS0FilterTable,
        __DS0FilterTable2,
        __DS0FilterTable3,
        __DS0FilterTable4,
        __DS0FilterTable5,
        __DS0FilterTable6,
        __ApplyFilterSQDS0,
        __ValueFilterDM0,
        "Product_Name_Clock_Rank_on_Revenue", 'TRANSACTION'[Product Name Clock Rank on Revenue],
        "Product_Name_Clock_Rank_on_Revenue_Other_Customers", 'TRANSACTION'[Product Name Clock Rank on Revenue Other Customers],
        "Rank on Revenue Is Lower then Other Customers", 'TRANSACTION'[Rank on Revenue Is Lower then Other Customers]
    ),
    [Product_Name_Clock_Rank_on_Revenue], 1,
    'PRODUCT'[Product Name Clock], 1
)
ORDER BY
    [Product_Name_Clock_Rank_on_Revenue],
    'PRODUCT'[Product Name Clock]

 

I am not a DAX expert, but interpreting the query it seems that it calculates 2 help-tables: __ApplyFilterSQDS0 and __ValueFilterDM0. In this particular example, they do not seem very useful; By removing them and calculating all 3 measures in the final result, I was able to halve the run time (5.1 seconds!), with identical results:

 

DEFINE 
	MEASURE 'TRANSACTION'[Rank on Revenue Is Lower then Other Customers] = ( 
    	[Product Name Clock Rank on Revenue Other Customers] - [Product Name Clock Rank on Revenue] 
	)
    
    VAR __DS0FilterTable =
        FILTER (
            KEEPFILTERS ( VALUES ( 'CUSTOMER'[Customer] ) ),
            SEARCH ( "<customer name>", 'CUSTOMER'[Customer], 1, 0 ) >= 1
        )
    VAR __DS0FilterTable2 =
        FILTER (
            KEEPFILTERS ( VALUES ( 'TRANSACTION TYPE'[Transaction Level 1] ) ),
            OR (
                'TRANSACTION TYPE'[Transaction Level 1] = "<name 1>",
                'TRANSACTION TYPE'[Transaction Level 1] = "<name 2>"
            )
        )
    VAR __DS0FilterTable3 =
        FILTER (
            KEEPFILTERS ( VALUES ( 'PRODUCT'[Product Level 4] ) ),
            'PRODUCT'[Product Level 4] = "<product>"
        )
    VAR __DS0FilterTable4 =
        FILTER (
            KEEPFILTERS ( VALUES ( 'CALENDAR'[In Last 6 Years] ) ),
            'CALENDAR'[In Last 6 Years] = TRUE
        )
    VAR __DS0FilterTable5 =
        FILTER (
            KEEPFILTERS ( VALUES ( 'PRODUCT'[FPC] ) ),
            'PRODUCT'[FPC] = "FPC Planten Groen"
        )
    VAR __DS0FilterTable6 =
        FILTER ( KEEPFILTERS ( VALUES ( 'CALENDAR'[Year] ) ), 'CALENDAR'[Year] = 2015 )

EVALUATE
TOPN (
    501,
    SUMMARIZECOLUMNS (
        'PRODUCT'[Product Name Clock],
        __DS0FilterTable,
        __DS0FilterTable2,
        __DS0FilterTable3,
        __DS0FilterTable4,
        __DS0FilterTable5,
        __DS0FilterTable6
        // 00:00.0
        
        ,"Product_Name_Clock_Rank_on_Revenue", 'TRANSACTION'[Product Name Clock Rank on Revenue]
        // 00:01.0

	,"Product_Name_Clock_Rank_on_Revenue_Other_Customers", 'TRANSACTION'[Product Name Clock Rank on Revenue Other Customers]
	// 00:02:4

	,"Difference", 'TRANSACTION'[Rank on Revenue Is Lower then Other Customers]
	// 00:03.5
		
	// The 3 measures combined:
	// 00:05.1
    )
)
ORDER BY 'PRODUCT'[Product Name Clock]

 

Did I get the same results by coincidence? Or are the helper tables generally necessary?

I am happy to provide more details if needed, cause the lack of performance is a kind of big issue for us!

 

Kind regards,

Sebastiaan

 

Status: New
Comments
Super Contributor

@sebastiaan-pbi

 

It seems that __ApplyFilterSQDS0 and __ValueFilterDM0 are two filtered tables. Are you able to get the same results with more data and involve some slicers in your data source?

 

Best Regards,
Herbert