DAX measures are the center of each SSAS Tabular mannequin, Energy BI and Energy Pivot answer. You write numerous 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 complicated options you’ll have lots 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 present measures might be such a ache within the neck. On this publish I’m going to take a step additional and present you a easy solution to get the entire information mannequin dependencies then visualise the dependencies in Energy BI. You could find the obtain hyperlink on the finish of this publish.
A easy search in Google brings you a bunch of helpful articles speaking in regards to the topic. Among the bests, in my thoughts, are as under:
On this publish 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). Working the DMV we are able to see what measures are references by different measures, what columns are referenced within the calculated columns and rather 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 have to run the next DMV on high 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 have to discover the native port quantity then you definately’re good to go. The one half which may not look very simple at first, could be discovering the database in Energy BI Desktop mannequin.
A straightforward manner, after you discover the native port variety of an opened Energy BI Desktop file, is to search out the database identify from SQL Server Administration Studio (SSMS) when connecting to the Energy BI Desktop mannequin:
- Open SSMS
- Choose “Evaluation Providers” as “Server Kind”
- Kind in “localhost:PORT_NUMBER” as “Server Identify” then click on “Join”
- Broaden “Databases” to see the database identify
- Open Energy BI Desktop
- Choose “SQL Server Evaluation Providers database” then click on “Join”
- Enter the “Server” and “Database”
- Be sure to choose the “Import” mode
- Broaden “MDX or DAX Question” and replica/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 choosing 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 rely 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 should utilize a community customized visible. I exploit “Pressured-Directed Graph”, however, you should utilize 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 large your mannequin is and the variety of mannequin dependencies you ought to see one thing just like the screenshot under.
As you see in the visible can look very messy and actually unreadable. The reason being that we’re displaying all mannequin dependencies. To make it extra readable and helpful we are able to 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 appears higher now, however, it’s nonetheless not good. Let’s format the visible to make it a bit higher.
- Click on the “Pressure-Directed Graph” visible
- Click on format tab from the “Visualization” pane
- Broaden “Hyperlinks” then flip the “Arrow” choice on. This may present the move of the dependencies
- Broaden “Nodes” and alter “Max identify size” to 50. The default is 10 which truncates the article names and makes them unreadable
You may 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 may click on on a referenced object identify from the desk on the best hand facet to see all dependent measures within the Pressure-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 may obtain the Energy BI Template (PBIT) file from right here.