Power BI

Getting the right number of tables for a good data model

Data modeling is one of the core topics covered in Power BI Advanced training in Sydney and Power BI Advanced training in Melbourne. Data Model refers to the abstract model that demonstrates the logical structure of data and the relationships that exist in the data. In Microsoft Power BI, Data Model refers to everything that is loaded from query. A data model consists of Two or more tables related to each other. For example, Let’s consider the data model in the image. The sales table in the center is related to each of the other tables, i.e., Products, Customers, Territory and Calendar.
Data Model – Common Issues And Solution | Power BI Training In Sydney | Power BI Training In Melbourne

A typical data model consists of:

Fact Table / Data Table

If we record a value of an event or something that happened, it’s called a fact and the complete record is known data table in the BI world. Fact tables record measurements or metrics for a specific event. They are usually number heavy, thin (less no. of columns) and long (large no. of rows). They also contain foreign keys to relate to dimensional data sets.

Dimension Table / Lookup Table:

Dimension or lookup table have the additional information. Dimension tables are text heavy. They usually have a relatively small number of records compared to fact tables, but each record may have a very large number of attributes to describe the fact data. Dimension tables have unique identifiers called primary keys

Fact Table vs. Dimension Table

In a Data model, a fact and dimension table are linked by many to one relationship. Dimension table exist on the 1 side of relation and the fact tables exist on many side of the relationship. Both of these tables have a common column that constitutes their relationship. In the dimension table, this column is the primary therefore holds uniquely identified values. Whereas, in the fact table, this column acts a foreign key therefore holds duplicates.

Getting Started

For this chapter, we will disable the auto detection of relationships by Power BI. This is done to create an environment where you can dive deeply into model creation. Disabling relationship detection is done purely to enhance concept building and is not preferred in ideal conditions.

To disable the auto detection of relationship by Power BI:

  1. Click on File Tab in the Ribbon.
  2. Click on Options and Settings.
  3. Click on Options.

A pop-up menu appears.

Data Model – Common Issues And Solution | Power BI Training In Sydney | Power BI Training In Melbourne

4. Click on Data Load in the Current File Section.

5. Uncheck all the options in the Relationship Section.

6. Click ok.

Importing Data

The Importing Data phase of a data modeling involved procuring the right tables that can answer the business question at hand.

To Gather Data for Data Model:

  1. Click on the check box at the left of Product Sheet.

It consists of three columns addressing the product details, i.e., Product Code, Product and Product Type. Each Product has a distinct Product Code and falls under only one Product Type. 

  1. Click on the box at the left of Product Line Prod Type Sheet.

Each Product Type is a subcategory of Product Line. This sheet records the subcategory to category mapping. 

  1. Click on the box at the left of Product List Sheet.

Product list contains the details of each Product and the list price associated per unit.

  1. Click on the box at the left of Sales Person Per Product Type Sheet.

This sheet contains details of Sales Person associated with the Sales. There exists a many to many relationships between the sales person and the product. 

  1. Click on the box at the left of Sales Sheet.

Sales sheet contains transactional Data for the Sales.

  1. Click Load.

Now you have five queries loaded in your Query Editor. At this stage your data model should contain five un connected tables as shown in the image below.

Data Model – Common Issues And Solution | Power BI Training In Sydney | Power BI Training In Melbourne

Conclusion:

Data model consists of the right number of tables with the right relationship between them. A typical data model consists of a fact table and a dimension table. In this blog post, the concept of data model is introduced and the first two stages in the creation of a perfect data model have been discussed. This blog is from the archive focused on various data modeling problems and solutions.

Back to list

Leave a Reply

Your email address will not be published. Required fields are marked *