Thursday, January 04, 2018

Connect to your Power BI Desktop Model using SSMS

Did you know that you can connect to your Power BI Desktop Model from Sql Server Management Studio (SSMS)?

If not, this blog post is for you.

In this blog post I will be showing you how to connect to your Power BI Model that you have opened with Power BI Desktop from SSMS.

Whenever I try to Get Data in Power BI, I always closely monitor the Task Manager for Memory usage.
I always notice that there is the Microsoft Sql Server Analysis /services and Power Bi Desktop applications top the memory usage as shown below.



What this means is Power BI Desktop uses Sql Server Analysis Services for getting data into its memory. Even though your local system does not have SSAS installed, it uses the msmdsrv executable that’s in the bin folder of the Power BI Desktop folder as shown below:



So there is a close connection between Power BI Desktop and SSAS.

Now coming to the actual content of this blogpost – In order to connect to the Power BI Desktop model from SSMs, you need to know the exact port that the local instance of SSAS is running.  To find out this port, follow the below steps –
Step 1   Open the Power BI Desktop model you are trying to access from SSMS using the Power BI Desktop application (October 2017). I used the Power BI Desktop application optimized for Power BI Report Server.

Step 2   Now browse to the Local App data folder using the path below –

C:\Users\username\AppData\Local\Microsoft\Power BI Desktop SSRS\AnalysisServicesWorkspaces\AnalysisServicesWorkspace422556974\Data

If you are using the regular Power BI Desktop model the local appdata folder will be as below
C:\Users\username\AppData\Local\Microsoft\Power BI Desktop\AnalysisServicesWorkspaces\AnalysisServicesWorkspace422556974\Data

Step 3.  In the above folder locate a text file named msmdsrv.port.txt as shown below :








Step 4  Open the file and copy the number of the port that is displayed.

Step 5  Now open SSMS and connect to Analysis services by giving localhost:portnumber  as shown below :



It uses Windows Authentication to connect to the model.

Step 6  Now you can see the databases and associated tables in the model as shown below.


  
There you go ... You are now connected to your Power BI Model using SSMS.


9 comments:

soumya said...

This blog is really good I would like to say thanks please share more content on MSBI Online Training Hyderabad

latesttechnologyblogs said...

It was so nice article. I was really satisfied by seeing this article. Power BI Online Training

latesttechnologyblogs said...

Enjoyed reading the article above, really explains everything in detail, the article is very interesting and effective. Thank you and good luck for the upcoming articles Power Bi training

kalyani said...


Enjoyed reading the article above, really explains everything in detail, the article is very interesting and effective. Thank you and good luck for the upcoming articles Learn power bi online training
power bi online training Hyderabad
power bi online course
power bi training
power bi certification
power bi online training India

kalyani said...

Enjoyed reading the article above, really explains everything in detail, the article is very interesting and effective. Thank you and good luck for the upcoming articles Learn power bi online training
power bi online training Hyderabad
power bi online course
power bi training
power bi certification
power bi online training India

kalyani said...

Enjoyed reading the article above, really explains everything in detail, the article is very interesting and effective. Thank you and good luck for the upcoming articles Learn power bi online training
power bi online training Hyderabad
power bi online course
power bi training
power bi certification
power bi online training India

kalyani said...


Enjoyed reading the article above, really explains everything in detail, the article is very interesting and effective. Thank you and good luck for the upcoming articles Learn power bi online training
power bi online training Hyderabad
power bi online course
power bi training
power bi certification
power bi learn online
best power bi course
learn power bi
microsoft power bi training
power bi online training India

laxmi said...

sql azure online training
hyperion online training
hyperion online training

laxmi said...

business analyst online training
power bi training institute
tableau training
hyperion online training

Deploy the Azure Machine Learning Model

In the previous post I have discussed how to create an Azure Machine Model.  In this post I will be discussing how to Deploy this model. Pre...