Centralized Data Modeling using Power BI Templates

The centralized data modeling approach is really helpful in many practical scenarios. This article focuses on achieving it using Power BI Templates and sharing the centralized data model across the organization. In addition, we are going to highlight the importance of maintaining common reporting tables in a central location for better management.

CodingSight - Centralized Data Modelling using Power BI Templates

What is Data Modeling in DBMS?

Data modeling is the way you can arrange and link your organizational data (typically in the form of tables) for reporting and analysis.

In other words, it is the strategy of lining tables with each other to get useful information by following the standard practices and domain knowledge of the organization.

Traditionally, it stands for implementing the star or snowflake schema from the perspective of the data warehouse BI solution.

What is Centralized Data Modeling?

Centralized data modeling means a generic data model consisting of some commonly used tables, relationships, and hierarchies that are shared across the organization. These elements the starting point for Power BI report development to anyone eligible, interested, and capable to do so.

How to Load Data in Power BI?

Data can be loaded into the Power BI report by importing it from another source, such as the data warehouse database specially optimized for business intelligence operations.

Power BI Templates

A Power BI Template is a template/general structure that the Power BI user or developer can build to share across all members of the organization and create other reports by using it.

Power BI Template and Report: What is the difference? 

  • A Power BI report is a single standalone report that can you can publish for multiple business users to view.
  • A Power BI Template is a structure that contains some generally used tables and their relationships. These elements can be shared with other internal users or developers to build further reports. In some cases, you provide such templates to business users who are capable of building reports.

The major difference between the Power BI Template and the Power BI report is that the template does not hold any (imported) data. The report does.

In other words, the Power BI Template is a structure of the data (database) without the data ready for reporting. The Power BI report is a report that has both the structure and the data arranged as tables with relationships and hierarchies.

How to create Power BI Template from Report?

Yes, we can do it.  In fact, it is a standard way of creating a Power BI template.

Example of Data Modeling in Power BI Desktop

There does not seem to be any direct method to create a new Power BI Template in Power BI Desktop. So, we begin with a Power BI report that we’ll turn into a template by simply exporting it as a template file.

However, this sample export must be strong and solid enough to present a template for the organization’s centralized data modeling strategy.

Prerequisites

  1. Knowledge of the database and data warehouse basic concepts
  2. Understanding the Power BI reports
  3. Power BI Desktop must be installed on your PC/Laptop

The Sample Organization Business Requirement and Proposed Solution

Our sample organization is called Work From Home. It offers a smart range of products suitable for work from home to both private and business clients.

This company asked their report developer to build a centralized data model. This model is going to be used by internal users for making Power BI reports based on the commonly used data structure.

The report developer has come up with the idea of using the Power BI Template to form a centralized data modeling strategy.

Visualizing the Data Source

Let us assume the company has a fully functional data warehouse database consisting of the following tables:

  1. ProductsWFH
  2. ClientsWFH
  3. FACT Sales

Manually Populating Data Source Tables

We are going to manually create and populate the main tables of the data warehouse database to save additional steps required to import data from a database.

Open your Power BI Desktop and close the data import window that shows up straight away.

On the Home tab click Enter data:

Entering date manually to create and populate table

Create a blank table called ProductWFH by entering the following data:

ProductKeyNamePrice
1Desk100
2Chair50

This is illustrated as follows:

Create a blank table called ProductWFH by entering the following data

Similarly, enter data for another table called ClientWFH:

ClientKeyType
1Private Client
2Business Client

The output is:

Similarly, enter data for another table called ClientWFH

Finally, let us set up the FACT table as per standard practices of a data warehouse business intelligence solution. We’ll call it FACT Sales and enter the following data:

FactKeyClientKeyProductKeyAmount
111200
222100
31350

This is shown as below:

Finally, let us set up the FACT table as per standard practices of a data warehouse business intelligence solution. We'll call it FACT Sales and enter the following data

The Data Model Design Check

If we switch to the Model view by clicking the Model icon on the left navigation bar in Power BI Desktop, we’ll see that our data model has already been set up along with relationships.

This automated step took place because Power BI is smart enough to detect keys to build a relationship. As long as we follow this standard pattern, we do not need to join tables manually via keys:

Data Model built on the basis of the keys we used in our tables

There is one small potential problem in the above design, however. It is that the relationship between Product and Sales is one-to-one. We must resolve it.

Editing the Product (ProductWFH) and Sales (FACT Sales) Table Relationship

As we had input the data manually into the FACT table, the system assumed that Sales has a one-to-one relationship with Product automatically.

Switch to the Data view and click on FACT Sales to see this table:

Editing the Product (ProductWFH) and Sales (FACT Sales) Table Relationship

Unlike ClientKey (client), our ProductKey (product) occurs only once for each row. That is why Product and FACT Sales have a one-to-one relationship. But we know that one product may be ordered by many clients. This is our issue to fix.

Switch to the Model view, right-click on the relationship between ProductWFH and FACT Sales, and click Properties:

Switch to the Model view, right-click on the relationship between ProductWFH and FACT Sales, and click Properties

Change the Cardinality to One-to-many (1:*). Then select the Single Cross filter direction and click OK:

Changing the Cardinality between the two tables

Check the changes to see the updated tabular relationships:

Data Model Updated in Power BI Desktop

The Data Model is ready to become the standard centralized framework that we can share across the organization to build the Power BI reports. We see that the same client can place many orders, and the same product can be ordered by many clients.

Export the Power BI Report as the Power BI Template

Now is the time to turn our Power BI file into a Power BI template. On the File tab, click Export and select Power BI Template:

Export the Power BI Report as the Power BI Template

Add some description and click OK:

Add some description and click OK

We have named our template WFH-Centralized-PowerBI-Template and saved it to a centrally shared local or remote location:

We have named our template WFH-Centralized-PowerBI-Template and saved it to a centrally shared local or remote location

Now we can see the stored centralized data model in action.

Using the Power BI Template to build new Reports

Open a new instance of Power BI Desktop and import the Power BI Template:

Using the Power BI Template to build new Reports

Select the location where the template is stored:

Select the location where the template is stored

View the results:

Data Model retrieved from the stored Power BI Template

Congratulations! You have successfully built the centralized data modeling strategy in the form of a Power BI Template. Now you can share this template across your organization and create any Power BI report based on it.

Professional Life Tip

Remember that we can use some other strategies in professional life scenarios. For instance, we can centralize the business logic and data model using SQL Server Analysis Services or Azure Analysis Services. This way, we build and manage the data model which will be consumed by the Power BI report.

Things to Do

Try the following things to improve your skills further:

  1. Refer to the previous How to Create the Date Table in Power BI article and try adding the OrderDate column to FACT Sales and join it with the Date table
  2. Create a Date Table and add custom date hierarchies to the template (refer to Learn to Create and Use Custom Date Hierarchies in Power BI)
  3. Try creating another template for the sample database referenced in the article How and When to use Multiple Date Tables in Power BI
Haroon Ashraf

Haroon Ashraf

Haroon's deep interest in logic and reasoning at an early age of his academic career paved his path to become a data professional. He began his professional life as a computer programmer more than 10 years ago working on his first data venture to migrate and rewrite a public sector database driven examination system from IBM AS400 (DB2) to SQL Server 2000 using VB 6.0 and Classic ASP along with developing reports and archiving many years of data. His work and interest revolves around Database-Centric Architectures and his expertise include database and reports design, development, testing, implementation and migration along with Database Life Cycle Management (DLM).