DMVs (Dynamic Management Views) are, as the Analysis Services documentation states, “queries that return information about model objects, server operations, and server health”. They’re also available in Azure Analysis Service, Power BI and Power Pivot and are useful for a variety of reasons, for example for generating documentation.
Several as-yet undocumented DMVs have appeared in Power BI recently and one that caught my eye was DISCOVER_M_EXPRESSIONS. Unfortunately, when I tried to run it in DAX Studio against an open Power BI file I got an error saying it was only available in the Power BI Service:
Luckily, now that XMLA Endpoints are now in preview and SQL Server Management Studio 18 has been released (which supports connections to Power BI via XMLA Endpoints) we can test it against a published dataset stored in a Premium capacity. The following query can be run from a DAX query window in SQL Server Management Studio:
[sourcecode language=’text’ padlinenumbers=’true’]
select * from
$system.discover_m_expressions
[/sourcecode]
…returns a list of all the Power Query queries in the selected dataset and their M code:
If you don’t have Premium you can run the same query from an Excel table against any published dataset using the technique I blogged about here:
I know there are other methods for doing this (for example using copy/paste) it’s useful to be able to do this via a DMV because it means you can automate the process of extracting all your M code easily.
Some of the other new DMVs look like they are worthy of a blog post too – I can guess what most of them do from their names, but others are more mysterious and perhaps hint at features that have not been announced yet.
Good to see this. I used to harvest it from the connection string of the data sources. But this way cleaner! Thanks Chris
Hi Chris!
Compelling article. I’m curious how can this technique be adapted to work with Dataflows and mapping ot source data fields to target fields in target entities within a Dataflow?
Any thoughts or comments?
Respectfully,
Darryll Petrancuri
I don’t think it can be adapted to work with dataflows, because DMVs are a feature of the SSAS engine and dataflows are not part of that (whereas the M queries inside a dataset are). I don’t know for sure though.
Hi Chris!
And if I want to do the opposite???
Protect the code so that it can not be extracted.
Because, if I write:
select * from $ system.mdschema_measures
I can also extract the DAX measurements
Is there a possibility to protect the code?
Thanks a lot
No, you can’t do that
Thanks a lot for this information Chris !
I checked the documentation but I didn’t find “DISCOVER_M_EXPRESSIONS”. Could you precise where it is please ?
Waiting impatiently to have the same DMV to access to the desktop.
Chears
Didier
I think it’s still quite new – after publishing this post I heard from the dev team that it was not quite finished, which is why it isn’t documented yet.
Anyone seeing strange behavior from DISCOVER_M_EXPRESSIONS recently? It is returning a subset of the mcode in my dataset.
Yes, I have noticed that. It does not returned it in full
https://docs.microsoft.com/ms-my/power-bi/admin/troubleshoot-xmla-endpoint#discover_m_expressions