Extract model.bim file from Power BI & SSAS Tabular with Python

Model.bim is a JSON file which is the skeleton of your Power BI or SSAS Model, using this file you can create a database from scratch, for example you can define all the objects such as Measures, Tables, Connection sources etc in the JSON format and then deploy it to a server to create a database.

Power BI & Analysis Services continuosly work with Model.bim, when you create a measure or add a new column the model.bim file is getting edited as well.

Extracting model.bim file is pretty easy you can use Tabular Editor and then just use it in anyway you want.

But I want to show you a complex method, lol. We can actually get the model.bim file using Python as well all we need is to run some methods of TOM ( Tabular Object Model ) library.

In the last blog I showed how you can generate a database/dataset using model.bim and how to setup stuff - https://www.antmanbi.com/post/create-ssas-tabular-and-power-bi-dataset-with-python-from-model-bim

Most of the code remains the same this time as well.

import json
import clr

folder = r"C:\Windows\Microsoft.NET\assembly\GAC_MSIL"

clr.AddReference(folder + 
r"\Microsoft.AnalysisServices.Tabular\v4.0_15.0.0.0__89845dcd8080cc91\Microsoft.AnalysisServices.Tabular.DLL")

clr.AddReference(folder +
r"\Microsoft.AnalysisServices\v4.0_15.0.0.0__89845dcd8080cc91\Microsoft.AnalysisServices.DLL")

import Microsoft.AnalysisServices as AS
import Microsoft.AnalysisServices.Tabular as Tabular

workspace = "spring\sql2019"
username = 'windows username'
password = 'windows password'
conn_string = f"DataSource={workspace};User ID={username};Password={password};"

server = Tabular.Server()
server.Connect(conn_string)

The above code creates a connection with the server, next you need to identify the database either by index or by name. For Power BI we will use index and for SSAS name works.

database = server.Databases.GetByName("Contoso")

So once we have a reference to the Database we can actually use a TMSL command ScriptCreate more info here: https://learn.microsoft.com/en-us/analysis-services/tmsl/create-command-tmsl?view=asallproducts-allversions

To generate the script you need to use the JsonScripter class of TOM and load the file.

script = Tabular.JsonScripter.ScriptCreate(database)

This is what the script looks like:

we need to just remove the create part to make it look like a model.bim file, and for that we can convert the JSON script into python's data structure and then access only relevant fields.

For that we use json.loads

json_file = json.loads(script)
edited = json_file['create']['database']

The edited variable accesses the content of the database key in the dictionary. Once this is done we can go back to JSON format with

raw_json = json.dumps(edited, indent=4)

And finally save the file:

with open(r'C:\Users\antsharma\OneDrive\Desktop\IR.bim', 'w') as model_bim:
    model_bim.write(raw_json)

print(script)
server.Disconnect()

Complete code:

import json
import clr

folder = r"C:\Windows\Microsoft.NET\assembly\GAC_MSIL"

clr.AddReference(folder + 
    r"\Microsoft.AnalysisServices.Tabular\v4.0_15.0.0.0__89845dcd8080cc91\Microsoft.AnalysisServices.Tabular.DLL")

clr.AddReference(folder +
    r"\Microsoft.AnalysisServices\v4.0_15.0.0.0__89845dcd8080cc91\Microsoft.AnalysisServices.DLL")

import Microsoft.AnalysisServices as AS
import Microsoft.AnalysisServices.Tabular as Tabular

workspace = "spring\sql2019"
username = 'windows username'
password = 'windows password'
conn_string = f"DataSource={workspace};User ID={username};Password={password};"

server = Tabular.Server()
server.Connect(conn_string)

database = server.Databases.GetByName("Contoso Import")

script = Tabular.JsonScripter.ScriptCreate(database)
json_file = json.loads(script)
edited = json_file['create']['database']

raw_json = json.dumps(edited, indent=4)

with open(r'C:\Users\antsharma\OneDrive\Desktop\IR.bim', 'w') as model_bim:
    model_bim.write(raw_json)

server.Disconnect()

Comparing the model.bim extracted with Python and the one using TE3. - If you use any online JSON comparer you will notice there is only one slight difference which is added by Tabular Editor only.

For PBI Desktop and Power BI Service there are only few changes.

Power BI Service

1. workspace variable needs to point to the XMLA endpoint of the PPU or Premium workspace

2. username and password will be PBI Service username and password

Power BI Desktop -

1. workspace variable is the port number on which Analysis Services is running, you can find that with DAX Studio.

2. Since every PBI Desktop file has a unique instance of Analysis Services running, each PBI file can have only 1 database so we don't need the model name we can just use 0 as index

So the code will be:

workspace = "localhost:######" # = 5 digit port number
server = Tabular.Server()
server.Connect(workspace)

database = server.Databases[0]