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.
I got an error when connecting:
DataSource.Error: This server does not support Windows credentials. Please try Microsoft account authentication.
I’m logged into PBI Desktop with my network login, which typically automatically translates to MS Authentication (?).
Hi Dan,
Thanks for your message. Were you able to sort it out? If not, are you able to connect to the dataset from i.e. SSMS?
Thanks,
Tom
Thanks for this post Tom. Could you please share the pbix file ? It is a great report.
Hi Mathieu,
Just sent you the file. Hope this helps :)
/Tom
Thanks, would it be possible for you to share the pbix file so we can play around with it ?
Hi Khan,
Thanks for the message! Email with pbix file is out :)
Hope it helps
/Tom
Hi Tom,
Thank you for the great work ,I am myself trying to implement such analysis and found your tips very valuables. May I ask if you can share your pbix file with me as well?
Thanks in advance.
Nicolas
Hi Nicolas,
Mail is out! Happy playing :)
/Tom
Wow! This is awsome and gave me a lot of ideias to implement here. Could you share your pbix with me, pleaseeeeeeeeeee? :) I’m dying here :D:D
Thank you so much.
I bought the book you recommended. It was already on my wish list, but I needed a little incentive.
Hii Mônica,
Mail is out :) Hope this helps!
/Tom
Hello, can you share with me as well your pbix! Thanks!
Hi DellTypes :) Mail’s out!
Great article and very good references.
Could you share me the pbix file to run it against my dataset?
Thanks a lot!!!
Hi Magda! Just sent you the mail with the file. Hope it helps :)
/Tom
Thanks for the awesome informative blog Tom, can you please share the .pbix, I’m keen to have a look. Cheers!
Hi Kumar! Mail’s out :)
/Tom
Nice Job ! congratualtions and thank you ! really good !
can u share with me that report sample ? =)
Thanks Jean :) Just sent you the mail!
/Tom
Great Share Tom. At the risk of sounding like a broken record, could you please share the pbix.
Haha Richard, no broken records here! :) Just dropped you the email!
/Tom
Thanks for the awesome blog Tom, can you please share the .pbix.
Hey! I just sent out the mail :)
/Tom
Hi Tom,
Very useful information. Can you please share the report sample if possible?
Thank you
Hi Krishna,
Email is sent :)
/Tom
Hi Tom,
This looks incredibly useful, would you mind sharing the pbix please?
Thanks
Dave
Just sent you the file :)
/Tom
Hi Tom ,
it’s just beautiful and very useful . Can you share the .pbix please ?
Thanks very much
Mail’s out :)
/Tom
Thanks Tom, I received the .pbix files. But I don’t see the data viz part. Is this normal?
Hi! Yes, I have only sent out the data of the DMVs. The report itself, I have never shared, mostly because this shall only be some kind of inspiration. However, I am currently working on a proper report for Power BI monitoring in Fabric. That one, I will most likely share. Stay tuned :)
Edit: The article has just been published here: http://www.tackytech.blog/how-to-monitor-the-fallback-behavior-of-direct-lake-semantic-models/
Hi Tom ,
Very useful! Can you share the .pbix please ?
Thanks
Mail is out :)
Greetings,
Thanks to you I am now able to extract the Queries and everyting associated with the dataset as a PBIX data including the Queries and Measures used. Makes documentation so easy too. Would it be possible to share the PBIX file pls.
Thanks for the kind words :) I just sent you the pbix files!
Hi Tom, This is so inciteful!! Would you mind emailing me a copy to palease?
Hiya! I just sent you the files :)
Could you please Share PBIX file for this
Thanks for a great article.
Could I also please receive the pbix file?
thanks
Hi Andreas! I sent it out yesterday :)
Thanks for the great article. The last page totally replicates the scenario i looking for to implement in my project.
It would be a great help if you can share the PBIX.
Hi ,
You did a great work. I am also looking for to visualize the hierarchies for SQL metadata and Power BI.
So could you please share the PBIX ?
Thanks
Mail’s out :)
Hey, could also share the PBIX to me? Great article
Hi Yannick! Just sent you the files :)
Hi there
Great article! Thank you.
Would you mind to send me the pbix as well?
Thanks in advance.
Just sent the e-mail. Hope it helps! :)
Hey Tom! This is a great article! I do not think I am pulling the same tables that are needed to make that first example image. Would you mind sharing your pbix with me? I would love to see what I can do with it. Thank you for the article, really interesting!
Hi Jacob, Mail’s out :)
Hi! Great article, would you mind sending the pbix to me as well? Thank you in advance.
Hello Jax, I just sent you the file. Hope it helps!
Hello Tom, Great article!
Would it be possible to send me the pbix please? Also the one from the Direct Lake fallback monitoring?
Thank you very much in advance.
Hi Rami, Files should be on your end now. Happy playing :)
Thank you for the useful information on DMVs. Please forward a copy to the .pbix.
Copy is on its way :)
This is really insightful! Could you share the pbix file with me please? I would love to play around with it!
Hi Mallika :) File should be on its way…
Thanks for the article! Just what I’m looking for. Can you send me the .pbix please?
Mail’s out! Sorry for the delay…
Hi thanks for sharing this. Very insightfu with great potential. Could you please share a copy of the pbix file? Would love to have a look and see how this could be adapted in our environment. Thanks
Hi Shimmy, the files should arrive shortly!
This is fantastic, would love to play around with the PBIX file please :)
Hi James,
Mail’s out! Happy playing :)
Thank you for this article. This is very interesting.
Could I have access to the pbix file, please?
Hi Marina! Mail has just been sent :)
That was a great article and an insight
Thank you :)
This is fantastic blog , would love to play around with the PBIX file please share the file also i have a scenario where i have referential table i need time for all my fact tables
Mails out :) I am not sure about your question though. Fancy to clarify it a bit more? :)