Analytical Data Modelling

Introduction

Analytical data modelling is the design of tables, keys, relationships, business definitions, and analytical structures that allow data to be explored reliably. In Power BI, a report can appear visually attractive while producing incorrect results if its underlying model has ambiguous relationships, inconsistent granularity, duplicated facts, or poorly designed dates. Modelling is therefore not merely a technical preparation step; it is a form of analytical governance.

The central design pattern for business intelligence is the star schema. A star schema separates measurable business events from the descriptive contexts used to analyse them. Fact tables record events such as sales transactions, shipments, or service calls. Dimension tables describe those events through attributes such as product, customer, date, salesperson, and location.

A well-designed model supports three outcomes:

• accurate aggregation;
• understandable report development; and
• efficient query performance.

This chapter explains the principles required to build reliable star schemas in Power BI and related analytical platforms.

Learning Objectives

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

1. distinguish fact tables from dimension tables;
2. define grain before designing a model;
3. select suitable keys and establish valid relationships;
4. design date dimensions and analytical hierarchies;
5. explain additive, semi-additive, and non-additive measures;
6. describe slowly changing dimensions;
7. identify common modelling errors and their consequences; and
8. apply these principles to a practical Power BI scenario.

1. The Analytical Modelling Perspective

Operational systems are usually designed to process transactions. Their tables may be highly normalized to reduce duplication and protect data integrity. Analytical models have a different purpose: they must make patterns, comparisons, trends, and measures easy to query.

A useful analytical model answers questions such as:

• What was revenue by product category and month?
• Which customer segments generated the greatest margin?
• How many units were shipped late by region?
• What was inventory at the end of each reporting period?

These questions combine measures, which are usually numeric, with dimensions, which provide the perspectives of analysis.

1.1 Facts and dimensions

A fact is a business event or measurable observation. Examples include one order line, one payment, one shipment, or one daily inventory snapshot. A fact table normally contains foreign keys to dimensions and numerical measures.

A dimension describes the who, what, where, when, or why of a fact. Examples include Customer, Product, Store, Employee, and Date. Dimension tables normally contain descriptive attributes used for filtering, grouping, and labeling.

A fact table is not defined simply by containing numbers. A table of monthly budgets, for example, is a fact table even though it may contain planned rather than actual values. Similarly, a dimension may contain numeric attributes, such as a customer’s credit limit, but its primary role remains descriptive.

2. Grain: The Foundation of a Reliable Model

The grain of a fact table is the precise meaning of one row. It must be stated before columns, measures, or relationships are designed.

Examples of grain statements include:

• one row per sales order line;
• one row per product per store per day;
• one row per customer service case;
• one row per account per month.

The grain determines what can be counted and summed safely. If a sales table has one row per order line, then Quantity is normally additive across order lines. If an order total is repeated on every line, summing that column will overstate revenue.

2.1 Why grain matters

Suppose Order 100 contains three products. In an order-line fact table, it appears as three rows. The order identifier occurs three times, but the line identifier is unique. Counting rows gives line count, not order count. The correct number of orders is obtained by counting distinct order identifiers, subject to the model’s filter context.

A useful conceptual relationship is:

Here, i identifies a fact row, Quantityᵢ is the quantity on row i, Unit priceᵢ is the applicable unit price, and Discountᵢ is the discount amount for that row. This expression is appropriate only when each fact row represents a distinct sale line and the monetary fields use consistent currency and tax conventions.

2.2 Mixed grain: a dangerous error

A table is at mixed grain when some rows represent order lines while others represent orders, daily totals, or adjustments. Aggregating such a table produces unreliable results because rows do not represent comparable events. The remedy is usually to separate processes into different fact tables, such as Sales Fact, Returns Fact, and Inventory Snapshot Fact.

3. Star Schema Architecture

In a star schema, a central fact table connects directly to surrounding dimensions. The fact table is the centre of analytical activity, while dimensions provide slicing and grouping.

A simple sales model may contain:

• FactSales;
• DimDate;
• DimProduct;
• DimCustomer;
• DimStore; and
• DimSalesperson.

The preferred relationship pattern is generally one-to-many: one dimension row relates to many fact rows. For example, one product can appear on many sales lines.

3.1 Star schema versus snowflake schema

A snowflake schema normalizes dimensions into additional related tables. For example, Product may connect to Subcategory, which connects to Category. This can reduce repeated descriptive values, but it introduces more joins and may make report development less intuitive.

