How to check if relationships in power bi are valid in software enginnering way?

[jaryszek]

Hello,

any automatic method to validate all relationships in power bi ? 
I am not relying only on building them manually...

Best,
Jacek

[jaryszek]

Thank you very much for this. How to export whole data model into JSON format?

[v-dineshya · Accepted solution]

Hi @jaryszek ,

Thank you for reaching out to the Microsoft Community Forum.

Please try below options to export the full data model into JSON format:

1. Use Tabular Editor (v2 or v3)

Tabular Editor can load a model from: .pbix (with XMLA endpoint enabled), .bim file and a live connection dataset (Premium workspace or Desktop with External Tools)

Steps: Open Tabular Editor. Load your model (via XMLA or file). File > Save As > Save as .bim (which is JSON).

Note: The .bim file is a JSON representation of the full model: tables, columns, relationships, measures, etc.

2. Use pbi-tools

pbi-tools can decompile a .pbix file into individual files (JSON, M scripts, etc).

Steps:

a. Install pbi-tools via dotnet:

dotnet tool install --global pbi-tools

b. Run:

pbi-tools extract your-report.pbix

c. Output: Model metadata (relationships, measures, tables) in structured JSON files (inside Model folder). Power Query scripts. Layout and report JSON.

Note: Supports automation in CI/CD pipelines.

3. Use Power BI XMLA Endpoint (Premium or Desktop with External Tools)

You can connect to the model via XMLA using tools like: SSMS (SQL Server Management Studio), Tabular Editor, Power BI .NET SDK (TOM) and DAX Studio

From here you can: Script the model into JSON (via Tabular Editor or TOM). Export the model as .bim from SSMS or Tabular Editor.

4. Use .NET TOM (Tabular Object Model)

Write a .NET Core app or PowerShell script to connect to Power BI (via XMLA or file), and extract model metadata.

Sample C# TOM code snippet:

var server = new Server();
server.Connect("powerbi://api.powerbi.com/v1.0/myorg/MyWorkspace");
var db = server.Databases["YourDataset"];
string json = db.Model.ToJson();
File.WriteAllText("model.json", json);

5. Power BI ALM Toolkit (GUI tool)

This allows model comparison and can export the entire model as a .bim (JSON) file.

Please refer community thread and Microsoft article.

Export-PowerBIDataflow (MicrosoftPowerBIMgmt.Data) | Microsoft Learn

Solved: Export data in JSON format - Microsoft Fabric Community

Export data from a Power BI visualization - Power BI | Microsoft Learn

If my response has resolved your query, please mark it as the "Accepted Solution" to assist others. Additionally, a "Kudos" would be appreciated if you found my response helpful.

Thank you

[v-dineshya]

Hi @jaryszek ,

Thank you for reaching out to the Microsoft Community Forum.

Please find below automated methods to validate Power BI relationships:

1. Use Tabular Object Model (TOM) via .NET or PowerShell

Power BI models are built on Tabular Analysis Services, and you can programmatically inspect them using TOM.

Steps: Load the model (.bim or live dataset in Power BI Desktop or service). Iterate over all Model.Relationships.

Validate: Column existence, Cardinality (OneToMany, etc.), Relationship direction and Referential integrity (e.g. foreign keys in fact tables exist in the dimension tables).

2. Validate Referential Integrity via DAX Queries

You can programmatically run DAX queries via tools like DAX Studio, Power BI XMLA endpoint, or Tabular Editor, to check for orphan keys (facts without matching dimensions).

Sample DAX to detect orphan keys:

EVALUATE
EXCEPT (
SUMMARIZE(FactTable, FactTable[DimKey]),
SUMMARIZE(DimTable, DimTable[Key])
)

Note: If this query returns any rows, the relationship is not valid in referential terms. You can script this per relationship.

3. Tabular Editor + Scripting

You can use Tabular Editor 2 or 3 to load your model and validate relationships via its scripting interface (C#-like).

4. Automated Testing via PBI Inspector / pbi-tools

Use open-source tools like: pbi-tools, PowerBIInspector and Power BI ALM Toolkit

These allow: Extraction of .pbix into model metadata. Programmatic diff and validation and Scripting validations over the relationships JSON block.

5. Custom Validation Toolchain (Python/Node.js)

If you're generating model metadata (model.bim or JSON), you can validate relationships before pushing them to Power BI:
Parse the relationships section. Confirm fromColumn and toColumn exist in the defined tables. Optionally, connect to your data source and run sample queries to test FK integrity.

Please refer below community thread and Microsoft official article.

Solved: Is there any tool for Power BI Data Validations au... - Microsoft Fabric Community

Explore and validate relationships in Power BI semantic models and pandas dataframes - Microsoft Fab...

If my response has resolved your query, please mark it as the "Accepted Solution" to assist others. Additionally, a "Kudos" would be appreciated if you found my response helpful.

Thank you