Introduction
Power BI is a business analytical service that connects,cleans and transforms raw data into visial reports and dashboards that are interactive.In Power BI, data schemas define how tables are structured and related within a data model.
While data modelling is the process of analyzing and defining all the different data types your business collects and produces, as well as the relationships between those bits of data,schemason the other hand refers to the structure and organization of data within a data model.
Data modelling in Power BI
Data modelling involves creating visual representation from data collected.It helps in supporting exploration,ensure your Power BI can solve large volume of dat effectively and promotes accurate reports among other things.It involves organizing data,establishing relationship and selecting most appropiate schema.
Fact table
This hold data that are quantifiable or measurable for analysis.Examples include sales amount,quantity,profit and discount.
Dimension table
This hold text data that are descriptive.Examples include:
- customer name
- date
- location
Relationships in Power BI
Relationships describe how tables are related in a data model. Power BI uses these relationships to merge data from different tables during analysis.
Common Types of Relationships
One-to-Many (1:*) – Most common (e.g., one customer → many sales)
One-to-One (1:1) – Rare
Many-to-Many (:) – Used cautiously
Relationship Direction
Single direction (recommended for simplicity and performance)
Both directions (can cause ambiguity if misused)
Well-defined relationships are essential to ensure correct calculations and avoid incorrect aggregations.
Schemas in Power BI
Star schemas
It is simple and commonly used schema in warehousing that consist of central fact table surrounded by dimension tables, forming a star-like pattern.
Star schemas are ideal for straightforward reporting and querying. They are efficient for read-heavy operations, making them suitable for dashboards and summary reports.
Snowflake schema
This is a normalized version of the star schema and unlike the star schema ,dimension tables are further divided by splitting dimension tables into multiple related tables, resulting in a more complex structure that looks like a snowflake.
It is used in scenarios requiring detailed data models and efficient storage. They are beneficial when dealing with large datasets where data redundancy needs to be minimized.
Galaxy schema
It involves multiple fact tables that share dimension tables, creating a complex, interconnected data model.Galaxies schemas are suitable for large-scale enterprise environments where multiple related business processes need to be analyzed.
Implementing Schemas in Power BI
Creating a Star Schema
Set Up Fact and Dimension Tables: Identify and create the central fact table and surrounding dimension tables.
Link Tables: Establish relationships between the fact table and dimension tables using foreign keys.
Optimize for Performance: Index key columns and use efficient data types to enhance query performance.
Implementing a Snowflake Schema
Normalize Dimension Tables: Split dimension tables into related sub-tables to reduce redundancy.
Create Relationships: Define relationships between sub-tables and the main dimension tables, ensuring referential integrity.
Optimize Storage: Use appropriate storage and indexing strategies to manage complex joins efficiently.
Setting Up a Galaxies Schema
Identify Fact Tables: Determine the various fact tables needed for different business processes.
Share Dimension Tables: Create shared dimension tables to link multiple fact tables.
Ensure Efficient Querying: Design the schema to support complex queries and optimize performance through indexing and data partitioning.
Top comments (0)