Introduction
Power BI is a business intelligence tool that enables the conversion of raw data into reports and dashboards. However, the quality of the Power BI report is influenced by how the data is prepared and structured in advance.
Data modelling is the act of organizing the tables, columns, keys, and relationships in such a way that allows Power BI to process how the various data parts are connected. A correctly constructed model brings better performance of the report, eases calculations in DAX, minimizes redundancy of data, improves scalability, and simplifies understanding of the solution.
The article describes key modelling schemes, fact and dimension tables, relationships, direction of filters, and joins in Power Query. It also illustrates the difference between the functions of merging tables in Power Query and creating relationships in the Power BI model.
1. Data Modelling in Power BI
What is Data Modelling?
Data modelling in Power BI involves designing the structure of tables and defining how they relate to one another. Instead of treating every dataset as one large table, related information can be separated into logical tables and connected using keys.
For example, a sales system may contain:
- Customer information
- Product information
- Date information
- Location information
- Sales transactions
These can be organized into a model where a central FactSales table connects to several descriptive dimension tables.
A good data model:
- Improves report and query performance
- Makes DAX measures easier to write
- Reduces unnecessary data duplication
- Makes relationships and filter propagation easier to understand
- Allows the model to scale as more data is added
- Makes reports easier to maintain
- Reduces the risk of incorrect calculations
1.1 Flat Table
A flat table stores all information in one table. All data is kept in one single grid or file without links to other tables; it lacks relational database connections, which often leads to repeated or redundant information.
Example
| Sale_ID | Date | Customer | Product | Category | City | Quantity | Sales |
|---|---|---|---|---|---|---|---|
| 1001 | 01/09/2026 | John | Laptop | Electronics | Nairobi | 1 | 80,000 |
| 1002 | 02/09/2026 | Mary | Mouse | Electronics | Mombasa | 2 | 3,000 |
| 1003 | 03/09/2026 | John | Keyboard | Electronics | Nairobi | 1 | 4,000 |
Structure
FLAT TABLE
┌───────────────────────────────────────────┐
│ Sales │
├───────────────────────────────────────────┤
│ Sale_ID │
│ Date │
│ Customer │
│ Product │
│ Category │
│ City │
│ Quantity │
│ Sales │
└───────────────────────────────────────────┘
Advantages
- Simple to understand
- Easy to import
- Convenient for small datasets
- Requires fewer relationships
- Suitable for simple analysis
Disadvantages
- Creates significant data duplication
- Can increase model size
- Changes to customer or product information may need to be repeated across many rows
- Can make data maintenance difficult
- Less suitable for large datasets
Appropriate Use
A flat table can be appropriate for a small, simple dataset where the data is already clean and there are relatively few repeated descriptive values.
For large business intelligence solutions, however, a flat table is generally less desirable than a properly designed dimensional model.
1.2 Star Schema
A star schema consists of a central fact table surrounded by dimension tables.
Structure
┌───────────────┐
│ DimCustomer │
│ CustomerID PK │
└───────┬───────┘
│ 1
│
│ *
┌───────────────┐ ┌────▼───────┐ ┌───────────────┐
│ DimDate │───────►│ FactSales │◄───────│ DimProduct │
│ DateKey PK │ 1:* │ SaleID │ 1:* │ ProductID PK │
└───────────────┘ │ CustomerID │ │ ProductName │
│ ProductID │ │ Category │
│ DateKey │ └───────────────┘
│ Quantity │
│ Sales │
└─────▲──────┘
│
│ *
┌───────┴───────┐
│ DimLocation │
│ LocationID PK │
│ City │
│ Region │
└───────────────┘
The fact table sits at the center, while dimensions provide descriptive information; the fact table has columns that link it to the other tables.
Advantages
- Excellent for Power BI reporting
- Simple and intuitive structure
- Generally provides good query performance
- Makes DAX easier to understand
- Minimizes unnecessary duplication
- Makes filtering predictable
- Easy to extend with additional dimensions
Disadvantages
- Requires careful modelling
- Dimension tables may contain some repeated information within their own hierarchy
- Requires appropriate primary and foreign keys
Appropriate Use
The star schema is particularly appropriate for:
- Sales reporting
- Financial reporting
- Inventory analysis
- Customer analytics
- Production reporting
- Operational dashboards
It is generally the preferred structure for Power BI semantic models, sitting between raw database tables and end-user tools, and turning complex schemas and SQL into everyday concepts like "Revenue" or "Customer."
1.3 Snowflake Schema
A snowflake schema extends the star schema by dividing dimensions into related tables. For example, instead of storing Product, Category, and Department in one DimProduct table, you can separate them.
Structure
┌───────────────┐
│ DimCategory │
│ CategoryID PK │
│ CategoryName │
└───────▲───────┘
│ *
│
1 │
┌───────┴───────┐
│ DimProduct │
│ ProductID PK │
│ CategoryID FK │
│ ProductName │
└───────┬───────┘
│ *
│
1 │
┌───────▼───────┐
│ FactSales │
│ ProductID FK │
│ CustomerID FK │
│ SalesAmount │
└───────────────┘
Advantages
- Reduces duplication within dimensions
- Useful for complex hierarchical data
- Can represent normalized source systems more closely
- Useful where dimensions are very large or have complex structures
Disadvantages
- More tables and relationships
- More complex filter paths
- DAX and report development can become less intuitive
- May require additional joins during analysis
- More difficult for beginners to understand
Appropriate Use
Snowflake schemas can be appropriate where dimensions have complex hierarchies or substantial repeated data. However, unnecessarily snowflaking a Power BI model can add complexity without providing enough benefit.
Schema Comparison
| Feature | Flat Table | Star Schema | Snowflake Schema |
|---|---|---|---|
| Number of tables | One | Multiple | Multiple |
| Simplicity | Very high | High | Lower |
| Data duplication | High | Low | Very low |
| Relationships | Minimal | Simple | More complex |
| DAX simplicity | Moderate | High | Lower |
| Scalability | Limited | Excellent | Excellent |
| Power BI suitability | Small models | Excellent | Selective |
| Maintenance | Difficult as data grows | Easy | More complex |
2. Fact Tables and Dimension Tables
A dimensional model normally consists of fact tables and dimension tables.
Fact Table
A fact table records measurable business events. For example, FactSales may contain:
- Sale_ID
- Date_Key
- Customer_ID
- Product_ID
- Location_ID
- Quantity
- Sales_Amount
- Cost_Amount
- Profit_Amount
Fact tables normally contain foreign keys linking them to dimensions and numeric values that can be aggregated. Examples include:
- FactSales
- FactOrders
- FactTransactions
- FactInventory
- FactProduction
Dimension Table
A dimension table contains descriptive information used to analyze facts.
- Descriptive Attributes: Stores qualitative data like names, categories, and locations (answering who, what, where, when)
- Primary Key: Uses a unique identifier for each row to link with foreign keys in a fact table
- Denormalized Structure: Built wide and flat with fewer rows than fact tables to speed up read performance
Examples include:
DimCustomer
- CustomerID
- CustomerName
- CustomerType
- Gender
- City
DimProduct
- ProductID
- ProductName
- Category
- Brand
- UnitPrice
DimDate
- DateKey
- Date
- Month
- Quarter
- Year
DimLocation
- LocationID
- City
- County
- Region
- Country
Facts answer questions such as: How many? How much? How often?
Grain or Granularity
The grain of a fact table describes exactly what one row represents.
For example, one row in FactSales might represent one product sold in one sales transaction. This distinction is extremely important:
- If one row represents an individual product transaction, the table might have:
SaleID | ProductID | CustomerID | Quantity | SalesAmount. - If one row represents an entire customer order, the grain is different.
A clearly defined grain prevents incorrect aggregation and double-counting.
Practical Star Schema Example
A retail company could use:
DimCustomer
│
│ 1:*
▼
DimDate ───────► FactSales ◄────── DimProduct
▲
│
│ 1:*
│
DimLocation
The central FactSales table stores transactions, while the dimensions provide context. For example, selecting Category = Electronics from DimProduct filters FactSales and allows the report to calculate total electronics sales.
3. Relationships in Power BI
A relationship defines how two tables are connected through common columns. For example:
DimCustomer.CustomerID
│
│ 1:*
▼
FactSales.CustomerID
CustomerID is unique in DimCustomer but can occur many times in FactSales. This allows Power BI to determine which sales belong to which customer; it acts like a link between the tables.
3.1 One-to-Many Relationship (1:*)
This is the most common relationship in a Power BI star schema. One record in the dimension can correspond to many records in the fact table.
Example
DimCustomer FactSales
CustomerID 1 ─────────── * CustomerID
1001 1001
1002 1001
1003 1002
Customer 1001 can have many sales transactions.
When to Use
Use 1:* when:
- The dimension contains unique values
- The fact table contains repeated foreign-key values
This should normally be the default relationship in a star schema. In short: a single record in one table (the parent) can connect to multiple records in another table (the child), but each child record links back to only one parent.
3.2 One-to-One Relationship (1:1)
A one-to-one relationship means each record in one table corresponds to exactly one record in another table. It occurs when one record in a database table or entity connects to exactly one record in another table, and vice versa.
Example
EmployeeDetails EmployeeSecurity
EmployeeID 1 ─────────── 1 EmployeeID
When to Use
It may be useful when information about the same entity has been intentionally split into two tables. However, a 1:1 relationship should not be used simply because tables can be joined — in many cases, combining the information into a single dimension is simpler.
3.3 Many-to-Many Relationship (:)
A many-to-many relationship occurs when multiple records in one table can relate to multiple records in another.
Example
Students * ───────── * Courses
A student can take many courses, and a course can have many students.
In dimensional modelling, many-to-many relationships should generally be handled carefully. A bridge table is often preferable:
DimStudent BridgeStudentCourse DimCourse
1 * * 1
│──────────────────┘ └──────────────────│
The bridge table resolves the many-to-many association. In a standard star schema, a single row in a fact table links to only one row in a dimension table (a many-to-one relationship). However, real-world business processes often break this rule. A bridge table sits between the tables, containing pairs of foreign keys or group keys that map multiple dimension items to a single event or group.
Primary Keys and Foreign Keys
A primary key uniquely identifies a record. For example:
DimCustomer
| CustomerID |
|---|
| 1001 |
| 1002 |
| 1003 |
CustomerID must be unique.
A foreign key references the primary key of another table — a field in one table that points to the unique ID (primary key) in another table to connect them.
How It Works
- The Link: It builds a bridge between two lists of data so the database knows they belong together.
- The Rule: It acts like a security guard, stopping you from adding data to a table if it does not match a real ID in the connected table.
FactSales
| CustomerID |
|---|
| 1001 |
| 1001 |
| 1002 |
| 1003 |
| 1001 |
The same CustomerID can appear many times because a customer can make many purchases.
Referential Integrity
Referential integrity means that foreign-key values should correspond to valid records in the related dimension. For example, if FactSales contains CustomerID 1005 but DimCustomer does not contain CustomerID 1005, the model has a referential integrity problem.
Active and Inactive Relationships
An active relationship is normally used automatically when Power BI propagates filters between tables. An inactive relationship exists but is not used automatically.
For example, a FactSales table might contain both OrderDate and DeliveryDate. DimDate could have relationships to both columns, but normally only one relationship is active. DAX can explicitly use the inactive relationship when required, for example with USERELATIONSHIP().
4. Filter Direction
Filter direction determines how filters travel between related tables.
Single-Direction Filtering
In a typical star schema, filtering moves from the dimension to the fact.
DimProduct
│
│ Filter
▼
FactSales
Suppose a report user selects Product Category = Electronics. The filter travels from DimProduct to FactSales, so only sales associated with Electronics are included in the calculation. This is generally the preferred approach because it is predictable and reduces ambiguity; thus requires defining clear objectives, communicating openly, and documenting specific expectations.
Bidirectional Filtering
Bidirectional filtering allows filters to travel in both directions.
DimProduct
↕
FactSales
Although this can be useful in specific scenarios, it should not be enabled unnecessarily. Potential problems include:
- Ambiguous filter paths
- Unexpected results
- Circular filtering paths
- More complicated DAX behavior
- Increased model complexity
Therefore, single-direction filtering should normally be preferred in a standard star schema, while bidirectional filtering should be used only when there is a clear modelling reason.
5. Joins in Power Query
Power Query is the data preparation and transformation component of Power BI. A join, performed using Merge Queries, combines information from two tables based on matching columns. For example:
Customers
| CustomerID | CustomerName |
|---|---|
| 1 | John |
| 2 | Mary |
| 3 | Peter |
Orders
| OrderID | CustomerID | Amount |
|---|---|---|
| 101 | 1 | 10,000 |
| 102 | 1 | 5,000 |
| 103 | 2 | 8,000 |
| 104 | 4 | 3,000 |
CustomerID is the matching column.
5.1 Left Outer Join
A Left Outer Join retains all records from the left table and matching records from the right table.
Customers LEFT JOIN Orders
| CustomerID | CustomerName | OrderID | Amount |
|---|---|---|---|
| 1 | John | 101 | 10,000 |
| 1 | John | 102 | 5,000 |
| 2 | Mary | 103 | 8,000 |
| 3 | Peter | null | null |
Customer 3 is retained even though there is no order.
Use Case: Useful when you want all customers and any orders associated with them.
5.2 Right Outer Join
A Right Outer Join retains all records from the right table and matching records from the left table.
Customers RIGHT JOIN Orders
| CustomerID | CustomerName | OrderID | Amount |
|---|---|---|---|
| 1 | John | 101 | 10,000 |
| 1 | John | 102 | 5,000 |
| 2 | Mary | 103 | 8,000 |
| 4 | null | 104 | 3,000 |
Order 104 is retained even though CustomerID 4 does not exist in Customers.
5.3 Full Outer Join
A Full Outer Join retains all records from both tables.
Customers FULL JOIN Orders
| CustomerID | CustomerName | OrderID | Amount |
|---|---|---|---|
| 1 | John | 101 | 10,000 |
| 1 | John | 102 | 5,000 |
| 2 | Mary | 103 | 8,000 |
| 3 | Peter | null | null |
| 4 | null | 104 | 3,000 |
This is useful when the objective is to identify both matched and unmatched records.
5.4 Inner Join
An Inner Join retains only records that have matching values in both tables.
| CustomerID | CustomerName | OrderID | Amount |
|---|---|---|---|
| 1 | John | 101 | 10,000 |
| 1 | John | 102 | 5,000 |
| 2 | Mary | 103 | 8,000 |
Customer 3 and Order 104 are excluded because they do not have matches on both sides.
Use Case: Useful when only matching records are required.
5.5 Left Anti Join
A Left Anti Join returns records from the left table that have no matching record in the right table.
Customers LEFT ANTI JOIN Orders
| CustomerID | CustomerName |
|---|---|
| 3 | Peter |
This can be used to identify customers who have never placed an order.
5.6 Right Anti Join
A Right Anti Join returns records from the right table that have no matching record in the left table.
| OrderID | CustomerID | Amount |
|---|---|---|
| 104 | 4 | 3,000 |
This identifies orders containing CustomerIDs that do not exist in the Customers table.
Join Summary
| Join Type | Records Retained |
|---|---|
| Left Outer | All left + matching right |
| Right Outer | All right + matching left |
| Full Outer | All records from both |
| Inner | Only matching records |
| Left Anti | Unmatched left records |
| Right Anti | Unmatched right records |
6. Power Query Joins vs Power BI Relationships
Although both concepts connect tables, they perform different functions.
Power Query Merge
A Power Query merge occurs during the data preparation stage. It combines columns from one query with another query based on matching values.
Customers
+
Orders
↓
Merged Table
The result can contain: CustomerID, CustomerName, OrderID, OrderDate, Amount. A merge can therefore physically create a wider table containing columns from both sources.
Power BI Relationship
A relationship is created in the data model after data loading/transformation. It does not physically combine the tables.
DimCustomer
│
│ 1:*
▼
FactSales
The two tables remain separate. Power BI uses the relationship to propagate filters and evaluate calculations.
Key Differences
| Power Query Merge | Power BI Relationship |
|---|---|
| Data preparation stage | Data modelling stage |
| Combines columns | Connects tables logically |
| Creates a resulting query/table | Tables remain separate |
| Used for transformation | Used for analysis |
| Can increase table width | Preserves dimensional structure |
| Similar to SQL JOIN | Similar to a logical model relationship |
When should we merge like real ?
A merge may be appropriate when:
- A column is genuinely required in the resulting table
- The data is naturally one table after transformation
- You need to clean or enrich a dataset before loading it
- Combining the tables reduces unnecessary complexity
However, excessive merging can produce extremely wide tables with duplicated descriptive data. For example, repeatedly merging customer, product, location, and other descriptive information into FactSales can effectively recreate a flat table.
Why Keep Fact and Dimension Tables Separate?
Keeping them separate is normally preferable because:
- It reduces redundancy
- It makes the model easier to understand
- It improves dimensional analysis
- It supports reusable dimensions
- It simplifies DAX
- It allows dimensions to filter multiple fact tables
- It makes the model easier to maintain and expand
For example:
DimCustomer
│
▼
DimDate ────────► FactSales ◄──────── DimProduct
▲
│
│
DimLocation
The same DimDate could also be used to analyze FactOrders, FactReturns, and FactInventory.
7. Recommended Power BI Model
For a typical business intelligence project, a star schema is recommended:
DimCustomer
│
│ 1:*
▼
FactSales
▲
│ 1:*
┌────────────────────┼────────────────────┐
│ │ │
│ │ │
DimProduct DimDate DimLocation
│ │ │
└──────────── 1:* ───┴──── 1:* ──────────┘
More precisely, each dimension should normally have a one-to-many relationship to the fact table:
DimCustomer 1 ───────── * FactSales
DimProduct 1 ───────── * FactSales
DimDate 1 ───────── * FactSales
DimLocation 1 ───────── * FactSales
Recommended Design Principles
- Use a star schema provides a good balance between performance, simplicity, and scalability. 2.Keep dimensions separate from facts reduces redundancy and makes the model easier to maintain.
- Use 1:*\ relationships dimension tables should normally contain unique keys, while fact tables contain repeated foreign keys.
- Prefer single-direction filtering filters should normally flow Dimension then Fact, reducing ambiguous filter paths and making report behaviour easier to predict.
- Define the grain of every fact table before building measures, determine exactly what one row represents.
- Use meaningful primary and foreign keys like
DimProduct.ProductIDandFactSales.ProductID. - Use bidirectional relationships carefully should only be introduced where the business requirement genuinely requires them.
- Avoid unnecessary snowflaking snowflake design can be useful for complex dimensions, but a simpler star schema is generally easier to work with in Power BI.
- Use Power Query for transformation cleaning, filtering, splitting, merging, and shaping data should generally happen before the data reaches the semantic model.
- Use relationships for analysis once tables are appropriately prepared, relationships should connect the fact and dimension tables without unnecessarily physically merging them.
Conclusion
The key to effective Power BI reporting is having a well-constructed data model. Data modelling defines how Power BI interprets the data of the business, affecting various aspects of reporting such as performance, DAX calculations, filtering, and maintenance of models.
A flat table is easy to use, minimizing overhead costs when working with smaller datasets. However, as the data grows, dealing with flat tables can become problematic. A snowflake schema provides excellent normalization but adds additional tables and complexity in building relationships. In business intelligence applications, a star schema is considered the most efficient approach, balancing simplicity and performance.
Fact tables need to have a defined level of detail, while dimension tables should have the descriptive characteristics that allow understanding of the facts. Dimensions should have one-to-many relationships with facts, which have unique primary keys and repeating foreign keys.
The correct interpretation of relationship connectors is important to provide proper filter propagation, meaning that single relationships from dimensions to facts should generally be applied. Bidirectional relationships should be used only when necessary, as they can create confusion.
Finally, it is important to differentiate between the concepts of Power Query merging and Power BI relationships. For a typical business intelligence project, the recommended architecture is therefore:
DIMENSIONS
│
│ 1:*
▼
FACT TABLE
│
│
DAX MEASURES
│
▼
POWER BI REPORT
This approach provides a model that is readable, scalable, efficient, and easier to maintain, while allowing users to analyze business performance from multiple perspectives.
Top comments (0)