Visualizing SSAS Calculation Dependencies using PowerBI

 

UPDATE: This does not work for Tabular Models in Compatibility Level 120 or above as they do not expose the calculation dependencies anymore!

 

One of my best practices when designing bigger SQL Server Analysis Services (SSAS) Tabular models is to nest calculations whenever possible. The reasons for this should be quite obvious:

  • no duplication of logics
  • easier to develop and maintain
  • (caching)

However, this also comes with a slight drawback: after having created multiple layers of nested calculations it can be quite hart to tell on which measures a top-level calculations actually depends on. Fortunately the SSAS engine exposes this calculation dependencies in one of its DMVs – DISCOVER_CALC_DEPENDENCY.
This DMV basically contains information about all calculations in the model:

  • Calculated Measures
  • Calculated Columns
  • Relationships
  • Dependencies to Tables/Columns

Chris Webb already blogged about this DMV some time ago and showed some basic (tabular) visualization within an Excel Pivot table (here). My post focuses on PowerBI and how can make the content of this DMV much more appealing and visualize it in a way that is very easy to understand.
As the DMV is built up like a parent-child hierarchy, I had to use a recursive M-function to resolve this self-referencing table which actually was the hardest part to do. Each row contains a link to a dependent object, which can have other dependencies again. In order to visualize this properly and let the user select a Calculation of his choice to see a calculation tree, I needed to expand each row with all of its dependencies, keeping their link to the root-node:

Here is a little example:

Object Referenced_Object
A B
B C

The table above is resolved to this table:

Root Object Referenced_Object
A A B
A B C
B B C

The Root-column is then used to filter and get all dependent calculations.
The PowerBI file also contains some other M-functions but those are mainly for ease-of-use and to keep the queries simple.

Once all the data was loaded into the model, I could use one of PowerBI’s custom visuals from the PowerBI Gallery – the Sankey Chart with Labels
SSAS_Visualizing_Tabular_Calc_Dependencies

Here is also an interactive version using the Publishing Feature of Power BI:

 

You can use the Slicers to filter on the Table, the Calculation Type and the Calculation itself and the visual shows all the dependencies down to the physical objects being Tables and Columns. This makes it a lot easier to understand your model and the dependencies that you built up over time.
I attached the sample-PowerBI-file below. You simply need to change the connectionstring to your SSAS Tabular Server and refresh the data connections.

The PowerBI-file (*.pbix) can be downloaded here: SSAS_CalcDependencies.pbix

19 Replies to “Visualizing SSAS Calculation Dependencies using PowerBI”

  1. HI
    WOW very very good blog I loved it

    Correct me if I am wrong , now with [Compatibility Level] = SQL Server2016 (1200)) we can not use

    SELECT * FROM $SYSTEM.DISCOVER_CALC_DEPENDENCY

    what can be replaced with the select statement so that your POWER BI file will work?

    Thanks for the grate blog

    Sincerely
    Nik

    • Hi Nik,
      you are right, this does not work anymore with Compatibility Level 1200 as the DMV was disabled – unfortunately.
      I am not aware of any DMV that offers similar functionality/content, sorry

      I will keep you updated here once this changes

      PS: I will also add a short note at the beginning of the Blog about this issue

      thanks,
      -gerhard

  2. $SYSTEM.TMSCHEMA_MEASURES will return measure information but, as far as I can tell, there is no way to build the dependencies

  3. This is amazing, Gerhard. Is there any way it can be tweaked so that it reports on the relationships of whatever PowerBI file it is used in?

    Basically I’d like to change this:

    let
    Source = AnalysisServices.Database(“.\TAB2014”, “AdventureWorks Tabular Model SQL 2012″, [Query=”SELECT *#(lf)FROM $SYSTEM.DISCOVER_CALC_DEPENDENCY”]),

    so that the source is always the PowerBI file that the code lives in. That way, I can just pop this code into my existing PowerBI solutions, and use it to help document those solutions.

    I’m not sure if that’s possible, and I’m a newbie when it comes to PowerBI and M

  4. why this method doesn’t work with Power BI. What I meant is if you replace the SSAS tabular connection string with a Power BI workbook port and catalog name, the query SELECT *
    FROM $SYSTEM.DISCOVER_CALC_DEPENDENCY always returns errors. However if you run query against other DMVs it worked fine. Any idea why this is the case

    • Hi Bob,
      it is a known “bug” that the DMV is not populated for models in Compatibility Mode 1200, it might be the same also for 1400, I have not tested this yet.
      please also read the previous comments from other users which are mainly about the same topic

      -gerhard

  5. Hi Gerard,

    Just found this as I have a complex model with a lot of measure branching built in SSAS T that needs documenting, so happy to find this. From all the solutions posted to the web, yours is the only one that appears to deal with dependency levels. I am trying the reuse your model but am getting stuck (‘fraid my PQ skills need some work…).

    The issue appears to come from the ‘Resolve Dependency’ step: An error occurred in the ‘LookupValue’ query. Expression.Error: There weren’t enough elements in the enumeration to complete the operation.

    What can I do to resolve this?

    Many thanks!
    Deston

    • Hi Deston,

      well, compared to most other solutions out there I actually resolve the whole dependency tree instead of just showing the plain values of the DMV and relying on a Visual to display it (more or less) properly. To do this I use a recursive function. This works based on a unique value that I create for 3 columns of the DMV and which I then use to do a self-join on the same table again as the parent.
      The DMVs change from time to time and new fields get added. There should be a mapping table somewhere that defines all different types of calculations and also a pattern of how to create that unique column value. I assume that there is some mapping missing. can you check on this?
      (sorry I do not have too much time at the moment to look into this in more detail)
      you may also send me the model so I can investigate – this would be much faster than me trying to reproduce the issue

      kind regards,
      -gerhard

  6. Hi Gerhard, I realise this is a pretty old post but just to say thanks it’s been really useful.

    I’ve refreshed your queries and (I hope) handled all of the new object-types that are surfaced in the DMVs (took a bit of work for me to understand your M wizardry!!) Just to let you know that it works nicely on the latest Power BI (enhanced metadata) format.

Leave a Reply to Bob Cancel reply

Your email address will not be published. Required fields are marked *

*