Chris Webb's BI Blog

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

Documenting dependencies between DAX calculations

with 10 comments

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:

image

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:

image

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′

image

(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:

image

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

image

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.

Written by Chris Webb

September 17, 2011 at 9:41 am

Posted in BISM, DAX

10 Responses

Subscribe to comments with RSS.

  1. What’s the benefit of using DAX over SQL when dealing with multiple tables?

    Paul Morgan

    September 17, 2011 at 10:01 am

    • I suppose the main reason is that DAX is a lot more ‘multidimensional’ than SQL (although not as multidimensional as MDX), and therefore it’s easier to build a lot of BI-style calculations such as moving averages, time series calculations, contributions and so on in DAX. A second argument is that it’s very Excel-like, which means that Excel power users (the kind of people that PowerPivot is aimed at) will be able to understand it better than SQL; I’ll admit that this isn’t really relevant to people like me though.

      Chris Webb

      September 17, 2011 at 4:05 pm

  2. Very nice Chris. Perfect for documentation purposes and for providing the information back to the consumer of the models.

    denglishbi

    September 17, 2011 at 12:43 pm

  3. This is another feature to add in BIDS Helper!

    Marco Russo

    September 20, 2011 at 12:07 am

  4. [...] In my last post I demonstrated a simple way to interrogate an Excel based PowerPivot model using either MDX,DMV or the new DAX Query Table commands. This is a great way to dig deep into a PowerPivot model, for example, this new Denali DMV discover_calc_dependency can be use to  describe the dependencies between a model’s DAX measures. [...]

  5. Excellent Post. I had recently “found” the xml where this data is stored, but I had not yet come up with a great way to get at it. Thanks Chris

    Barbara Raney

    September 22, 2011 at 9:28 pm

  6. Hi Chris,
    Thanks for the blog articles.
    Have you seen any similar tools for MDX multidimensional? I need to map out some complex dependencies between calculations that can get deeply nested.

    Thanks, JasonH

    JasonH

    September 18, 2012 at 6:07 pm


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 3,219 other followers

%d bloggers like this: