Download
data warehouse v applying the star schema n.
Skip this Video
Loading SlideShow in 5 Seconds..
Data Warehouse (V): Applying the star schema PowerPoint Presentation
Download Presentation
Data Warehouse (V): Applying the star schema

Data Warehouse (V): Applying the star schema

2 Views Download Presentation
Download Presentation

Data Warehouse (V): Applying the star schema

- - - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

  1. Data Warehouse (V): Applying the star schema Developed by: Dr Eddie Ip Modified by: Dr Arif Ansari

  2. Outline • Review of star schema • Example • Class exercise • The DW Design Process

  3. Review: Dimensional Design • Fact/Measure: The thing one wants to measure • Dimension: The way one wants it to be broken into

  4. Example • Kitchen appliances manufacturer • Sells products to customers (retailers) through sales reps • p4-8 Adamson & Venerable (reader)

  5. Dimension Fact

  6. Star Schema • Fact table (at the center) plus its related dimension tables (on the sides) • Example (p. 6-7 A & V)

  7. Design principles • Facts : fully normalized (no redundancy) • PK = concatenated key=concatenation of dimension keys • Dimensions : Denormalized (all relevant attributes in one table) • Different processes, different stars • Different grains, different fact tables

  8. Grain • Level of detail at which measures are recorded • Also provide meaning to a number stored in the fact table

  9. Grain • All rows in one fact table should be at the same grain • E.g., sales from salesperson W for item X on day Y under order Z

  10. Class Exercise • Star schema of a computer Hotel Example

  11. DW: Data Modeling • Process of translating business concepts into actual physical data structures • Dimensional Business Model (star schema) > Physical Model • Some organization may keep a traditional logical model (ER diagram) • Simplicity of star schema may be sufficient

  12. Design: DW vs OLTP • DW designed to answer business questions • DW: to provide answers to business users • Dimensional design -- user’s view of the DB • Capacity should be built in the DW at the design stage: what facts, what dimension • OLTP: to ensure operations run well • OLTP Design -- IT professional’s view of the DB

  13. Design: DW vs OLTP • OLTP • High transaction rate • Constantly changing (volatile) • No redundancy (normalized) • Predictable SQL queries • Recoverable (e.g., mirrored disk)

  14. Design : DW vs OLTP • DW • Data structure understandable (not necessarily normalized) • Mostly static (non-volatile) • Unpredictable and complex SQL queries (for analytical purposes) • Recoverable: regular backups of static DB

  15. Summary • Review of star schema • Class exercise ( star schema of a computer) • Design Issues: DW Vs OLTP