Data Modelling for Business Intelligence

Introduction

Business intelligence depends less on attractive charts than on the quality of the model beneath them. A report may contain correct-looking visuals while producing incorrect results because tables are joined at the wrong grain, dates are interpreted inconsistently, or a many-to-many relationship duplicates values. Data modelling provides the structure that allows Power BI, Tableau, and other BI platforms to convert operational data into reliable analysis.

A data model identifies business events, describes the entities involved, defines relationships, and establishes how measures should be calculated. The central design task is to represent business questions clearly while preserving accurate detail and acceptable performance.

Learning Objectives

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

• distinguish transactional, dimensional, and semantic models;
• identify fact and dimension tables and define their grain;
• design a basic star schema and appropriate relationships;
• create date dimensions, hierarchies, and business-friendly measures;
• explain slowly changing dimensions and many-to-many relationships;
• select suitable aggregation strategies; and
• evaluate modelling decisions for accuracy, performance, governance, and usability.

1. Three Views of Data Modelling

Transactional models

A transactional, or operational, model is designed to record business activity efficiently. It commonly uses normalized tables so that each fact is stored once. For example, an order-processing system may contain separate tables for orders, order lines, customers, products, payments, and shipping. Normalization reduces update anomalies: changing a customer address does not require changing it in every historical order row.

Transactional models are optimized for frequent inserts and updates, concurrency, and operational integrity. They are not automatically suitable for analytical reporting. Queries may require many joins, historical attributes may be overwritten, and the structure may reflect software processes rather than business questions.

Dimensional models

A dimensional model reorganizes data for analysis. It normally contains measurable business events in fact tables and descriptive context in dimension tables. The classic arrangement is the star schema: one central fact table is connected directly to several dimensions.

Dimensional models emphasize understandable queries, predictable filtering, historical analysis, and efficient aggregation. They may deliberately contain repeated descriptive values because analytical usability and query performance are more important than eliminating every instance of redundancy.

Semantic models

A semantic model is the business-facing layer that gives data consistent meaning. In Power BI, a semantic model includes tables, relationships, measures, calculated columns, hierarchies, formatting, security rules, and metadata. Tableau workbooks and published data sources perform comparable functions.

The semantic model should answer questions such as: What does “sales” include? Is revenue recognized at order date or shipment date? Are returns negative sales or a separate event? Which currency and exchange rate are used? A technically valid model is not necessarily semantically valid unless these definitions are explicit.

2. Grain: The Foundation of a Fact Table

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

• one row per product per sales order line;
• one row per customer per day;
• one row per store per month; or
• one row per shipment event.

A fact table should not mix grains casually. If sales rows are at order-line level and monthly targets are inserted into the same table, aggregating the target may repeat it once for every order line. Separate fact tables with conformed dimensions are usually safer.

A useful design statement is: “One row in FactSales represents one product line on one customer order.” This statement determines which dimensions can relate naturally to the fact and which measures are additive.

Additivity of measures

Measures may be classified as follows:

• Additive: can be summed across all relevant dimensions, such as sales amount or units sold.
• Semi-additive: can be summed across some dimensions but not time, such as account balance or inventory at a point in time.
• Non-additive: should not be summed, such as percentages, ratios, and unit prices. They should generally be recalculated from their components.

For example, a sales margin percentage should be calculated as:

Here, Sales and Cost are monetary totals in the same currency. The formula assumes Sales is non-zero. Averaging row-level margin percentages can produce a misleading result because it gives small and large transactions equal weight.

3. Fact and Dimension Tables

Fact tables

A fact table records events, measurements, or periodic snapshots. It typically contains:

• foreign keys to dimensions;
• numeric measures such as quantity, revenue, cost, or discount; and
• identifiers useful for tracing a source transaction.

A fact table may be transactional, periodic snapshot, or accumulating snapshot. A transactional fact records each event, a periodic snapshot records a state at regular intervals, and an accumulating snapshot tracks a process with milestones, such as order placement, picking, dispatch, and delivery.

Dimension tables

Dimensions provide descriptive context. Typical dimensions include Date, Product, Customer, Employee, Store, Supplier, and Geography. A dimension normally has a primary key, descriptive attributes, and sometimes reporting attributes such as category, region, or manager.

A warehouse dimension should usually use a surrogate key rather than relying only on an operational identifier. A surrogate key is an internal, stable identifier such as ProductKey = 1048. The operational product code remains as a business key. Surrogate keys support historical versions and prevent source-system key changes from corrupting relationships.

