Extract Model Metadata

Trying to extract information about your tabular model? Here is a way to quickly extract relevant metadata for all common model elements (listed below).

• Tables

• Columns

• Measures

• Hierarchies

• Data Sources

• Relationships

• Roles*

• Row Level Security

• Perspectives

• Translations

• Calculation Groups

• KPIs

• Object Level Security

• Model

This can be done for any form of tabular model - a .bim file, database.json file, SSAS Server, Azure Analysis Services Server, or a Power BI Premium dataset. Power BI Premium datasets are able to do this via the XMLA endpoints as well as the support for Service Principals using the XMLA endpoints.

*Note that the Role metadata cannot yet be extracted for Power BI Premium datasets. This is expected to be possible once the XMLA endpoints feature becomes generally available later this month.

How to do it

1.) Download and install Tabular Editor.

2.) Download the C# script available here on GitHub.

3.) Open the script in a text editor and update the folderName parameter to a folder on your computer (this is where the metadata will go).

4.) Identify where you want to extract metadata from based on the options below.

• .bim file

• database.json file

• SQL Server Analysis Services Server

• Azure Analysis Services Server

• Power BI Premium dataset

5.) Run the relevant script below accordingly from the command prompt. Make sure to update the items in orange, using the details below for assistance. All of the < and > signs are to be omitted and are simply placeholders.

.bim File

start /wait /d "C:\Program Files (x86)\Tabular Editor" TabularEditor.exe "<.bim file location>" -S "<C# script file location>"

database.json File (based on the Save-to-Folder technique)

start /wait /d "C:\Program Files (x86)\Tabular Editor" TabularEditor.exe "<database.json file location>" -S "<C# script file location>"

SSAS Server

start /wait /d "C:\Program Files (x86)\Tabular Editor" TabularEditor.exe "<Server Name>" "<Database Name>" -S "<C# script file location>"

Azure Analysis Services

start /wait /d "C:\Program Files (x86)\Tabular Editor" TabularEditor.exe "Provider=MSOLAP;Data Source=asazure://<westeurope>.asazure.windows.net/<AASServerName>;User ID=<xxxxx>;Password=<xxxxx>;Persist Security Info=True;Impersonation Level=Impersonate" "<Database Name>" -S "<C# script file location>"

Power BI Premium Dataset

start /wait /d "C:\Program Files (x86)\Tabular Editor" TabularEditor.exe "Provider=MSOLAP;Data Source=powerbi://api.powerbi.com/v1.0/myorg/<Premium Workspace>;User ID=app:<Application ID>@<Tenant ID>;Password=<Application Secret>" "<Dataset Name>" -S "<C# script file location>"

Note: If using Power BI Premium, make sure to enable XMLA Read/Write Endpoints by following the instructions here (note the minimum SKU to enable XMLA Read/Write is an A3).

Where to get the Azure Analysis Services Info

1.) Go to to the Azure Portal: portal.azure.com.

2.) In the search bar, search for 'Analysis Services'.

3.) Select your Azure AS instance.

4.) On the Overview tab, copy the Server Name (as highlighted below).

Where to get the app info

1.) Go to the Azure Portal: portal.azure.com.

2.) In the search bar, search for 'Azure Active Directory'.

3.) Copy the Tenant ID from the Overview page (as shown below).

4.) Go to the 'App Registrations' page (there is a link on the left navigation bar).

5.) Copy the Application ID (as shown below).

6.) Click on the 'Display Name' (here you will again see the App ID & Tenant ID).

7.) Click on 'Certificates & Secrets' (there is a link on the left navigation bar).

8.) Click 'New Client Secret', give it a name, and click 'Add'.

9.) Copy the client secret (make sure to write this down somewhere as you won't be able to go back and retrieve it from the portal).

Result

The resulting output of running these scripts is a set of text files with the extracted metadata - one text file for each of the model elements. The text files will appear in the folder specified in the folderName parameter within the C# script.

Simply copy these results into Excel for easy viewing and filtering.

Conclusion

Thanks to the TOM API and Tabular Editor, model metadata can easily be extracted programmatically. Additionally, this capability and much more is now available to folks using Power BI Premium thanks to the enabling XMLA Endpoints and Service Principals. Lastly, this gives a taste of the automation capabilities at your fingertips when using Tabular Editor for any tabular modeling scenario.