February 1, 2023|tutorial|

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.

68 Comments

  1. Dan Pitz April 27, 2023 at 2:47 pm - Reply

    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 (?).

    • tackytechtom April 29, 2023 at 5:35 am - Reply

      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

    • Mathieu November 16, 2023 at 4:58 am - Reply

      Thanks for this post Tom. Could you please share the pbix file ? It is a great report.

      • tackytechtom November 16, 2023 at 4:49 pm - Reply

        Hi Mathieu,
        Just sent you the file. Hope this helps :)
        /Tom

  2. Khan July 30, 2023 at 4:24 pm - Reply

    Thanks, would it be possible for you to share the pbix file so we can play around with it ?

    • tackytechtom August 14, 2023 at 2:39 am - Reply

      Hi Khan,

      Thanks for the message! Email with pbix file is out :)

      Hope it helps

      /Tom

  3. Nicolas October 23, 2023 at 7:19 am - Reply

    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

    • tackytechtom October 24, 2023 at 10:01 am - Reply

      Hi Nicolas,

      Mail is out! Happy playing :)

      /Tom

  4. Monica October 27, 2023 at 12:39 pm - Reply

    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.

    • tackytechtom October 29, 2023 at 8:40 am - Reply

      Hii Mônica,
      Mail is out :) Hope this helps!
      /Tom

  5. DellTypes November 29, 2023 at 5:47 pm - Reply

    Hello, can you share with me as well your pbix! Thanks!

    • tackytechtom November 30, 2023 at 6:22 pm - Reply

      Hi DellTypes :) Mail’s out!

  6. Magda December 14, 2023 at 4:07 pm - Reply

    Great article and very good references.
    Could you share me the pbix file to run it against my dataset?
    Thanks a lot!!!

    • tackytechtom December 14, 2023 at 5:39 pm - Reply

      Hi Magda! Just sent you the mail with the file. Hope it helps :)
      /Tom

  7. Kumar January 9, 2024 at 2:01 am - Reply

    Thanks for the awesome informative blog Tom, can you please share the .pbix, I’m keen to have a look. Cheers!

    • tackytechtom January 9, 2024 at 1:33 pm - Reply

      Hi Kumar! Mail’s out :)
      /Tom

  8. jean souza January 9, 2024 at 1:44 pm - Reply

    Nice Job ! congratualtions and thank you ! really good !

    can u share with me that report sample ? =)

    • tackytechtom January 9, 2024 at 2:10 pm - Reply

      Thanks Jean :) Just sent you the mail!
      /Tom

  9. Richard Robinson January 9, 2024 at 11:21 pm - Reply

    Great Share Tom. At the risk of sounding like a broken record, could you please share the pbix.

    • tackytechtom January 10, 2024 at 5:33 pm - Reply

      Haha Richard, no broken records here! :) Just dropped you the email!
      /Tom

  10. veer January 14, 2024 at 4:42 pm - Reply

    Thanks for the awesome blog Tom, can you please share the .pbix.

    • tackytechtom January 15, 2024 at 6:59 pm - Reply

      Hey! I just sent out the mail :)
      /Tom

  11. Krishna January 22, 2024 at 1:31 am - Reply

    Hi Tom,

    Very useful information. Can you please share the report sample if possible?

    Thank you

    • tackytechtom January 22, 2024 at 11:22 am - Reply

      Hi Krishna,
      Email is sent :)
      /Tom

  12. Dave February 13, 2024 at 10:17 pm - Reply

    Hi Tom,

    This looks incredibly useful, would you mind sharing the pbix please?

    Thanks
    Dave

    • tackytechtom February 14, 2024 at 11:53 am - Reply

      Just sent you the file :)
      /Tom

  13. HTAB February 16, 2024 at 7:57 pm - Reply

    Hi Tom ,

    it’s just beautiful and very useful . Can you share the .pbix please ?

    Thanks very much

    • tackytechtom February 16, 2024 at 10:09 pm - Reply

      Mail’s out :)
      /Tom

  14. HTAB February 18, 2024 at 2:05 pm - Reply

    Thanks Tom, I received the .pbix files. But I don’t see the data viz part. Is this normal?

  15. Kike March 22, 2024 at 11:37 am - Reply

    Hi Tom ,

    Very useful! Can you share the .pbix please ?

    Thanks

    • tackytechtom March 23, 2024 at 10:40 pm - Reply

      Mail is out :)

  16. Arvind April 4, 2024 at 1:39 pm - Reply

    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.

    • tackytechtom April 5, 2024 at 10:10 am - Reply

      Thanks for the kind words :) I just sent you the pbix files!

  17. KARIN FITZGERALD April 29, 2024 at 1:03 am - Reply

    Hi Tom, This is so inciteful!! Would you mind emailing me a copy to palease?

    • tackytechtom April 29, 2024 at 6:36 am - Reply

      Hiya! I just sent you the files :)

  18. Puneet May 15, 2024 at 10:53 pm - Reply

    Could you please Share PBIX file for this

  19. Andreas June 12, 2024 at 11:54 am - Reply

    Thanks for a great article.
    Could I also please receive the pbix file?
    thanks

    • tackytechtom June 13, 2024 at 6:39 am - Reply

      Hi Andreas! I sent it out yesterday :)

  20. Lalit June 17, 2024 at 1:00 pm - Reply

    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.

  21. Lalit June 17, 2024 at 1:35 pm - Reply

    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

    • tackytechtom July 7, 2024 at 7:02 pm - Reply

      Mail’s out :)

  22. Yannick Reinke July 24, 2024 at 5:27 am - Reply

    Hey, could also share the PBIX to me? Great article

    • tackytechtom July 24, 2024 at 5:51 pm - Reply

      Hi Yannick! Just sent you the files :)

  23. Roberto Seeker August 15, 2024 at 8:03 pm - Reply

    Hi there
    Great article! Thank you.
    Would you mind to send me the pbix as well?
    Thanks in advance.

    • tackytechtom August 16, 2024 at 10:44 am - Reply

      Just sent the e-mail. Hope it helps! :)

  24. Jacob Hunter August 26, 2024 at 2:19 pm - Reply

    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!

    • tackytechtom August 28, 2024 at 3:13 pm - Reply

      Hi Jacob, Mail’s out :)

  25. Jax August 28, 2024 at 12:45 pm - Reply

    Hi! Great article, would you mind sending the pbix to me as well? Thank you in advance.

    • tackytechtom August 28, 2024 at 3:13 pm - Reply

      Hello Jax, I just sent you the file. Hope it helps!

  26. Rami August 28, 2024 at 2:54 pm - Reply

    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.

    • tackytechtom August 28, 2024 at 3:18 pm - Reply

      Hi Rami, Files should be on your end now. Happy playing :)

  27. Dave Smith August 28, 2024 at 3:34 pm - Reply

    Thank you for the useful information on DMVs. Please forward a copy to the .pbix.

    • tackytechtom August 28, 2024 at 6:00 pm - Reply

      Copy is on its way :)

  28. Mallika Varahagiri August 30, 2024 at 7:05 am - Reply

    This is really insightful! Could you share the pbix file with me please? I would love to play around with it!

    • tackytechtom August 30, 2024 at 4:18 pm - Reply

      Hi Mallika :) File should be on its way…

  29. Eric H September 16, 2024 at 5:37 pm - Reply

    Thanks for the article! Just what I’m looking for. Can you send me the .pbix please?

    • tackytechtom October 5, 2024 at 7:09 pm - Reply

      Mail’s out! Sorry for the delay…

  30. Shimmy Lo September 30, 2024 at 5:22 am - Reply

    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

    • tackytechtom October 5, 2024 at 7:10 pm - Reply

      Hi Shimmy, the files should arrive shortly!

  31. James C October 8, 2024 at 12:20 am - Reply

    This is fantastic, would love to play around with the PBIX file please :)

    • tackytechtom October 8, 2024 at 1:13 pm - Reply

      Hi James,

      Mail’s out! Happy playing :)

  32. Marina P October 30, 2024 at 2:16 pm - Reply

    Thank you for this article. This is very interesting.
    Could I have access to the pbix file, please?

    • tackytechtom November 1, 2024 at 2:21 pm - Reply

      Hi Marina! Mail has just been sent :)

  33. neville November 21, 2024 at 11:01 am - Reply

    That was a great article and an insight

    • tackytechtom November 21, 2024 at 2:20 pm - Reply

      Thank you :)

  34. Sri February 4, 2025 at 9:32 am - Reply

    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

    • tackytechtom February 4, 2025 at 9:10 pm - Reply

      Mails out :) I am not sure about your question though. Fancy to clarify it a bit more? :)

Leave A Comment