Data Modelling for Business Intelligence

Introduction

Business intelligence (BI) converts operational data into information that supports monitoring, explanation, prediction and decision-making. A dashboard may appear to be a visual product, but its reliability depends largely on the model beneath it. If transactions are represented at inconsistent levels of detail, customer identities are duplicated, or measures are defined ambiguously, even an attractive report can produce incorrect conclusions.

Data modelling is the disciplined process of representing business entities, events, relationships, rules and metrics so that data can be stored, integrated and analysed consistently. In BI, modelling connects operational systems with analytical questions. It determines what a business can count, compare, filter, aggregate and explain.

This chapter examines relational, dimensional and semantic modelling. Particular attention is given to facts, dimensions, measures, granularity, star and snowflake schemas, hierarchies, slowly changing dimensions and calculated metrics.

Learning Objectives

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

• distinguish relational, dimensional and semantic models;
• identify fact tables, dimension tables, measures and business keys;
• define and enforce the grain of a fact table;
• design and compare star and snowflake schemas;
• construct useful hierarchies and analyse their consequences;
• select suitable slowly changing dimension techniques;
• distinguish additive, semi-additive and non-additive measures;
• create defensible calculated metrics;
• evaluate data-model quality, governance and implementation risks.

1. The Role of a Data Model in BI

A data model is an organised description of data objects, their attributes, relationships and business rules. It provides a shared structure between source-system specialists, data engineers, analysts, managers and report developers.

An effective BI model should answer four questions:

1. What business process is being measured?
2. What event or state does each record represent?
3. At what level of detail is it represented?
4. Which dimensions and measures are available for analysis?

For example, “sales” may mean an order, an invoice, a shipment, a payment or a sales target. These are different business processes and should not automatically be combined in one fact table. A clear model prevents users from comparing incompatible events.

A model also establishes a common vocabulary. Terms such as revenue, active customer, product, order date and gross margin must have explicit definitions. Without this semantic consistency, two departments may produce different values for the same indicator while each believes its calculation is correct.

2. Relational Modelling

Relational modelling represents data in tables consisting of rows and columns. A row represents an instance of an entity or event, while a column represents an attribute. Tables are connected through keys.

2.1 Entities, attributes and keys

An entity is a distinguishable business object, such as Customer, Product or Employee. An attribute describes an entity, such as CustomerName or ProductCategory.

A primary key uniquely identifies each row in a table. A foreign key stores a reference to a key in another table. For example, CustomerID may be the primary key of Customer and a foreign key in Order.

A relationship may be one-to-one, one-to-many or many-to-many. An order may contain many order lines, but each order line normally belongs to one order. A many-to-many relationship, such as students enrolling in many courses, is usually resolved with an associative table such as Enrolment.

2.2 Normalisation

Normalisation reduces duplication and update anomalies in operational databases. In simplified terms:

• First normal form requires atomic values and no repeating groups.
• Second normal form requires non-key attributes to depend on the whole composite key.
• Third normal form requires non-key attributes not to depend on other non-key attributes.

Normalisation is valuable for transaction processing because it supports consistency. However, highly normalised structures can require many joins for analytical queries. BI systems therefore often transform relational source data into dimensional structures that are easier to understand and aggregate.

2.3 Operational and analytical priorities

Operational systems prioritise fast inserts, updates and transactional integrity. Analytical systems prioritise fast reading, filtering, aggregation and historical comparison. A model suitable for order entry is not necessarily suitable for executive reporting. This is why data warehouses and analytical models commonly use a separate modelling layer rather than exposing operational tables directly.

3. Dimensional Modelling

Dimensional modelling organises data around measurable business processes. It normally contains fact tables, which record events or states, and dimension tables, which provide descriptive context.

A dimensional model is designed from business questions. For example, a retail sales model might support analysis of sales by date, store, product, customer and promotion.

3.1 Fact tables

A fact table contains records at a declared grain. Typical columns include:

• foreign keys to dimensions;
• numeric measurements;
• degenerate dimensions, such as an invoice number;
• technical metadata, such as load timestamp.

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

Facts may represent:

• transactions, such as individual sales lines;
• periodic snapshots, such as inventory at the end of each day;
• accumulating snapshots, such as the stages of an order fulfilment process.

3.2 Dimension tables

