It’s very easy to get TOM (Tabular Object Model) for datasets hosted in Power BI Service and extract various metadata from it (tables, measures, etc.). We will do it just now using a simple C# console application and download TOM for one sample dataset using Power BI User credentials. Similarly, you can do that as Service Principal.
Let’s set up a few prerequisites first.
1. Setting the Power BI Tenant
Note: If you don’t have a Power BI account with administrative rights then use this guide to create one.
Login to your Power BI account with administrative privileges and in Admin Portal make sure you have enabled access to XMLA endpoints for your tenant in Power BI Admin Portal.
Because we will call the XMLA endpoint as a Power BI User with Premium Per User capacity, go to the Premium Per User section and allow read-only access to XMLA endpoints in Dataset workload settings.
For the Power BI Premium per Capacity settings check this video from 6:48 because there it needs to be set too.
Then go to your workspaces and choose the one you will connect to.
Copy the Workspace connection because we will use it later in the C# Console app.
Note: XMLA endpoints are supported only for workspaces hosted on Premium capacity. If you will try to connect to a non-premium workspace you will get this exception:
Microsoft.AnalysisServices.ConnectionException: Initial catalog property is required in order to connect to Power BI Pro workspaces.
You can test the proper configuration using the SQL Server Management Studio.
Fill in your Power BI User account credentials (same as for powerbi.com) once prompted. You will see a list of available databases after successful authentication.
2. Downloading Tabular Object Model using C# console application
Because our Power BI XMLA endpoint is ready for connection now, we will test it from a simple C# console application. You can build your own or just download the solution targeting .NET.Core 5.0.
Two NuGet packages are required to connect to Analysis Services running under the hood of Power BI Service.
The main method:
using System;
using Microsoft.AnalysisServices;
namespace PowerBIXMLA
{
class Program
{
static void Main(string[] args)
{
Server server = new Server();
// Connect as Power BI User
string workspaceConnection = "powerbi://api.powerbi.com/v1.0/myorg/YOUR_WORSPACE_NAME";
string userId = "";
string password = "";
string connectStringUser = $"DataSource={workspaceConnection};User ID={userId};Password={password};";
// Connect as Service Principal
/*
string workspaceConnection = "powerbi://api.powerbi.com/v1.0/myorg/YOUR_WORSPACE_NAME";
string tenantId = "";
string appId = "";
string appSecret = "";
string connectStringServicePrincipal = $"DataSource={workspaceConnection};User ID=app:{appId}@{tenantId};Password={appSecret};";
*/
// Connect
server.Connect(connectStringUser);
// List Databases (Datasets - tabular models) and extract TOM (Tabular Object Model)
foreach (Database database in server.Databases)
{
Console.WriteLine(database.Name);
string tmslTable = JsonSerializer.SerializeDatabase(database);
Console.WriteLine(tmslTable);
}
}
}
}
You need to modify the section with Power BI User credentials:
string workspaceConnection = "powerbi://api.powerbi.com/v1.0/myorg/YOUR_WORSPACE_NAME"; string userId = ""; string password = "";
- worskspaceConnection: this is the Workspace Connection value we have copied above (powerbi://api.powerbi.com/v1.0/myorg/Premium WS)
- userId: Power BI User account name (username@domain.xx). The same you are using to sign in to Power BI Service.
- password: Your Power BI User password.
We can list all server Databases once connected and use the SerializeDatabase() method to get the Tabular Object Model.
Rebuild the application and run int. In case of success you will see:
Because we have called the SerializeDatabase() method the whole Database is serialized to JSON. Database-level properties are marked with blue. The Tabular Object Model is marked with green.
If you would like to serialize just the Tabular Object Model you can call SerializaeObject() method inside the Tabular namespace.
Microsoft.AnalysisServices.Tabular.JsonSerializer.SerializeObject(database.Model);
To do more with the main Tabular Object Model just reference the Tabular namespace and process the object like any other strong-typed objects.
using Microsoft.AnalysisServices.Tabular;
...
Model model = database.Model;
foreach (Table t in model.Tables)
{
Console.WriteLine(t.Name);
}
Finally, it’s important to understand that datasets uploaded to Power BI Service from Power BI *.pbix files are in fact hosted as standard Analysis Service Tabular Models in the Power BI Service. That means that you can use the same approach when connecting to the on-prem Tabular Analysis Services instance. Only the authentication process will be different. The XMLA itself is fully compatible.