DAX measures are the guts 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 through different measures develop in a short time. Particularly in complicated options you’ll have a whole bunch 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 will be such a ache within the neck. On this publish I’m going to take a step additional and present you a easy method to get the entire knowledge mannequin dependencies then visualise the dependencies in Energy BI. You’ll find the obtain hyperlink on the finish of this publish.
A easy search in Google brings you a bunch of helpful articles speaking concerning the topic. A number of the bests, in my thoughts, are as beneath:
On this publish I exploit a DMV that offers us all the things we wish. ( 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 far 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 methodology for documentation.
This methodology 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 ends in Energy BI.
SELECT * FROM $System.DISCOVER_CALC_DEPENDENCY
For Energy BI you’ll have to discover the native port quantity then you definitely’re good to go. The one half that may not look very easy at first, could be discovering the database in Energy BI Desktop mannequin.
A simple approach, after you discover the native port variety of an opened Energy BI Desktop file, is to seek 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 Sort”
- Sort in “localhost:PORT_NUMBER” as “Server Title” then click on “Join”
- Develop “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”
- Be sure you choose the “Import” mode
- Develop “MDX or DAX Question” and replica/paste the next DMV then click on OK
SELECT * FROM $System.DISCOVER_CALC_DEPENDENCY
- After importing knowledge 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 favor to capitalize every phrase within the “Object Sort” and “Referenced Object Sort” columns. You are able to do this by deciding on each columns then proper click on and choose “Capitalize every Phrase” from “Remodel” 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 Rely = COUNTROWS('Mannequin Object Dependencies')
Now that we efficiently loaded knowledge into Energy BI, it’s time to visualise the dependencies. As acknowledged 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 Title” to “Goal” and “Object Rely” 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 Sort” and “Referenced Object Sort” 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 “Drive-Directed Graph” visible
- Click on format tab from the “Visualization” pane
- Develop “Hyperlinks” then flip the “Arrow” choice on. This can present the circulation of the dependencies
- Develop “Nodes” and alter “Max title size” to 50. The default is 10 which truncates the article 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 Drive-Directed Graph and in addition 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 Sort” slicer.
You possibly can obtain the Energy BI Template (PBIT) file from right here.
