23 Dic 2024 Why You Should Avoid Merging Business Areas in A Power BI Semantic Model

Creating semantic models in Power BI empowers users to build their own reports whilst ensuring standardised data and seamless connectivity to consistent data sources. Semantic models provide a solid foundation for reporting workflows by defining key metrics, applying updates post-deployment, and managing security roles. Designed with reusability in mind, they simplify tasks for both technical teams and business users.

However, a frequent request – and challenge – is to combine multiple semantic models into a single, unified one. For instance, business users may have been generating some reports from a Recruitment semantic model and others from an Employee Performance model, but when they want to merge these into a single object to consolidate their reporting capabilities, the process gets tricky and needs careful planning.

When designing a semantic model for self-service reporting, the focus is on defining a specific business area and creating a schema that captures its events and data, unlike designing a report from scratch. While combining two distinct areas into a single semantic model can enable users to generate more complex and integrated reports, this approach comes at a price.

Disadvantages of Combining Multiple Semantic Models Into One

1. Usability: Contrary to what we may think, merging multiple business areas into a single semantic model will reduce its usability. Let’s think about the audience. A model that integrates two business areas will only be accessible to users that have both the required knowledge and permissions for both sets of data. The more areas we combine, the narrower the potential audience becomes. On top of that, if access is granted to users for just one of the areas, Object Level Security (OLS) must be applied, again increasing the model’s complexity and making it harder to maintain.
2. Ownership: As we mentioned above, combining multiple areas reduces the audience size, and we need to consider that the owner of the Power BI model must be part of this smaller group. From a technical perspective, fewer developers are likely to have the expertise to create, test, and maintain such a comprehensive model. This means we will likely have multiple contributors working on the same Power BI solution, but none of them will be able to see the entire picture.
3. Design: Usually, when we create a semantic model, we try to keep it simple and use a star schema. However, when combining multiple models the star schema no longer applies, and relationships between tables become harder to manage. Although we can use the DAX function USERELATIONSHIP() for specific measures, it won’t prevent the model from becoming more complex, and any future modifications will become increasingly difficult to implement.
4. Size: From a business point of view, a combined semantic model may potentially have hundreds of fields to choose from when users want to build a report. Similar field names can create confusion, and while adding field descriptions can help, the resulting model would still mean extra effort for users to create reports. And from a technical point of view, the larger number of tables and rows increases the model size, which leads to longer refresh times if we use Import mode.
5. Dependencies: There’s a greater risk of dependency issues with combined models. For instance, a refresh failure in one source table—perhaps tied to a small business area—can cascade, rendering the entire model inaccessible. This can lead to situations where, for example, we cannot analyse Employee Performance data because a table from Recruitment did not load properly, and such interdependencies are difficult to justify to stakeholders.
6. Different refresh times: Different business areas often have different requirements for data freshness and refresh frequency. With different refresh schedules for each area, business users might struggle to determine which datasets are up-to-date.

A Few Alternatives

Despite the disadvantages we’ve discussed, there are cases where a business needs an analysis covering multiple areas. In such scenarios, relying on Power BI’s self-service capabilities may not be the optimal solution. Instead of attempting to create a unified semantic model for a broad audience—many of whom may not be Power BI experts—it is often more practical to develop an ad hoc Power BI report with a dedicated semantic model. By keeping the report and the self-service model as separate objects, we can ensure that the purposes of both deliverables remain distinct and significantly mitigate the challenges we’ve outlined in this blog post.

If the objective of combining areas is to offer an overall view to a wider audience whilst keeping the areas independent, it might be a good option to create individual reports for each area and combine them into a Power BI dashboard. This approach, together with Row Level Security in the underlying semantic models, offers users a unified view of multiple areas. However, it’s important to note that they will not be able to cross data between models.

In conclusion, Power BI is a versatile and powerful tool that offers business users the ability to create their own reports. However, we need to maintain clean, well-structured solutions to ensure that the model has long-term usability and adoption.

If you need assistance with Power BI Semantic Models, optimising your data strategy, or implementing best practices, we’re here to help! Contact us now to see how we can help you to unlock the full potential of your Power BI environment.