Dimensions describe the circumstances surrounding facts. Common dimensions include Date, Customer, Product, Supplier, Employee, Store and Geography.

A dimension usually contains a surrogate key, descriptive attributes and, where necessary, effective dates and current-record indicators. A surrogate key is a warehouse-generated identifier that is independent of the source-system key. It is particularly important when the same business entity has multiple historical versions.

3.3 Measures

A measure is a value used for analysis. Measures may be stored in a fact table or calculated from stored fields.

Additive measures can be summed across all relevant dimensions. Sales revenue and units sold are usually additive. Semi-additive measures can be summed across some dimensions but not others. An end-of-day bank balance can be summed across accounts, but summing it across dates may be meaningless. Non-additive measures cannot be meaningfully summed; percentages, ratios and unit prices are common examples.

For a sales line, revenue may be calculated as:

Here, Quantity is measured in units, UnitPrice is currency per unit, and DiscountAmount is currency. The formula is applicable when the discount is recorded as an absolute amount for that line. If discount is represented as a percentage, the formula must be adapted and the percentage must not be subtracted as if it were currency.

A percentage should generally be calculated from aggregated components rather than averaged from row-level percentages. For example, a margin percentage is:

Revenue and Cost must use the same currency and reporting scope, and Revenue must not be zero. This is preferable to averaging individual line margins when line revenues differ.

4. Grain: The Foundation of a Reliable Fact Table

Grain is the precise meaning of one row in a fact table. Examples include “one row per product per customer order line” and “one row per store per day.” Grain must be stated before selecting measures or designing keys.

Consider an order-line fact with the grain “one row per product on an order.” If an order contains three products, it produces three fact rows. Order-level shipping cost must not be repeated on all three rows unless a defensible allocation rule is applied; otherwise, total shipping will be overstated.

A practical grain statement includes:

• the business process;
• the event or state represented;
• the entities that define uniqueness;
• the time reference;
• the included and excluded records.

For a transaction fact, a useful uniqueness test is the combination of its dimensional foreign keys and any degenerate transaction identifier. Duplicate combinations may indicate duplicate ingestion, an incorrect grain or a legitimate repeated event that requires another identifying attribute.

4.1 Conformed dimensions

A conformed dimension has consistent meaning and keys across fact tables. A common Date dimension allows sales, returns and inventory to be compared by the same calendar attributes. A conformed Product dimension supports consistent product analysis across sales and procurement.

Conformed dimensions are essential when combining facts. However, facts should not be joined directly at incompatible grains without care. Joining daily inventory to order-line sales can multiply rows and produce inflated totals. Measures should normally be aggregated to a compatible grain before being combined.

5. Star and Snowflake Schemas

A star schema contains a central fact table directly connected to relatively denormalised dimension tables. It is called a star because the fact is surrounded by dimensions.

Figure: A star schema in which a central sales fact is analysed through descriptive dimensions.

A snowflake schema normalises one or more dimensions into related sub-dimensions. Product may be separated into Product, Brand and Category tables. Snowflaking can reduce repeated storage and improve maintenance of shared hierarchies, but it introduces more joins and can make self-service analysis less intuitive.

Neither pattern is universally superior. The choice depends on query performance, data volume, governance, platform capabilities and the skills of model users.

6. Hierarchies and Time

A hierarchy provides meaningful levels for navigation and aggregation. A product hierarchy might be Department → Category → Subcategory → Product. A geography hierarchy might be Country → Region → City → Store.

Hierarchies must reflect business rules rather than merely visual convenience. They should be tested for uniqueness and completeness. If one product belongs to two categories simultaneously, a simple strict hierarchy may be inappropriate; a bridge table or a primary-category rule may be required.

The Date dimension commonly includes calendar year, quarter, month, week and day, together with fiscal attributes, working-day flags and holiday indicators. A fiscal calendar should not be inferred from a calendar hierarchy if the organisation’s reporting year differs from the calendar year.

A parent-child hierarchy is suitable when levels vary, such as an organisational reporting structure. It is more flexible but often more complex to query than a fixed-level hierarchy.

7. Slowly Changing Dimensions

A slowly changing dimension (SCD) records changes to descriptive attributes over time. Customer address, sales territory and product classification may change while historical facts must remain interpretable.

