Sunday, June 26, 2022
HomeBusiness IntelligenceDAX Measure Dependencies in SSAS Tabular and Energy BI

DAX Measure Dependencies in SSAS Tabular and Energy BI


DAX measures are the guts of each SSAS Tabular mannequin, Energy BI and Energy Pivot answer. You write a lot of DAX measures and also you probably 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 advanced options you might 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 might be such a ache within the neck. On this publish I’m going to take a step additional and present you a easy technique to get the entire knowledge mannequin dependencies then visualise the dependencies in Energy BI. You will discover 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 under:

On this publish I exploit a DMV that offers us the whole lot we wish. ( Chris Webb already mentioned the DMV right here: Doc Dependencies Between DAX Calculations). Operating the DMV we are able to see what measures are references by different measures, what columns are referenced within the calculated columns and way more.

This can be a very helpful DMV that helps us getting a greater understanding of the mannequin we’re engaged on. We are able to 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 ends in Energy BI.

SELECT * FROM $System.DISCOVER_CALC_DEPENDENCY

For Energy BI you’ll must discover the native port quantity you then’re good to go. The one half which may not look very easy at first, could be discovering the database in Energy BI Desktop mannequin.

A simple 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 Companies” as “Server Sort”
  • Sort in “localhost:PORT_NUMBER” as “Server Identify” then click on “Join”

Connect to Power BI Desktop Model from SSMS

  • Develop “Databases” to see the database identify

Browse Power BI Desktop Model in SSMS

 

  • 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
  • Develop “MDX or DAX Question” and duplicate/paste the next DMV then click on OK
SELECT * FROM $System.DISCOVER_CALC_DEPENDENCY

Import SSAS Tabular Metadata to Power BI

  • 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

SSAS Tabular Model Object Dependency with DMV in Power BI

  • I additionally do choose to capitalize every phrase within the “Object Sort” and “Referenced Object Sort” columns. You are able to do this by choosing each columns then proper click on and choose “Capitalize every Phrase” from “Remodel” sub-menu

Capitalize Each Word in Power BI

  • “Shut & Apply”
  • The final step is to create a quite simple measure to rely 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 said earlier, you need to use a community customized visible. I exploit “Pressured-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 Rely” 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.

Using Force-Directed Graph Custom Visual in Power BI

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 are able to 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.

Using Force-Directed Graph Custom Visual in Power BI

It seems 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
  • Develop “Hyperlinks” then flip the “Arrow” choice on. This can present the circulation of the dependencies
  • Develop “Nodes” and alter “Max identify size” to 50. The default is 10 which truncates the item 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.DAX Measure Dependencies in SSAS Tabular and Power BIYou possibly can click on on a referenced object identify from the desk on the proper hand aspect to see all dependent measures within the Pressure-Directed Graph and in addition DAX expressions of the dependent measures within the different desk.DAX Measure Dependencies in SSAS Tabular and Power BITo see what measures referenced calculated columns, simply choose “Calc_columns” from the “Referenced Object Sort” slicer.DAX Calculated ColumnDependencies in SSAS Tabular and Power BI

You possibly can obtain the Energy BI Template (PBIT) file from right here.

RELATED ARTICLES

LEAVE A REPLY

Please enter your comment!
Please enter your name here

Most Popular

Recent Comments