A star schema commonly stores Product, Subcategory, and Category attributes in one product dimension. In Power BI, this denormalized dimension is often easier for users to understand and can simplify filtering.

Snowflaking may be justified when dimensions are very large, shared by several models, or maintained by separate authoritative systems. It should not be introduced merely because normalization is familiar from transactional database design.

4. Keys and Relationships

4.1 Primary, foreign, and surrogate keys

A primary key uniquely identifies a row in a table. A foreign key stores a value that refers to a key in another table. In a star schema, the dimension key is usually copied into the fact table as a foreign key.

A surrogate key is an artificial key created for the analytical model, commonly an integer. It has no business meaning. A natural key comes from the source system, such as CustomerCode or ProductCode.

Surrogate keys are especially valuable when dimension history must be preserved. If a customer changes region, the old and new versions can have different surrogate keys while retaining the same natural customer code.

4.2 Relationship cardinality

Cardinality describes how many rows on one side can relate to rows on the other side.

• One-to-many (1:*): one dimension row relates to many fact rows. This is the standard pattern.
• One-to-one (1:1): each row on one side relates to at most one row on the other side. It may indicate a split table or an unnecessary model complication.
• Many-to-many (:): rows on both sides can match multiple rows. This requires careful design and can create ambiguous filter paths.

A dimension key must be unique on the one side. If ProductKey appears more than once in DimProduct, Power BI cannot treat that table as a valid one-side dimension without further transformation.

4.3 Filter direction

Single-direction filtering from dimension to fact is usually the safest default. It reflects the business question: select a product, then see the related sales. Bidirectional filtering can be useful in carefully designed bridge-table scenarios, but it may create ambiguity, unexpected totals, and performance problems.

4.4 Unknown members and referential integrity

Facts may arrive before their corresponding dimension records, or source systems may contain invalid codes. Instead of leaving unmatched facts without a usable category, the model can assign them to an Unknown dimension member, often with a key such as 0 or −1. This preserves row counts and makes data-quality exceptions visible.

The unknown member must not be confused with a genuine blank value. A blank may mean that the attribute was not supplied, whereas Unknown means that the fact could not be matched to a valid dimension record.

5. Measures and Additivity

A measure is an analytical value calculated from the model. Measures should express approved business logic rather than encourage users to sum arbitrary columns.

5.1 Additive measures

An additive measure can be summed across all relevant dimensions. Sales quantity and sales revenue are usually additive across products, customers, and dates, provided that the grain and currency rules are correct.

5.2 Semi-additive measures

A semi-additive measure can be summed across some dimensions but not across time. Account balance and inventory level are common examples. Adding daily closing inventory across a month does not usually produce meaningful inventory; the report may instead show the closing value on the last available date.

5.3 Non-additive measures

Ratios, percentages, averages, and unit prices are generally non-additive. A margin percentage should be calculated from aggregated margin and revenue, not averaged from row-level percentages.

For example:

Revenue and Cost must be measured over the same filter context and expressed in the same currency. The formula requires Revenue ≠ 0. Averaging individual margin percentages is valid only under special conditions, such as equal revenue weights, which rarely hold in business data.

6. Date Dimensions and Time Intelligence

A dedicated date dimension is one of the most important components of a Power BI model. It should contain one row for every calendar date in the required reporting range, including dates with no transactions.

Typical attributes include:

• Date;
• DateKey;
• year;
• quarter;
• month number;
• month name;
• week number;
• day of week;
• fiscal year; and
• working-day indicator.

Month names should be sorted by month number rather than alphabetically. Fiscal calendars must be explicitly defined because fiscal years do not necessarily begin in January.

A date dimension supports consistent filtering and calculations such as year-to-date, prior-year comparison, rolling periods, and period-over-period change. It also allows a report to show zero activity for dates without facts, which is important for trend analysis.

6.1 Role-playing dates

A fact may contain several dates, such as OrderDate, ShipDate, and DeliveryDate. Each represents a different analytical role. A model can use separate role-playing date dimensions, such as Order Date and Ship Date, or manage inactive relationships and explicit measures carefully. The design should make the active analytical meaning clear to report users.

7. Hierarchies and Drill Paths

A hierarchy is an ordered set of attributes that supports analysis from a broad level to a detailed level. Examples include:

• Year → Quarter → Month → Date;
• Category → Subcategory → Product; and
• Country → Region → City → Store.

