topic Re: Is there a way to run queries on all datasets in a workspace via XMLA/SSMS? in Developer

Is there a way to run queries on all datasets in a workspace via XMLA/SSMS?

arpost — Mon, 31 Oct 2022 21:21:08 GMT

Does anyone know how to get run MDX on all datasets in a workspace via SSMS? I've successfully connected to the XMLA endpoint via SSMS (per MS doc). I can then run queries like this just fine when I connect to a specific dataset/database in the workspace:

SELECT * FROM $System.TMSCHEMA_PARTITIONS

However, I can't figure out how to run this at a "global" level for all datasets/tables at once without having to explicitly connect to EACH dataset first. Hoping someone else has a brilliant idea on how to solve for this.

Re: Is there a way to run queries on all datasets in a workspace via XMLA/SSMS?

Anonymous — Tue, 01 Nov 2022 06:18:39 GMT

Hi @arpost ,

Please refer from this solved post with similar issue like yours.

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)

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Is there a way to run queries on all datasets in a workspace via XMLA/SSMS?

arpost — Tue, 01 Nov 2022 14:49:45 GMT

I appreciate it, @Anonymous. I try to avoid 3rd-party solutions where possible for data security.