how to query and store meta data of your semantic model with sempy and dax info functions.
introduction.
The new native DAX DMV functions are quite interesting, since they simplify the querying of a semantic model’s meta data. Thus, they can tremendously enhance our capabilities for documenting and monitoring. For the ones that have worked with DMVs in Analysis Services or Power BI before, these new INFO DAX functions expose the TMSCHEMA DMVs. Here a way on how you can call them in the DAX query view:
Unfortunately, at time of writing this post, you could not use those functions i.e. in calculated tables or columns in order to process and use the data further. In this demo, one way being suggested is to copy and paste the data from the DAX query view to wherever you need them. Another way could be to connect to the Power BI semantic model and run the INFO DAX functions against it. We have used a very similar approach when querying the DMVs straight from Power Query. You might wanna follow that article but instead of using the DMV Query syntax, you could just shoot those DAX INFO functions. Here a screenshot on an example, but more information can be found in the article:
In this article here, I’ll show you yet another way. We will be utilising Sempy in a Fabric notebook from where we run the INFO DAX queries. Then we push the meta data into a Lakehouse and finally build a semantic model on top of it. I also do not dislike the idea of having the meta data separate from the actual semantic model. Of course, you could even combine the meta data of several semantic models pushing your governance to the next level.
Once again, the approach here is indeed a viable alternative to querying the DMVs directly via Power Query.
prerequisites.
1. A Fabric capacity and workspace
2. A Fabric Lakehouse, the one in this article is called AdventureWorks_DMV_Lakehouse
plan of action.
1. What’s the goal?
The goal is to load the meta data of your semantic model into a Lakehouse via a Fabric Notebook. After, we create a semantic model on top of it using direct lake:
Next, we specify the name of our semanticmodel as well as the DMVs we’d like to query and save in the Lakehouse. Obviously, it is a stretch to load in all of them, as we did below. Feel free to just use the ones you actually need. Note, the DMV DATACOVERAGEDEFINITIONS is commented out (Line 17) since this one threw an error (both via the Python code and in Power BI Desktop):
Now, this snippet does the magic. It loops over the DMV list defined above, does some cleansing and finally loads the data into the attached Lakehouse:
3. Showtime
This is how it shall look like, when running the script. On the left, we can see the tables that have been created and loaded by the Notebook. In addition, the script logs which tables got loaded, too. Note, empty tables are ignored.