1 / 15

Data Warehouse (V): Applying the star schema

Learn about star schema, dimensional design, and fact tables in data warehousing, with examples and design principles explained in simple terms. Explore how star schema differs from traditional ER modeling and its relevance in DW and OLTP systems.

jgrizzard
Download Presentation

Data Warehouse (V): Applying the star schema

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 (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

More Related