Flat Tables
Our interactions with spreadsheet expose us to flat tables which is one large table storing all the data. Every row store all the information of one record and it’s easier to read because everything is one place.
However, many values are repeated across rows, making the table inefficient. This increases storage requirements and makes updating and maintaining the data more difficult.
Imagine in a instance where a doctor record changes , and he had treated 1000 patients, how would we update his information across 1000 rows?
Power BI introduces schemas
Unlike spread sheets, Power Bi is designed to work with relational tables other than one flat table. It can store data across several tables while ensuring the relationship is maintained.
Data modelling/Normalization
In modelling, Power Bi enables us to move from one flat table to several related tables.
The table below represents part of a hospital's records. Each row stores information about a patient visit, including details about the attending doctor. Since the same doctor may attend to many patients, the doctor's information such as gender, specialty, and years of experience is repeated across multiple rows.
We can create a separate table to store the doctors' information and establish a relationship with the main table using the Doctor ID. Whenever we need additional information about a doctor, such as their specialty, we use the Doctor ID to reference the Doctors table and retrieve the required details.
As you can see, the hospital has only four doctors, so there is no need to repeat their information throughout the hospital records.
We can do this and create several tables while maintaining a relationship with the fact table using an ID, which in this case is the primary key.
Example: In the below table the doctors id is captured in the facts table to maintain the relationship.
Database normalization is organizing data so that the same information is not repeated unnecessarily.
The main table is called the fact table. It stores the primary records and contains the IDs used to link to the related dimension tables.
The separate tables created from the repeated information are called dimension tables. They store description information, such as doctor details, procedure information, or hospital departments. These dimension tables surround the fact table, forming a star known as a star schema.
Relationship Cardinality
This represents the relationship between different tables. How many columns in one table match in the other tables
One-to-One: Each record in Table A relates to exactly one record in Table B, there is often little benefit in keeping them separate
One-to-Many: One record in Table A is associated with multiple records in Table B. example 1 record from the doctors table represents many records of the doctor treating various patients
Many-to-Many: Records in both tables can relate to multiple records in the other.
By using data normalization and relationships with the right cardinality, Power BI enables data to be organized more efficiently than a flat spreadsheet. This reduces data redundancy, improves maintainability, and makes analysis and reporting more effective.
Top comments (0)