Ralph Kimball introduced the data warehouse/business intelligence industry to dimensional modeling in 1996 with his seminal book, The Data Warehouse Toolkit. Since then, the Kimball Group has extended the portfolio of best practices.
Drawn from The Data Warehouse Toolkit, Third Edition, the “official” Kimball dimensional modeling techniques are described on the following links and attached .pdf:
Fundamental Concepts
- Gather business requirements and data realities
- Collaborative dimensional modeling workshops
- Four step dimensional design process
- Business processes
- Grain
- Dimensions for descriptive context
- Facts for measurements
- Star schemas and OLAP cubes
- Graceful extensions to dimensional models
Basic Fact Table Techniques
- Fact table structure
- Additive, semi-additive, and non-additive facts
- Nulls in fact tables
- Conformed facts
- Transaction fact tables
- Periodic snapshot fact tables
- Accumulating snapshot fact tables
- Factless fact tables
- Aggregated fact tables or cubes
- Consolidated fact tables
Basic Dimension Table Techniques
- Dimension table structure
- Dimension surrogate keys
- Natural, durable and supernatural keys
- Drilling down
- Degenerate dimensions
- Denormalized flattened dimensions
- Multiple hierarchies in dimensions
- Flags and indicators as dimension attributes
- Null attributes in dimensions
- Calendar date dimension
- Role playing dimensions
- Junk dimensions
- Snowflaked dimensions
- Outrigger dimensions
Integration via Conformed Dimensions
- Conformed dimensions
- Shrunken rollup dimensions
- Drilling across
- Value chain
- Enterprise data warehouse bus architecture
- Enterprise data warehouse bus matrix
- Opportunity/stakeholder matrix
Slowly Changing Dimension Techniques
- Type 0: Retain original
- Type 1: Overwrite
- Type 2: Add new row
- Type 3: Add new attribute
- Type 4: Add mini-dimension
- Type 5: Add mini-dimension and Type 1 outrigger
- Type 6: Add Type 1 attributes to Type 2 dimension
- Type 7: Dual Type 1 and Type 2 dimensions
Dimension Hierarchy Techniques
- Fixed depth positional hierarchies
- Slightly ragged/variable depth hierarchies
- Ragged/variable depth hierarchies
Advanced Fact Table Techniques
- Fact table surrogate key
- Centipede fact table
- Numeric values as attributes or facts
- Lag/duration facts
- Header/line fact tables
- Allocated facts
- Profit and loss fact tables using allocations
- Multiple currency facts
- Multiple units of measure
- Year-to-date facts
- Multipass SQL to avoid fact-to-fact table joins
- Timespan tracking in fact tables
- Late arriving facts
Advanced Dimension Table Techniques
- Dimension-to-dimension table joins
- Multivalued dimensions and bridge tables
- Behavior tag time series
- Behavior study group
- Aggregated facts as dimension attributes
- Dynamic value banding
- Text comments
- Multiple time zones
- Measure type dimensions
- Step dimensions
- Hot swappable dimensions
- Abstract generic dimensions
- Audit dimensions
- Late arriving dimensions
Special Purpose Schemas
Here’s the .pdf version of Kimball Dimensional Modeling Techniques.