Data Warehouse VI Examples of the star schema
- Slides: 16
Data Warehouse (VI): Examples of the star schema Developed by: Dr Eddie Ip Modified by: Dr Arif Ansari 1
Outline • Review of concept of grain • Advanced concepts – Slowly changing dimensions – Snow flake schema – Additivity of measures & other issues • PC Manufacturer example • Homework 1 B 2
Dimension Fact 3
Grain • Level of detail of data • Important concept – defines what a fact really represents 4
Slowly changing dimension • Dimension tables are relatively stable • Sometimes still need change • Change different from OLTP – E. g. , customer address change • Types of change – Type 1 – Type 2 – Type 3 5
Slowly changing dimensions • Type 1 – Simple update • Type 2 – Add new row to dimension table – E. g. , new address, same customer • Type 3 – Add new column – E. g. , new territory designation of sales force 6
Snow-flake schema • Dimension tables further normalized – Number of attributes large (high cardinality) – Create tables for attributes within a dimension table 7
Snow-flake schema • E. g. store dimension has district as attribute • District has district description, district manager, district HQ attributes • Alternative design (snow-flake): 2 separate fact tables 8
Dimension Fact Snow flake schema 9
Additivity • Can facts be added (summarized) across dimensions? – Fully additive (across all dimensions) • Sales, number of units sold – Semi-additive • Additive across some dimensions – E. g. , account balances cannot be added across days – Non-additive 10
Multiple stars • Basic idea: one process, one star • Different stars may be necessary when the business requires data – Of varying grains – Are measured at different intervals (order and shipping) 11
Class exercise • Star schema of a hotel 12
Homework 1 B: Car maker DW • From A& V p. 33 • Concerns: – Is minivan incentive ($200/minivan) working? – Are dealers abusing the lease option? 13
Car maker DW • Measures = ? – Actual sales price – MSRP base price – Options price – Dealer add-ons – MSRP full price (=MSRP base + option) … • Dimension = ? – E. g. , product 14
Car maker DW • Queries can be created to address the concerns • Examples of Reports (p. 40, 41, 42) 15
Summary • • Review of Star Schema Example Class exercise The DW design process (DW Vs OLTP) 16