Type 0: Retain the original value

The original value is never changed. This is appropriate for attributes whose historical value must remain permanently fixed, such as an original acquisition channel.

Type 1: Overwrite

The old value is replaced by the new value. Type 1 is useful when corrections are required or historical analysis is not needed. It is simple but removes the ability to report using the former value.

Type 2: Add a new version

A new dimension row is created for each relevant change. The row usually includes a surrogate key, effective-from date, effective-to date and current-record flag. Existing facts retain the key associated with the historical version.

For a Type 2 dimension, a point-in-time lookup generally selects the record satisfying:

The boundary convention must be documented. The condition assumes non-overlapping validity periods and a consistent time zone or date definition. An open-ended current record may use a designated maximum date.

Type 3: Add a previous-value attribute

The current and previous values are stored in the same row. This supports limited comparison, such as current region versus immediately previous region, but does not preserve an unlimited history.

SCD selection should be driven by reporting requirements. Type 2 is not automatically the best choice: it increases storage, complicates loading and may confuse users if current and historical views are not clearly labelled.

8. Semantic Modelling and Metrics

A semantic model is a business-facing layer that translates physical data structures into understandable analytical objects. It may expose entities such as Sales, Customers and Products while hiding technical keys, staging tables and implementation details.

Semantic models commonly define:

• relationships and filter propagation;
• friendly names and descriptions;
• approved measures;
• data types and formats;
• row-level security;
• hierarchies and drill paths;
• synonyms and metadata for discovery.

A metric is not merely a calculation; it is a governed definition. “Active customer” might mean a customer with at least one completed purchase in the previous 90 days. The period, transaction status, customer exclusions and time zone must be specified.

Useful metrics include:

Each formula requires a defined numerator and denominator. Average Order Value should not divide revenue by order lines when the stated concept is orders. Conversion Rate requires a consistent definition of “qualified visit.” A denominator of zero must be handled explicitly, usually by returning null or a documented not-applicable result rather than an arbitrary zero.

Metrics should also specify filter behaviour. For example, revenue may respond to date, region and product filters, while a benchmark metric may intentionally ignore a product filter. Hidden filter behaviour is a major source of disagreement between reports.

9. Worked Example: Designing a Retail Sales Model

Suppose a retailer wants to analyse sales by date, product, store and customer.

Step 1: Identify the process

The process is completed retail sales, excluding cancelled orders. Returns are not silently subtracted from sales; they are modelled as a separate return process or explicitly represented with signed quantities according to an approved accounting policy.

Step 2: Declare the grain

The grain is one row per product line on a completed customer order. Therefore, a five-line order produces five fact rows.

Step 3: Select dimensions

The dimensions are Date, Product, Store and Customer. OrderNumber is retained as a degenerate dimension because users may need to drill from aggregated results to an order identifier.

Step 4: Select measures

Quantity, GrossAmount, DiscountAmount and CostAmount are stored at line grain. Net revenue is calculated as GrossAmount minus DiscountAmount, provided both values use the same currency and treatment of tax.

Step 5: Check additivity

Quantity and monetary line amounts are additive across date, product, store and customer, subject to the organisation’s currency policy. Unit price is not additive. A stock balance would require a snapshot model and should not be joined as though it were a sales-line measure.

Step 6: Establish history

If a product changes category, Type 2 history may be required so that prior sales remain associated with the category applicable at the time of sale. If management only wants current category reporting, Type 1 may be sufficient, but the loss of historical classification must be accepted.

Step 7: Define metrics

If the model contains revenue of 48,000 currency units and 1,200 completed orders, then:

If revenue is 48,000 and cost is 30,000:

The results are meaningful only if revenue and cost cover the same transactions, period, currency and tax treatment.

10. Applied Case: Northstar Retail Group

Background

This illustrative case concerns Northstar Retail Group, which operates stores and an online channel. Executives reported that regional sales dashboards disagreed with finance reports. Product managers also found that historical category trends changed whenever products were reclassified.

Problem

The organisation had separate reports built from point-of-sale, e-commerce and finance extracts. One report counted orders, another counted order lines, and a third included cancelled transactions. Product category was overwritten in the source system, so historical sales were shown under current categories.

Analysis

