Data Modelling Approaches in Power BI
A data modelling approach refers to the way tables are organized and connected in Power BI to support reporting and analysis. The choice of modelling approach affects how easily users can understand the data, how relationships work, how DAX calculations behave, how quickly reports perform, and how easily the model can grow.
The three common approaches that can be considered when designing a Power BI model are:
- Flat Table
- Star Schema
- Snowflake Schema
Flat Table
Flat Table stores most of information in a single data
Instead of separating customers, products, dates, and sales into different tables, everything is placed into one large table
Advantages of a Flat Table
1. Simple to understand
Beginners can easily look at the table and understand the data
2. Easy for simple analysis
For small datasets, creating basic charts and calculations can be straightforward.
3. Few relationships
Because most information is already in one table, there is little need to create relationships between tables.
4. Use for simple datasets
If the dataset is small and does not contain much repeated information, a flat table may be perfectly adequate.
Disadvantages of Flat table
1. Data redundancy
The same customer, product, or category information may be repeated many times.
2. Larger dataset
Repeated information can increase the amount of data that Power BI has to store.
3. Poor scalability
As the business grows, the table can become extremely large and difficult to manage.
4. Difficult maintenance
Changing descriptive information may require changes across many records.
5. Less suitable for complex DAX
As the model becomes more complicated, calculations and filtering can become harder to manage.
Star Schema
The Star Schema is one of the most important and commonly recommended approaches for analytical models in Power BI.
In a star schema, a central Fact Table contains business events and numerical measurements, while surrounding Dimension Tables contain descriptive information
It is called a star schema because it looks like a star.
The middle table is fact table
The tables around it are dimension tables
How Star Schema Works
Imagine a supermarket.
Every time a customer purchases something, a sales transaction occurs.
That transaction can be stored in:
FactSales
The fact table may contain:
- Sale ID
- Customer ID
- Date ID
- Location ID
- Quantity
- Sales Amount
- Profit
However, the fact table does not need to store all the descriptive details about the customer or product.
Those details can be stored separately.
Advantages of Star Schema
1. Better Performance
Power BI can efficiently work with a fact table and relatively smaller dimension tables.
This is especially useful when dealing with large datasets
2. Simpler DAX
Measures become easier to create and understand.
For example:
Total Sales = SUM(FactSales[SalesAmount])
The same measure can then be analyzed by:
DimProduct[Category]
DimCustomer[CustomerType]
DimDate[Year]
DimLocation[County]
