Data modelling in power Bi
Data modelling is setting up tables, relationships, calculations and access for analysis scenarios.This helps so that data can be analyzed correctly and efficiently.
for example, a sales department can have information about,
-Order ID
-Order date
-Customer Name
-City
-Product
-Category
-Amount
instead of putting everything into one large table ,the data can be organized into related tables.
importance of a well designed model
1.Reporting and analytics
it helps in data consistency by standardizing definitions and metrics across different systems so everyone uses the same number.it ensures filters and slicers automatically propagate across related visuals without missing or duplicating records.
2.DAX calculations
3.It prevents incorrect results when writting time-intelligence or cross table measures.
4.it also simplifies code syntax.
Performance
5.A well-designed data model reduce unnecessary data duplication and improve power BI's ability to process queries efficiently.
6.Scalability.It accommodate additional products, customers transactions and other data without the need of the entire structure to be redesigned.
7.maintability.It makes it easier for another analyst to understand and modify the report.
Data modelling approaches
1.Flat table
it is a single ,wide table that contains all your data,e.g Order ID,Order date,Customer name,City,Product category and amount
Example
2.star schema
Definition: One central fact table surrounded by dimension tables, each joined directly to the fact table. It looks like a star.
Example,
dim_doctors
|
|
dim_patients --- Visits_fact --- dim_department
|
|
dim_diagnosis
|
dim_procedure
|
dim_wards
Advantages
• Simple, intuitive and the layout Power BI is optimized for.
• Fast: one hop from dimension to fact and efficient compression.
• Easy DAX and easy filtering.
• Easy for report authors to understand.
Advantages
• Dimensions are denormalised, so some repetition remains (for example, category name repeated per product).
• Requires upfront design and transformation effort.
When appropriate: Almost all analytical models. It is Microsoft's recommended default.
Performance/complexity: Best balance. Few relationships, short filter paths, low comp
3.Snowflake schema approach
It is a star schema where dimensions are normalised into further related tables, so dimensions have their own sub-dimensions.
advantages
• Less redundancy, so easier to maintain hierarchies (Category → Product).
• Mirrors normalised source databases.
Disadvantages
Easy to set up, with no need to handle any connections or relationships.
Fine for quick, one-off exploration.
Disadvantages
-Heavy repetition of words many times, which makes the files bigger and the system slower.
-Updating anomalies: when you rename a category, you have to change many rows.
-It's difficult to create accurate time-based analysis without having a proper date table.
Comparison
1.Feature flat table star schema and snowflake schema
Tables can have one fact along with several dimensions, or they can include a fact along with dimensions and sub-dimensions.
Redundancy High Moderate Low
Performance in Power BI is poor when handling large data sets, but it's best when dealing with smaller data. It’s good, but slightly slower than the best.
Model complexity Lowest Low Highest
DAX simplicity Awkward Easy Moderate
Recommended? Only small or ad hoc, yes (default), only when justified.
2.Fact table and Dimension Table
A fact table keeps track of business events along with the numerical outcomes of those events. It typically contains:
Foreign keys to dimensions (DateKey, ProductKey, CustomerKey)
Numeric measures: Quantity, SalesAmount, Discount, Cost
Sometimes degenerate dimensions such as an OrderNumber
Fact tables are long because they have many rows, but they are not wide since they only have a few columns. Examples: FactSales, FactOrders, FactTransactions.
Dimension tables
A dimension table holds descriptive information that helps in filtering, grouping, and labeling the facts. It has a special key called the primary key and also includes text and category columns. Dimension tables are not very long, having fewer rows, but they have many columns that provide detailed descriptions.
DimCustomer: CustomerKey, Name, Gender, Segment, Join Date
DimProduct: ProductKey, Product Name, Category, Brand, Unit Price.
DimDate: DateKey, Date, Month, Quarter, Year, Weekday
DimLocation: LocationKey, City, County, Country
Measures versus attributes
Measures are numerical outcomes from business events that you combine by adding, averaging, or counting.
Attributes are descriptive fields that you can use to break down or analyze your data, such as Category, City, or Month.
Grain
The grain refers to what is represented by one row in the fact table. Examples:
"One row for each product in each order line" (FactSales).
"One row per order" (FactOrders)
One entry for each account every day, which is a daily balance summary.
Define the grain first. Every measure and foreign key has to be accurate at that level of detail. Combining different types of grain values (such as order-level and line-level) in the same table leads to counting the same items twice.
FactSales
| Sales ID |Date key |Customer key | Product key |Location key | Quantity |Sales amount|
|---------|-----------|-------------|------------|--------------|
|1 |20260105 | 11 |501 |3 |
|1 |65000
|2 |20260105 |11 |502 |3 |
|2 |3000
|3 |20260106 |12 |501 |4 |
|1 |65000 |
Each foreign key refers to a dimension where the key is unique. Choosing "Mombasa" in the DimLocation or "Tech" in the DimProduct narrows down the FactSales data to only the rows that match those selections. This is the star schema presented in Section 1.
- Relationships in Power BI What is a relationship?
A relationship connects two tables by using a column in each, which helps Power BI understand how they are linked and allows it to pass filters from one table to the other. Without relationships, data from different tables can't be connected: if you select "Nairobi" in a slicer, it won't change the sales totals because Power BI won't know which rows of data belong to Nairobi.
Keys, uniqueness and integrity
Primary key (PK): A column that uniquely identifies every row in a table, like CustomerKey in DimCustomer.
A foreign key is a column in one table that points to the primary key of another table, like the CustomerKey in the FactSales table.
Unique values: The "one" side of a relationship should have distinct, non-empty values.
Cardinality refers to the type of relationship between columns, such as one-to-many or other similar types.
Referential integrity means that every value in a foreign key of the fact table must be found in the corresponding dimension table. If FactSales includes CustomerKey 99 but DimCustomer does not, those rows appear under a (Blank) category in visuals, making reports misleading.
Why is CustomerID unique in DimCustomer but appears multiple times in FactSales: DimCustomer has one row for each customer, meaning each person is represented only once. FactSales records one entry for each time a customer makes a purchase, and a customer can make multiple purchases. CustomerID 11 is listed once in the DimCustomer table but appears many times in the FactSales table. That is exactly a one-to-many relationship.
_One-to-many (1:*)
How it works: A single row on the "one" side connects to multiple rows on the "many" side. Filters move from the single side to the multiple side.
Example: DimProduct (1) → FactSales (*).
DimProduct (1) ─────────────► (*) FactSales
ProductKey PK ProductKey FK
Use it: This is the standard, default relationship in star schemas. Use it whenever a dimension describes a fact.
Avoid it: When the "one" column has duplicate entries. Power BI will then force a many-to-many.
One-to-one (1:1)
How it works: Each row in one table can match only one row in the other table, and both columns are unique. Filters propagate in both directions.
Employees (EmployeeID) and EmployeeSensitiveDetails (EmployeeID), such as salary or national ID, are kept in a separate table for security.
Employees (1) ◄────────────► (1) EmployeeDetails
EmployeeID PK EmployeeID PK
Use it: Occasionally, such as when dividing a table for security reasons or when two sources refer to the same entity.
Usually, it's better to combine the two tables into one using Power Query, which makes the model easier to work with.
Many-to-many (:)
How it works: Neither column is unique, so there may be multiple rows that match on each side. Power BI doesn't require a unique key, but the results can vary based on how filters work and may be difficult to predict.
Students and Courses, where a student enrolls in multiple courses and a course is attended by multiple students. Another example is budget data at the category level connected to sales data at the product level.
Students () ◄────────► () Enrollments?
The recommended design is a bridge table:
DimStudent has a one-to-many relationship with BridgeEnrollment, which in turn has a one-to-many relationship with DimCourse.
Use it: Only when a bridge table isn't practical, or when you need to compare tables that have different levels of detail, like budget data grouped by month versus sales data grouped by day.
Avoid it: As a shortcut to skip cleaning your data. It causes ambiguity and unexpected totals.
Active and inactive relationships
At any given time, only one active connection can exist between two tables, and this is shown as a solid line in the Model view. Additional ones are inactive (dotted).
FactSales has OrderDate and ShipDate, both connecting to DimDate[Date]. OrderDate is the active relationship. To calculate sales by ship date, turn on the other one in DAX:
DAX
Sales by Ship Date =
CALCULATE (
SUM ( FactSales[SalesAmount] ),
USERELATIONSHIP ( FactSales[ShipDate], DimDate[Date] )
)
- Filter Direction How filters propagate
Filters travel along relationships. In a standard star schema, when you apply a filter to a dimension, that filter is passed down to the fact table.
Single-direction filtering
Filters only move from the "one" side to the "many" side.
A user chooses the category "Tech" in the DimProduct slicer.
DimProduct is filtered to FactSales, and the Total Sales shows only Tech sales.
The slicer filters DimProduct to Tech products.
That filters FactSales to include only the rows where the ProductKey matches the specified products.
SUM(FactSales[SalesAmount]) returns Tech sales only.
This is the default and preferred behaviour.
Both (bidirectional) filtering
Filters work both ways: a filter applied to the fact table also affects the dimension.
DimProduct ◄──filter──► FactSales
Legitimate use: For instance, a slicer on DimProduct that displays only products with actual sales, or many-to-many relationships handled through a bridge table.
How to use it carefully
Ambiguous filter paths: When multiple dimensions are linked to a single fact table, bidirectional filters can lead to different paths connecting two tables. Power BI might not allow the relationship to be activated or could cause unexpected outcomes.
Unexpected results happen when filters affect the entire model, meaning a slicer applied to one dimension can also filter another.
Performance cost: Using filters can make calculations slower because they require more processing work.
The model becomes more difficult to understand and fix.
Performance/complexity: Lowest modelling complexity but poor scalability. Memory usage increases rapidly when text columns are repeated.
Star schema
Definition: There is one main fact table with dimension tables around it, and each dimension table is connected directly to the fact table. It looks like a star.
Best practice is to keep everything set to one direction by default, and only turn on two-way filtering when it's actually needed. Often the same outcome can be reached more safely in DAX by using CROSSFILTER() within a single measure.
- Joins in Power Query A join combines rows from two tables by matching them on a common column. In Power Query, you perform this action by going to the Home tab and selecting Merge Queries, or choosing Merge Queries as New. You pick two tables, choose the matching column in each one, select the type of join you want, and then expand the joined table to include its columns. Example data
Customers table
|Customer ID| Name |City
|
|-----------|---------|-----
-|C1 | Brian |Nairobi
|
|C2 | Amos | Mombasa
|C3 |Dorcas |Kisumu
|C4 |Cynthia |Nakuru
Orders
OrderID CustomerID Amount
O101 C1 5,000
O102 C1 2,500
O103 C3 8,000
O104 C5 1,200
Note: Brian (C2) and David (C4) have no orders, and order O104 belongs to C5, who is not in Customers. Merges always start by using Customers as the first table on the left and Orders as the second table on the right, pairing them based on the CustomerID.
Left Outer Join
How it works: It keeps all the rows from the left table and only includes the matching rows from the right table.
Records kept: All customers and corresponding order information are retained. Unmatched customers get nulls.
Right Outer Join
How it works: It keeps all the rows from the right table and only the matching rows from the left.
Records kept: Every order along with the corresponding customer information.
CustomerID Name City OrderID Amount
C1 Amina Nairobi O101 5,000
C1 Amina Nairobi O102 2,500
C3 Cynthia Kisumu O103 8,000
null null null O104 1,200
Use case: Display all orders and mark those that don't have a corresponding customer record.
Full Outer Join
How it works: It keeps all the rows from both tables and matches them where possible.
Records are kept: all information is stored; missing data is represented by nulls where there is no matching information.
CustomerID Name City OrderID Amount
C1 Amina Nairobi O101 5,000
C1 Amina Nairobi O102 2,500
C2 Brian Mombasa null null
C3 Cynthia Kisumu O103 8,000
C4 David Nakuru null null
null null null O104 1,200
Use case: Combining two systems to identify all the matches and differences in a single outcome.
Inner Join
How it works: It keeps only the rows that are the same in both tables.
Records kept: Only customers who have placed orders, and orders that are linked to customers.
CustomerID Name City OrderID Amount
C1 Amina Nairobi O101 5,000
C1 Amina Nairobi O102 2,500
C3 Cynthia Kisumu O103 8,000
Use case: "Analyze customers who are currently active and have made a purchase."
Left Anti Join
How it works: It keeps the rows from the left table that do not have a matching row in the right table.
Records retained: Customers with no orders.
CustomerID Name City
C2 Brian Mombasa
C4 David Nakuru
Use case: Identifying customers who have not placed any orders yet in order to run a marketing campaign.
Right Anti Join
How it works: It keeps the rows from the right table that do not have a matching row in the left table.
Records retained: Orders with no matching customer.
OrderID CustomerID Amount
O104 C5 1,200
Use case: Identifying records that are no longer linked to any other data, which is a data quality check to ensure that all references between data sets are correct and complete.
Summary
Join type Keeps
Left Outer All left + matching right
Right Outer All right + matching left
Full Outer Everything from both
Inner Matching rows only
Left Anti Left rows with no match
Right Anti Right rows with no match
- Power Query Joins vs Power BI Relationships
Both link tables together, but they function in different steps and serve different reasons.
Aspect Power Query join (Merge) Power BI relationship
When it happens, it can occur during data loading or refreshing (ETL stage) or when a report or query is run.
What it does is physically combines tables into a new table or adds columns. It keeps tables separate and links them logically.
Result: One wider table (rows may multiply or shrink) Filters propagate between tables; no data merged.
Join types include six kinds such as left, inner, anti, and others. Cardinality includes ratios like 1 to many, 1 to 1, and so on. Filter direction is also considered.
Impact on model size can lead to larger size and extra copies. It is efficient because each entity is stored only once.
Aggregation values are set at the row level. Dynamic: measures update based on the filter context.
Best for data preparation: adding more information, fixing errors, making data easier to use, handling complex structures, and finding records that don't match. Analytical modeling: breaking down data into different categories to analyze it effectively.
Rule of thumb:
Use Power Query joins to clean and structure your data: simplify a snowflaked dimension, add more information to a table using a lookup column, or find rows that don’t have matches with an anti join.
Use relationships to analyze data by linking a fact table to dimensions, allowing measures to react to slicers and filters.
Merge the DimProduct and DimCategory tables in Power Query to create a single DimProduct table, which flattens the snowflake schema. Link the DimProduct table to the FactSales table using a one-to-many relationship. Combining FactSales with all dimensions into a single flat table is possible, but it reduces the performance, flexibility, and ease of maintenance that a star schema provides.
Conclusion
A good Power BI model keeps facts like events and measurements separate from dimensions that provide descriptive details. It connects them with the right one-to-many relationships and uses filters that only go in one direction by default. The star schema is the preferred approach since it is efficient, straightforward, and works well with DAX. Power Query joins help organize the data and also show any issues with data quality by using anti joins, while relationships allow the final model to answer business questions in a flexible way.
Top comments (0)