Every BI Developer knows the golden rule of Power BI: Build a Star Schema.
In theory, it sounds easy. You have your central Fact table containing your metrics, surrounded by flattened Dimension tables containing your attributes. But in the real world of Enterprise BI, things get messy. Timelines are tight, source databases are highly normalized, and a developer eventually decides it’s just „easier” to link a Sub-Category dimension directly to the Category dimension instead of flattening them in Power Query.
Congratulations, you’ve just built a Snowflake Schema.
Why is this a problem? The VertiPaq engine is aggressively optimized for single-hop relationships. When you create a snowflake (a dimension attached to another dimension), your DAX queries are forced to propagate filters across multiple relationship hops. This drastically increases query execution time, spikes CPU utilization, and leads to those frustratingly slow, spinning visuals on your reports.
If you are auditing a legacy model, manually dragging boxes around the „Model View” canvas to spot these chained relationships is a nightmare.
Instead of trusting your eyes, you should trust math. Here is how you can programmatically detect them.
The Logic: Finding the Hubs
If you extract your model’s relationship metadata (using the .pbip or .bim files) into a simple table that lists FromTable, FromColumn, ToTable, and ToColumn, you can use DAX to find the architectural bottlenecks.
The logic is remarkably simple: A table is acting as a „Snowflake Hub” if it exists on the „Many” side of one relationship, AND on the „One” side of another.
Using DAX, we can isolate these tables by creating two virtual lists and finding their intersection:
// Conceptual DAX to detect Snowflake Schemas
VAR _TablesOnManySide =
// Assuming 'FromTable' represents the foreign key / many side
SELECTCOLUMNS( Audit_Relationships, "TableName", Audit_Relationships[FromTable] )
VAR _TablesOnOneSide =
// Assuming 'ToTable' represents the primary key / one side
SELECTCOLUMNS( Audit_Relationships, "TableName", Audit_Relationships[ToTable] )
VAR _SnowflakeHubs =
// Find tables that exist in BOTH lists
INTERSECT( _TablesOnManySide, _TablesOnOneSide )
RETURN
_SnowflakeHubsIf a table name survives the INTERSECT function, it means it is receiving a relationship from one table, and passing it along to another. It is a chained dimension. It is a snowflake.
Enforce the Standard Automatically
You can take this logic and build it into your own custom governance dashboards. But if you are managing multiple enterprise clients or a large internal BI team, building and maintaining these auditing parsers is a massive time sink.
That’s why I integrated this exact Snowflake Detection algorithm directly into ModelLens.
As part of the Semantic Model Auditor framework under theBItoolbox, ModelLens doesn’t just extract your metadata. Its Dual-Scoring Engine actively evaluates your relationship graph. If it detects a snowflake schema via the INTERSECT logic, it instantly flags the specific tables in the Audit Findings table and deducts points from your Advanced Architecture Score.
You get immediate, mathematical proof of where the bottlenecks are, along with the PDF documentation to show your stakeholders exactly what needs to be flattened in Power Query to fix the performance.
Stop hunting for bad relationships visually. Let the engine find them.
Check out how the ModelLens scoring framework evaluates models here:
https://thebitoolbox.gumroad.com/l/modellens