Semantic model design patterns
Battle-tested relational star schemas, TMDL definitions, and production DAX measure suites for enterprise Power BI and Microsoft Fabric architectures. Eliminate bidirectional joins, optimize VertiPaq memory, and guarantee predictable calculation outcomes.
SaaS Subscription ARR & MRR Waterfall
Subscription reporting requires calculating monthly recurring revenue movements across irregular customer contract dates. Naive calculations double-count multi-year contracts or fail to capture mid-term tier upgrades.
A monthly snapshot fact table linked to customer and product dimensions via clean 1:* star schema relationships, supplemented with an MRR movement calculation bridge.
Relational Star Schema Topology
Granularity: One row per active customer per calendar month end.
Customer master with cohort date and industry segmentation.
Tier definitions, seats, and list price.
Canonical enterprise date table marked as date table.
Certified Production DAX Measures
Ending MRR
Pure additive scalar sum over the filtered month end snapshot.
Ending MRR =
SUM('Fact_Subscription_Monthly'[MonthlyAmount])Starting MRR
Evaluates the ending MRR of the immediately preceding month.
Starting MRR =
CALCULATE(
[Ending MRR],
PREVIOUSMONTH('Dim_Date'[Date])
)Net Retention Rate (NRR)
Tracks expansion and contraction within existing cohorts without customer churn contamination.
Net Retention Rate (NRR) =
VAR PriorCustomers = CALCULATETABLE(VALUES('Dim_Customer'[CustomerKey]), PREVIOUSMONTH('Dim_Date'[Date]))
VAR CurrentRevFromPrior =
CALCULATE(
[Ending MRR],
PriorCustomers
)
VAR PriorRev = [Starting MRR]
RETURN
DIVIDE(CurrentRevFromPrior, PriorRev, 0)TMDL Definition
table Fact_Subscription_Monthly
lineageTag: a1b2c3d4-fact-sub
column CustomerKey
dataType: int64
sourceColumn: CustomerKey
column PlanKey
dataType: int64
sourceColumn: PlanKey
column DateKey
dataType: int64
sourceColumn: DateKey
column MonthlyAmount
dataType: decimal
formatString: \$#,##0.00
sourceColumn: MonthlyAmount
measure 'Ending MRR' = SUM('Fact_Subscription_Monthly'[MonthlyAmount])
formatString: \$#,##0.00Have a Bespoke Enterprise Modeling Challenge?
I design certified semantic models for Microsoft Fabric and Power BI: reconciling irregular fiscal calendars, multi-tier commission structures, and multi-tenant row-level security.
Privacy: the analysis runs in your browser and your selections never leaves it. I only count that the tool was used and when a report is downloaded — no inputs, results or personal data are recorded.
Get the next Engineering Note
Practical DAX, semantic model and Fabric notes. 1–2 a month, unsubscribe any time.
Why Schema Topology Trumps Raw Compute
Dimensional Modeling Discipline
Every pattern strictly separates facts and dimensions into high-performance Kimball star schemas to prevent VertiPaq memory bloat and Cartesian joins.
Eliminating Bidirectional Ambiguity
Avoid performance-killing bidirectional cross-filtering cascades by solving complex currency conversion and cohort metrics with scalar DAX iteration.
Plain-Text TMDL Blueprinting
Each architecture pattern includes exportable TMDL definitions ready to commit directly into your Git-backed PBIP repository.
Frequently Asked Questions About Semantic Model Architecture
What is an enterprise semantic model design pattern?
It is a battle-tested relational schema layout and DAX calculation strategy designed to solve common complex business reporting challenges (such as MRR churn, currency translation, or inventory balance) with maximum query performance and zero ambiguity.
Why should we avoid snowflake schemas in Power BI?
Snowflake schemas introduce multiple relationship hops between dimensions and fact tables. While common in transactional normalized databases, in columnar engines like VertiPaq and Fabric Direct Lake, snowflake hierarchies cause unnecessary relationship traversals, slower visual load times, and complex DAX formulas.
How do I import these TMDL snippets into my project?
Save the exported `.tmdl` files into your Power BI Project repository under `semantic-model/definition/tables/`. Reopen the `.pbip` project in Power BI Desktop or deploy via Git in Microsoft Fabric.
Can pbiexpert design a custom semantic model for our organization?
Yes. Through my semantic model review, I audit your data warehouse schemas, design certified star schemas, and write production-grade DAX measures tailored to your business rules.