Lesson overview
Teach dimensional modelling for analytical systems, including fact tables, dimension tables, star schema, snowflake schema and OLAP-style slice, dice, drill-down and roll-up.
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
- Identify fact tables and dimension tables.
- Compare star schema and snowflake schema.
- Explain OLAP operations such as slice, dice, drill-down and roll-up.
Learning outcomes
- Students can identify fact tables and dimension tables.
- Students can compare star schema and snowflake schema.
- Students can explain OLAP operations such as slice, dice, drill-down and roll-up.
Key vocabulary, acronyms and terminology
- fact table
- Central table containing measures and foreign keys to dimensions.
- dimension table
- Descriptive table used to analyse facts by context.
- star schema
- Dimensional model with fact table connected directly to dimensions.
- snowflake schema
- Dimensional model where dimensions are further normalised.
- OLAP
- Online Analytical Processing for multidimensional analysis.
- drill-down
- Moving from summary to more detailed analysis.
Detailed teaching notes
Core concept
Dimensional models organise analytical data around measurable events and the dimensions used to ask business questions about those events.
Representation choice
A sales fact table might contain sales_amount and quantity, linked to date, product, store and customer dimensions. In a star schema, those dimensions are wide and direct; in a snowflake schema, dimension details are split into related tables.
Method and reasoning
Start with the business process and grain of the fact table, then identify dimensions. Choose star for simpler query performance and snowflake where dimension normalisation or shared hierarchies are important.
Risk and limitation
A fact table without a clear grain causes double counting. Every row must represent a precise level, such as one sale line, one daily summary or one click event.
Degree-level deep dive
Grain controls correctness
The fact table grain states what one row means. If the grain is unclear, measures can be double-counted, mixed across levels or interpreted inconsistently in OLAP reports. Always define the event or summary level before choosing measures.
Dimensions support navigation
Star schemas make dimensions wide and direct for simpler queries. Snowflake schemas normalise dimensions into hierarchies, which can help maintenance but may make querying less direct.
What excellent work shows
A strong answer defines the fact grain, identifies measures and dimensions, and gives an OLAP operation such as slice, dice, roll-up or drill-down. It should explain the consequence of the modelling choice.
Concrete example to study
Star schema sketch
FactSubmission(date_key, module_key, status_key, submission_count)
DimDate(date_key, week, semester, year)
DimModule(module_key, module_code, school)
DimStatus(status_key, status_label)Reasoning
- The fact table grain is submissions counted by date, module and status.
- A roll-up can move from week to semester.
- A drill-down can move from school to module.
Reveal takeaway
Dimensional modelling works only when the grain and dimensions match the questions users ask, because every OLAP total depends on what a fact row represents.
Worked example
Scenario
A university analyses enrolments by module, semester and student demographic.
Worked solution
- Define the grain: one enrolment event per student per module per semester.
- Create a fact table with count measure and keys to module, date and student dimensions.
- Use dimensions to slice by semester or dice by module and demographic.
- Roll up from module to department or drill down from year to semester.
Reveal model result
The dimensional model supports OLAP questions because facts and descriptive dimensions are separated deliberately.
Applied retrieval task
Design a star schema for website visits.
Deliverables
- Define the fact table grain.
- Choose at least three dimension tables.
- Write one slice and one drill-down question.
Success checks
- The grain prevents double counting.
- Dimensions support the stated questions.
Common misconception
A fact table is not just any large table. It must have a clear event or summary grain and measurable facts.
Quick checks
1. In this lesson, why does fact table matter?
2. Which answer best shows degree-level understanding of star schemas, snowflake schemas and olap?
Digital exam practice
Example exam task
A university wants OLAP reports showing assessment submissions by module, school, week and submission status. 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 Star Schemas, Snowflake Schemas and OLAP the answer should design fact and dimension tables, state the grain, compare star and snowflake schema choices, and explain an OLAP operation. 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
- State the fact-table grain clearly.
- Choose measures and dimension keys.
- Design a star schema and explain when snowflaking a dimension may help.
- Describe slice, dice, roll-up or drill-down for the scenario.
Model answer
Reveal model answer
The fact table could have one row per assessment submission event, with measures such as submission_count or lateness_minutes and keys to date, module, school and status dimensions. A star schema keeps those dimensions directly attached for simpler BI queries. A snowflake schema might split module into module and school tables if the hierarchy is shared or maintained separately. OLAP can drill down from school to module or roll up weekly submissions to semester totals.
Practise next
- Define the grain for a sales fact table and explain why it matters.
- Compare a star and snowflake design for a product dimension.
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
Sketch how slowly changing dimensions would affect a staff or product dimension.
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: Data Marts, Analytical Pipelines and Dashboards.