Documenting dependencies between DAX calculations

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

There’s an interesting new DMV available for PowerPivot or the Tabular model in Denali called DISCOVER_CALC_DEPENDENCY that allows you to list all the dependencies between objects in your model. The full documentation is here:
http://msdn.microsoft.com/en-us/library/gg471590(v=sql.110).aspx

…but I thought it would be good to blog about because some of the practical uses of it are not explored in BOL.

For the purposes of illustration, I created a simple Tabular model with two tables, a relationship between the tables, some calculated columns and some measures. In SQL Management Studio I then connected to this database with an MDX query window and ran the following query:

select * from $system.discover_calc_dependency

Here’s what I got back:

OK, so there’s a lot going on here and unless you’ve got really good eyesight you won’t be able to make out much of this. I’d like to draw your attention to the second column though, where you can see the types of objects we can see dependencies for: Measures, Hierarchies, Calculated Columns, and Relationships. We can filter by this column, for example using a query like this:

select distinct [table], [object], [expression] from $system.discover_calc_dependency
where object_type=’MEASURE’

(None of that SystemRestrictSchema rubbish needed, thank goodness) This just returns the measures in the model, and is probably the most interesting thing we can get from this DMV. Here are the results of this query:

This then shows us a list of the three measures in our model, what table they’re on, and the DAX expression behind them. Pretty useful. Even better, though, if one measure depends on another measure or calculated column, you can find the related object and its expression too. In this case [Sum of Sales After Tax Times 2] is a measure that sums the results of a calculated column, as the following query shows:

select referenced_object_type, referenced_table, referenced_object, referenced_expression
from $system.discover_calc_dependency
where [object]=’Sum of Sales After Tax Times 2′

(I’m not sure where that dependency on RowNumber is coming from, though…)

So this is all very useful for you as a developer, for documentation and so on. But wouldn’t it be useful if your users could see all this too? Well, they can, using a technique very similar to the one I blogged about here. Back in BIDS, after the initial deployment of the database, I added a new Analysis Services connection  pointing to the Analysis Services database I’d just deployed – so the SSAS database was using itself as a datasource. I was then able to use the first query above,

select * from $system.discover_calc_dependency

To populate a table inside my existing model:

After the model had been deployed again, this meant I could browse the results of the DMV using an Excel Pivot Table:

I’m sure in the future many users, especially if they’re PowerPivot users feeling a little frustrated at the lack of control they have over the Tabular model you’ve built, will be very interested in seeing these formulas so they can understand how they work and reuse them in their own models. And hopefully in the long run the information returned by this DMV will make not only importing data from Tabular models back into PowerPivot much easier, but also make importing parts of existing Tabular models into new PowerPivot models much easier.

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 .