Data modelling is critical in Power Bi and it determines how effective your analytics work will be.
What is it?
Well, data modelling is the process of structuring data into tables and connecting those tables so they can work together.
This helps the data in your data model easy to explore. Think of it this way, instead of throwing all your clothes on one drawer, you have different drawers for your shirts, trousers, jeans and even vests.
Good data modelling aids in curing this headache.
In Excel, the data is stored in one table. In Power BI, this data is stored in several tables which are connected. The tables are of two types:
- Fact table.
- Dimension table.
Fact Table
A fact table contains the quantitative data such as sales, revenue. They contain measurable business events.
Each row in this table is an event/transaction.
For example, a data set showing retail business with several stores will have a fact table with entities such as Total sales, Quantity, Unit price and OrderID.
Fact tables are basically used for calculations. They have keys that link it to the dimension table
Dimension Table
They contain descriptive data that describes the data in the fact table and helps you in slicing and filtering your fact table.
For example, Customer dimension(contact ID, Gender), Product dimension(product ID, name), Date dimension(date, day, month, year, quarter)
A fact table contains your data and changes frequently while a dimension table describes this data and is updated less frequently
A fact table and dimension(s) table are organized in the data model using a schema
SCHEMA
This is a structure/shape of how your data is organized in a database or models.
It answers questions such as:
- How many tables do you have in your model?
- Which tables are connected?
- What kind of relationships exist between them?
Star Schema
A central fact table is directly connected to several dimension tables. If you open it in a model view, it looks like a star.
Examples; FactSales ( Orderdate, Salesdate, Revenue, Cost)
They have a simple structure, easy to understand, scale and gives better performance in Power BI due to fewer joins making it the preferred choice.
Snowflake Schema
Just like the Star schema, the fact table is connected to the dimension tables. However, the dimension tables are further normalized into multiple related tables.
The core of a snowflake schema is just a star schema.
For example, a Customer dimension table is further divided into another dimension such as the City( CityID, Name, County).
Star schema is preferred for being simple and having faster analysis performance while snowflake schema is preferred in complex data environment as they reduce redundancy.
Relationships in Power BI
Relationships in Power BI are crucial as they indicate which tables are connected. This is done using a common column (Key) between the tables.
This is helpful when doing DAX calculations.
Let's say we need to know the order a particular customer made. We will need a common column between the customer table and the order table such as CustomerID.
This column will be used to create a relationship between Customer table and Order table by Keys.
Keys
Relationships are built on Keys.
In the Order table, each order will have it's own ID that can never be repeated. This is called a Primary key
There are also Foreign keys. In the same Order table, we have the customer ID which tells us which customer made an order. However as this key is coming from a foreign table which is the customer table, it is called a Foreign key.
Cardinality
Let's say a customer has made several orders. This customer's customer ID will appear once in the Customer table but several times in the Order table.
If the customer makes one order, the customer ID will appear once in the Customer table and once in the Order table
These are different relationships between the tables (Cardinality)
Types of Cardinality
One to Many (1:M or 1-*)
A single record in one table is connected to many records in another table.
For example, one customer having many orders.
The reverse is true for Many to One relationship.
One to One (1:1 or 1-1)
A single record in one table is connected to a single record in another table.
For example, a National ID can only belong to one person.
Many to Many (M:M or M-M)
Multiple rows are matching in both tables.
For example, students can enroll in multiple courses and each course will have multiple students.
Many to Many relationships are troublesome as a lot is happening at the same time.
All these steps are done in Power BI to have good modelling as it forms the foundation of our analysis in Power BI. It makes our DAX calculations precise and correct by removing ambiguity in relationships making our data easier to understand.
I hope you now have a strong understanding about data modelling in Power BI and its importance.
Thank you for reading.
Top comments (0)