Power BI Semantic Models - Building a Single Source of Truth

Microsoft renamed Power BI datasets to "semantic models" earlier this year. More than a rebrand, it signals Microsoft's direction: semantic models are the foundation for consistent analytics across the organization. Here's how we're using them at Julius Silvert.

The Problem We Were Solving

A year ago, we had dozens of Power BI reports built by different people using different data connections. The sales team calculated revenue one way; finance calculated it differently. Pick accuracy meant something different to operations than it did to the warehouse supervisor who built the WMS dashboard.

This created arguments in meetings. "Your numbers are wrong." "No, your numbers are wrong." Both were right - they were just measuring different things with the same name.

Semantic models solve this by centralizing metric definitions. Revenue is calculated once, in one place, using one formula. Everyone who needs revenue data references the same semantic model. Arguments about definitions become discussions about whether the definition is correct, not whose report is right.

Our Architecture

We implemented a layered architecture:

Bronze Layer (Raw Data): Data flows from source systems (S2K, BFC Dakota, Adobe Commerce, Shopify) into Azure Data Lake in raw form. No transformation, just extraction. This preserves the original data for troubleshooting.

Silver Layer (Cleaned Data): Dataflows in Power BI perform cleaning and standardization. Date formats align. Customer IDs reconcile across systems. Data quality issues get flagged and resolved.

Gold Layer (Semantic Models): Semantic models define the business metrics. Sales revenue. Gross margin. Pick accuracy. On-time delivery. These are the models that report builders use.

Report Layer: Reports connect to semantic models, never to raw data. Report builders focus on visualization and storytelling, not data modeling.

This separation of concerns means that when a source system changes (like our upcoming S2K to D365 migration), only the bronze and silver layers need modification. The gold layer semantic models maintain consistent metric definitions. Reports don't break.

Key Semantic Models We Built

Sales Performance Model

Our most-used model. Includes:

• Revenue (invoiced sales, net of returns)
• Gross margin (by customer, product, category)
• Order count and average order value
• Customer acquisition and retention metrics
• Sales rep performance measures

Dimensions include: Customer, Product, Time, Geography, Sales Rep, Order Channel (phone, web, EDI).

This model refreshes twice daily - 6 AM and 2 PM. Sales team uses it for morning pipeline review and afternoon forecasting.

Operations Performance Model

Warehouse and delivery metrics:

• Pick accuracy (units picked correctly / total units picked)
• Lines per hour (by picker, by shift, by zone)
• Order cycle time (order entry to truck departure)
• Delivery on-time percentage
• Inventory turns and days on hand

Dimensions include: Product, Warehouse Zone, Picker, Shift, Date, Delivery Route.

This model refreshes every 4 hours during business hours. Operations supervisors use it for real-time-ish monitoring.

Financial Performance Model

Month-end and management reporting:

• Revenue and COGS by accounting period
• EBITDA calculations
• AR aging and collection metrics
• AP metrics and vendor payment terms
• Cash flow indicators

This model connects to our trial balance and subledger data. It refreshes daily but includes period-end adjustments that require manual journal entries.

Technical Implementation

DAX Calculations

We invested significant time in DAX measures. Getting calculations right is harder than it seems:

// Revenue - seems simple but has nuance
Revenue = 
CALCULATE(
    SUM(Sales[InvoiceAmount]),
    Sales[InvoiceType] = "Invoice",  // Exclude quotes, credits
    Sales[IsCancelled] = FALSE()      // Exclude cancelled invoices
) - 
CALCULATE(
    SUM(Sales[InvoiceAmount]),
    Sales[InvoiceType] = "Credit"     // Subtract credit memos
)

Simple revenue calculation required defining: what's an invoice vs. a quote? How do we handle credit memos? What about cancelled transactions? Each decision is documented in the model's documentation layer.

Composite Models

For some reports, we need data from multiple semantic models. Power BI's composite model feature lets reports connect to multiple models while maintaining live connections.

Our executive dashboard connects to Sales, Operations, and Financial models simultaneously. This enables cross-functional analysis without consolidating everything into one massive model.

Row-Level Security

Different users should see different data. Sales reps see their own customers. Regional managers see their region. Executives see everything.

We implement RLS in the semantic models, not the reports. This ensures security is consistent regardless of which report a user views.

Governance Structure

Semantic models require governance. Without it, you end up back where you started - multiple conflicting definitions.

Change control: Any change to a semantic model measure definition requires approval from the metric owner. We track changes in documentation and communicate significant changes to report users.

Certification: We use Power BI's certification feature to mark authoritative semantic models. Users see which models are official vs. experimental.

Discovery: We maintain a catalog of semantic models with descriptions, owners, and refresh schedules. Users can find the right model for their needs without asking IT.

Usage monitoring: We track which semantic models and reports are used, by whom, how often. Unused models get reviewed for retirement. Heavily used models get performance optimization attention.

Performance Optimization

Large semantic models can be slow. Our optimizations:

Aggregations: Pre-calculated aggregations for common query patterns. Daily sales summaries load faster than summing millions of invoice lines.

Incremental refresh: We don't reload all history every refresh. Incremental refresh updates only recent data, dramatically reducing refresh time and resource consumption.

Proper data types: Text fields that should be numbers, dates stored as text - these kill performance. We fix data types in the silver layer before they reach semantic models.

Star schema design: Fact tables connect to dimension tables. No snowflaking, minimal bridging tables. Simple models perform better.

User Adoption

Building semantic models is worthless if people don't use them. Our adoption strategy:

Training: We trained power users on how to build reports connected to semantic models. Not everyone needs to know the details, but the key report builders do.

Templates: We created report templates pre-connected to semantic models. Users start with working connections rather than figuring it out themselves.

Support: Dedicated support for report building questions. When someone struggles, we help them rather than letting them build something incorrect.

Showcasing success: When a department builds a great report using semantic models, we share it broadly. Success stories drive adoption better than mandates.

Lessons Learned

Start with critical metrics. We tried to build comprehensive models initially. Too ambitious. Better to start with 5-10 critical metrics that everyone needs, then expand.

Documentation is essential. Metric definitions in people's heads are worthless. Every measure needs documented definition, calculation logic, and data sources. We use a combination of model documentation features and a separate Wiki.

Performance testing matters. A model that works with test data might crawl with production data. Test with realistic data volumes before releasing to users.

Plan for the D365 migration. Our models currently source from S2K. When we migrate to D365, the semantic model definitions stay constant, but the underlying data connections change. This decoupling was intentional and will pay off during migration.

Future Direction

Microsoft's roadmap includes:

Default semantic models in Fabric: Every lakehouse will automatically have a semantic model. This reduces the manual work of creating models.

Direct Lake: Semantic models can query Fabric lakehouses directly without importing data. This enables real-time analysis on huge datasets.

Copilot integration: Natural language queries against semantic models. Users ask questions in English, Copilot generates DAX. We'll see how well this works in practice.

We're not rushing to adopt these features, but they're on our radar. The semantic model architecture we've built will serve as the foundation for whatever comes next.