Hierarchies should reflect genuine business navigation, not merely every column available in a table. A hierarchy does not automatically guarantee valid aggregation. For example, geographical levels may overlap if stores belong to multiple sales territories. Such business rules must be resolved before hierarchy design.

Power BI hierarchies support drill-down, but users should be informed about the meaning of each level. A calendar hierarchy and a fiscal hierarchy should not be mixed without clear labels.

8. Slowly Changing Dimensions

A slowly changing dimension (SCD) manages changes to descriptive attributes over time. Customer address, employee department, product category, and account status may change after facts have already been recorded.

8.1 Type 0: retain original value

The original value is never changed. This is appropriate when historical identity must remain fixed, such as a birth date or original acquisition channel.

8.2 Type 1: overwrite

The old value is replaced by the new value. Type 1 is simple and suitable when history is not analytically important or when correcting an error. However, historical reports may change when the dimension is updated.

8.3 Type 2: preserve history

A new dimension row is created for each meaningful change. Typical columns include:

• surrogate key;
• natural business key;
• effective start date;
• effective end date; and
• current-row indicator.

Facts must receive the surrogate key corresponding to the dimension version valid at the event date. This allows a sale made before a customer moved region to remain associated with the old region.

8.4 Type 3: limited previous value

A current value and one or more previous values are stored in the same row. Type 3 is useful when the business needs only limited comparison, such as current department and previous department. It does not preserve an unlimited history.

SCD implementation requires agreement between business stakeholders and data engineers. A change in product category may be treated as a correction, a historical event, or a new classification depending on reporting policy.

9. Conformed Dimensions and Multiple Fact Tables

A conformed dimension has consistent definitions and keys across multiple fact tables. A common DimDate can connect Sales, Returns, and Inventory facts. A common DimProduct allows comparison across those processes.

Conformed dimensions support cross-process analysis, but facts must remain at their own grains. Sales and inventory should not be physically merged merely because both contain ProductKey and DateKey. Their measures and event meanings differ.

A model containing multiple fact tables should avoid direct fact-to-fact relationships. Dimensions should normally act as shared points of analysis. This design reduces ambiguity and makes measures easier to interpret.

10. Common Modelling Errors

10.1 Building a single flat table

A flat table may appear convenient, but it commonly repeats customer and product attributes, increases storage, and encourages incorrect aggregation. It also makes attribute maintenance difficult.

10.2 Connecting dimensions to dimensions unnecessarily

Dimension-to-dimension relationships can create multiple filter paths. If Category is already an attribute in DimProduct, connecting DimCategory separately may be redundant or ambiguous.

10.3 Using many-to-many relationships without a bridge

Many-to-many relationships can produce duplicated results. When a customer belongs to multiple segments or a product has multiple tags, an explicit bridge table should represent the association. Measures must then be tested carefully.

10.4 Mixing transaction and snapshot data

A transaction records an event, whereas a snapshot records a state at a point in time. Adding sales transactions to inventory snapshots produces meaningless totals. Separate fact tables should be used.

10.5 Treating blanks as zero

A blank may indicate no observation, missing data, or an undefined calculation. Replacing all blanks with zero can misrepresent business reality. For example, zero sales and no sales record are not always equivalent.

10.6 Relying on automatic date tables

Automatic date tables may be useful for prototypes, but enterprise models generally benefit from a governed, marked date table with fiscal and business-calendar attributes.

10.7 Ignoring data-quality controls

Duplicate keys, unmatched foreign keys, invalid dates, negative quantities, and inconsistent currencies can undermine a model. Validation should occur during data preparation and after refresh.

11. A Practical Modelling Workflow

A disciplined workflow reduces rework.

1. Identify business processes. Determine whether the subject is sales, inventory, finance, service, or another process.
2. Declare the grain. Write one precise sentence describing one fact row.
3. List dimensions. Identify who, what, when, where, and other analytical perspectives.
4. Define measures. Distinguish additive values from ratios, balances, and counts.
5. Choose keys. Select natural and surrogate keys according to history and integration needs.
6. Profile the source data. Check uniqueness, nulls, duplicates, data types, and unmatched codes.
7. Build dimensions first. Standardize attributes, create unknown members, and apply SCD rules.
8. Build fact tables at declared grain. Avoid mixing processes or levels of detail.
9. Create relationships. Prefer dimension-to-fact, one-to-many, single-direction relationships.
10. Validate totals. Compare model results with trusted source reports and manually calculated samples.
11. Document definitions. Record grain, refresh rules, currency, time zone, and measure logic.

