Dimensional Modelling and Star Schemas

Introduction

Business intelligence depends not only on attractive reports but also on the quality and structure of the data behind them. In Power BI, a well-designed model makes measures understandable, filters reliable, and analysis fast. A poorly designed model may produce duplicated totals, ambiguous relationships, slow queries, and misleading management information.

Dimensional modelling is a method for organising analytical data around measurable business events and the descriptive context needed to analyse those events. Its most widely used structure is the star schema, consisting of a central fact table connected directly to several dimension tables.

This chapter explains how to identify facts and dimensions, define grain, choose keys, distinguish normalization from denormalization, compare star and snowflake schemas, manage slowly changing dimensions, and understand additive behaviour. The discussion is focused on practical modelling for Power BI and certificate-level professional practice.

Learning Objectives

By the end of this chapter, you should be able to:

• distinguish fact tables from dimension tables;
• define the grain of a fact table before loading data;
• design a basic star schema for business reporting;
• explain normalization, denormalization, and snowflake schemas;
• explain the purpose of surrogate keys;
• identify and implement common slowly changing dimension strategies;
• classify measures as additive, semi-additive, or non-additive;
• apply dimensional modelling principles in Power BI; and
• recognise data-quality, governance, and security risks in analytical models.

1. The Analytical Data Model

An operational database is designed primarily to record transactions accurately and efficiently. For example, a sales system may store customers, products, orders, payments, and delivery events in separate related tables. An analytical model has a different purpose: it must support questions such as:

• What was revenue by product category and region?
• How many units were sold each month?
• Which customer segments are growing?
• What was inventory at the end of each day?

Dimensional modelling organises data so that these questions can be answered through understandable dimensions and carefully defined measures.

A model normally contains:

• Facts: events or states that can be measured, such as sales, shipments, claims, or account balances.
• Dimensions: descriptive entities used to filter, group, and label facts, such as date, product, customer, employee, or store.
• Measures: calculations performed over facts, such as revenue, quantity, margin, and average order value.
• Relationships: connections between tables, normally from a dimension on the one side to a fact on the many side.

Grain: the first design decision

The grain is the precise meaning of one row in a fact table. It must be stated before selecting columns or writing measures. Examples include:

• one row per product per sales order line;
• one row per customer per month;
• one row per store per day; or
• one row per account per day-end balance.

A statement such as “one row per order” is not equivalent to “one row per order line.” If an order contains five products, an order-line fact table contains five rows. Confusing these grains can result in incorrect quantities, duplicated order values, and invalid ratios.

A useful design statement is:

This fact table records one [business event or periodic state] for each [set of dimensional identifiers] at [specified time or status].

The grain controls which dimensions can be attached and how measures behave. A fact table should not combine rows with different grains unless the design explicitly supports that combination.

2. Fact Tables and Dimension Tables

Fact tables

A fact table usually contains:

• foreign keys to dimensions;
• numeric measurements;
• transaction identifiers or degenerate dimensions where appropriate; and
• sometimes operational audit fields such as load date or source system.

A sales fact might contain DateKey, ProductKey, CustomerKey, StoreKey, OrderNumber, Quantity, UnitPrice, DiscountAmount, and SalesAmount.

Fact tables are often large. They should therefore contain only information needed for analysis at the declared grain. Long descriptive text, such as a product description, normally belongs in a dimension rather than being repeated on every fact row.

Dimension tables

A dimension describes the business context of facts. A product dimension may contain ProductKey, ProductCode, ProductName, Brand, Category, Size, and StandardCost. A customer dimension may contain CustomerKey, CustomerCode, CustomerName, Segment, City, and Country.

Dimensions generally have fewer rows than facts, but their attributes are essential for slicing and grouping. They should be designed for usability: names should be clear, columns should have meaningful data types, and technical columns should normally be hidden from report users.

Relationships in Power BI

The common dimensional relationship is one-to-many:

• one dimension row relates to many fact rows;
• the filter normally travels from the dimension to the fact; and
• the fact table contains the foreign key.

For example, one Product row may relate to many Sales rows. A product category filter should reduce the visible rows in Sales. In Power BI, single-direction filtering from dimensions to facts is usually safer than unnecessary bidirectional filtering, which can create ambiguity and unexpected results.

Figure: A star schema places a fact table at the centre and connects it directly to descriptive dimensions.

3. Designing a Star Schema

A practical design process is as follows.

Step 1: Select the business process

Choose a process that the organisation wants to analyse, such as sales, purchasing, inventory, service calls, or recruitment. A fact table normally represents one business process rather than an entire department.

