Chris Webb's BI Blog

Analysis Services, MDX, PowerPivot, DAX and anything BI-related

DAX Measures, MDX Measures and Type

with one comment

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]

image

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

image

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.

Written by Chris Webb

February 8, 2012 at 9:39 pm

Posted in DAX, MDX

One Response

Subscribe to comments with RSS.

  1. That’s a real eye-opener: I hadn’t really considered the potential for Tabular models as aids to ETL, but it makes perfect sense.

    rjback

    February 9, 2012 at 9:29 am


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 2,868 other followers

%d bloggers like this: