How to Connect Power BI Dataset in SSMS

Did it ever come to your thoughts if it could be possible to connect Power BI data in PBIX file and query in SQL Server Management Studio? Yes, this is possible. You can connect to your data in PBI desktop file just like you connect to Analysis services and then query it using DAX. Let’s see how –

First of all, you need to open your Power BI report in PBI desktop and then open command prompt as an administrator.

Next, run the following command –

In the output, scroll to the process “msmdsrv.exe” and note that port number which is like 65051 in the below screenshot

Thereafter, open SQL Server Management Studio and connect Analysis Services using this port –

Once you are connected, you can look at its tables in object explorer as –

and not just this, you can get an idea of the dataset size by right clicking the database and looking at the properties –

If you like to query using DAX then this can be a perfect choice for you to interact with PBI dataset in a more flexible way e.g.

Advertisements

One comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s