Step 2: Declare the grain

Write a precise sentence describing one fact row. This prevents incompatible data from being mixed.

Step 3: Identify dimensions

List the dimensions that describe the selected process. Sales commonly uses Date, Product, Customer, Store, Promotion, and Salesperson. Not every process needs every dimension.

Step 4: Identify facts

Select measurements available at the declared grain. For sales-line grain, Quantity and ExtendedSalesAmount are usually appropriate. A monthly target may not belong in the same table if its grain is one row per month and region.

Step 5: Define keys and relationships

Each dimension should have one unique row for each key. The fact table stores the corresponding foreign keys. Unknown, missing, or not-applicable values should be handled deliberately rather than left as accidental nulls.

Step 6: Validate business rules

Check whether totals reconcile to the source system, whether keys are unique, whether dates are valid, and whether measures are recorded in consistent units and currencies.

Example: retail sales

Suppose a retailer records the following sales lines:

If the grain is one row per product per order line, each row belongs in FactSales. Date, Product, and Store are dimensions. Quantity is additive across all three dimensions. SalesAmount is also additive if all rows use the same currency and the measure represents monetary value at the same grain.

A basic DAX measure could be:

Total Sales = SUM ( FactSales[SalesAmount] )

The DAX expression is simple because the model supplies the filter context. For example, a visual grouped by Product[Category] automatically evaluates Total Sales separately for each category, provided the relationship is valid.

4. Normalization and Denormalization

Normalization organises data to reduce redundancy and update anomalies. In a normalized operational design, customer details may appear once in a Customer table, while orders refer to the customer through a key. Normalization supports consistent updates and efficient transaction processing.

Denormalization intentionally stores related descriptive information together or repeats values to simplify reading and improve analytical usability. A star schema is a form of analytical denormalization: attributes such as Category and Brand may be stored together in DimProduct rather than separated into many related tables.

The choice depends on workload:

Denormalization is not permission to duplicate data carelessly. Repeated attributes can become inconsistent unless the refresh process controls them. In Power BI, a clean star schema commonly improves both usability and performance, especially when dimensions are relatively narrow and relationships are simple.

5. Star Schemas and Snowflake Schemas

A star schema connects dimensions directly to a central fact. A snowflake schema normalizes one or more dimensions into additional related tables. For example, DimProduct may connect to DimCategory, which connects to DimDepartment.

Snowflaking may be useful when:

• a hierarchy is shared by many dimensions;
• a very large attribute structure must be maintained centrally;
• governance requires separate ownership of reference entities; or
• normalization reduces substantial duplication.

However, snowflake schemas introduce more relationships, more joins, and a more complex filter path. They may be less intuitive for report authors and can make troubleshooting harder. In Power BI, dimensions are often kept denormalized unless there is a clear reason to separate them.

Neither shape is universally correct. The central question is whether the design provides correct grain, clear relationships, manageable maintenance, and acceptable performance.

6. Surrogate Keys

A surrogate key is a system-generated identifier for a dimension row. It has no business meaning. A ProductKey such as 10427 may identify a particular dimension version, while the source ProductCode may be “P10”.

Surrogate keys are valuable because business keys can change, be reused, differ across source systems, or fail to identify historical versions. They also allow multiple rows for the same business entity when its descriptive attributes change over time.

A dimension should usually contain both:

• a surrogate key used in relationships; and
• a natural or business key retained for traceability.

A common unknown member has a reserved key such as 0. If a fact arrives before its dimension record is available, it can temporarily reference the unknown member rather than breaking the relationship or becoming an unexplained blank.

Surrogate keys do not automatically solve duplication. The ETL or Power Query process must still ensure that each dimension key is unique and that fact foreign keys map correctly.

7. Slowly Changing Dimensions

A slowly changing dimension (SCD) is a dimension whose descriptive attributes change at a relatively low rate compared with transaction facts. The modelling question is whether reports should preserve historical descriptions or show only the current description.

Type 0: retain the original value

The original attribute is never changed. This is suitable for attributes such as an original acquisition date or birth date, where historical stability is required.

Type 1: overwrite the value

The existing value is replaced. This is appropriate when corrections are required or historical analysis of the former value is not important. If a customer moves from London to Bristol and the row is overwritten, old sales will appear under Bristol when analysed by current customer city.

Type 2: create a new version

A new dimension row is created when a tracked attribute changes. The old row remains available for historical facts. Typical columns include EffectiveFrom, EffectiveTo, and IsCurrent.

