How to run a MDX query for all datasets available in workspace

[Krishanu]

Hi,

I have a premium workspace with multiple datasets. How can I run a simple MDX query (like SELECT * FROM $System.DISCOVER_M_EXPRESSIONS ) for all datasets available in that worksapce via SSMS (XMLA endpoint)?
The MDX runs for each dataset, now we have to run this for mutiple datasets and in multiple workspaces. 
Any advise or help is really appreciated. Thanks.

[lbendlin]

Assuming you can use the new-ish AAD based Power BI REST API custom connector you can do all this now in Power Query.

GitHub - migueesc123/PowerBIRESTAPI: A Microsoft Power BI Data Connector or Power Query Connector fo...

- collect all workspaces you have access to

- collect all datasets in these workspaces

- run MDX query against the datasets  (via the Analysis Services connector)

[Yanant1020]

I have this exact same need and we have lots of datasets. As far as I can tell the linked blog post that is five years old is likely working with limitations that do not exist anymore. Is there a better solution to this issue you have found? I need to be able to query ALL our published datasets on a schedule to retrieve information about them.

[v-luwang-msft]

Hi @Krishanu ,

Has your problem been solved, if so, please consider Accept a correct reply as the solution to help others find it.

Best Regards

Lucien

[v-luwang-msft · Accepted solution]

Hi  @Krishanu ，

The following article have a solution about how to run dax or mdx queries against  Power Bi，you could have a look.

Can I run my own DAX or MDX queries against Power BI?

Wish it is helpful for you!

Best Regards

Lucien

[lbendlin]

You create linked server entities for all your datasets and then run a UNION ALL query.