While working on a Power BI automation platform using TOM/XMLA and multiple datasources, I faced refresh failures that were difficult to identify initially.
Our system dynamically generates tables and relationships at runtime using JSON metadata. However, the PBIX template already contained predefined relationships and Power BI automatic features such as:
Auto relationship detection
Auto date/time
This created conflicts during refresh.
Example:
Initially, the PBIX template assumed:
Table1
Col1 = relationship key
But at runtime, the datasource generated:
Table1
Col2 = relationship key
Even after updating relationships using TOM/XMLA during publishing, Power BI refresh still tried to validate relationships using older semantic assumptions from the PBIX template or automatically inferred metadata.
This caused:
relationship constraint errors
refresh failures
inconsistent semantic behavior
Auto relationship detection made the issue worse because Power BI sometimes created implicit relationships automatically based on matching column names.
Auto date/time also added hidden date tables and additional metadata, which increased model complexity in our dynamic schema environment.
As a temporary workaround, I skipped rows causing constraint issues, but that led to incomplete data loading.
Finally, the key learning was:
Dynamic semantic models should avoid hidden or automatic semantic assumptions.
The recommended approach is:
- disable auto relationship detection
- disable auto date/time
- keep PBIX templates lightweight
- dynamically create relationships for runtime-controlled datasources
- retain only stable relationships for external datasources such as SharePoint or Excel
This helped improve refresh stability and reduced semantic conflicts in dynamic Power BI models.
Introduction
While working on a dynamic Power BI report generation platform using TOM/XMLA and multi-datasource integration, I encountered a refresh issue that initially appeared to be related to relationship constraints and data integrity. However, after deeper analysis, the actual root cause was connected to Power BI automatic semantic modeling behaviors such as:
Auto relationship detection
Auto date/time generation
Static PBIX semantic assumptions
Dynamic runtime schema changes
This article shares my understanding of the issue, the challenges faced, and the architectural learnings from handling dynamic semantic models in Power BI.
Background
Our platform supports multiple datasources:
Internal Datasource
The internal datasource provides metadata dynamically in JSON format, including:
Tables
Columns
Relationships
Row data
Relationship keys
The schema can change at runtime.
Example:
Initial Schema
Table1
- Col1 -> Relationship/Primary Key
- Col2
- Col3 Runtime Schema Table1
- Col1
- Col2 -> Relationship/Primary Key
- Col3
The effective relationship key changes dynamically.
External Datasources
We also support external datasources such as:
SharePoint
Excel
Semi-structured external tables
These datasources are comparatively less deterministic because:
- schemas may change
- data types may vary
- uniqueness is not guaranteed
- relationship inference is difficult
- Existing Architecture
Initially, the PBIX template contained:
- predefined tables
- predefined relationships
- semantic metadata
- report visuals
At runtime:
- schema gets updated dynamically
- relationships are updated using TOM/XMLA
- semantic model is published
- refresh is triggered
At first glance, this appeared correct.
However, refresh started failing with relationship constraint issues.
The Actual Issue
The problem was not only related to duplicate or blank values.
The core issue was:
Semantic mismatch between static PBIX relationship metadata and runtime-generated schema.
Example:
PBIX Template Assumption
Table1.Col1 = relationship key
Runtime Datasource
Table1.Col2 = relationship key
Even though relationships were updated using TOM/XMLA during publishing, Power BI refresh processing still attempted to validate semantic relationships using stale or previously inferred metadata.
As a result:
- refresh validated outdated key assumptions
- relationship constraints failed
- refresh processing became inconsistent
- How Auto Relationship Detection Made It Worse
Power BI automatic relationship detection can silently create relationships based on:
- matching column names
- inferred datatypes
- heuristic matching
Example:
Table1.Col1
Table2.Col1
Power BI may automatically create:
Table1.Col1 ↔ Table2.Col1
Even if runtime metadata intended:
Table1.Col2 ↔ Table2.Col2
In dynamic schema environments, this becomes dangerous because:
hidden semantic assumptions get introduced
runtime-generated relationships lose determinism
unexpected joins appear
stale relationship lineage remains
refresh validation becomes unpredictable
How Auto Date/Time Also Contributed
Power BI Auto Date/Time creates:
hidden local date tables
implicit relationships
hidden hierarchies
additional semantic metadata
For every detected datetime column.
In static enterprise models this may be acceptable.
But in dynamic runtime-generated models:
datetime columns may change
columns may disappear
datatype may evolve
runtime schema may not match PBIX assumptions
This creates additional hidden metadata dependencies.
As a result:
refresh complexity increases
semantic lineage becomes unstable
hidden relationships may survive schema updates
model debugging becomes harder
Temporary Workaround
As a mitigation, logic was added to:
skip rows causing constraint violations
avoid loading problematic records
continue refresh execution
However, this was only a temporary workaround because:
data became partially loaded
semantic inconsistency still existed
stale relationship assumptions remained inside the model
The root issue was not the rows themselves.
The root issue was semantic mismatch.
Architectural Learning
The major realization was:
Dynamic semantic models should avoid static semantic assumptions.
Especially:
predefined relationship keys
auto-generated semantic metadata
hidden inferred relationships
implicit date hierarchies
when schemas are runtime-driven.
Recommended Approach
- Disable Auto Relationship Detection
Recommended setting:
File → Options → Current File → Data Load
Disable:
☐ Auto detect new relationships after data is loaded
Reason:
prevents implicit relationship generation
keeps relationship orchestration deterministic
gives full control to TOM/XMLA logic
- Disable Auto Date/Time
Recommended setting:
File → Options → Current File → Data Load
Disable:
☐ Auto date/time
Reason:
avoids hidden date tables
reduces semantic model complexity
improves metadata stability
avoids hidden runtime dependencies
- Use Thin PBIX Templates
PBIX templates should mainly contain:
report pages
visuals
themes
formatting
bookmarks
Avoid embedding dynamic relationship assumptions.
- Dynamically Generate Relationships for Runtime-Controlled Datasources
For internally governed datasources:
create relationships dynamically
use runtime metadata
validate schema before refresh
use TOM/XMLA as source of truth
- Retain Stable Relationships for External Datasources
For:
SharePoint
Excel
external semi-structured sources
retain only stable and carefully governed relationships.
Because runtime inference for external sources is less deterministic.
Final Understanding
The issue was not a Power BI bug.
It was an architectural conflict between:
static PBIX semantic assumptions
automatic Power BI semantic inference
dynamic runtime-generated schemas
The most important learning was:
In dynamic semantic modeling systems, explicit semantic control is far safer than implicit automatic inference.
Disabling automatic relationship detection and auto date/time significantly improves predictability, refresh stability, and semantic consistency in runtime-generated Power BI models.
Conclusion
Power BI works extremely well with stable semantic models and governed schemas.
However, when building advanced runtime-driven semantic model automation platforms using:
TOM/XMLA
dynamic metadata
runtime schema generation
multi-datasource integration
it becomes important to reduce hidden semantic assumptions inside PBIX templates.
For dynamic architectures:
- deterministic relationship orchestration
- explicit semantic governance
- runtime-controlled schema validation
become critical for stable refresh and semantic integrity.
This experience helped me better understand how Power BI internally handles semantic metadata, relationships, and refresh validation in dynamic modeling scenarios.
Top comments (0)