In this tutorial, you’ll learn about Dynamic Management Views (DMV) in DAX Studio and how to use them to directly load datasets in Power BI.
A Dynamic Management View or DMV returns server information which you can use to monitor the health of a Power BI server and diagnose any problems that may occur.
This tutorial will be using the DAX Studio Masterclass sample report file.
Extracting Data From Dynamic Management Views
First, you need to extract the entire code of an existing file to a new Power BI data model. To do so, open the existing Power BI file in DAX Studio to identify and copy the port number.
Once done, go back to the new Power BI file. Under the Home tab, click the Get data option and select Analysis Services.
In the database wizard, enter the port number in the Server textbox.
For the Database entry, you can get it by executing the DMV below. The results give a Catalog Name which shows the ID of the data model.
To load the data, you need to change the option to Import. In the DAX query box, paste another DMV for the measures.
Once done, click OK and then Connect. You can see that you’re able to load the result of the DMV in Power BI.
Before loading this data, you need to filter it out so that you’ll only load the columns you need. Click the Transform Data option.
In the Power Query Editor, you can choose which columns to retain or remove. To do this, click the Choose Columns option under the Home tab. Then, check the column names you need.
Afterwards, you can see that by simply using a DMV, you’re able to extract the content of a data model inside another Power BI file.
Creating Measures Using Dynamic Management Views
If you want to get information for another table in your data model, you only need to change the DMV and run it against the database.
If you’re asked permission to run the native database, click Edit Permission and then Run. Once the table loads, you’ll see that you now have the data of another table in your data model.
However, if you click the next step in the Applied Steps pane, you’ll see that it doesn’t work. This is because it’s referencing the columns of a different query. Click X to remove it.
To fix this, you need to find the DMV of the relationships table in DAX Studio.
Copy this code and paste it in your measure in Power BI. You’ll see that you’re able to view the details regarding the relationships that exists between the tables in your data model.
Using Parameters
Instead of manually changing the DMV in each query, you can apply a parameter.
Using a parameter allows you to store information and then use the shared expression to run multiple queries. You can modify the content of a parameter without having the need to go through each query and manually change the DMV.
To create a parameter in Power BI, go to the Home tab and click Manage Parameters.
Let’s create a parameter for the Port Number and the Database. Assign each parameter with a name and select its value Type. In this case, they’re both Texts. For the Current Value, paste the port number in the PortNumber parameter, and the database ID in the DatabaseName parameter.
Once done, you can now use these parameters to reference the port number and database name inside the queries.
Loading The Final Data
After you’ve made the necessary changes to the dataset in your table, you can now load it inside the data model in Power BI by clicking the Close & Apply option.
You’ve now successfully loaded the results of each DMV directly to Power BI.
It’s recommended to periodically refresh the data model to continuously gather useful information from the Power BI file through the extracted data in the DMVs.
***** Related Links *****
Connect DAX Studio To Power BI And SSAS
DAX Query Structure In Power BI – Two Engines & DAX Studio
What Is DAX Studio In Power BI Desktop
Conclusion
To summarize, Dynamic Management Views can be used to load datasets from DAX Studio to Power BI. They can be used to create queries and measures.
Dynamic Management Views provide a common mechanism to extract data from any SQL database. Other than using it to connect datasets between programs, it’s more often used for troubleshooting and performance monitoring.
All the best,
Enterprise DNA Experts