For example:

Facts dated before 1 July 2024 use CustomerKey 501; later facts use 842. This allows historical sales to remain associated with London when the report is designed to analyse the city at the time of sale.

Type 3: retain limited prior history

A current and previous value may be stored in the same row. This is simpler than Type 2 but retains only limited history and is less flexible for long-term analysis.

The correct SCD strategy depends on the reporting question. It should be agreed with business owners rather than selected solely for technical convenience.

8. Additive Behaviour of Measures

Measure classification is essential for correct aggregation.

Additive measures

An additive measure can be summed across all relevant dimensions. Sales amount, quantity sold, and cost amount are often additive, assuming consistent units, currency, and grain.

Semi-additive measures

A semi-additive measure can be summed across some dimensions but not across time. Account balance and inventory level are typical examples. Summing daily closing inventory across a month usually produces a meaningless value. A report may instead use the last available balance, an average balance, or another explicitly defined business rule.

Non-additive measures

A non-additive measure cannot be meaningfully summed. Percentages, ratios, unit prices, and averages are common examples. Instead of summing row-level percentages, recompute the ratio from appropriate additive components.

For a weighted average selling price, the correct calculation is generally:

Here, total sales amount and total quantity sold must refer to the same filter context and compatible units. A simple average of invoice prices gives every row equal weight and may produce a different result.

For example, if 2 units are sold for £15 each and 8 units for £10 each:

• total sales amount = (2 × £15) + (8 × £10) = £110;
• total quantity = 2 + 8 = 10 units; and
• weighted average price = £110 ÷ 10 = £11 per unit.

The simple average of £15 and £10 is £12.50, which is not representative because the prices apply to different quantities.

In DAX, a weighted average might be represented as:

Weighted Average Price =
DIVIDE ( [Total Sales], [Total Quantity] )

The DIVIDE function is preferable to a direct division because it handles a zero or blank denominator safely according to the model’s desired result.

9. Conformed Dimensions and Multiple Facts

A conformed dimension has consistent meaning and structure across fact tables. A shared DimDate can filter sales, purchases, and inventory. A shared DimProduct can support comparisons between sales and returns.

Conformed dimensions enable integrated analysis, but facts must still be kept at appropriate grains. A sales fact and an inventory snapshot fact should not normally be merged simply because both contain ProductKey and DateKey. They represent different processes and have different additive behaviour.

When several fact tables are used in one Power BI model, avoid creating a direct fact-to-fact relationship. Dimensions should usually act as shared filtering structures. If the facts use different date meanings—such as OrderDate and ShipDate—the model may require role-playing date dimensions or inactive relationships with carefully designed measures.

10. Illustrative Case: Northstar Retail

The following is a fictional illustrative case.

Background

Northstar Retail operates 35 stores and an online channel. Its managers report monthly revenue, units, margin, customer segment, and stock levels. The existing Power BI report imports a wide sales extract containing repeated customer and product descriptions.

Problem

Managers notice that revenue is sometimes duplicated when category and store filters are combined. Historical reports also change when customers move between regions. Inventory totals are inflated when users sum daily stock values across a month.

Analysis

The modelling review identifies three problems:

1. The source extract mixes sales-line and order-level fields, so its grain is unclear.
2. Customer region is overwritten in place, preventing historical analysis.
3. Inventory is a periodic snapshot and is being treated as an additive transaction measure.

Approach and solution

The team declares the sales grain as one row per product per order line. It creates FactSales with DateKey, ProductKey, CustomerKey, StoreKey, Quantity, SalesAmount, CostAmount, and OrderNumber. Product, Customer, Store, and Date are designed as dimensions.

A separate FactInventorySnapshot is created at one row per product, store, and day. Inventory is not joined directly to FactSales. A shared Date, Product, and Store dimension filters both facts.

Customer region is implemented as a Type 2 attribute. Each change creates a new CustomerKey, preserving the region that applied when the sale occurred. Unknown dimension members are added for late-arriving records.

Measures are defined explicitly:

Total Revenue = SUM ( FactSales[SalesAmount] )

Total Units = SUM ( FactSales[Quantity] )

Gross Margin =
    SUM ( FactSales[SalesAmount] )
        - SUM ( FactSales[CostAmount] )

Closing Inventory =
    CALCULATE (
        SUM ( FactInventorySnapshot[UnitsOnHand] ),
        LASTDATE ( DimDate[Date] )
    )

