31-07-2013, 12:32 PM
Dimensional Modeling
[attachment=56857]
What does Data Warehouse look like
Kimball Data Warehouse
Back Room Staging Area (The kitchen)
Data Presentation Area (The Dinning Room)
Star Schema
Corporate Information Factory (CIF)
Normalized Dimensions
Dual ETL Loading, Warehouse and Data Marts
Operational Data Store (ODS)
Hybrid Data Warehouse
Benefits
Performance (Integer relationships, natural partitioning, Single joins benefit SQL optimizer)
Source system independence and multiple integration
Supports Change management
Usability/Simplicity (easy to read, interpret, join, calculate)
Presentation (Consistency, Taxonomy, Labeling)
Reuse (Conformed dimensions reduce redundancy, Role-plays)
Dimension Change Strategy
Type 1: Is used when the old value of the attribute has no significance or can be discarded.
Easy and Fast
Type2: Partitions history so that fact tables properly reflect original values.
Requires use of Surrogate Keys
Causes table growth due to additional history rows
Users must be aware of the added complexity
Effective Dates used secondary to cleaner fact joins
Dimension Role Playing
A single table that plays multiple roles (using views) to create synonym dimension attributes.
Most common role playing dimension is the Date Dimension. i.e. separate role playing dimensions for order date and ship date.
Identify the Facts
Must be true to the grain defined in step 2.
Typical facts are numeric additive figures.
Facts that belong to a different grain belong in a separate fact table.
Facts are determined by answering the question, “What are we measuring?”
Percentages and ratios, such as gross margin, are non-additive. The numerator and denominator should be stored in the fact table.