What is data modelling?
It is the process of connecting different data sources, defining how they relate to one another and analyzing them so that the y can be analyzed differently.
What is a schema?
Is the visual pattern or layout of your model. It is the blueprint of how your Fact and Dimension tables are arranged and linked.
Types of Schemas
1. Star schema
In this set up, a single Fact table sits in the center, surrounded by Dimension Tables that connect directly to it.
Why it works;
- Performance: Power BI is optimized for this structure. It minimizes the number of joins required to filter data
- Simplicity: Its intuitive for users to navigate.
- Usability: Data Analysis Expressions measures are easier to write when the model is a star.
2. Snowflake schema
A snowflake schema occurs when a Dimension table is further normalized meaning it connects to another dimension table rather than directly to the Facts table.
Snowflake schema saves a bit of storage space by reducing the data redundancy, it hurts performance in Power BI.
Managing Relationships
Relationships are the pipes through which filters flow.
Rules followed to keep your model healthy:
- Cardinality; This is where one row in a dimension table matches to many rows in a Fact table.
- Cross filter direction; Keep this set to Single by default. Setting it to bi can create circular dependencies and cause numbers to calculate in ways you didn't intend.
- Active vs Inactive; You can only have only one active relationship between two tables. If you need to connect a Sales table to a Date table via both "Order Date" and "Ship date", use an inactive relationship for the second one and activate it using the DAX function
USERELATIONSHIP
Why Good Modelling is Critical
- Speed: Power BI uses a "columnar" engine. It can scan millions of rows in a Star Schema instantly, but it struggles with wide, messy tables or complex "snowflake" chains.
- Accuracy: If your relationships are set up incorrectly (e.g., using "Many-to-Many" when you should use "One-to-Many"), your totals and averages will be wrong.
- Simplicity: A clean model means you don't have to write 20-line formulas to get a simple Sales-Year-Over-Year calculation.
Top comments (0)