Querying Attributes and Measures in DAX Multidimensional
Reposted from Jason Thomas blog with the author's permission.
The past week has been pretty exciting for Analysis Services geeks - Microsoft SQL Server 2012 With Power View For Multidimensional Models Customer Technology Preview (CTP) has been made available for download (and if you haven’t tried it out yet, download it from this link). Now the obvious part is that Power View can consume data from SSAS Multidimensional objects now and that was a long time request from the community. This will enable the business to use their existing investments in SSAS Multidimensional to utilize the latest end user tools like Power View. The best part is that all this is achieved through native support for DAX in Multidimensional and there is no translation of DAX into MDX. Now this opens up a whole new door of possibilities. For eg, we should be able to design SSRS reports using DAX queries on top of SSAS Multidimensional objects (and who knows, we might be able to replace some of the slower MDX calculations with faster DAX equivalents). Welcome to the world of DAX Multidimensional (or DAXMD)!
Now the purpose of this post is to introduce you on how to query Multidimensional objects in DAX, and you should be able to get a good overview on how the multidimensional objects are mapped in tabular from here. But there is something extra in this blog that the official documentation has not mentioned when it comes to querying attributes which don't have the same name and key values (for SSAS beginners, an attribute in SSAS MD can have different values for it’s key and name, while in SSAS Tabular, you have to specify the key and name as different columns). For the purpose of this post, I am using the Adventure Works DW 2008R2 database and querying using SQL Server Data Tools (SSDT).
Before we start, lets have a look at the summary
Armed with this knowledge, let us start querying in DAXMD
1) Querying Attributes with same Key and Name
Let us take the example of Calendar Quarter of Year in the Date dimension
We can write a simple DAX query as shown below
evaluate
values ('Date'[Calendar Quarter of Year])
Note that Date is a role playing dimension in the cube and you will need to use the name of the cube dimension as the table name (and not the database dimension name).
2) Querying Attributes with different Key and Name
Let us take the example of Calendar Year.
Now let us see what the query below will result in.
evaluate
values ('Date'[Calendar Year])
We will get an error - Column [Calendar Year] is part of composite key, but not all columns of the composite key are included in the expression or its dependent expression.
I spent hours trying to figure out what this error means and finally managed to figured out that it happens only when the key and names are different. It made sense also as DAXMD will only recognize the single attribute as two different columns and not as one. But I had no idea of what syntax too use. Enter Jeffrey Wang (blog) from the Anaysis Services Dev team -
“ This is a design decision for good performance. A single MDX attribute can produce several DAX columns, some visible, some not, but you can query them in SSMS regardless whether they are visible or not. In general, a MDX attribute, named A, can produce several columns like A.Key0, A.Key1, A. The design requires you to group by at least A.Key0 and A.Key1 if you want to group by any of the three columns. So to query attribute A in DAX, you can do either Summarize('Table', [A.Key0], [A.Key1]) or Summarize('Table', [A.Key0], [A.Key1], [A]). In simple cases where an attribute, named B, only generates a single DAX column, which will also be named B, you can simply query Values([B]) like regular tabular columns.
This constraint helps us achieve good performance since we can send all queries to the MDX engine which only groups by the entire attribute regardless how many DSV columns were used to create this attribute. Otherwise we would have to eliminate duplicate values on the DAX side after MDX query returns the resultset. Hope this makes sense. “
What this means is that your query will have to follow the below format
evaluate
summarize('Date', 'Date'[Calendar Year.Key0], 'Date'[Calendar Year])
Adding the .Key format to the column name is a new addition to the DAX language as far as I know. I am reasonably sure that this would be how Power View also issues the DAX when such attributes are used in the model, though I haven’t tested it so far. If anyone of you have seen the profiler traces of Power View, feel free to comment below and add.
3) Querying Attributes with Multiple Keys
Let us take the example of Calendar Quarter.
As mentioned previously, we will have to group by all the keys at least and then the name, if needed.
evaluate
summarize('Date',
'Date'[Calendar Quarter.Key0],
'Date'[Calendar Quarter.Key1],
'Date'[Calendar Quarter])
4) Querying Measures in a Measure Group
Let us take the example of Internet Sales Amount in the Internet Sales measure group.
We can just write a simple DAX query to display the Internet Sales measure group by Calendar Quarter of Year as shown below
evaluate
summarize('Date',
'Date'[Calendar Quarter of Year],
"test", 'Internet Sales'[Internet Sales Amount])
We can also refer to the measure without the table name in the query. Also note that we don’t need to provide any aggregation to the measure, else we might get the following error - Column 'Internet Sales Amount' in table 'Internet Sales' cannot be found or may not be used in this expression. This makes sense also as the engine should use the aggregation that is defined in the multidimensional cube.
5) Querying Measures without a Measure Group
This section refers to those calculated measures that are made in the multidimensional cube which are not associated with a measure group. Since all the calculated measures in Adventure Works cube are associated with some or the other measure group, I made a simple calculated measure called test in the calculated member script of the cube as shown below
Create Member CurrentCube.[Measures].[Test] As 2;
Now I can refer to this Test measure in a DAX query as shown below
evaluate
summarize('Date',
'Date'[Calendar Quarter of Year],
"test", 'Measures'[Test])
Hopefully, this should get you started thinking in DAXMD now! 
 | Jason has been working with Microsoft BI tools since he joined the IT industry in 2006. Since then he has worked across SSAS, SSRS and SSIS for a large number of clients like Unilever, Imperial Tobacco, IATA, Schneider Electric, etc. He is currently working at MindTree Ltd, Bangalore (www.mindtree.com) and his personal blog site can be found at http://www.sqljason.com His blogs can also be found at the popular technical site BeyondRelational.com at http://beyondrelational.com/blogs/jason |