Table of Contents
Fundamental Concepts ........................................................................................ 1
Gather Business Requirements and Data Realities ...................................... 1
Collaborative Dimensional Modeling Workshops .......................................... 1
Four-Step Dimensional Design Process ........................................................ 1
Business Processes ...................................................................................... 1
Grain ............................................................................................................. 2
Dimensions for Descriptive Context .............................................................. 2
Facts for Measurements ................................................................................ 2
Star Schemas and OLAP cubes .................................................................... 2
Grace Extensions to Dimensional Modeling .................................................. 3
Basic Fact Table Techniques ............................................................................... 4
Fact Table Structure ...................................................................................... 4
Additive, Semi-Additive, and Non-Additive Facts .......................................... 4
Nulls in Fact Tables ....................................................................................... 4
Conformed Facts ........................................................................................... 4
Transaction Fact Tables ................................................................................ 4
Periodic Snapshot Fact Tables ..................................................................... 5
Accumulating Snapshot Fact Tables ............................................................. 5
Factless Fact Tables ..................................................................................... 5
Aggregate Fact Tables or Cubes ................................................................... 5
Consolidated Fact Tables .............................................................................. 6
Basic DimensionTable Techniques ...................................................................... 7
DimensionTable Structure ............................................................................. 7
Dimension Surrogate Keys ............................................................................ 7
Natural, Durable, and Supernatural Keys ...................................................... 7
Drilling Down ................................................................................................. 7
Degenerate Dimensions ................................................................................ 7
Denormalized Flattened Dimensions ............................................................ 8
Multiple Hierarchies in Dimensions ............................................................... 8
Flags and Indicators as Textual Dimension Attributes ................................... 8
Null Attributes in Dimensions ........................................................................ 8
Calendar Date Dimensions ........................................................................... 8
Role-Playing Dimensions .............................................................................. 9
Junk Dimensions ........................................................................................... 9
Snowflaked Dimensions ................................................................................ 9
Outrigger Dimensions ................................................................................... 9
Integration via Conformed Dimension ................................................................ 10
Conformed Dimensions ............................................................................... 10
Shrunken Rollup Dimensions ...................................................................... 10
Drilling Across ............................................................................................. 10
Value Chain ................................................................................................. 10
Enterprise Data Warehouse Bus Architecture ............................................. 10
Enterprise Data Warehouse Bus Matrix ...................................................... 11
Opportunity/Stakeholder Matrix ................................................................... 11
Slowly Changing Dimension Techniques ........................................................... 12
Type 0: Retain Original ................................................................................ 12
Type 1: Overwrite ........................................................................................ 12
Type 2: Add New Row ................................................................................. 12
Type 3: Add New Attribute........................................................................... 12
Type 4: Add Mini-Dimension ....................................................................... 12
Type 5: Add Mini-Dimension and Type 1 Outrigger ..................................... 12
Type 6: Add Type 1 Attributes to Type 2 Dimension ................................... 13
Type 7: Dual Type 1 and Type 2 Dimensions .............................................. 13
Kimball Dimensional Modeling Techniques