12. Illustrative Case: Northstar Retail

Background

Northstar Retail sells consumer electronics through physical stores and an online channel. Management wants a Power BI dashboard showing revenue, units, margin, returns, and inventory by product, customer region, channel, and fiscal period.

Problem

The initial source extract contains one row per order line, but it also repeats order-level freight and order total on every line. Product categories have changed twice during the last three years. Inventory is supplied as a daily closing balance, while returns arrive as separate transactions.

If the extract is loaded as one flat table, order totals and freight will be overstated, historical category reporting will change whenever product classifications are updated, and inventory may be incorrectly added to sales.

Analysis and approach

The modelling team defines the following structures:

• FactSales: one row per order line;
• FactReturns: one row per returned item line;
• FactInventorySnapshot: one row per product, store, and day;
• DimProduct: Type 2 history for category changes;
• DimCustomer;
• DimStore;
• DimChannel; and
• DimDate containing the fiscal calendar.

Freight is allocated to order lines according to an agreed business rule or stored in a separate order-level fact. It is not summed from a repeated order-level column. Product category changes create new DimProduct surrogate-key rows, and the sales facts receive the key valid on the sale date.

Inventory is analysed with an end-of-period measure rather than a sum across days. Returns remain separate so that gross sales, returns, and net sales can be reported transparently.

Outcome

The resulting model allows management to compare current and historical product classifications, analyse sales and returns independently, and view inventory at the correct reporting date. Reconciliation tests show that line-level revenue agrees with the source system and that order counts use distinct order identifiers rather than row counts.

Learning points

The case demonstrates that reliable dashboards depend on business definitions as much as on Power BI visual design. Grain, historical policy, and fact separation must be resolved before report construction.

13. Governance, Security, and Contemporary Practice

Analytical models may contain personal, financial, or commercially sensitive information. Governance should address access, retention, lineage, and responsible use. Row-level security can restrict users to permitted regions or business units, but it must be tested with representative accounts. A visually filtered report is not a substitute for model-level security.

Data lineage and documentation help users understand where a measure originated and which assumptions it contains. Certified semantic models can reduce duplication by providing an approved source for common metrics.

AI-assisted modelling and natural-language querying can accelerate exploration, but they do not eliminate the need for a sound schema. An AI-generated answer is only as reliable as the relationships, definitions, and data quality of the model. Sensitive data should also be handled according to organizational privacy policies and applicable data-protection law.

Best-Practice Checklist

Before publishing a model, verify that:

• every fact table has a documented grain;
• dimension keys are unique;
• relationships have appropriate cardinality and direction;
• unmatched facts are handled explicitly;
• date attributes support the organization’s calendar;
• measures use approved definitions;
• snapshots are not summed like transactions;
• SCD policies are documented;
• totals reconcile to trusted sources; and
• security and refresh behaviour have been tested.

Chapter Summary

Analytical data modelling converts business events and descriptive context into a structure suitable for reliable analysis. The star schema is the central pattern: fact tables record events at a declared grain, while dimension tables provide the perspectives used to filter and group those events. Keys and relationships must preserve uniqueness, valid cardinality, and predictable filter propagation.

Date dimensions, hierarchies, conformed dimensions, and carefully designed measures make a model useful to business users. Additive, semi-additive, and non-additive measures require different aggregation logic. Slowly changing dimensions determine whether historical descriptions are overwritten or preserved. Type 2 dimensions are particularly important when reports must reflect the classifications valid at the time of an event.

Common errors include mixed grain, duplicated order-level values, unexplained many-to-many relationships, fact-to-fact joins, and inappropriate treatment of snapshots. A professional workflow begins with business-process analysis and grain definition, continues through profiling and relationship design, and ends with reconciliation, documentation, security, and governance.

Key Terms

Analytical model: A structured representation of data designed for analysis and reporting.

Cardinality: The number relationship between rows on two related tables.

Conformed dimension: A dimension shared consistently by multiple fact tables.

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

Fact table: A table containing business events, observations, keys, and measures.

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

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

Hierarchy: An ordered set of analytical levels used for navigation and drill-down.

Natural key: A business identifier supplied by a source system.

Non-additive measure: A measure that cannot be meaningfully summed across dimensions.