The modelling team documented the business processes and found that sales, returns, inventory and targets had different grains. They also identified inconsistent definitions of revenue, tax and completed order status. Directly joining these datasets would have multiplied measures.

Approach and solution

The team created separate sales and returns fact tables, a daily inventory snapshot and a monthly target fact. Date, Product, Store, Customer and Channel were designed as conformed dimensions. Product classification used Type 2 history, while spelling corrections used Type 1 updates. A governed semantic layer defined Revenue, Completed Orders, Average Order Value and Gross Margin Percentage.

The team then reconciled a sample month to finance, tested duplicate keys, documented null handling and restricted sensitive customer attributes through role-based access. Dashboard authors were required to use certified measures rather than recreate calculations independently.

Outcome

Regional totals became consistent because each report used the same inclusion rules and grain-aware measures. Historical category analysis became stable, and managers could distinguish current product structure from historical classification. The principal improvement was not a particular database technology; it was the explicit alignment of business definitions, grain, history and metric governance.

Learning points

• Reconciliation problems often originate in definitions rather than arithmetic.
• Separate business processes should not be forced into one fact table.
• Historical analysis requires deliberate dimension-versioning policies.
• Certified semantic measures reduce duplication and improve trust.

11. Quality, Governance and Implementation

A BI model should be tested at structural, data and business levels. Structural tests examine primary-key uniqueness, foreign-key validity, relationship cardinality and unexpected nulls. Data tests examine row counts, totals, duplicates, date ranges and reconciliation to trusted sources. Business tests ask whether results make sense to subject-matter experts.

Important controls include:

• documented grain for every fact table;
• data dictionaries and metric definitions;
• ownership for dimensions and measures;
• lineage from report to source;
• version-controlled model changes;
• automated freshness and quality monitoring;
• access controls and row-level security;
• privacy minimisation and retention rules.

Customer data may contain personally identifiable information. Models should expose only attributes required for legitimate analytical purposes. Access should follow least-privilege principles, and sensitive fields should be masked, encrypted or excluded where appropriate. Regulatory requirements may govern consent, retention, cross-border transfer and rights of access or deletion.

AI-assisted BI can generate descriptions, suggest relationships or produce natural-language queries, but it does not remove the need for model governance. AI-generated calculations may use incorrect denominators, misinterpret relationships or expose restricted data. Human review, auditability and permission-aware deployment remain necessary.

12. Common Failure Modes and Best Practices

Common failures include mixing multiple grains, joining facts before aggregation, using natural keys as historical identifiers, overwriting dimensions without considering history, averaging ratios incorrectly and allowing every report author to define core metrics independently.

Best practices are to:

1. begin with business processes and decisions, not source tables;
2. state grain in one precise sentence;
3. separate facts by process and grain;
4. use conformed dimensions where cross-process analysis is required;
5. make additive behaviour explicit;
6. use surrogate keys when historical versions are needed;
7. publish certified semantic measures with descriptions;
8. test totals against authoritative sources;
9. document assumptions, exclusions, time zones and currency rules;
10. design for both performance and understandable self-service use.

Chapter Summary

Data modelling provides the structure and meaning on which trustworthy business intelligence depends. Relational models support transactional integrity through entities, keys and relationships, whereas dimensional models organise analytical data around facts and descriptive dimensions. The grain of a fact table is its most important design statement because it determines what one row means and which measures can be safely aggregated.

Star schemas provide simple, accessible analytical structures; snowflake schemas normalise selected dimensions and may reduce redundancy at the cost of additional joins. Hierarchies enable controlled navigation and aggregation, while conformed dimensions support consistent analysis across processes. Slowly changing dimension techniques determine whether historical attribute values are retained, overwritten or partially preserved.

A semantic model makes technical structures understandable and governs relationships, security and reusable metrics. Reliable calculated metrics require clearly defined numerators, denominators, filters, units and exclusions. Finally, quality testing, privacy protection, lineage, ownership and human oversight are essential to ensure that a technically functioning model also produces defensible business information.

Key Terms

Data model: A structured representation of data objects, relationships and business rules.

Relational model: A table-based model using rows, columns and keys.

Primary key: An attribute or set of attributes that uniquely identifies a row.

Foreign key: An attribute that references a key in another table.

Normalisation: Structuring data to reduce redundancy and update anomalies.

