Create SSAS Tabular and Power BI Dataset with Python from Model.bim

In this blog let's take a look at how you can use the Model.bim file extracted either from SSAS Tabular, Power BI Desktop or Power BI Service Dataset to generate a new dataset either on premise or on PBI Service.

Setup (common for both SSAS Tabular & PBI Service)

I would suggest that you create a new Python Environment dedicated for this task as it uses a library that clashes with another.

To create a virtual environment just open the terminal or PowerShell window in a particular folder and run the command

python -m venv pbi_env

Once this is done you need to activate the environment .\pbi_env\Scripts\activate and install pythonnet library which allows us to work with .net Dlls.

If you didn't create a new virtual environment and you already have Clr library then you first need to uninstall it and then install Pythonnet otherwise you will get runtime errors.

To reference this environment in the choice of your IDE just use the path of the python.exe file located at C:\Users\antsharma\Downloads\Blog\pbi_env\Scripts\python.exe

In VS Code you can set interperter using the option in the bottom pane

Just enter the path of the python.exe

once this is done we need to reference 2 DLLs Microsoft.AnalysisServices.Tabular and Microsoft.AnalysisServices

If you have PBI Desktop or SSAS already installed then you will find them in the folder: C:\Windows\Microsoft.NET\assembly\GAC_MSIL

This is how you import those libraries:

Json is imported because the Model.bim file is stored as JSON structure.

Next part that is common for both is to intiate the Server, provide the connection string and connect.

server.Databases.GetNewName ensures that you don't overwrite the existing model with same name and create a new one each time. password is your windows credentials.

Creating SSAS Tabular Database

So from this point onwards things are to be done slightly differently as the model.bim of PBI Desktop and PBI Service differs from model.bim of SSAS Tabular because of the compatibilty levels and defaultPowerBIDataSourceVersion so you can't just use one for another you need to edit the model.bim file before deployment, which I will show you in a moment.

Next we need model.bim, now it can be something you already have or something that you want to extract from the existing database, use Tabular Editor to extract the model.bim file of an existing database, open TE and connect to any existing model and choose save as and save the file somewhere

Create a new variable:

bim_file = r"C:\Users\antsharma\OneDrive\Desktop\Contoso Import.bim"

Next we will open this bim file using the json library imported earlier:

I opened the file and replaced the id and name with the new name so that every time we run the code a new database gets created.

Now the problem is if we read JSON using Python the JSON structures will be converted into equivalent Python structures which are not supported in Tabular Model Scripting Language, so we need to convert it back to the original format as well as Deserialize the Database to convert the JSON strings to objects and after this all we need to do is generate the TMSL script and execute it

Complete code for the SSAS Tabular version:

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 = "domain\servername"
username = 'domain\username'
password = '*****************'
conn_string = f"DataSource={workspace};User ID={username};Password={password};"

server = Tabular.Server()
server.Connect(conn_string)
new_dataset_name = server.Databases.GetNewName('SSAS Tabular Model w Python')

bim_file = r"C:\Users\antsharma\OneDrive\Desktop\Contoso Import.bim"

with open(bim_file) as bim:
    json_file = json.load(bim)
    json_file['id'] = new_dataset_name
    json_file['name'] = new_dataset_name


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

db = AS.JsonSerializer.DeserializeDatabase(
    raw_json, 
    DeserializeOptions = 'default', 
    CompatibilityMode = 'Analysis Services'
)
# Compatibility Modes:
    # Analysis Services = 1 (Basic AnalysisServices mode - used on SSAS and AAS)
    # Excel PowerPivot = 4
    # PowerBI = 2
    # Unknown = 0

script = Tabular.JsonScripter.ScriptCreateOrReplace(db)
server.Execute(script)
server.Disconnect()

If you go to the server you will see the new database is created, we have duplicated Contoso Import to another database.

Now you just need to process it.

Creating Power BI Dataset

There are only few changes that we need to make to the code.

workspace = "powerbi://api.powerbi.com/v1.0/myorg/Your Workspace" 
# ^ XMLA Endpoint
username = 'your login id form PBI Service'
password = 'your PBI Service password'

Earlier I mentioned that there are differences in the model.bim files of different flavours of Analysis Services, so if you have a model.bim from SSAS Tabular you can small changes and make it workable for the PBI Dataset.

I am going to use the model.bim used in the previous example and create a dataset from it.

So when we open the model.bim we can actually modify it to be:

with open(bim_file) as bim:
    json_file = json.load(bim)
    json_file.update({'compatibilityLevel':1571})
    json_file['id'] = new_dataset_name
    json_file['name'] = new_dataset_name
    json_file['model']['defaultPowerBIDataSourceVersion'] = "powerBI_V3"

The above code changes the compatibility as well as the defaultPowerBIDataSourceVersion.

Another change that you need to make is to the CompatibilityMode:

db = AS.JsonSerializer.DeserializeDatabase(raw_json, DeserializeOptions = 'default', CompatibilityMode = 'PowerBI')

# Compatibility Modes:
    # Analysis Services = 1
    # Excel PowerPivot = 4
    # PowerBI = 2
    # Unknown = 0

Once done run the code and you will see that the dataset is created, if you run it multiple times it will suffix 1, 2, 3 etc.

Once done just map the correct datasource to the gateway and you are good to go.

Complete code for PBI Dataset version:

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 = "powerbi://api.powerbi.com/v1.0/myorg/Your Workspace" 
username = 'your login id form PBI Service'
password = 'your PBI Service password'
conn_string = f"DataSource={workspace};User ID={username};Password={password};"

server = Tabular.Server()
server.Connect(conn_string)
new_dataset_name = server.Databases.GetNewName('Power BI Dataset w Python')

bim_file = r"C:\Users\antsharma\OneDrive\Desktop\Contoso Import.bim"

with open(bim_file) as bim:
    json_file = json.load(bim)
    json_file.update({'compatibilityLevel':1571})
    json_file['id'] = new_dataset_name
    json_file['name'] = new_dataset_name
    json_file['model']['defaultPowerBIDataSourceVersion'] = "powerBI_V3"


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

db = AS.JsonSerializer.DeserializeDatabase(raw_json, DeserializeOptions = 'default', CompatibilityMode = 'PowerBI')
# Compatibility Modes:
    # Analysis Services = 1
    # Excel PowerPivot = 4
    # PowerBI = 2
    # Unknown = 0

script = Tabular.JsonScripter.ScriptCreateOrReplace(db)
server.Execute(script)
server.Disconnect()