Data Warehouse Modelling
- More on Dimensions
- Case Study
PA220: Database systems for data analytics
Contents
• More on dimensions
• revision of SCD on other examples
• time zone issues
• country specifics
• Case study of a grocery chain
• sales and inventory
• bus matrix creation
October 10, 2024 PA220 DB for Analytics 2
Revision of SCD on other examples
• See admt07_dw pdf
October 10, 2024 PA220 DB for Analytics 3
Time zones and location
• A business operating in multiple countries
• transactions in different time zones
• country specific information
• holidays, holiday shop opening hours, cargo-delivery limitations
• location data
• latitude, longitude
October 10, 2024 PA220 DB for Analytics 4
Source: The Data Warehouse Toolkit, by Kimbal
Time zones
• UTC, GMT – can be used to conform the time events
• Time zone offset as a degenerate dimension in the fact table?
• + consolidates/normalizes events over different time zones
• + can still differentiate time zones
• - grouping data by
time periods
(morning / rush hours / …)
October 10, 2024 PA220 DB for Analytics 5
Source: The Data Warehouse Toolkit, by Kimbal
Country Specifics
• Holidays vary
from country to country
• other law implications
October 10, 2024 PA220 DB for Analytics 6
Location Data
• GPS coordinates - longitude, latitude
• location of a shop, airport, …
• position of an aircraft, car, device, …
• many different values → cannot be “enumerated”
• not a regular dimension
• not a measure
• Attributes in a dimension – e.g., shop location
• Degenerate dimension – e.g., aircraft position during flight
October 10, 2024 PA220 DB for Analytics 7
Case study on a grocery chain
• See admt06_dw pdf
October 10, 2024 PA220 DB for Analytics 8