4. The Star Schema

In a star schema, the fact table is central and dimensions surround it. Relationships generally flow from the one side of a dimension to the many side of a fact table.

Figure: A star schema in which dimensions filter a central sales fact table.

A clean star schema often performs better and is easier to use than a highly normalized snowflake schema. Snowflaking can be justified where dimensions are extremely large, shared across many models, or require independent governance, but it introduces more joins and can make self-service analysis harder.

Relationships

A relationship defines how rows in one table correspond to rows in another. The common relationship is one-to-many: one ProductKey in DimProduct corresponds to many rows in FactSales. The dimension key must be unique on the one side.

Best practice in Power BI and Tableau-style modelling includes:

1. use a single, clear filtering direction from dimensions to facts where possible;
2. avoid unnecessary bidirectional filtering, which can create ambiguity and unexpected totals;
3. validate uniqueness on the dimension side; and
4. test totals after every relationship change.

A model may contain active and inactive relationships. For example, FactSales may contain OrderDateKey and ShipDateKey, while DimDate has one active relationship to OrderDateKey and an alternative inactive relationship to ShipDateKey. Measures can explicitly use the appropriate date role when the tool supports it.

5. Date Dimensions and Hierarchies

A date dimension should be a complete, continuous table covering the reporting period, including dates with no transactions. It commonly includes DateKey, calendar date, day name, day number, week, month, quarter, year, fiscal period, holiday flag, and working-day flag.

The date dimension should be marked or designated as a date table where the BI platform provides that feature. This prevents unreliable automatic date handling and supports time-intelligence calculations.

A hierarchy is an ordered path through attributes. A calendar hierarchy may be Year → Quarter → Month → Date. A geography hierarchy may be Country → Region → City → Store. Hierarchies should reflect valid drill paths; not every attribute belongs in one hierarchy. For example, Product Brand and Product Category may not have a strict parent-child relationship unless the business defines one.

A date key is often stored as an integer such as 20250308, while the actual date is stored in a date data type. The integer key is useful for warehouse integration, but visuals and calculations should use the correctly typed date column.

6. Slowly Changing Dimensions

A slowly changing dimension (SCD) manages changes in descriptive attributes. Consider a customer who moves from the North region to the South region. The correct treatment depends on whether reports should show the current region or the region at the time of each sale.

• Type 0: retain the original value permanently.
• Type 1: overwrite the old value; history is not preserved.
• Type 2: create a new dimension row for the new version, usually with effective-from, effective-to, and current-row fields.
• Type 3: retain limited history in additional columns, such as CurrentRegion and PreviousRegion.

Type 2 is appropriate when historical analysis must reproduce the context known at the time of the event. A fact row links to the relevant version through a surrogate key. The design must prevent overlapping effective periods and ensure that each event maps to one valid version.

Type 1 is simpler and appropriate for corrections, such as fixing a spelling error. It is inappropriate when management needs historical accountability. SCD policy should be documented for every important dimension.

7. Many-to-Many Relationships

A many-to-many relationship exists when one entity can relate to many entities on both sides. Examples include customers belonging to several loyalty groups, products having several suppliers, and sales representatives sharing responsibility for an account.

The robust solution is usually a bridge table. For a product-to-promotion relationship, DimProduct and DimPromotion can be connected through BridgeProductPromotion. The bridge contains one row for each valid pair. If allocation is required, it may also contain an allocation percentage.

Many-to-many designs require care because a fact may be counted repeatedly when filters travel through multiple paths. A bridge table should have a clearly defined grain, and measures should be tested against known totals. Where responsibility is shared, an allocation rule must be defined. If a sale of £1,000 is assigned equally to two representatives, each receives £500; without an allocation rule, reporting the full £1,000 for both representatives would overstate total sales.

8. Aggregation and Performance

Aggregation reduces the amount of detail scanned during common queries. A monthly product-store summary may support dashboards while the transaction-level fact remains available for investigation.

Suppose a detailed fact contains 24 million sales rows and a monthly summary contains 240,000 rows. A query answered entirely from the summary scans approximately 100 times fewer rows, assuming equivalent filtering and a suitable aggregation design:

This is a row-count comparison, not a guaranteed 100-fold speed improvement. Performance also depends on storage mode, compression, cardinality, joins, measures, hardware, and query design.

Aggregations are safe only when the summary preserves the required grain and measures. Additive totals such as quantity and revenue can usually be aggregated. Distinct customer counts are more difficult: summing monthly distinct counts can count the same customer more than once across months. Such measures require an appropriate distinct-count strategy or calculation at the required grain.

