DAX syntax for low / transaction level queries
Against Tabular models a DAX query will often significantly outperform an MDX query for what is nominally the same result set. XLCubed can generate DAX or MDX, so when we were approached at a trade show by a customer with Tabular SSAS performance problems in Excel we were confident we could address it straight away.
The use case was in the Finance sector, and the users needed to retrieve several thousand rows from the cube at the transactional level. To get to the lowest level they set up a Pivot Table with the lowest levels of all the hierarchies on the rows section. However performance was horrible, with l queries taking 20 minutes or more and some not returning at all. Simply connecting to the model with one of our DAX tables made very little difference initially - the model was large, and the number or columns combined with their cardinality meant that a lot of work was being done on the server.
The generated DAX was cross-joining all the values from each column, which had worked well with other customers, but not in this case due to volumes and the number of joins involved. DAX in itself is not a magic bullet and SSAS Tabular models can hit performance problems on low level data – we needed a new approach.
We discussed the issue and our thinking with our friends at SQLBI and determined that instead of cross-join we should use Summarize() instead as this only uses the rows in the database, and it can access columns related to the summarized table which were required for the report. As the customer’s report had the transaction ID in it the result wasn’t aggregated, even though we were using summarize. But we wanted to add true transactional reporting too, using the Related() function.
It’s also important to note that SQL 2016 adds a couple of new functions, SummarizeColumns() and SelectColumns(), both of which are useful for this type of reporting, but offer better performance than the older equivalents.
Having implemented the changes the results were impressive. Reports previously taking 20 minutes were down to less than 15 seconds and we had some very happy users and relieved BI developers.
The changes are contained in v9 of XLCubed, currently in beta, but for anyone hand-crafting DAX a sample of the change in syntax is detailed below:
Before:
EVALUATE
FILTER (
ADDCOLUMNS (
KEEPFILTERS (
CROSSJOIN ( VALUES ( 'Customer'[Education] ), VALUES ( 'Product'[Color] ) )
),
"Internet Total Units", 'Internet Sales'[Internet Total Units],
"Internet Total Sales", 'Internet Sales'[Internet Total Sales]
),
NOT ISBLANK ( [Internet Total Units] )
)
ORDER BY
'Customer'[Education],
'Product'[Color]
After:
EVALUATE
FILTER (
ADDCOLUMNS (
KEEPFILTERS (
SUMMARIZE ( 'Internet Sales', 'Customer'[Education], 'Product'[Color] )
),
"Internet Total Units", 'Internet Sales'[Internet Total Units],
"Internet Total Sales", 'Internet Sales'[Internet Total Sales]
),
NOT ISBLANK ( [Internet Total Units] ) || NOT ISBLANK ( [Internet Total Sales] )
)
ORDER BY
'Customer'[Education],
'Product'[Color]
More info can be found here.