In this tutorial, you’ll learn how to use a Power BI port number to connect a data model to SSAS, Excel, or C#.
The Power BI desktop runs a local instance of SSAS and then assigns it a random port number. This port number changes every time you close and open a Power BI file.
If you need to analyze data in Excel, or in any other program, you can use the port number to connect to the data model.
At the bottom-right of DAX Studio, you can see a text showing localhost followed by a number.
This is the port number that SSAS uses to operate in Power BI.
By knowing the port number, you can make connections between Excel and SSAS to create pivot tables and reports without having to load a new set of data in Power Pivot.
Connecting To An Existing File Using A Power BI Port Number
Open Power BI. Go to the Get data option and click Analysis Services.
This opens the SSAS database wizard. In the Server text box, type the port number of your DAX Studio program.
Click OK and then connect.
You’ll see that the Navigator now contains the data model provided by the port number.
After you click OK, Power BI then creates a live connection to the existing Power BI report contained by the local port number.
The Fields pane and the data model of the new Power BI file all contain the same data as the existing one. In the data model, a live connection is represented by a blue line at the top of each table.
You can even go to the report view and build a basic report using the different items in the Fields pane.
Other than DAX Studio, you can also find the port number of a report using the Tabular Editor.
Take note that once you close and reopen the Power BI file, the port number will change so you need to manually update it.
Connecting A Power BI Data Model To Excel
Next, let’s create a connection between Excel and Power BI. Open Excel and create a new blank workbook. Go to the Data tab and click the Get Data option. Select From Database and then From Analysis Services.
Type the port number in the Server Name and click Next.
In the dropdown box, you can see the name or internal ID of the database.
Click on Model and then click Next and Finish.
After you click Finish, the Import Data wizard will appear. You can then start creating pivot tables in your report.
And similar to how a pivot table works in Excel, simply drag and drop the Fields from the Fields List to their respective areas.
This allows you to create a quick Excel report based on the data model you have in Power BI.
Using A Power BI Port Number In A C# Script
You can also use the port number in a C# script. This allows you to get the Tabular Object Model (TOM) in Power BI along with a list of all its properties.
As an example, let’s get the list of all the table names in SSAS. The first thing you need to do is write a basic script. You need to declare a variable for the Server. You can connect to Power BI using the server.connect (“ “) syntax and then typing in the port number within the parentheses.
You need to declare a model object using the Model keyword. Since this example only has a single server, the zero ( 0 ) keyword is used. But for instances when you’re dealing with multiple databases, you need to use the corresponding number.
Next, you need to create a for loop so that you can iterate over all the tables in your Power BI data model.
You also need to write Console.Write and Console.ReadLine so that the window doesn’t automatically close after displaying the list of names.
Once done, click Start. You can see that the C# program is now able to retrieve a list of all the table names in the Power BI data model.
***** Related Links *****
How Do You Connect Power BI To R Script Visual?
Connect DAX Studio To Power BI And SSAS
Power Apps Data Sources: Connecting Your App To Multiple Sources
Conclusion
This tutorial showed a step-by-step guide of how to use the Power BI port number to connect a data model to various analytical platforms.
Using the port number is one of the simplest ways to form a live connection between two files or programs. The only drawback is that it needs to be manually updated whenever the file/program is reopened.
Enterprise DNA Experts
