In this article, I am going to introduce the Tabular Object Model (TOM) in the Power BI Data Model and provide an understanding of how this model can be accessed outside of the Power BI environment. For more info about the Tabular Object Model in the Power BI Data Model, please read this article. In this tutorial, we are going to use the Visual Studio Code to simply write a dotnet console application and try to access the Tabular Object Model from the Power BI file. With the help of this knowledge, programmers and BI developers can not only view the underlying model in the Power BI Data Model but also can enhance the data model programmatically by writing a few lines of code. It can also be further improved by automating the creation of the Power BI models with the help of the Tabular Object Model library in dotnet.
Installing Visual Studio Code
First things first, since we will be using the Visual Studio Code to connect to the Power BI Data Model, let us go ahead and install it on our machine. Head over to the official website of Visual Studio Code and download the corresponding version of the tool. Since I am on windows, I will go ahead and download the latest windows version of the tool.
Figure 1 – Installing Visual Studio Code on the Local Machine
Once you have downloaded the tool on your local, go ahead and install the same. Keep the default settings while installing, I have already installed the tool prior to this tutorial, so I will not be installing it again.
Install the Dot Net Core 3.1 SDK
Once you have successfully installed the Visual Studio Code, the next step is to download and install the dotnet core SDK. Please note that we need to install the dotnet core SDK and not the runtime. At the time of writing this article, the latest version of the dotnet core SDK available is 3.1.302. You can download it from here. The download will take some time, depending on the internet connection. Once it is downloaded, go ahead and install the same with recommended settings.
Figure 2 – Checking the Dotnet Core SDK version
As you can see in the figure above, run the command “dotnet –info” after the installation has been completed. You will be able to see the latest version that is installed on your machine along with the previously installed versions.
Creating the Console Application
Now that we have installed the dotnet core SDK on our machine, we are ready to create the console application that we will be using to interact with the Power BI Data Model. Create a directory under which you would like to create the application. Once inside the directory, run the following command.
|
1 |
dotnet new console |
This command will create a new dotnet console application in the directory, as mentioned, and you can see the result in the directory as follows. There are two files – “powerbi-console.csproj” and the “Program.cs” which are the main files for the project.
Figure 3 – New Dotnet Console Application Created
The Program.cs will be the main file where we will write all our code in regard to the Power BI Data Model. You can already open this file, and there will be some sample code in it that you can build and run.
Figure 4 – Console Application Sample
In order to build and run this project, execute the following two commands:
- dotnet build
- dotnet run
Figure 5 – Building and running the console application
As you can see, we have successfully built and executed our application. The next step is to include the Tabular Object Model library in this program so that we can use it in our application. You can run the command below to install the package in the console application.
|
1 |
dotnet add package Microsoft.AnalysisServices.NetCore.retail.amd64 --version 19.6.0-Preview |
Figure 6 – Adding the TOM Library to the application
The installation will take some time, and then you can see that the package has been successfully installed.
Using the Tabular Object Model library
Now that we have installed the TOM library into the console application, we can go ahead and start using this library in our code. For this tutorial, I am going to use a sample Power BI report, which I have downloaded from the official documentation from Microsoft. The idea is to create a small program that can connect to this Power BI report and display a list of tables inside it.
Let me go ahead and open up the sample report in Power BI Desktop. You can see the names of the tables on the right-hand pane.
Figure 7 – Sample Power BI Report
As you can see, there are six different tables in this Power BI model; we will try to display the same in the console application.
Head over to Visual Studio Code and paste the following code in there. Make sure that you modify the port number of the analysis services server as it will be different in your case. In order to find out the port number, you can follow any one of the techniques mentioned in this article.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
using System; using Microsoft.AnalysisServices.Tabular; namespace powerbi_console { class Program { static void Main(string[] args) { Server tabularServer = new Server(); tabularServer.Connect("localhost:57793"); Model dataModel = tabularServer.Databases[0].Model; foreach (Table table in dataModel.Tables) { Console.WriteLine($"Table Name: {table.Name}"); } } } } |
As you can see in the picture above, we have first created a server object with the name “tabularServer” which will connect to the localhost and the port that we have specified. Now, this server object might contain one or more databases. In our case, since we have only one tabular database, we will try to fetch the data model from the first database itself. Once the data model is fetched in the console application, we can then run a statement that will return the name of each of the tables in that data model.
Now, when you run the above program, you can see the output as follows:
Figure 8 – Console program output
Here, you can see that the list of table names that are present in the Power BI Data Model is being displayed. This is a very simple configuration where we have just listed the names of the tables in the data model. With the Tabular Object Model library, you can actually do a lot of other automated operations like adding a measure to the existing data model or copying measures from one model to another and stuff like that. Additionally, you can also register your console application with Power BI Desktop, and you can use your application as a toolbar inside of the Power BI Desktop application. These are some of the complex tasks that can be achieved using the Tabular Object Model client library for dotnet.
Although this should not be a preferred method of working with Power BI Data Models, the objective of this article was just to throw some light on how to get started with the console application while using the TOM library. There are a couple of tools used by the Power BI community like DAX Studio, Tabular Editor, etc. which can be used to modify the data model outside of the Power BI environment.
Conclusion
In this article, we have seen how to connect to a Power BI Data Model using Visual Studio Code and how to access the Tabular Object Model from within the code. The Tabular Object Model, also known as the TOM, is a client library provided by Microsoft which can be leveraged to build programs and get access to the underlying Power BI data model. This is really helpful in automating the creation and modification of the Power BI data models programmatically. If you start working with the Tabular Object Model library, it will help you understand how the tabular model works and will be helpful in building tools that are not yet available to be used for the Power BI data models.
He is a prolific author, with over 100 articles published on various technical blogs, including his own blog, and a frequent contributor to different technical forums.
In his leisure time, he enjoys amateur photography mostly street imagery and still life. Some glimpses of his work can be found on Instagram. You can also find him on LinkedIn
View all posts by Aveek Das
- Getting started with PostgreSQL on Docker - August 12, 2022
- Getting started with Spatial Data in PostgreSQL - January 13, 2022
- An overview of Power BI Incremental Refresh - December 6, 2021