In Power BI, model performance is also influenced by column cardinality, relationship design, storage mode, and whether transformations occur during data load or at query time. In Tableau, extracts, data-source filters, aggregate awareness, and workbook design have similar importance.

9. Worked Example: Designing a Retail Model

A retailer wants to analyse revenue, units, discount, margin, and customer retention by date, product, store, and customer segment.

Grain statement

One row in FactSales represents one product line on one completed sales order.

Proposed tables

• FactSales: DateKey, ProductKey, StoreKey, CustomerKey, OrderNumber, Quantity, GrossSales, Discount, Cost.
• DimDate: DateKey, Date, Month, Quarter, Year, FiscalYear, IsHoliday.
• DimProduct: ProductKey, ProductCode, ProductName, Brand, Category.
• DimStore: StoreKey, StoreName, City, Region, StoreFormat.
• DimCustomer: CustomerKey, CustomerCode, Segment, SignupDate.

Net sales is defined as GrossSales − Discount. Gross margin is NetSales − Cost. For a sample set of sales with GrossSales = £12,000, Discount = £1,000, and Cost = £7,000:

The model should not store only Margin% as a fact because percentages are non-additive. It should store the monetary components and calculate the ratio at the filter context selected by the user.

10. Illustrative Case: Northstar Retail

Background. Northstar Retail operates 80 stores and an online channel. Its executives report sales by month, while regional managers need daily store and product analysis.

Problem. The existing dashboard joins an order table directly to a customer table and uses the order date as text. Returns are mixed with sales, customer regions are overwritten when customers move, and several products have multiple category assignments. Monthly sales totals differ between the finance report and the BI dashboard.

Analysis. Investigation reveals three causes. First, product-category joins duplicate order lines. Second, the dashboard filters on shipment date while finance uses completed-order date. Third, current customer region is being applied retrospectively to historical orders.

Approach. The modelling team creates separate FactSales and FactReturns tables, each with an explicit grain. A Product-Category bridge is introduced, with a primary reporting category selected for standard totals. A complete DimDate is created, and OrderDate is documented as the official sales date. A Type 2 DimCustomer preserves historical regions. Finance definitions are implemented as governed measures in the semantic model.

Outcome. Finance and BI totals reconcile. Managers can analyse historical regional performance without rewriting the past, while analysts can inspect returns separately. The model also supports an alternative shipment-date measure without creating ambiguous active relationships.

Learning points. Reconciliation is a modelling activity, not merely a visual-design activity. Grain, date roles, bridge-table logic, and historical policy must be agreed with business owners before dashboards are published.

11. Governance, Ethics, and Quality Assurance

A model can be technically correct yet ethically harmful if it exposes sensitive information or embeds unfair classifications. Customer dimensions may contain personal data, demographic attributes, or inferred segments. Access should follow least privilege, and row-level security should be tested using realistic user roles.

Important controls include:

• data lineage from source to report;
• documented definitions for measures and dimensions;
• validation against trusted financial or operational totals;
• ownership of each table and measure;
• protection of personal and confidential data;
• auditability of changes; and
• retention and deletion policies consistent with applicable law.

AI-assisted modelling can suggest relationships, generate calculations, or identify anomalies. Such suggestions must be reviewed because automated tools may infer relationships from column names, overlook business grain, leak sensitive data, or produce plausible but incorrect measures. Human subject-matter validation remains essential.

12. Best-Practice Design Sequence

A practical modelling workflow is:

1. collect business questions and definitions;
2. identify business processes and facts;
3. declare the grain of each fact table;
4. identify dimensions and conformed dimensions;
5. select keys and define relationship cardinality;
6. decide how history will be managed;
7. build a complete date dimension;
8. define additive and non-additive measures;
9. test totals, filters, edge cases, and empty periods;
10. optimize performance only after correctness is established; and
11. document the semantic model and publish governed content.

The most important principle is that simplicity should be achieved without hiding business meaning. A compact model with clear grain and trusted measures is more valuable than a large model containing every available column.

Chapter Summary

Data modelling supplies the structure and meaning required for trustworthy business intelligence. Transactional models support operational processing, dimensional models organize facts and descriptive dimensions for analysis, and semantic models expose governed business meaning to report users. The grain of every fact table must be explicit because it determines valid relationships and aggregations. Star schemas normally connect a central fact table to dimensions through one-to-many relationships. Date dimensions, role-playing dates, and valid hierarchies support consistent time analysis. Slowly changing dimensions preserve or overwrite history according to an explicit policy. Bridge tables can resolve many-to-many relationships, but allocation and duplication risks must be controlled. Aggregations improve performance only when their grain and measures are appropriate. Finally, governance, security, reconciliation, and documentation are essential parts of professional BI modelling.

