Dynamic Management View (DMV) queries against PowerBI model. How to automate load into DB?

[Anonymous]

Hi 
I have been using SQL Server Management Studio to manually execute DMVs to query XMLA endpoints in our PowerBI Premium workspaces. These queries return useful information on the underlying PBI SSAS database such as:

• M expressions in the model
• Tables in the model
• Relationships between the tables
• Measures & DAX
• Calculated columns

I would like to programtically extract the above information from all our PowerBI Premium workspaces and load it into a central database (probably SQL Azure DB) for further analysis. However,  I am struggling to find relevant information (and examples) on the more complex querying capabilities of DMVs, and scheduling DMVs. Can anyone provide any insight (or links) as to whether:

• DMV Queries (against the PBI Analysis Services database) can be scheduled with a std scheduling tool like SQL Server Agent?
• DMVs support INSERTing data into destination databases in a SQL tables. I see references that DMV Syntax is very limited, so no JOINS ().
• Or would I be better off, taking a more pure script based approach and doing it in Python. 

Any advice, during this early planning stage would be welcome

Cheers
Steve

[tackytechtom]

Hi guys,

Though late to the party, I'd like to share two blog posts that might be of help for others.

If you'd like to analyse the model with Power BI, why not directly connecting Power BI with the meta data of the dataset? This post shows a way to do this:

https://www.tackytech.blog/how-to-build-meta-reports-on-top-of-power-bi-datasets-with-dynamic-manage...

If you really want to save the data from the DMVs into a database (i.e. Azure SQL Database), you can look into the following one, too. The blog post uses Azure Data Factory, but you could use any other tool really. It utilises the "executeQueries" Power BI API in a copy activity (source: executeQueries API, target: Azure SQL Database). Interesting enough you can not only query the meta data (via DMVs) from the dataset but also the actual tables.

https://www.tackytech.blog/how-to-query-and-copy-data-from-power-bi-datasets-via-azure-data-factory/

Hope this helps 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[vanessafvg]

https://www.fourmoo.com/2021/11/24/how-did-i-keep-my-power-bi-dataset-measures-documented-and-up-to-...

[amitchandak]

@Anonymous , Have you explored this option

https://azure.microsoft.com/en-in/blog/connecting-power-bi-to-an-azure-analysis-services-server/

[Anonymous]

Hi @amitchandak Thanks for your suggestion. 

We aren't in a position to change our Enterprise Architecture. So this isn't an option.

Thanks again

Steve