Had 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.
