“The goal is to turn data into information, and information into insight.” — Carly Fiorina.
Introduction
Power BI is much more than a visualization tool—it is a complete Business Intelligence (BI) platform that allows organizations to transform raw data into meaningful insights. However, the quality of reports depends heavily on how the underlying data is structured. A poorly designed model leads to slow reports, confusing DAX formulas, duplicated data, and difficult maintenance. A well-designed model, on the other hand, produces fast, scalable, and reliable analytics.
This article explains the principles of data modelling, fact and dimension tables, relationships, filter propagation, joins in Power Query, and concludes with the recommended design for real-world BI projects.
Data Modelling in Power BI
What is data modelling?
Data modeling in Power BI is the process of structuring, shaping, and connecting your data tables in the Model Data with Power BI tool to build a reliable semantic model for fast and accurate reporting. Instead of keeping everything in one spreadsheet, data is divided into logical tables that represent business entities such as customers, products, dates, and sales.
A strong data model improves:
- Performance by reducing unnecessary data duplication.
- DAX calculations because measures become simpler.
- Scalability when new data is added.
- Maintainability since tables have clear responsibilities.
- Report accuracy through consistent filtering.
A. Flat Table Schema
A flat table schema (also known as "One Big Table" or an un-pivoted/denormalized dataset) is a data modeling structure where all transactional data, attributes, and descriptive fields are consolidated into a single, wide table.A flat table stores every piece of information inside one large table.
Structure
Everything—customer names, products, locations, dates, and sales values—exists in one table.
Advantages
- Easy for beginners.
- Simple to import from Excel.
- No relationship configuration required.
Disadvantages
- Large file size.
- Repeated customer and product information.
- Slower performance.
- Difficult to maintain.
Best used when: Small datasets with limited reporting needs.
B. Star Schema
A Star Schema is a data modeling approach where a central fact table connects directly to multiple surrounding dimension tables
Structure
- One central fact table.
- Multiple surrounding dimensions.
- Relationships are usually one-to-many.
Advantages
- Excellent performance.
- Simple DAX.
- Easy filtering.
- Highly scalable.
Disadvantages
- Requires planning.
- Slightly more complex than a flat table.
Best used when: Sales, finance, inventory, healthcare, education, and enterprise BI.
C. Snowflake Schema
a data-modeling structure where dimension tables are normalized and split into multiple related sub-tables
Structure
Instead of storing category inside DimProduct, it is moved into DimCategory.
Advantages
- Reduces redundancy.
- Better normalized database design.
Disadvantages
- More tables.
- More complex relationships.
- Harder DAX and navigation.
Best used when: Very large enterprise warehouse environments.
2. Fact Tables and Dimension Tables
Understanding the operational dichotomy between Fact and Dimension tables is critical to structuring an efficient Star Schema
Fact Tables
Fact tables store the quantitative metrics generated by operational business events. They capture the observations, measurements, or numerical transactions of a business process.
Contents: Numeric values (Revenue, Quantity, Cost), timestamps, and alphanumeric Foreign Keys.
Grain/Granularity:This defines what an individual row in the table represents. For example, a row in FactSales might represent an individual item scan on a point-of-sale receipt (High Granularity), or it could represent aggregated daily sales per store (Low Granularity). Enforcing a consistent grain across a fact table is vital for valid data aggregation.
Examples: FactSales, FactOrder, FactTransactions.
Dimension Tables
Dimension tables store the descriptive attributes that provide context to your business metrics. They answer the "who, what, where, when, and why" questions of an analytical query.
Contents: Text-heavy rows containing descriptive context, hierarchies, and categorizations used for slicing, dicing, grouping, and filtering reports.
Examples: DimCustomer (Name, Segment, Email), DimProduct (Color, Brand, Category), DimDate (Year, Quarter, Month), DimLocation (City, State, Zip).
3. Relationships in Power BI
Relationships in Power BI are logical links between tables based on a shared key or column. They allow Power BI to combine data, calculate metrics, and propagate filters across multiple tables without needing to physically merge them.
A relationship tells Power BI how tables are connected. Without relationships, visuals from different tables cannot interact correctly.
Types of Relationships
• One-to-Many (1:) / Many-to-One (:1): The standard and recommended configuration. One table contains unique values (e.g., a Date or Customer dimension table), while the related table contains multiple matching records (e.g., a Sales or Orders fact table).
•** One-to-One (1:1)**: Both tables contain unique values for the key. Best practice is usually to merge these tables in Power Query instead.
• Many-to-Many (*:): Both tables contain multiple records for the same key. This often causes ambiguous filtering, duplicated totals, and performance issues unless managed with a bridge table.
Primary Key and Foreign Key
Primary Key is a column in a table where every single row has a unique value with no duplicates or NULL values while a foreign key is a column in a table that contains repeated values pointing back to a primary key in another table.
| Primary Key | Foreign Key |
|---|---|
| Unique values | Repeated values |
| Located in dimension | Located in fact |
| Identifies one record | Links to dimension |
the table above shows a comparison of primary and foreign keys
Active vs Inactive Relationships
A table may have multiple possible relationships but Only one relationship can be active. The inactive relationship can be activated inside DAX using USERELATIONSHIP() when needed.
4. Filter Direction
Relationships also determine how filters move through the model.Cross-filter direction in Power BI determines the pathway that filter contexts take when moving between related tables in a data model.
Types of Filter Directions
• Single Direction (Default): Filters flow in one direction only—from the "one" side (lookup/dimension table) to the "many" side (fact table). For example, selecting a product in a Product table filters the Sales table, but filtering the Sales table has no effect on the Product table.
• Both / Bi-Directional: Filters flow in both directions between the two tables. A filter applied to either table affects the other, allowing data in fact tables to filter related dimension tables.
5. Joins in Power Query
A join combines tables during data preparation using Merge Queries.
Joins in Power Query (Power BI) are performed via the Merge Queries feature, which combines two tables into a single table based on one or more common key columns.
Types of Joins in Power Query
Power Query supports six join types when merging tables:
•_ Left Outer (Default)_: All rows from the left table and matching from the right; unmatched right rows show as null.
•_ Right Outer_: All rows from the right table and matching from the left; unmatched left rows show as null.
• Full Outer: All rows from both tables, filling in null for missing matches.
• Inner: Only rows with matching values in both tables.
• Left Anti: Only rows existing exclusively in the left table.
• Right Anti: Only rows existing exclusively in the right table.
How to Do a Join in Power Query
- Launch the Power Query Editor via Transform Data in Power BI Desktop.
- Click Merge Queries (or Merge Queries as New) on the Home ribbon.
- Select the primary table and the secondary table from the grids. 4._ Select the matching key column(s) in both tables._
- Select the desired Join kind and click OK, then use the Expand icon to add columns from the related table.
6. Power Query Joins vs Power BI Relationships
Although both connect tables, they serve different purposes.
| Merge (Power Query) | Relationship (Model) |
|---|---|
| Physically combines data | Keeps tables separate |
| Happens during data loading | Happens after loading |
| Creates new columns | Creates filter paths |
| Used in ETL | Used for analytics |
A relationship allows Power BI to display Customer Name without duplicating data.
This is the preferred BI approach because:
- Less redundancy
- Smaller model
- Faster refresh
- Better scalability
Excessive merging creates wide tables similar to flat files, reducing the benefits of dimensional modelling.
7. Recommended Power BI Model
For most business intelligence projects, I recommend the Star Schema with one-to-many relationships and single-direction filter propagation.
Why the Star Schema is Recommended
• Smaller Size & Compression: Separating data into centralized fact tables and surrounding dimension tables reduces storage footprint and improves memory optimization.
• Optimized DAX Performance: Queries run faster because relationship paths are predictable and filter propagation is clean.
• Easier Management: Business logic, metrics, and report modifications are much simpler to maintain as your model scales.
Recommended Design
- Central FactSales table
- Dimensions: DimCustomer, DimProduct, DimDate, DimLocation
- One-to-Many relationships
- Single-direction filtering
- Use Power Query merges only during data cleansing or lookup enrichment
This design balances performance, clarity, flexibility, and long-term maintainability, making it the industry standard for professional Power BI solutions.
Conclusion
Effective Power BI reporting begins with an effective data model. Understanding star schemas, fact and dimension tables, relationships, filter direction, and Power Query joins enables analysts to build models that are accurate, scalable, and easy to maintain. While flat tables may suit small datasets and snowflake schemas benefit highly normalized warehouses, the star schema remains the best choice for most analytical solutions because it delivers excellent performance, simpler DAX, and cleaner report design.
Top comments (0)