The inventory expression is illustrative: in production, the business must define how to handle missing snapshot dates, multiple warehouses, and filters that produce more than one relevant stock row. The use of LASTDATE reflects a closing-balance interpretation, not a universal rule.

Outcome

Revenue totals reconcile with the finance system, historical regional analysis becomes stable, and inventory reports no longer imply that monthly stock is the sum of daily stock. Users also receive clearer dimension names and fewer ambiguous filters.

Learning points

• Grain must be declared before measures are designed.
• Separate business processes often require separate fact tables.
• Type 2 history protects the meaning of past facts.
• Snapshot measures require an explicit time-aggregation rule.
• A technically valid relationship can still support an invalid business interpretation.

11. Implementation and Quality Checks in Power BI

A practical implementation should include the following checks:

1. Dimension uniqueness: each dimension key must occur once.
2. Fact referential integrity: every foreign key should match a dimension key or a controlled unknown member.
3. Relationship cardinality: confirm one-to-many relationships and avoid accidental many-to-many relationships.
4. Date coverage: the date dimension should cover all relevant fact dates and include useful calendar attributes.
5. Data types and units: quantities, currencies, dates, and percentages must be typed consistently.
6. Reconciliation: compare key totals with trusted source reports.
7. Filter testing: test totals at overall, monthly, category, customer, and transaction levels.
8. Performance: remove unnecessary columns, use appropriate storage modes, and avoid excessive calculated columns when a transformation can be performed upstream.
9. User experience: hide technical keys, organise measures, provide clear names, and mark the date table appropriately.
10. Documentation: record grain, source systems, refresh rules, SCD behaviour, and measure definitions.

Governance, security, and ethics

Dimensional models can expose sensitive information even when they are technically correct. Customer dimensions may contain personal data, and employee dimensions may contain confidential employment information. Access should follow least privilege. Power BI row-level security may be required so that regional managers see only authorised regions.

Data minimisation, retention rules, lawful processing, and auditability should be considered. Historical Type 2 records must not become a reason to retain personal information indefinitely. Organisations should also document whether a report shows current attributes or attributes valid at the time of the event, because that distinction can affect managerial and regulatory decisions.

12. Contemporary Practice and Common Mistakes

Modern BI platforms increasingly combine warehouse, lakehouse, semantic-model, and self-service capabilities. Regardless of the storage technology, the modelling principles remain relevant: clear grain, stable keys, conformed dimensions, controlled history, and explicit measures.

Common mistakes include:

• building a single flat table without defining grain;
• relating two fact tables directly;
• using text business keys as the only historical identifier;
• enabling bidirectional filtering everywhere;
• summing percentages or inventory snapshots;
• mixing currencies or units without conversion;
• hiding data-quality problems with unexplained blanks; and
• allowing users to create competing definitions of revenue or margin.

AI-assisted modelling tools can suggest relationships, classifications, or DAX expressions, but suggestions require professional validation. An AI tool may infer a relationship from matching column names while overlooking duplicate keys, historical versions, or incompatible grain. Accountability for the published model remains with the analyst and organisation.

Chapter Summary

Dimensional modelling structures analytical data around facts and dimensions. A fact table records a measurable business process at a declared grain, while dimensions provide descriptive context for filtering and grouping. The star schema is usually preferred for Power BI because it offers direct relationships, understandable navigation, and predictable analytical behaviour.

Normalization reduces redundancy for transactional systems; denormalization accepts controlled repetition to improve analytical usability. A snowflake schema further normalizes dimensions but may increase complexity. Surrogate keys provide stable identifiers and support historical dimension versions. Slowly changing dimensions offer different approaches: Type 0 preserves original values, Type 1 overwrites values, Type 2 creates historical rows, and Type 3 stores limited prior history.

Measures must be classified as additive, semi-additive, or non-additive. Sales and quantities may be summed, inventory often requires a closing or average rule, and ratios should generally be recomputed from appropriate additive components. Conformed dimensions support analysis across multiple fact tables, but separate business processes should retain separate grains. Good Power BI models combine technical correctness, business clarity, governance, security, and documented definitions.

Key Terms

Fact table: A table recording measurable business events or periodic states.

Dimension table: A table containing descriptive attributes used to analyse facts.

Grain: The exact meaning of one row in a fact table.

Measure: A calculation evaluated over model data and filter context.

Star schema: A model with a central fact table connected directly to dimensions.

Snowflake schema: A dimensional model in which one or more dimensions are further normalized.

Normalization: Organising data to reduce redundancy and update anomalies.

