Skip to content

Set Up a Semantic Model in Microsoft Fabric

Introduction

A well-designed semantic model is essential for effective analytics in Microsoft Fabric and Power BI. Semantic models transform raw data into business-friendly views, enabling users to easily interpret and interact with information.

Use Import Mode

Import mode is recommended for semantic models in Microsoft Fabric.

Import mode offers several advantages:

  • High Performance: Data is loaded into memory, resulting in fast query responses.
  • Reliability: Models work offline and are less affected by source system performance.
  • Advanced DAX Support: Full access to DAX calculations and time intelligence functions.
  • Efficient Data Compression: Handles large datasets with reduced memory usage.
  • Automatic Query Optimization: The VertiPaq engine optimizes queries in-memory.

Define Essential Parameters for Deployment

Create and configure four key parameters to support deployment pipelines:

  1. Server: SQL analytics endpoint of the warehouse.

    • Ensures the model connects to the correct server for each environment.
    • Enables automatic updates to connection strings during deployment.

  2. Database: WH_Gold_Layer.

    • Specifies the database containing the star schema.
    • Maintains consistent data access across environments.

  3. CommandTimeout: 10/20/30 (minutes).

    • Sets the maximum query execution time.
    • Balances performance and resource management.

  4. LimitData: Three options.

    • GIT (top 0): No data for version control, minimizing file size.
    • Develop (top 1000): Limited dataset for development and testing.
    • All: Complete dataset for production.
    • Ensures appropriate data volumes for each stage of development.

Organize Queries in Power Query Editor

Structure your queries using folders for clarity and maintainability:

  • Parameter: Contains all model parameters.
  • Dimension: Contains dimension tables that provide context.
  • Fact: Contains fact tables with measures and metrics.

Power Query Template

Use the following Power Query template in the Advanced Editor for each dimension and fact table. Replace Schema and Item with your table details.

let
     Source = Sql.Database(#"Server", #"Database", [CommandTimeout=#duration(0, 0, #"CommandTimeout", 0)]),
     tableFromSource = Source{[Schema="DmTest",Item="vw_DimCategorie"]}[Data],
     rowsToReturn = 
          if #"LimitData" = "GIT"            then Table.FirstN(tableFromSource, 0)
          else if #"LimitData" = "Develop"   then Table.FirstN(tableFromSource, 1000)
          else tableFromSource
in
     rowsToReturn

Benefits

Adopting these guidelines provides the following benefits:

  • Consistent Deployments: Parameterization supports seamless transitions between development, testing, and production.
  • Efficient Development: The LimitData parameter enables rapid iteration with smaller data samples.
  • Clear Organization: Structured folders simplify navigation and maintenance.
  • Version Control Compatibility: GIT mode allows semantic models to be version controlled without large data files.
  • Maintainability: Standardized query templates ensure consistency and simplify troubleshooting.
  • Optimized Performance: Import mode and parameterized timeouts balance speed and resource usage.
  • Built-in Documentation: Clear structure helps new team members understand the model.
  • Scalability: This approach supports growth as your data model expands.

By following these best practices, you can build semantic models in Microsoft Fabric that are robust, maintainable, and optimized for performance throughout the development lifecycle.