Understanding How Auto Relationship Detection and Auto Date/Time Impact Power BI Refresh in Dynamic Semantic Models

Midhun Balakrishnan — Fri, 08 May 2026 07:28:26 +0000

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.

DEV Community: Midhun Balakrishnan

Understanding How Auto Relationship Detection and Auto Date/Time Impact Power BI Refresh in Dynamic Semantic Models