Key Terms

Aggregation: A summarized representation of detailed data.

Bridge table: An intermediate table used to resolve a many-to-many relationship.

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

Dimensional model: An analytical model organized around facts and dimensions.

Dimension table: A table containing descriptive attributes used to filter or group facts.

Fact table: A table recording business events, measurements, or snapshots.

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

Hierarchy: An ordered set of attributes supporting drill-down analysis.

Many-to-many relationship: A relationship in which multiple rows on each side can correspond.

Measure: A calculation or numeric value evaluated in a reporting context.

Non-additive measure: A measure that cannot be summed meaningfully, such as a percentage.

Periodic snapshot: A fact record capturing state at regular intervals.

Semantic model: A governed business-facing layer containing relationships, measures, metadata, and security.

Slowly changing dimension: A method for managing changes in dimension attributes over time.

Snowflake schema: A dimensional design in which dimensions are further normalized.

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

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

Transactional model: An operational model optimized for recording and updating business activity.

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 difference between additive and non-additive measures?
4. Why is a complete date dimension important in BI?
5. What problem does a bridge table solve?

Long Answer Questions

1. Compare transactional, dimensional, and semantic models, including their purposes and limitations.
2. Explain the structure and advantages of a star schema.
3. Describe Type 1 and Type 2 slowly changing dimensions and give a suitable use case for each.
4. Explain how relationship direction and cardinality affect BI results.
5. Discuss the governance and security responsibilities associated with semantic models.

Analytical / Application Questions

1. A sales fact contains order-line rows, while monthly targets are loaded into the same table. Explain the likely reporting problem and design a corrected model.
2. A customer can belong to several loyalty programmes, and a dashboard duplicates customer counts. Design a relationship structure and measurement approach that avoids duplication.
3. A company reports different revenue totals because one dashboard uses order date and another uses shipment date. Propose a date model and semantic definitions that resolve the disagreement.

Multiple Choice Questions with Answers

Question 1

What does the grain of a fact table describe?

A. The colour scheme of a report

B. The meaning of one row in the table

C. The number of dimensions in a model

D. The storage size of the database

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

Question 2

Which measure is normally non-additive?

A. Units sold

B. Sales revenue

C. Gross margin percentage

D. Quantity shipped

Correct Answer: C. Gross margin percentage

Question 3

What is the usual relationship between a product dimension and a sales fact table?

A. Many-to-many

B. One-to-one

C. One-to-many from product to sales

D. Many-to-one from sales to product only

Correct Answer: C. One-to-many from product to sales

Question 4

Which slowly changing dimension type overwrites the previous value?

A. Type 0

B. Type 1

C. Type 2

D. Type 3

Correct Answer: B. Type 1

Question 5

What is the principal purpose of a date dimension?

A. To replace all fact tables

B. To provide consistent calendar and fiscal analysis

C. To store customer addresses

D. To eliminate the need for measures

Correct Answer: B. To provide consistent calendar and fiscal analysis

Question 6

Which structure is commonly used to resolve a many-to-many relationship?

A. Bridge table

B. Date hierarchy

C. Snapshot table only

D. Transaction log

Correct Answer: A. Bridge table

Question 7

A sale has gross sales of £8,000, a discount of £500, and cost of £5,000. What is the gross margin?

A. £2,500

B. £3,000

C. £3,500

D. £7,500

Correct Answer: A. £2,500

Question 8

Why should monthly targets generally not be inserted into an order-line fact table?

A. Targets are always non-numeric

B. They may be repeated for every order line and overstated

C. Order lines cannot contain dates

D. Dimensions cannot filter targets

Correct Answer: B. They may be repeated for every order line and overstated

Question 9

What is a major advantage of a surrogate key in a Type 2 dimension?

A. It removes the need for history

B. It supports separate historical versions of an entity

C. It prevents all duplicate facts

D. It converts measures into dimensions

Correct Answer: B. It supports separate historical versions of an entity

Question 10

Why can summing monthly distinct-customer counts be incorrect for a yearly total?

A. Customers have no keys

B. The same customer may appear in several months

C. Months cannot be stored in a date dimension

D. Distinct counts are always additive

Correct Answer: B. The same customer may appear in several months