Power BI Metadata Catalog Using DAX Studio
Objective
As Power BI adoption grows across teams and organizations, it becomes harder to keep track of what data is being used, where it’s coming from, and how it flows through your reports. That’s where a metadata catalog comes in—a centralized view of the tables, columns, and data sources powering your Power BI datasets.
In this blog, I’ll show you how to create a metadata catalog using DAX Studio and the XMLA endpoint, extracting the actual source table names, schema, server, and database—all without writing a single line of M code.
Tools Required
- 🔹 Power BI Premium or Premium Per User (PPU)
- 🔹 Published dataset in Power BI Service
- 🔹 DAX Studio (free desktop tool)
- 🔹 XMLA endpoint enabled for your workspace
Step 1: Connect to the Dataset via XMLA
Open DAX Studio and choose Tabular Server as your connection option. Enter your workspace connection string, which looks like:
powerbi://api.powerbi.com/v1.0/myorg/<YourWorkspaceName>
This allows DAX Studio to connect to the data model behind your published dataset.
Step 2: Explore Your Models
Once connected, you’ll see a list of all datasets available in that workspace. Choose the dataset you want to inspect from the dropdown.
This lets you inspect the underlying semantic model, including all the tables, partitions, and data sources.
Step 3: Query the Partitions
Now run the following DMV query to extract the underlying data source information:
SELECT * FROM $SYSTEM.TMSCHEMA_PARTITIONS
This query will return:
- Table Name
- Partition Name
- Power Query M Code (QueryDefinition) used to load that table
From the M code, you can extract:
- Source Server
- Database
- Schema
- Source Table Name
Example M code snippet:
Source = Sql.Database(Server, Database),
View Name = Source{[Schema=”PBI”,Item=”*****”]}[Data]
This tells you that the Power BI model table Dim_CareArea is pulling from the SQL view ***** in the PBI schema.
Final Output: Your Metadata Catalog
Here’s an example of how your metadata catalog might look after parsing the M code:
Model Table Name | Source Table/View | Schema | Database | Server |
Dim_CareArea | vwUSCANCareArea | PBI | AnalyticsDB | SQLPROD01 |
FactVolume | Fact_SalesVolume | dbo | SalesDW | AZSQL01 |
DimEmployee | Employees | hr | HRDB | HRSQL02 |
Use Cases for the Catalog
- Governance: Track data lineage for auditing or compliance
- Documentation: Keep technical documentation in sync with live models
- Optimization: Spot unused tables or redundant queries
Pro Tip: Automate with Power BI
You can load the results from DAX Studio into Excel or Power BI Desktop, extract server/database/schema/table names from M code using Power Query, and build an interactive report that refreshes regularly.
Want a template? Let me know and I’ll create a downloadable version for you.