Lesson overview
Contrast operational normalised systems with analytical systems that use denormalisation, data warehouses, ETL and business intelligence reporting.
Starter
Write down the user need, the data being stored or searched, and the decision the system has to support. Then predict which representation, index, query method or governance control will matter most in this lesson.
Learning objectives
- Explain why analytical systems may denormalise data.
- Describe data warehouse and ETL roles.
- Connect warehouse design to business intelligence questions.
Learning outcomes
- Students can explain why analytical systems may denormalise data.
- Students can describe data warehouse and ETL roles.
- Students can connect warehouse design to business intelligence questions.
Key vocabulary, acronyms and terminology
- denormalisation
- Deliberately adding redundancy to make reading or reporting easier.
- data warehouse
- A central analytical store integrating data for reporting and analysis.
- business intelligence
- Use of data to monitor performance and support decisions.
- BI
- Business Intelligence: using governed data, reports and dashboards to support organisational decisions.
- ETL
- Extract, Transform and Load: a process for moving cleaned and reshaped data into analytical stores.
- ELT
- Extract, Load and Transform: a variant where data is loaded before transformation, often in cloud warehouses.
- measure
- A numeric value analysed in reports.
- dimension
- A descriptive perspective such as date, product or region.
Detailed teaching notes
Core concept
OLTP systems are designed for correct transactions; analytical systems are designed for reading, summarising and comparing. Denormalisation can make reporting simpler and faster when managed carefully.
Representation choice
A warehouse may store repeated product and date descriptors alongside sales measures so reports do not perform complex operational joins for every dashboard.
Method and reasoning
Extract from operational systems, transform data into consistent business definitions, load into warehouse structures and build BI reports over measures and dimensions.
Risk and limitation
Denormalised data can become inconsistent if not governed. Analytical stores need clear refresh rules, lineage and definitions so reports are trusted.
Degree-level deep dive
Analytics has different priorities
An OLTP schema protects transactions, while a warehouse supports repeated reading, summarising and comparison. Denormalisation can be justified when it makes analytical questions faster and simpler to answer.
ETL gives shared definitions
Extract, Transform and Load is where operational data is cleaned, conformed and reshaped. Without this step, dashboards can disagree because teams use different definitions of customer, sale, date or region.
What excellent work shows
A strong answer chooses measures, dimensions and refresh rules, then explains a denormalisation trade-off. It should mention lineage and governance because a fast dashboard is harmful if users cannot trust its definitions.
Concrete example to study
Sales reporting row
warehouse_sales_monthly(month, store_region, product_category, sales_amount, units_sold)
Example row: 2026-03, North East, Textbooks, 18420.50, 740Reasoning
- The row repeats descriptive region and category labels to simplify reporting.
- The sales amount and units sold are measures.
- Month, region and product category are dimensions used to group and compare performance.
Reveal takeaway
Denormalisation is justified here because the warehouse is optimised for repeated analytical reading, not transaction updates.
Worked example
Scenario
Managers want monthly sales by region and product category.
Worked solution
- Extract sales, product and region data from operational systems.
- Transform names, currencies and date formats into consistent definitions.
- Load a warehouse table with sales measures and descriptive dimensions.
- Build BI reports that group sales by month, region and category.
Reveal model result
The warehouse is not a replacement for the operational database. It is a read-optimised analytical layer for stable reporting and decision support.
Applied retrieval task
Design a simple BI reporting dataset.
Deliverables
- Name one operational source.
- Choose two measures and three dimensions.
- Explain one denormalisation used to speed reporting.
Success checks
- Measures and dimensions are separated clearly.
- The answer names a governance risk.
Common misconception
Denormalisation is not bad design by default. It is a deliberate analytics trade-off, but it must be controlled.
Quick checks
1. In this lesson, why does denormalisation matter?
2. Which answer best shows degree-level understanding of denormalisation, warehouses and business intelligence?
Digital exam practice
Example exam task
A retailer's operational database is normalised, but managers need fast dashboards for sales by week, store and product category. In your answer, define the relevant objects or data structures, use course-specific vocabulary, show the method rather than only the result, and finish with a decision about the storage or retrieval system.
Notation and technical toolkit
What a good answer is expected to show
A strong answer for Denormalisation, Warehouses and Business Intelligence the answer should explain denormalisation, data warehouse, ETL and business intelligence, and contrast OLTP with analytics needs. It should connect the formal or technical representation to the user's information need instead of listing terms without using them.
How to solve it
- Explain why the normalised OLTP schema is not ideal for repeated dashboard queries.
- Describe ETL from operational sources into a data warehouse.
- Choose measures and dimensions for the BI question.
- Justify denormalisation while noting governance and refresh risks.
Model answer
Reveal model answer
The OLTP database protects sales transactions, but the dashboard repeatedly reads large summaries by week, store and product category. A data warehouse can receive data through ETL, where operational sales are extracted, cleaned, conformed and loaded into read-optimised tables. Denormalisation may repeat store and category descriptors so BI queries are simpler and faster. The design needs refresh schedules and data definitions so the dashboard remains trusted.
Practise next
- Identify measures and dimensions in a student-attendance dashboard.
- Explain one danger of copying operational data into a warehouse without transformation.
Self-marking criteria
- Defines the scenario objects, data forms or system components before solving.
- Uses the lesson vocabulary accurately and in context.
- Shows a clear method with enough working for a marker to follow.
- Connects the result back to retrieval, storage, analytics or governance.
- States a limitation, trade-off or quality risk rather than presenting the answer as absolute.
Extension
Compare ETL with ELT and identify when each might be preferred. Link your comparison to data quality checks, warehouse cost, transformation ownership and how quickly a dashboard needs updated data.
Study route
Save a short worked answer from this lesson using this pattern: define the need or data, choose the representation, show the method, state the result and interpret the implication for the system.
Next lesson: Star Schemas, Snowflake Schemas and OLAP.