Setting up a Power BI Master Data Model

Mis à jour : 8 août 2020

Have you ever been exposed to problems of dubious or inaccurate information within your division?

For decades, analysts have been confronted with these kinds of problems.

For example: two employees create their calculations and when presenting the results, the figures differ and doubt sets in.

The result is that each of the people working on a subject will only work in silos, thus damaging the quality of the information. The latter will be considered unreliable and in some cases totally unusable. And this does not matter which tool is used: Excel, Table, Power BI, QlikView...This creates a consequent risk for the very credibility of the company.

The root of the problem is often a lack of security that causes an employee to appropriate data that he or she should not have.

In any company where the culture of analytics is important, the most delicate thing is not to define what you want to drive but how you want to drive it.

The governance of your data, more commonly known as data governance, is a fundamental point to guarantee the control and quality of your information.

How to have a single centralized data source?

Power BI is an extremely relevant tool to answer this type of problem. Indeed, the solution that we will present below consists in the implementation of a "master" data model. This corresponds to the creation of a data model integrating and centralizing the Key Performance Indicators (KPI's) within this data model.

The strength of a "master" model, presented here, is that you will control all the data of your company. Because it will reside on a single Power BI model, through which you can easily generate multiple custom reports.

With Power BI Desktop, users have unlimited access to create new reports and to use the various measures and dimensions offered by the tool without modifying the templates.

Standardize your management rules and centralize your KPIs

The company's KPIs are calculated upstream and each user accesses them through the same management rules previously established, and thus have real control over the management of your data, even when they reside in Self-Service mode.

Importantly, users will always be able to create new measures calculated in DAX (Data Analysis Expression), however, these measures will only reside in their reports and therefore will not modify the structure of the original "master" model.

How to avoid data redundancy in terms of storage?

Since each new user connects directly to this master model through Power BI Service, there is no duplication of data.

On Power BI Desktop, when a user connects to a data source outside of Power BI Service, he or she has access to two data import modes:

- The Direct Query mode: When you connect to a Direct Query model, you connect directly to the model and no query is defined. The entire external model appears in the field list.

- The Import mode: In Import mode, you import the data, Power BI keeps it in a cache and your file is not linked to the source. To update your file, you have to click on the refresh button and Power BI will import the data again.

The problem encountered with the import mode (Import) is that your users end up with their own model and your data is duplicated a multitude of times with the risk of losing control over its quality and meaning.

Delegating work within the team

Instead of each person working on a separate data model, this method makes it easier to delegate tasks. For example, one person may be responsible for the preparation of data (ETL) for the creation of KPIs. Others can focus on creating visuals.

Connect directly to Excel

Excel can connect directly to Power BI Service data sources. Your users will be able to connect to Excel just as they would with Power BI Desktop. They will therefore be able to analyze their KPIs naturally in Excel without having to change the analysis tool.

Control the security of your data

This centralized data model allows you to establish security on this one and only model and to keep this security in each of the reports that will be created thereafter. Otherwise, you will have to duplicate your security for each Power BI report published online. Security is therefore strengthened and centralized.

Power BI Premium allows you to initiate self-service BI at the corporate level.

If you are concerned about the size of your data model, Power BI Premium meets the corporate need by allowing 100 TB of storage capacity.

How is this possible?

These 100 TB will still be shared through small Power BI data models, each with a maximum capacity of 10 GB per PBIX. This 10 GB is actually equivalent to 100 GB of data compressed by the In-Memory Vertipaq* engine.

How to create a "master" model in Power BI service, step by step :

When you create the master template on Power BI Desktop, you do not need to create reports on the initial PBIX. You use this file as the basis for storing your template data only.

Step 1: Import your data in "Import" mode into Power BI Desktop. Power Bi Desktop is the development tool to model your data and define your management rules (KPIs).

Step 2: Once the data model is created, publish the "master" model on Power BI Service ( In the Power BI portal, you will then see your imported data model.

Step 3: Your users can now log in with Power BI Desktop on Power BI Service (native connector) or directly on Excel to create their custom reports.

Step 4: Once custom Power BI reports are created, users can publish their reports within App Workspace to collaborate more easily with colleagues. They can also publish their reports in Power BI Apps to share with all internal/external users in the company.

241 vues1 commentaire

Posts récents

Voir tout