When I first heard of data modelling in Power Bi it sounded like such a complex concept that came off intimidating, but fear not. In this article, I shall walk you through data modelling and its key concepts, schemas, and the different types of schemas, and lastly, the importance of data modelling.
Definitions
Data Model
A data model is a collection of data, calculations, and formatting rules that are combined to create an object that can be used to create a better understanding of an existing datasetas the data can be explored and queried.
Data Modelling
It involves the combination of data and measures that becomes your data model. This is done through creating a visual representation of either a whole information system or parts of it to communicate connections between data points and structures.
Components of Data Modelling
Tables
They are objects used to store and organize data. For a data model in Power BI we have different types of tables.
1. Fact table
Typically, the main table in a Power BI data model consists of quantitative data about different processes.
eg: product prices in a sales dataset
2. Dimensional Tables
This stores qualitative attributes about business or different processes.
eg Name of the product, location of sale
Relationships
Relationships can be described as the connection of different tables in one model by use of one common column. Relationships are important because they allow analysis of data from different tables.
In data Modelling in Power Bi we have different types of relationships
1. One-to-one relationship
This is when a single entity in one table matches exactly 1 entity in another table.
eg a sales table linked to an inventory table through a column like product_id
2. One-to-many relationships
A single entity in one table matches multiple entities in another table.
eg. customer table connected to a sales table as a single customer may have multiple sales.
3. Many-to-many relationship
Multiple rows in one table relate to multiple rows in another table.
NB: A many-to-many relationship is complex and is not recommended for data modelling.
Schemas in Data Modelling
This is the organization of data within a data model, which is the layout of tables within the the datamodel ie the fact tables and the dimensional tables in the model view.
The primary types of schemas in data modelling include:-
Star schema
This is the most efficient and preferred structure in Power Bi. It entails a central fact table surrounded by dimensional tables.
Snowflake schema
This is a data modelling approach where dimensional tables in Power Bi are broken down into multiple related tables.
eg, separating sales persons by regions
The table below shows a comparison between a snowflake schema and a star schema.
NB
Even though a snowflake table may be useful in reducing data repetition, it's however, less ideal due to slow performance and complex DAX calculations. A star schema is most preferred instead.
Importance of Data Modelling
- It ensures accuracy and integrity in data modelling.
- Enhances optimization of performance.
- It improves scalability and the overall performance of the data model.
Top comments (0)