DAX measures are the center of each SSAS Tabular mannequin, Energy BI and Energy Pivot answer. You write a lot of DAX measures and also you doubtlessly reference a few of them in different measures. So the variety of DAX measures you write and reference them by way of different measures develop in a short time. Particularly in advanced options you will have tons of of DAX measures. Whereas your answer works completely, to make a minor change or including a brand new measure to the answer or fixing an issue in your current measures may be such a ache within the neck. On this submit I’m going to take a step additional and present you a easy strategy to get the entire information mannequin dependencies then visualise the dependencies in Energy BI. Yow will discover the obtain hyperlink on the finish of this submit.
A easy search in Google brings you a bunch of helpful articles speaking in regards to the topic. A few of the bests, in my thoughts, are as beneath:
On this submit I exploit a DMV that offers us the whole lot we would like. ( Chris Webb already mentioned the DMV right here: Doc Dependencies Between DAX Calculations). Operating the DMV we will see what measures are references by different measures, what columns are referenced within the calculated columns and way more.
It is a very helpful DMV that helps us getting a greater understanding of the mannequin we’re engaged on. We will additionally use this technique for documentation.
This technique is pretty easy, you simply must run the next DMV on prime of your SSAS Tabular mannequin or your Energy BI Desktop file and Import the leads to Energy BI.
SELECT * FROM $System.DISCOVER_CALC_DEPENDENCY
For Energy BI you’ll must discover the native port quantity then you definitely’re good to go. The one half which may not look very simple at first, can be discovering the database in Energy BI Desktop mannequin.
A straightforward means, after you discover the native port variety of an opened Energy BI Desktop file, is to search out the database title from SQL Server Administration Studio (SSMS) when connecting to the Energy BI Desktop mannequin:
- Open SSMS
- Choose “Evaluation Companies” as “Server Kind”
- Kind in “localhost:PORT_NUMBER” as “Server Identify” then click on “Join”
- Broaden “Databases” to see the database title
- Open Energy BI Desktop
- Choose “SQL Server Evaluation Companies database” then click on “Join”
- Enter the “Server” and “Database”
- Ensure you choose the “Import” mode
- Broaden “MDX or DAX Question” and duplicate/paste the next DMV then click on OK
SELECT * FROM $System.DISCOVER_CALC_DEPENDENCY
- After importing information to Energy BI click on “Edit Queries” to open “Energy Question Editor”
- In Question Editor rename columns with extra consumer pleasant names
- I additionally do choose to capitalize every phrase within the “Object Kind” and “Referenced Object Kind” columns. You are able to do this by deciding on each columns then proper click on and choose “Capitalize every Phrase” from “Rework” sub-menu
- “Shut & Apply”
- The final step is to create a quite simple measure to depend the variety of dependencies within the present filter context
Object Depend = COUNTROWS('Mannequin Object Dependencies')Now that we efficiently loaded information into Energy BI, it’s time to visualise the dependencies. As said earlier, you need to use a community customized visible. I exploit “Compelled-Directed Graph”, however, you need to use few different accessible customized visuals.
After importing the customized visible put it on the web page then put “Referenced Object” to “Supply”, “Object Identify” to “Goal” and “Object Depend” to “Weight”. Relying on how huge your mannequin is and the variety of mannequin dependencies you ought to see one thing just like the screenshot beneath.
As you see in the visible can look very messy and actually unreadable. The reason being that we’re exhibiting all mannequin dependencies. To make it extra readable and helpful we will put “Object Kind” and “Referenced Object Kind” columns on the web page as slicers. Now if you choose “Measure” in each slicers you’ll see measure dependencies.
It seems higher now, however, it’s nonetheless not good. Let’s format the visible to make it a bit higher.
- Click on the “Power-Directed Graph” visible
- Click on format tab from the “Visualization” pane
- Broaden “Hyperlinks” then flip the “Arrow” choice on. It will present the stream of the dependencies
- Broaden “Nodes” and alter “Max title size” to 50. The default is 10 which truncates the thing names and makes them unreadable
You possibly can add extra visuals to get extra insights out of your SSAS Tabular mannequin or Energy BI Desktop object dependencies.
Here’s a screenshot of the pattern I made for you.
You possibly can click on on a referenced object title from the desk on the suitable hand aspect to see all dependent measures within the Power-Directed Graph and likewise DAX expressions of the dependent measures within the different desk.
To see what measures referenced calculated columns, simply choose “Calc_columns” from the “Referenced Object Kind” slicer.
You possibly can obtain the Energy BI Template (PBIT) file from right here.
