DAX Measures, MDX Measures and Type

Reposted from Chris Webb's blog with the author's permission.

One of the strengths of MDX is the fact that calculated measures in MDX are not strongly typed: they return variants. This means that it’s possible to create calculations that return values of several different types, such as the example below that returns text in some cases and integers in others:

WITH
MEMBER MEASURES.DEMO AS
IIF(
[Measures].[Internet Sales Amount]>7000000
, "TOO HIGH!"
, [Measures].[Internet Sales Amount])

SELECT
{[Measures].[Internet Sales Amount], MEASURES.DEMO}
ON 0,
[Date].[Calendar Year].[Calendar Year].MEMBERS ON 1
FROM [Adventure Works]

This gives you a lot of flexibility when writing calculations but it also can be a big problem when you want to extract data from a cube into another system, as anyone who has tried to do this will know.

In DAX and the Tabular model, on the other hand measures, like columns (calculated or otherwise) are strongly typed. Although you can’t see the type of a measure in SQL Server Data Tools when you create it, the automatically inferred type can be found in the MDSCHEMA_MEASURES schema rowset and which be queried as follows:

select *
from $system.mdschema_measures

If you try to use an expression for a measure like this that, as in my first example, could return text or an integer:

testtype:=IF(SUM(‘Internet Sales’[Sales Amount Value])>1000, 1, "test")

Then you get the error:

Measure ‘Internet Sales’[testtype] : The second and third arguments of function IF have different data types. This is not supported.

On balance I think I prefer having measures strongly typed, and for one thing it opens up the possibility of using the Tabular model for certain forms of ETL. I’ve already seen one customer of mine replace a CTE in TSQL by loading their data into Tabular and using the PATH functions instead, getting some significant performance benefits as a result, and I’m sure there will be plenty of other scenarios where ETL requires complex calculations to take place that the incredible performance of DAX will make loading all the data into a Tabular model (even if the final destination of the data is a relational data warehouse) a serious option.

Chris has been working with Microsoft BI tools since he started using beta 3 of OLAP Services back in the late 90s. Since then he has worked with Analysis Services in a number of roles (including three years spent with Microsoft Consulting Services) and he is now an independent consultant specialising in complex MDX, Analysis Services cube design and Analysis Services query performance problems. His company website can be found at http://www.crossjoin.co.uk and his blog can be found at http://cwebbbi.wordpress.com .