What are Power BI Semantic Models?

12/29/2023

Learn about semantic models in Power BI, their components, modes, and best practices to create and manage them.

As a long-standing leader in the business intelligence space, Power BI's constant innovation paves the way for other BI tools. This is further demonstrated by Microsoft’s announcement of Fabric, which they view as "the first truly unified analytics solution based on one copy of data," of which Power BI is a single component.

As such, the term 'dataset' in Power BI no longer captures the full scope of its functionality. A dataset does not just contain data--it contains transformations, calculations, and relationships as part of a comprehensive and versatile data model (a semantic model, as it were).

What are Power BI Semantic Models?

A semantic model in Power BI can be considered a logical layer containing the transformations, calculations, and relationships between data sources needed to create reports and dashboards. A semantic model serves as the single source of truth for reports across an organization.

While a semantic model can be built using Power BI Desktop (in a .pbix file), it does not need to contain any visuals. Think of a semantic model as the last stop in the data pipeline before reports and dashboards are built. Thereafter, once you share a semantic model with other members of the organization, they can build any number of reports and dashboards from just that one semantic model.

Semantic models hide the complex technical details behind reports so that both technical and non-technical users can concentrate on analyzing the data and answering business questions. Sharing and reusability are two stand-out features of semantic models.

What Makes Up a Semantic Model?

Semantic models consist of several different elements:

• Data connections to one or more data sources, either imported, through DirectQuery, or as part of a composite model.
• Transformations that clean and prepare the data for reporting.
• Defined calculations and metrics based on business rules to ensure consistent reports built from the semantic model. This ensures clarity and avoids discrepancies between analyses and reports.
• Defined relationships between tables allow users to focus on designing reports without knowing the underlying database structures and data models beforehand.

Semantic Model Modes

Choosing the right mode when connecting to your data in Power BI is an important first step in creating a semantic model since each has benefits and drawbacks that you must be aware of.

There are three modes of semantic models in Power BI:

• Import mode
• DirectQuery mode
• Composite mode

Import mode

This fully loads the data into the Power BI (.pbix) file. Whenever the Power BI report refreshes, the Vertipaq storage engine compresses, optimizes, and stores the data to disk. This leads to fast performance and flexible design options for report creators. In addition, import mode allows semantic model creators to use the full set of the Power Query M language functions to transform and prepare data as well as DAX functions to create calculations and measures.

DirectQuery mode

This mode only stores metadata about the model structure rather than the data itself. When the model is queried (such as by rendering a visual), the data is retrieved from the underlying data source. This is especially useful with large volumes of data or when there is a business requirement for near real-time data in a report.

Composite mode

Composite mode is a combination of import and DirectQuery mode. This mode is useful when the power and performance of import mode are needed, along with the ability to view real-time data. A table can be set to Dual storage mode, allowing the Power BI service to pick a more efficient mode depending on the nature of the query.

5 Steps to Creating Power BI Semantic Models

Creating a semantic model involves the same steps you may already be following in the initial stages of developing reports using Power BI desktop.

Naturally, the exact steps you would follow when creating a semantic model depend on your business needs and goal for the model. However, on a high level, these five steps are commonly encountered when creating a semantic model:

1. Import or connect to the required data sources using import mode, DirectQuery, or composite models.
2. Clean and transform the data to make it useful for users. This involves removing duplicates, addressing missing data, cleaning up text-based data columns, etc. The exact transformation steps depend on your data's unique requirements.
3. Define relationships between your data tables using good data modeling principles like the star schema.
4. Create measures and calculations based on your unique business requirements.
5. Once you are happy with your semantic model, publish it to the Power BI service.

Leveraging Semantic Models for Effective Reporting

Once you have created and published your semantic model to the Power BI service, you can create a live connection to the semantic model from within another Power BI report. In fact, you can create multiple reports from a single published semantic model, thereby ensuring consistency and reliability across all reports.

Previously, it was common to find multiple Power BI reports based on the same dataset, each with separate data models. This presented a challenge since there could be large differences between the reports, and it could be quite difficult and time-consuming to figure out what was causing the difference and which report needed to be changed. Creating a single semantic model that is shared across several Power BI reports completely solves this problem.

Managing Semantic Models

Semantic models can be easily shared across workspaces, democratizing data and fostering a strong data culture. However, this also requires effective management of those semantic models with proper controls and policies that maintain the data's integrity, security, and quality.

Ownership and Permissions

Managing access control and permissions for semantic models in Power BI can be done by admins, workspace members (depending on their role), and semantic model owners (the owner is the one who created the semantic model).

Semantic model permissions can be set and managed in two ways:

• First, through direct access control to semantic models for individual people or entire security groups.
• Second, by managing how semantic models and their reports are shared with other users not in the workspace.

Once you publish a semantic model to the Power BI service, you (as the semantic model owner) and admins can set one of four levels of permission for the semantic model:

• Read: Allows users to access reports that use data from the semantic model and view model settings.
• Build: Enables users to build new reports and dashboards from the model.
• Reshare: Enables users to give other people access to the semantic model.
• Write: Allows users to republish and make some changes to the model, except for certain settings like adding or changing data source credentials, for example.
• Owner: While not strictly a permission, the creators of a semantic model become the owners in the Power BI service and have all the permissions above.

Security Considerations

Power BI administrators can also restrict how data is accessed, shared, and modified within their Power BI tenant. They can limit semantic model reuse either completely or partially based on security groups, impacting various functionalities like copying reports across workspaces, editing reports based on shared semantic models, and the visibility of semantic models in the Power BI service or Desktop.

Semantic model owners also control who can access and use the data in their semantic models. They can make semantic models discoverable by certifying or promoting them, which helps users and report creators identify which semantic models are designated as 'official' and considered high quality in the organization.

Role-level security (RLS) is enforced for semantic models and all reports built from them. RLS can affect users' ability to read the data from a semantic model if they have build or read permission unless the specific RLS role allows it.

Best Practices and Considerations

Creating the minimum number of semantic models needed and using them as the source for Power BI reports in an organization is the accepted best practice.

Over and above that, it is recommended for semantic models to be:

• Comprehensive: semantic models should contain all the elements required to answer relevant business questions. These elements should also accurately and logically reflect business systems and processes.
• Optimized and performant: semantic models should be optimized so that their refreshes, DAX calculations, and visual loads are fast, even when there is an increase in data volume.
• Secure: semantic models should contain the required security elements that prevent unauthorized access to the data (such as row-level security).
• Flexible: semantic models should be adaptable to changes in business requirements.

 

Source: datacamp.com