Semantic Model Best Practices

Semantic models (formerly datasets) are the single most important artifact in Power BI. Every report, dashboard, and paginated report queries a semantic model. Every DAX calculation lives in a semantic model. Every row-level security rule, every hierarchy, and every business definition is encoded in the semantic model. When a semantic model is poorly designed—wrong relationships, missing measures, confusing names, inconsistent calculations—every report built on top inherits those problems. When a semantic model is well designed, report development becomes fast, results are trustworthy, and business users can explore data independently without waiting for IT. Our architecture consulting service includes semantic model design and optimization for enterprise-scale environments.

Shared vs Dedicated Models

The most important architectural decision is whether to build shared semantic models or dedicated per-report models:

| Approach | Advantages | Disadvantages | Best For | |---|---|---|---| | Shared models | Single source of truth, consistent metrics, lower maintenance | Requires governance, model changes affect all reports | Enterprise BI with standardized KPIs | | Dedicated models | Independence, no cross-report impact, faster iteration | Metric inconsistency, duplication, higher maintenance | Exploratory analytics, ad-hoc projects | | Composite models | Combine shared model with local extensions | Added complexity, potential performance impact | Departmental extensions to central models |

Best practice for enterprise deployments: build a small number of shared semantic models (3-5 per major business domain), certify them, and require all production reports to connect to certified models. Allow dedicated models only for exploration and prototyping.

Star Schema Design

Every semantic model should follow star schema conventions. Power BI's VertiPaq engine is optimized specifically for dimensional models:

Fact Table Design

• Define the grain explicitly: "One row = one order line item" or "One row = one daily account balance"
• Keep facts narrow: Only foreign keys (integer), measures (numeric), and degenerate dimensions (transaction numbers)
• Remove text columns: Product names, customer names, and category labels belong in dimension tables
• Use appropriate aggregation types: Mark additive measures (Revenue, Quantity) for SUM. Semi-additive measures (Balance, Inventory) need LASTNONBLANK in DAX.

Dimension Table Design

• Denormalize hierarchies: Product → Subcategory → Category → Department should be columns in ONE Product dimension, not four linked tables
• Use surrogate integer keys: Never use natural keys (product codes, email addresses) for relationships
• Include display-friendly columns: Full names, formatted codes, sort-order columns
• Mark date tables: Every model needs a dedicated Date dimension marked as a date table for time intelligence

Relationship Rules

• One-to-many from dimension to fact (dimension on "1" side)
• Single filter direction (dimension filters fact)
• Integer keys on both sides
• No bidirectional relationships unless explicitly justified and documented
• No fact-to-fact relationships—connect facts through conformed dimensions

Naming Conventions

Consistent naming eliminates confusion and makes models self-documenting:

Table Names

• Remove source system prefixes: "DimCustomer" → "Customer", "FactSales" → "Sales"
• Use plural nouns for fact tables: "Sales", "Orders", "Transactions"
• Use singular nouns for dimensions: "Customer", "Product", "Date"
• Use business terms, not technical terms: "Geography" not "GeoLookup"

Column Names

• Use Title Case with spaces: "Order Date", "Product Category", "Customer Name"
• Include units where relevant: "Revenue USD", "Weight KG"
• Prefix key columns consistently: "Product Key", "Customer Key" (hidden from report view)
• Use "Is" prefix for boolean columns: "Is Active", "Is Current Year"

Measure Names

• Start with the metric name: "Revenue", "Profit Margin", "Customer Count"
• Add time context only for time-modified versions: "Revenue YTD", "Revenue PY"
• Include calculation type for ratios: "Margin %", "Growth %", "Conversion Rate"
• Avoid abbreviations that business users might not understand

Display Folders

Organize measures into logical display folders so report creators can find what they need:

• Financial Metrics: Revenue, Cost, Profit, Margin
• Volume Metrics: Order Count, Unit Quantity, Customer Count
• Time Intelligence: YTD variants, PY comparisons, Moving Averages
• Ratios & KPIs: Conversion rates, satisfaction scores, utilization percentages

Hide all columns that should not appear in reports (keys, technical IDs, intermediate calculation columns) by setting their visibility to hidden.

Measure Design Patterns

Base Measures

Every model needs a set of foundational measures that serve as building blocks:

• Define explicit SUM/COUNT measures rather than relying on implicit aggregation: `Revenue = SUM(Sales[Amount])` is better than dragging the Amount column onto a visual
• Create COUNT measures for distinct counting: `Customer Count = DISTINCTCOUNT(Sales[CustomerKey])`
• Use DIVIDE() instead of division operator to handle divide-by-zero gracefully: `Margin % = DIVIDE([Profit], [Revenue])`

Time Intelligence Measures

Build time intelligence on top of base measures using a consistent pattern:

• Year-to-Date: `Revenue YTD = TOTALYTD([Revenue], 'Date'[Date])`
• Previous Year: `Revenue PY = CALCULATE([Revenue], SAMEPERIODLASTYEAR('Date'[Date]))`
• Year-over-Year Growth: `Revenue YoY % = DIVIDE([Revenue] - [Revenue PY], [Revenue PY])`

For models with many base measures, consider calculation groups instead of creating individual time intelligence variants of every measure.

Measure Documentation

Document every measure's business definition, formula logic, and intended use. In Tabular Editor, use the Description property on each measure. This description appears as a tooltip in Power BI Desktop when report creators hover over the measure name—making the model self-documenting.

Performance Optimization

Column Reduction

Every column in the model consumes memory. Remove columns that no reports use:

• Source system audit columns (CreatedDate, ModifiedBy, ETL_BatchID)
• Alternative key columns when surrogate keys are used for relationships
• High-cardinality text columns that are never filtered or displayed (free-text comments, long descriptions)

Data Type Optimization

• Use Integer instead of Decimal for whole numbers (order quantities, counts)
• Use Fixed Decimal for financial amounts (avoids floating-point precision issues)
• Avoid Text columns with high cardinality in fact tables—move to dimensions

Aggregation Tables

For models exceeding 100 million fact rows, create aggregation tables with pre-computed summaries at higher grain levels (daily instead of transaction-level, region instead of store-level). Power BI's aggregation feature automatically routes queries to the aggregation table when the detail level allows, delivering sub-second response times on billion-row datasets.

Governance and Certification

Model Certification

Fabric and Power BI Service support model certification—a formal endorsement that a semantic model meets organizational standards:

1. Quality criteria: Define what "certified" means—tested measures, documented calculations, validated against source, RLS configured
2. Certification process: Model owner submits for certification, designated reviewer validates against criteria, certification badge applied
3. Visibility: Certified models appear with a badge in the data hub, making them easy to discover and trust
4. Recertification: Schedule quarterly reviews to ensure certified models remain accurate as source data and business rules evolve

Row-Level Security

Implement RLS on shared models before publishing to production. Every model serving multiple user groups needs security rules that restrict data visibility based on the authenticated user's role or identity.

Lineage and Impact Analysis

Use the Fabric lineage view to understand which reports depend on each semantic model. Before modifying a certified model (renaming columns, removing measures), check the impact analysis to identify all affected reports and notify their owners.

Related Resources

• Star Schema Best Practices
• Direct Lake Mode
• Power BI Performance Optimization
• Power BI Consulting Services