Power BI Meta Data extraction using Python

In this blog we are going to learn about Power BI.pbit files, Power BI desktop file Meta data, Extraction of Power BI Meta data and saving it as an excel file using .pbit file and a simple Python code using libraries like Pandas, OS, Regex, JSON and dax_extract.

What is Power BI and .pbix files?

Power BI is a market leading business intelligence tool by Microsoft for Cleaning, Modifying and Visualizing raw data to come up with actionable insights. Power BI comes with its own data transformation engine called power query and a formula expression language called DAX (Data Analysis Expressions).

DAX gives power BI the ability to calculate new columns, dynamic measures, and tables inside Power Bi desktop.

By default, Power BI report files are saved with .pbix extension which is a renamed version of a ZIP file which contains multiple components, such as the visuals, report canvas, model metadata, and data.

What is Power BI .pbit file

.pbit is a template file created by Power Bi desktop which is also a renamed version of a ZIP file that contains all the Meta data for the Power BI report but doesn’t contain the data itself. Once we extract .pbit file we get a DataModelSchema file along with other files which contain all the Meta data of a Power BI desktop files.

Later in this blog we will be using these .pbit and DataModelSchema files to extract Power BI desktop Meta data.

What is the Meta data in a Power BI Desktop file

Regarding what you see in the Report View in a Power BI desktop, meta data is everything. You can think of all the information as meta data, including the name, source, expression, data type, calculated tables, calculated columns, calculated measures, relationships and lineage between the model’s various tables, hierarchies, parameters, etc.

We will mainly concentrate on extracting Calculated Measures, Calculated Columns, and Relationships in this blog.

Extraction of Meta data using Python

Python was used to process and extract the JSON from the.pbit file and DataModelSchema. We first converted JSON to a Python dictionary before extracting the necessary Meta data.

Below are the steps we will need to achieve the requirement:

 

1. Exporting .pbix file as .pbit file

There are two ways to save our power BI desktop file as .pbit file.

• Once we are in Power BI desktop, we have an option to save our file as power BI template(.pbit) file
• We can go to File–>Export–>Power BI Template and save the .pbit file at the desired directory.

2. Unzipping .pbit file to get DataModelSchema file

We can directly unzip the .pbit file using the 7z-Zip file manager or any other file manager. Once we Unzip the file, we will get a folder with the same name as that of the .pbit file. Inside the folder we will get the DataModelSchema file, we will have to change its extension to .txt for reading in python.

3. Reading .pbit and Data model schema file in python

We have an option to directly read the .pbit file in python using the dax_extract library. Second option to read the text file in python and using the JSON module convert it into a Python dictionary. Code can be found in the GitHub repository link given at the end of this file.

4. Extracting Measures from the dictionary

The dictionary that we get consists details of all the tables as separate lists, Individuals tables have details related to the columns and measures belonging to that table, we can loop on each table one by one and get details of columns, Measures etc. Below is an example of the Python code can be found in the GitHub Repository link given at the end of this file.

	table Number	table Name	Measure Name	Measure Expression
0	5	Query Data	% Query Resolved	CALCULATE(COUNT(‘Query Data'[Client ID]),’Quer…
1	5	Query Data	Special Query Percentage	CALCULATE(COUNT(‘Query Data'[Client ID]),’Quer…
2	6	Asset Data	Client Retention Rate	CALCULATE(COUNT(‘Asset Data'[Client ID]),’Asse…

 

5. Extracting calculated columns from the Dictionary

Like how we extracted the measures we can loop on each table and get details of all the calculated columns. Below is the sample output of the Python code can be found in the GitHub Repository link given at the end of this file.

 

	table no	Table Name	name	expression
6	2	Calendar	Day	DAY(‘Calendar'[Date])
7	2	Calendar	Month	MONTH(‘Calendar'[Date])
8	2	Calendar	Quarter	CONCATENATE(“Q”,QUARTER(‘Calendar'[Date]) )
9	2	Calendar	Year	YEAR(‘Calendar'[Date])

 

Also Read:  Certainty in streaming real-time ETL

6. Extracting relationships from the dictionary

Data for relationships is available in the model key of the data dictionary and can be easily extracted. Below is the sample output of the Python code can be found in the GitHub Repository link given at the end of this file. 

 

	From Table	From Column	To Table	To Column	State
0	Operational Data	Refresh Date	LocalDateTable_50948e70-816c-4122-bb48-2a2e442…	Date	ready
1	Operational Data	Client ID	Client Data	Client ID	ready
2	Query Data	Query Date	Calendar	Date	ready
3	Asset Data	Client ID	Client Data	Client ID	ready
4	Asset Data	Contract Maturity Date	LocalDateTable_d625a62f-98f2-4794-80e3-4d14736…	Date	ready
5	Asset Data	Enrol Date	Calendar	Date	ready

 

7. Saving Extracted data as an Excel file

All the extracted data can be saved in empty lists and these lists can be used to derive a Pandas data frame. This Pandas data frame can be exported as Excel and easily used for reference and validation purposes in a complex model. Below snapshot gives an idea of how this can be done.

Conclusion

In this blog we learnt about extracting metadata from .pbit and DataModelSchema file. We have created a Python script that allows users to enter the file location of .pbit and DataModelSchema file and then metadata extraction along with excel generation can be automated. The code can be found on the below GitHub also sample excel files can be downloaded from below GitHub link. Hope this is helpful and will see you soon with another interesting topic.