Today I realize a report with Reporting Services as a data source that used a multidimensional cube in Analysis Services. I wrote an MDX query to query the cube. The query seemed to be made well, but was not performing. I rewrote the query using DAX and I had a better performance.
This is because the data received from the multidimensional cube are not always optimized for use with Reporting Services. From the Cumulative Update 4 Service Pack 1, you can access the multidimensional data writing DAX queries instead of MDX, obtaining a result that is natively a table and it is hoped that will be better for the requirements of a dataset in Reporting Services.
Before showing an example do a bit of theory. Behaviour of DAX expressions:
- To evaluate DAX expressions use MDX query pane in SSMS
- DAX is working as a SQL language with difference of functions and syntaxes
- Relationships among the tables of Tabular Model/ Power Pivot model are used for getting JOINS in the DAX query execution. We need not to write JOINS and GROUP BY clauses in DAX .
First, let’s talk about the EVALUATE statement that is used to write queries DAX this function can be used to get the data in result set from a table.
EVALUATE <<Tabular Expression or Name of the table>>
Argument to the Evaluate function can only be a table name or any expression that return a table.
As per behaviour of DAX, this is similar to SQL as:
SELECT * from [Products]
We cannot compare here MDX for this DAX, as there is no way of getting table level attributes in MDX. We need to specify attributes required in MDX SELECT statement.
The second statment is SUMMARIZE: Function return required totals and counts, based on several groups (of different columns or say attributes from different tables in Tabular Model). We use SUMMARIZE along with EVALUATE for getting desired result in SSMS.
SUMMARIZE (<<Table>>, <<Grouping Column1>>, <<Grouping Column2>>,,,<<Name for desired Aggregation1>>, <<Expression for Aggregation or Calculated MeasureName1>>, <<Name for desired Aggregation2>>, <<Expression for Aggregation or Calculated MeasureName2>>)
EVALUATE ( SUMMARIZE('Internet Sales' ,'Product' [Product.Key0] ,"Internet Sales Amount",'Internet Sales'[Internet Sales Amount]))
Here a SQL query on database can be like:
SELECT [ProductID],SUM([InternetTotalSales]) FROM [InternetSales] A LEFT JOIN Product P ON P.ProductID = A.ProductID GROUP BY [ProductID]
Easy, but in MDX it can be as:
SELECT NON EMPTY [Product].[Product].MEMBERS ON 1 ,[Measures].[Internet Sales Amount] ON 0 FROM [Adventure Works]
To show an example I used the Database of multidimensional cubeAdventureWorksDW2012Multidimensional-SE and before I run an MDX query to extract sales by product
SELECT Measures.[Internet Sales Amount] ON COLUMNS, NON EMPTY [Product].[Product].[Product] ON ROWS FROM [Adventure Works] CELL PROPERTIES VALUE
EVALUATE ADDCOLUMNS ( SUMMARIZE ( 'Internet Sales', Product[Product], Product[Product.Key0] ), "Sales", [Internet Sales Amount] ) ORDER BY Product[Product]