1 / 16

Star Schema in Data Warehouse: Examples & Concepts

Explore star schema in data warehousing: grain, slowly changing dimensions, snowflake schema, additivity of measures, examples, class exercises, and design process differences from OLTP. Homework and case studies included.

wandaklein
Download Presentation

Star Schema in Data Warehouse: Examples & Concepts

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Data Warehouse (VI): Examples of the star schema Developed by: Dr Eddie Ip Modified by: Dr Arif Ansari

  2. Outline • Review of concept of grain • Advanced concepts • Slowly changing dimensions • Snow flake schema • Additivity of measures & other issues • PC Manufacturer example • Homework 1B

  3. Dimension Fact

  4. Grain • Level of detail of data • Important concept – defines what a fact really represents

  5. 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

  6. 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

  7. Snow-flake schema • Dimension tables further normalized • Number of attributes large (high cardinality) • Create tables for attributes within a dimension table

  8. 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

  9. Dimension Fact Snow flake schema

  10. 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

  11. 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)

  12. Class exercise • Star schema of a hotel

  13. Homework 1B: Car maker DW • From A& V p.33 • Concerns: • Is minivan incentive ($200/minivan) working? • Are dealers abusing the lease option?

  14. 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

  15. Car maker DW • Queries can be created to address the concerns • Examples of Reports (p. 40, 41, 42)

  16. Summary • Review of Star Schema • Example • Class exercise • The DW design process (DW Vs OLTP)

More Related