Intro to Dimensional Modeling

Introduction to dimensional modeling based on "The Data Warehouse Toolkit" by Ralph Kimball and Margy Ross

Transcript

1. INTRO TO DIMENSIONAL MODELING Reporting from operational data is for amateurs. Friday, September 30, 11
2. “ WE H AV E M O UN TAI NS O F DATA I N T H I S C OMPANY, BUT W E C A N ’ T AC C ESS I T.” Friday, September 30, 11
3. DIMENSIONAL MODELING Business driven User understandability Query performance Friday, September 30, 11
4. “HOLDING THE FORT AGAINST ASSAULTS ON SIMPLICITY” Friday, September 30, 11
5. OPERATIONAL DATA STORE Take orders Sign up customers Repetitive, consistent workflows User interacts with one row at a time Friday, September 30, 11
6. “TURN THE WHEELS OF THE BUSINESS” Friday, September 30, 11
7. DATA WAREHOUSE STORE Compare data (with last week, month, year...) Never deals with one row at a time Constantly answer different questions Friday, September 30, 11
8. “WATCH THE WHEELS OF THE BUSINESS TURN.” Friday, September 30, 11
9. FACT Business measurement Same grain Numeric, additive Many rows, few columns Friday, September 30, 11
10. “THE MOST USEFUL FACTS ARE NUMERIC AND ADDITIVE.” Friday, September 30, 11
11. POS RETAIL SALES TRANSACTION FACT Friday, September 30, 11
12. DIMENSION Descriptive Few rows, many columns Query constraints, grouping “by” words. “by store, by brand, by week” Slice and dice data Friday, September 30, 11
13. “A DATA WAREHOUSE IS ONLY AS GOOD AS ITS DIMENSION ATTRIBUTES.” Friday, September 30, 11
14. STORE DIMENSION Friday, September 30, 11
15. PRODUCT DIMENSION Friday, September 30, 11
16. DATE DIMENSION The workhorse. Friday, September 30, 11
17. R E TA I L S C H E M A A S R A I L S M O D E L S Friday, September 30, 11
18. SIMPLE QUERY Friday, September 30, 11
19. Department Description Brand Description Sales Dollar Amount Calendar Year Month Bakery Baked Well $3,009 2011-08 Bakery Baked Well $4,201 2011-09 Frozen Foods Icy $13,978 2011-08 Frozen Foods Icy $9,324 2011-09 ... ... ... ... Friday, September 30, 11