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
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
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
$SYSTEM.TMSCHEMA_MEASURES will return measure information but, as far as I can tell, there is no way to build the dependencies
yes, I just got it confirmed by the Product Team – at the time being it is not possible to get calculation dependencies for models running in Compatibility Level 1200
Hi
Thanks for the reply, I am sure you know how to go around it and find other ways, maybe this might help a little bit
http://sqldataside.blogspot.ca/2016/10/document-track-changes-tabular-model.html
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
Hi Jeffrey,
in theory, this would be possible. Each PowerBI workbook that is opened host a hidden Analysis Services Instance on your lokal machine which can be accessed via the port. The port can be found in some of your user settings which again can be queried by PowerBI/PowerQuery – an example can be found here: http://www.thebiccountant.com/2017/06/07/analyse-memory-consumption-powerbi/
The next problem you will face is that $SYSTEM.DISCOVER_CALC_DEPENDENCY is not available/populated in Tabular Models in Compatibility Level 1200 so I am afraid my approach will not work for the current PowerBI workbook.
However, I have not tested it on my own yet and would be very curious too! Kindly let me know your findings so I can update my blog post!
regards,
-gerhard
ok, so according to the SSAS 2017 RC1 the CALC_DEPENDENCY DMV will be supported again: https://blogs.msdn.microsoft.com/analysisservices/2017/07/17/whats-new-in-sql-server-2017-rc1-for-analysis-services/ so it might also make it into PowerBI (sooner or later)
Cool, fingers crossed. Thanks for your reply.
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
Thanks for the reply. Hope SSAS 2017 release will make this work in Power BI soon
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
Thank you Gerhard! Where can I send the file to?
http://wordpress.gbrueckl.at/about/
Hi Gerhard,
I’m looking for a query that will return not the depencies but which sources has de SSAS model (SQL, EXCEL, CSV, etc) do you know how to? I read about adomd, but is it possible with plain Transact-SQL, Profiler or something else?
https://docs.microsoft.com/en-us/dotnet/api/microsoft.analysisservices.adomdclient.adomdconnection.getschemadataset?redirectedfrom=MSDN&view=sqlserver-2016#overloads
I have not looked into that yet but there are some references for the DMVs that exists in SSAS:
https://docs.microsoft.com/en-us/sql/analysis-services/instances/use-dynamic-management-views-dmvs-to-monitor-analysis-services?view=sql-server-2017
if you really want to dig into it, you can check out the full SSAS documentatin https://msdn.microsoft.com/en-us/library/ee320606(v=sql.105).aspx
you may also use XMLA Discover like this:
DISCOVER_DATASOURCES
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.