how to build meta reports on top of power bi semantic models (former datasets) with dynamic management views (DMVs).

introduction.

Did you know that you can query a Power BI semantic model’s meta data by using Dynamic Management Views (DMVs)? Check out Microsoft’s documentation for more information. I thought, why not pushing that meta data from a Power BI semantic model straight into a Power BI report. There are plenty of insights that these DMVs provide, like consumption, queries, sessions or information about the semantic model itself. One use-case I found out from the Power BI community was to fetch the refresh times per table and partitions straight from one of the DMVs. But how can we actually connect Power BI to a semantic model in order to query its own meta data? Read on to find out how to achieve this.
In December 2023, Microsoft released the new Info DAX functions which makes it a lot easier to get access to those DMVs. Unfortunately, Initially you could not use that native INFO DAX in calculated columns or tables. However, you can also fire the INFO DAX queries instead of the direct DMVs in Power Query. There is an example on how to do this further down, too. In fact, we used this approach when creating a Direct Lake monitoring report.
Yet another way is described in the blog post how to query and store meta data of your semantic model with sempy and dax info functions. Feel free to check it out!

.

prerequisites.

1. A Power BI premium capacity or a PPU license. XMLA endpoints can only be used on premium workspaces
2. A semantic model published to a premium workspace

.

plan of action.

1. What is our starting point?
2. Connect Power BI to the semantic model
3. Example report

.

1. What is our starting point?

Let’s quickly recap the prerequisites.

. A premium workspace in Power BI with a semantic model:

.

Our model looks like this:

We are using the large AdventureWorks sample in an Azure SQL Database. If you would like to find out how to add the real AdventureWorks sample (and not the lightweight version) to an Azure SQL database, make sure to check out this blog post.

.

2. Connect Power BI to the semantic model

Go to powerbi.com and open the settings of your premium workspace. Then, copy the workspace connection:

.

In Power BI Desktop, click on Get data and find Analysis Services under Common data sources

.

Paste the Workspace Connection URL into the Server and the semantic model name into Database. Also, write the query SELECT * FROM $System.DISCOVER_CALC_DEPENDENCY into MDX or DAX query (optional).

.

Note, here you could also run the new native INFO DAX queries instead of the DMV query. In the example below we run the INFO.TABLE() DAX function:

Load in the data. Note, below the data from the DISCOVER_CALC_DEPENDENCY DMV is displayed:

.

Next, navigate to the Power Query window.

.

The M code shall look like the following.

.

---

= AnalysisServices.Database("powerbi://api.powerbi.com/v1.0/myorg/tomsworkspace", "adventureworks-dataset", [Query="SELECT * FROM $System.DISCOVER_CALC_DEPENDENCY", Implementation="2.0"])

---

.

Before adding new queries, make sure to rename the query:

.

Now, just copy (select the query + ctrl + c) and paste the query (ctrl + v) into the Queries area on the left. Then, substitute the table/view with the DMV you’d like.

.

There is huge number of DMVs. A full list, can be found here. Also, you can get a list of all DMVs with the following query:

.

---

= AnalysisServices.Database("powerbi://api.powerbi.com/v1.0/myorg/tomsworkspace", "adventureworks-dataset", [Query="SELECT * FROM $System.DBSchema_Tables
WHERE TABLE_TYPE = 'SCHEMA'
ORDER BY TABLE_NAME ASC", Implementation="2.0"])

---

.

3. Example report

Here a couple of example report pages for some inspiration. The last page showing the lineage for hierarchies, measures and relationships uses a function in Power Query which disentangles the nested dependencies. The function does this dynamically with a variable number of levels. There will be another blog post on how to do this, in the future. Also, the book The Definitive Guide to DAX by Marco Russo and Alberto Ferrari explains plenty of these DMVs that you can use for performance tuning and spotting bottlenecks. I highly recommend that book for plenty of other reasons. For me, it’s a must read for mastering DAX and Power BI.

For more Power BI report example, you can visit my gallery.

.

end.

Fun fact: You can query these views even from other clients after you have established a connection to the Power BI semantic model. Querying also means, you can store it somewhere else. Consequently you could build up a history tracking the metrics back in time. In the next blog post, we will exactly do this.