Primary key: A value or set of values that uniquely identifies a row.

Role-playing date: A date dimension used in different business roles, such as order date or ship date.

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

Slowly changing dimension: A method for managing changes to descriptive dimension attributes.

Snowflake schema: An analytical schema with normalized dimension structures.

Star schema: A model with a central fact table surrounded by dimension tables.

Surrogate key: An artificial key created for the analytical model.

Unknown member: A dimension row used when a fact cannot be matched to a valid dimension record.

Review Questions

Short Answer Questions

1. What is the grain of a fact table, and why must it be declared before modelling?
2. Distinguish between a fact table and a dimension table.
3. What is the usual relationship pattern between a dimension and a fact table?
4. Why is a dedicated date dimension useful in Power BI?
5. What is the difference between a natural key and a surrogate key?

Long Answer Questions

1. Explain the structure and advantages of a star schema compared with a snowflake schema.
2. Describe additive, semi-additive, and non-additive measures, giving one example of each.
3. Explain Type 1, Type 2, and Type 3 slowly changing dimensions and their appropriate uses.
4. Discuss how mixed grain and duplicated order-level values can produce incorrect dashboard results.
5. Describe a professional workflow for designing and validating a Power BI analytical model.

Analytical / Application Questions

1. A sales table contains one row per order line, but the order total is repeated on every line. Explain how you would model revenue and order count without overstating either value.
2. A company wants to report inventory by day and monthly closing inventory. Design the fact structure and explain why daily inventory should not normally be summed across a month.
3. A product moved from Category A to Category B on 1 July. Explain how a Type 2 dimension would preserve historical reporting and identify the keys and dates required.

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

C. The number of dimensions connected to the table

D. The refresh frequency of the table

Correct Answer: B. The precise meaning of one row

Question 2

In a conventional star schema, which relationship is most appropriate between DimProduct and FactSales?

A. Many-to-many with bidirectional filtering

B. One-to-one with no filtering

C. One-to-many from DimProduct to FactSales

D. Many-to-one from FactSales to DimProduct with duplicate product keys

Correct Answer: C. One-to-many from DimProduct to FactSales

Question 3

Which measure is generally semi-additive?

A. Sales quantity

B. Sales revenue

C. Inventory balance

D. Number of order lines

Correct Answer: C. Inventory balance

Question 4

What is the main purpose of a surrogate key in a Type 2 slowly changing dimension?

A. To store the product price

B. To identify each historical version of a dimension member

C. To replace all fact-table measures

D. To sort month names alphabetically

Correct Answer: B. To identify each historical version of a dimension member

Question 5

Why should month names usually be sorted by month number?

A. To remove all date relationships

B. To display months in calendar order rather than alphabetical order

C. To convert a fiscal year into a calendar year

D. To make every month a unique dimension

Correct Answer: B. To display months in calendar order rather than alphabetical order

Question 6

A fact table has one row per order line. Which operation normally gives the number of orders?

A. Count all rows

B. Sum order totals

C. Count distinct order identifiers

D. Average the order identifier

Correct Answer: C. Count distinct order identifiers

Question 7

Which formula correctly expresses gross margin percentage when revenue is non-zero?

A. Revenue ÷ Cost × 100

B. (Revenue − Cost) ÷ Revenue × 100

C. Cost ÷ (Revenue − Cost) × 100

D. (Revenue + Cost) ÷ Revenue × 100

Correct Answer: B. (Revenue − Cost) ÷ Revenue × 100

Question 8

What is a common consequence of mixing transaction rows and daily snapshot rows in one fact table?

A. All measures become automatically additive

B. The model cannot contain any dimensions

C. Aggregations may combine incomparable events and become meaningless

D. Date filtering becomes unnecessary

Correct Answer: C. Aggregations may combine incomparable events and become meaningless

Question 9

Which design is usually preferred for multiple fact tables such as Sales and Returns?

A. Connect the fact tables directly with many-to-many relationships

B. Use shared conformed dimensions where appropriate

C. Merge all rows regardless of grain

D. Remove the date dimension

Correct Answer: B. Use shared conformed dimensions where appropriate

Question 10

In a Type 1 slowly changing dimension, what happens when an attribute changes?

A. A new historical row is always created

B. The old value is overwritten by the new value

C. The dimension row is deleted from the model

D. The fact table is converted into a dimension

Correct Answer: B. The old value is overwritten by the new value