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.

Certified Architecture Blueprints
SaaS & ARR · Advanced Pattern

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.

Architectural Solution

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

Fact_Subscription_MonthlySnapshot

Granularity: One row per active customer per calendar month end.

Dim_CustomerDimension

Customer master with cohort date and industry segmentation.

Dim_PlanDimension

Tier definitions, seats, and list price.

Dim_DateDimension

Canonical enterprise date table marked as date table.

Active Model Relationships
Fact_Subscription_Monthly*:1Dim_Customer(Single)
Fact_Subscription_Monthly*:1Dim_Plan(Single)
Fact_Subscription_Monthly*:1Dim_Date(Single)

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.00
Enterprise Semantic Architecture

Have 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.

Request Architecture Consultation

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.

1–2 useful notes a month. Unsubscribe at 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.