Fact table: A table recording measurable events or states at a declared grain.

Dimension table: A table containing descriptive context for facts.

Measure: A numeric value used for analysis or calculation.

Grain: The exact meaning and level of detail represented by one fact row.

Surrogate key: A system-generated identifier used independently of a source key.

Degenerate dimension: A dimension identifier stored in a fact table without a separate dimension table.

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

Star schema: A central fact connected directly to denormalised dimensions.

Snowflake schema: A dimensional schema with one or more normalised dimensions.

Hierarchy: Ordered levels that support navigation and aggregation.

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

Semantic model: A business-facing layer that defines analytical objects, relationships and metrics.

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 others.

Metric governance: The controlled definition, ownership and use of analytical measures.

Review Questions

Short Answer Questions

1. What is the difference between a fact table and a dimension table?
2. Why must the grain of a fact table be declared before measures are selected?
3. Distinguish an additive measure from a non-additive measure.
4. What is the main purpose of a surrogate key in a Type 2 slowly changing dimension?
5. State one advantage and one limitation of a star schema.

Long Answer Questions

1. Compare relational and dimensional modelling, including their different priorities and typical uses.
2. Explain how a retail sales star schema could be designed from business process through to measures.
3. Describe Types 0, 1, 2 and 3 slowly changing dimensions and give an appropriate use for each.
4. Explain why conformed dimensions are important when integrating sales, returns and inventory analysis.
5. Discuss the role of a semantic model in metric consistency, security and self-service BI.

Analytical / Application Questions

1. A company joins daily inventory records directly to sales-line records and reports inflated totals. Diagnose the modelling problem and propose a grain-aware solution.
2. A product changes category on 1 July, and management needs both current-category and historical-category reporting. Recommend a dimension strategy and explain how facts should be associated with dimension versions.
3. An executive dashboard reports a 42% average margin by averaging individual transaction margins. Explain why this may be misleading and propose a more defensible calculation using aggregated revenue and cost.

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 exact meaning and level of detail represented by one row

C. The storage format used by the database

D. The number of dimensions connected to the fact

Correct Answer: B. The exact meaning and level of detail represented by one row

Question 2

Which measure is generally non-additive?

A. Units sold

B. Revenue

C. Unit price

D. Quantity shipped

Correct Answer: C. Unit price

Question 3

What is the principal characteristic of a Type 2 slowly changing dimension?

A. It permanently deletes old values

B. It overwrites every historical value

C. It creates a new dimension version when a relevant change occurs

D. It stores only the previous value in an extra column

Correct Answer: C. It creates a new dimension version when a relevant change occurs

Question 4

Which schema normally has a central fact table connected directly to descriptive dimensions?

A. Star schema

B. Network schema

C. Hierarchical schema

D. Flat-file schema

Correct Answer: A. Star schema

Question 5

Revenue is 72,000 currency units and completed orders total 1,800. What is Average Order Value?

A. 4 currency units per order

B. 40 currency units per order

C. 400 currency units per order

D. 1,800 currency units per order

Correct Answer: B. 40 currency units per order

Question 6

Which practice best prevents double counting when combining facts at different grains?

A. Join all facts before applying filters

B. Aggregate facts to a compatible grain before combining them

C. Remove all dimension keys

D. Replace measures with text descriptions

Correct Answer: B. Aggregate facts to a compatible grain before combining them

Question 7

What is the main purpose of a conformed dimension?

A. To ensure consistent meaning across fact tables

B. To remove the need for foreign keys

C. To store only numerical measures

D. To replace all source systems

Correct Answer: A. To ensure consistent meaning across fact tables

Question 8

Which formula correctly represents Gross Margin Percentage?

A. Cost ÷ Revenue × 100

B. Revenue ÷ Cost × 100

C. (Revenue − Cost) ÷ Revenue × 100

D. (Revenue + Cost) ÷ Revenue × 100

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

Question 9

Which dimension technique overwrites an old attribute value and does not preserve its history?

A. Type 0

B. Type 1

C. Type 2

D. Type 3

Correct Answer: B. Type 1

Question 10

A daily bank balance can usually be summed across accounts but not across dates. What type of measure is it?

A. Fully additive

B. Semi-additive

C. Non-numeric

D. Degenerate

Correct Answer: B. Semi-additive