I recently was building a model in Excel that was using data from a Tabular Model. I started going down the import path by selecting import from Analysis Services in the PowerPivot window and use the MDX query designer to select the data that I want to import into PowerPivot / Excel Model.
I imported the data but it took over one hour to import the data. I was very unsatisfied with the time it took me to refresh the data. Then I got thinking, MDX is not really designed for these tabular result sets, DAX is. As this is a tabular model I can use DAX to get the results. Unfortunately I don’t have a automated DAX query editor but writing the DAX equivalent of this particular MDX query is quite simple. After rewriting the query to DAX is brought back the import time to …. 3 minutes. From one hour on the same dataset.
These are the steps needed to import the data, to start you get this empty window that asks you to specify the MDX query:
Now instead of writing the MDX you can click on Design to have the MDX query designer help you build it:
Now press OK or click on the designer Icon in the top right to get to see the MDX. The query builder will generate the following MDX:
SELECT NON EMPTY { [Measures].[Sum of SalesAmount] } ON COLUMNS,
NON EMPTY { ([DateTable].[FullDateAlternateKey].[FullDateAlternateKey].ALLMEMBERS *
[Product].[Color].[Color].ALLMEMBERS *
[Product].[ModelName].[ModelName].ALLMEMBERS ) }
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS
FROM ( SELECT ( { [DateTable].[CalendarYear].&[2004] } )
ON COLUMNS FROM [Model])
WHERE ( [DateTable].[CalendarYear].&[2004] )
CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
Now rewriting this to DAX, interesting to note is that DAX works inside out, start by reading the comments/functions from the most inner function and work your way to the outside to see what the query returns:
EVALUATE
//Calculate the table to filter the results by the CalendarYear 2004
CALCULATETABLE(
//Add columns to table containing the measures
ADDCOLUMNS(
//Filter the results where we actualy have a measure
FILTER(
//Crossjoin all the columns required in the resultset
CROSSJOIN(
values(DateTable[FullDateAlternateKey]),
//when multiple columns from same table add them together with
//Summarize for performance
Summarize(values(Product),
Product[color],
Product[ModelName])
)
, Not IsBlank(Sales[Sum of SalesAmount])
)
, “[Total]”, Sales[Sum of SalesAmount]
)
, DateTable[calendaryear] = 2004
)
The above query is also a general approach on how you should write a query to retrieve a tabular result set using DAX to get the fastest result. It should work in most circumstances.