Denormalization: Intentionally combining or repeating data to simplify analytical access.

Surrogate key: A system-generated identifier without business meaning.

Natural key: A key originating from a business or source system.

Foreign key: A column that refers to a key in another table.

Conformed dimension: A shared dimension with consistent meaning across fact tables.

Slowly changing dimension: A dimension whose descriptive attributes change over time.

Type 1 SCD: A strategy that overwrites an old dimension value.

Type 2 SCD: A strategy that creates a new dimension row for a changed version.

Additive measure: A measure that can be summed across relevant dimensions.

Semi-additive measure: A measure that can be summed across some dimensions but not all, commonly time.

Non-additive measure: A measure that should not be summed, such as a ratio.

Unknown member: A controlled dimension row used when a matching descriptive record is unavailable.

Filter context: The set of filters under which a DAX measure is evaluated.

Review Questions

5 Short Answer Questions

1. What is the grain of a fact table, and why must it be declared first?
2. State two differences between a fact table and a dimension table.
3. What is the purpose of a surrogate key?
4. How does a Type 1 slowly changing dimension differ from a Type 2 dimension?
5. Why is inventory commonly classified as semi-additive?

5 Long Answer Questions

1. Explain the structure and advantages of a star schema for Power BI reporting.
2. Compare normalization, denormalization, and snowflake schemas in terms of usability, redundancy, and complexity.
3. Describe a complete process for designing a sales star schema, from selecting the business process to validating the finished model.
4. Explain the four common slowly changing dimension strategies and identify an appropriate use for each.
5. Discuss how conformed dimensions allow multiple fact tables to be analysed together without combining incompatible grains.

3 Analytical / Application Questions

1. A sales table contains one row per order, but each row also contains a repeated order total for every product line. Design a corrected fact structure and explain which measures would otherwise be duplicated.
2. A bank wants to report customer balances at month-end and daily transaction activity. Propose fact tables, dimensions, and a suitable aggregation rule for balances.
3. A customer changes region halfway through a financial year. Explain how Type 1 and Type 2 implementations would produce different historical reports, and recommend one for analysing regional performance at the time of sale.

Multiple Choice Questions with Answers

Question 1

Which statement best defines the grain of a fact table?

A. The number of columns in the table

B. The precise meaning of one row in the table

C. The total number of dimension tables

D. The physical storage size of the table

Correct Answer: B. The precise meaning of one row in the table

Question 2

In a typical star schema, where are descriptive attributes such as product category stored?

A. In the fact table

B. In a transaction log only

C. In a dimension table

D. In a measure table

Correct Answer: C. In a dimension table

Question 3

What is the main purpose of a surrogate key in a dimension?

A. To store a calculated percentage

B. To provide a stable system-generated identifier

C. To replace every numeric measure

D. To force bidirectional filtering

Correct Answer: B. To provide a stable system-generated identifier

Question 4

Which slowly changing dimension strategy creates a new row when a tracked attribute changes?

A. Type 0

B. Type 1

C. Type 2

D. Type 3

Correct Answer: C. Type 2

Question 5

Which measure is usually semi-additive rather than fully additive?

A. Units sold

B. Sales amount

C. Inventory level

D. Cost amount

Correct Answer: C. Inventory level

Question 6

A report has total sales of £110 from 10 units. What is the weighted average selling price?

A. £10 per unit

B. £11 per unit

C. £12.50 per unit

D. £110 per unit

Correct Answer: B. £11 per unit

Question 7

What is a common risk of directly relating two fact tables?

A. It always removes all filters

B. It can create ambiguous or incorrect filtering

C. It guarantees historical accuracy

D. It converts facts into dimensions

Correct Answer: B. It can create ambiguous or incorrect filtering

Question 8

Which design is normally preferred for a simple Power BI semantic model?

A. A star schema with direct dimension-to-fact relationships

B. A collection of unrelated flat files

C. A fact-to-fact chain with bidirectional filters

D. A snowflake structure in every situation

Correct Answer: A. A star schema with direct dimension-to-fact relationships

Question 9

What should generally be done with a percentage measure?

A. Sum all percentages across rows

B. Recompute it from suitable additive components

C. Store it only as text

D. Convert it into a surrogate key

Correct Answer: B. Recompute it from suitable additive components

Question 10

If a customer’s region changes and historical sales must retain the old region, which approach is most suitable?

A. Type 1 overwrite

B. Type 2 versioning

C. Deleting the customer row

D. Removing the region dimension

Correct Answer: B. Type 2 versioning