In the previous post we saw how to connect Power BI Desktop dataset in SQL Server Management Studio (SSMS). Today, we will look at the datasets which have been published to Power BI service.
Datasets in Power BI are backed by the analytical model similar to Azure Analysis Services and hence connected in SSMS as Analysis Services connection. This connection is established through XMLA endpoint which is by default enabled in Power BI Premium. Power BI Premium Per User also supports this. You can find the XMLA endpoint connection string under the workspace settings –
Simply copy/paste this connection string in SSMS to connect Analysis Services as –
Each dataset inside the workspace appears as a separate database here just like an Analysis Services model is deployed as a SSAS or AAS database as shown below –
You might be wondering about lot of other local date tables in addition to the actual tables of model in the dataset. These are internal date tables corresponding to each date column in your tables which are used to present date columns as hierarchy. You can disable this under the options in Power BI Desktop as –
We disabled this for the current report only and re-published the report. After this, you can see that none of those local date tables exist anymore –
However, it was not needed by any means and nowhere related with connecting the dataset or querying it but just in case you wonder at it in the first glance so we thought to clarify this right away.
So, this is how you can connect to Power BI Service datasets in SSMS and thereafter can query using DAX expressions.
There are different formats for connection string URLs as shown below from Microsoft’s documentation –
Since we were working in the same tenant so third option was application to us. You can match this in the snapshot too as highlighted above during the post.
To conclude this post, note that this is not applicable to “My Workspace” as my workspace is a private workspace and doesn’t support Premium capacity with obvious reasons. So, you can test or bring this into practice for any other workspace created with premium capacity enabled on it.