Power BI Metadata Extract Using SQL Server Management Studio

This blog will help to reduce efforts in creating a data dictionary for Power BI datasets. Power BI shared datasets ensure the reusability of the existing dataset/data model. It is easier to maintain a single dataset and create reports connected to this dataset. In this case, the content creators should understand the data model and attributes present in the dataset for effective use. Data dictionary comes into the picture in such cases.

Metadata (Data about data) is essential to create a data dictionary. At the moment, there is no straightforward option to extract metadata from Power BI.

No Worries!

I have an easy way to extract the metadata using the SQL Server Management Studio.

Requirements:

• Premium Workspace – Don’t you have premium capacity? No problem, I have something for Non-Premium datasets. Keep reading.
• XMLA Endpoint in Capacity Settings – Set as Read Only or Read Write

• SQL Server Management Studio – SSMS 18.x or later

Datasets published under Premium Capacity Workspace:

Go to Power BI Service -> Navigate to the dataset that you wanted to extract metadata -> Dataset Settings

Go to Server Settings -> Copy Connection String.

Open SSMS,

• Server Type: Analysis Services
• Server Name: Paste Connection string.
  • Sample Connection String -powerbi://api.powerbi.com/v1.0/myorg/Power BI Test – Dev
• Authentication: Azure Active Directory – Password or Azure Active Directory Universal with MFA

Refer below image,

Provide your credentials and click connect.

Expand databases and it will show available datasets under your workspace.

Select your dataset -> Right click -> Script -> Script Database as -> Create To -> File

Save the script file. This file has end to end details about your dataset from Data source connection till Row-Level security.

Datasets published under Non-Premium workspaces

Download your dataset as a pbix from Power BI Service

Open it in Power BI Desktop and follow the below steps,

• Open Task Manager -> Details -> Find PID of msmdsrv.exe

• Open Command Prompt and run netstat -anop tcp
• Find Port number for the PID

• Open SSMS,
  • Server Type: Analysis Services
  • Server Name: localhost:64301
  • Authentication: Windows Authentication
• Export Script as mentioned in the previous part

You can use this approach for both premium and non-premium datasets. The script will provide the metadata of your dataset. With this metadata information, you can generate a data dictionary for your report authors.

Please feel free to reach Cittabase for more information. Visit our blogs for more topics on Power BI.