Dimensional Modeling: The Kimball Method (Download PDF version)
Excellence in dimensional modeling is critical to a well-designed data warehouse/business intelligence system, regardless of your architecture. This course gives you the opportunity to learn directly from the industry’s dimensional modeling thought leader, Margy Ross.
The Data Warehouse Toolkit, 3rd Edition (Kimball/Ross, 2013) established an extensive portfolio of dimensional techniques and vocabulary, including conformed dimensions, slowly changing dimensions, junk dimensions, mini-dimensions, bridge tables, periodic and accumulating snapshot fact tables, and the list goes on. The word “Kimball” is synonymous with dimensional modeling.
In this intensive class, you will learn practical dimensional modeling techniques covering basic to advanced patterns and best practices. Concepts are taught through a combination of lectures, class exercises, small group workshops, and individual problems, based on real-world industry scenarios. Students will gain an in-depth understanding of dimensional modeling so they can confidently apply the techniques in their workplace.
Instructor
Margy Ross, co-author of The Data Warehouse Toolkit, 3rd Edition and several other Toolkit books with Ralph Kimball. She’s focused on diemensional modeling for over three decades and previously co-instructed Kimball University’s classes with Ralph. Margy’s taught dimensional modeling concepts to nearly 15,000 students worldwide.
Dimensional Modeling Fundamentals
- DW/BI system objectives
- Role of dimensional modeling in the independent mart, Kimball, Corporate Information Factory, and hybrid architectures
- Fact and dimension table characteristics
- Fact table granularity
- Benefits of dimensional modeling
- 4-step design process
Retail Sales Case Study
- Transaction fact tables
- Denormalized dimension table hierarchies
- Dealing with nulls
- Degenerate dimensions
- Surrogate keys for dimensions
- Dimension role-playing
- Date and time-of-day dimension considerations
- Centipede fact tables with normalized dimensions
- Snowflake schemas with normalized dimensions
- Factless fact tables
Invoicing Design Workshop
- Complications with operational header/line data
- Allocated facts at different levels of detail
- Simultaneous facts and dimensions
- Abstract, generic dimensions
- Freeform text comments
- Junk dimensions for miscellaneous transaction indicators
- Multiple currencies and units of measure
Course Details – Day 2
Inventory Case Study
- Implications of business processes on data architecture
- Semi-additive facts
- Periodic and accumulating snapshot fact tables
- Conformed dimensions – identical and shrunken roll-ups
- Enterprise Data Warehouse Bus Architecture and bus matrix
- Drilling across fact tables
- Consolidated cross-process fact tables
- Individual exercise: Translate business requirements into enterprise DW bus matrix
Higher Education Design Review Exercise
- Common design flaws and mistakes to avoid
- Bridge tables and primary designation for multivalued dimension attributes
- Checklist for conducting design reviews
Slowly Changing Dimensions
- Type 0: retain original
- Type 1: overwrite
- Type 2: add new row
- Type 3: add new attribute, plus multiple type 3 attributes
- Type 4: add mini-dimension, plus type 4 challenges
- Advanced techniques to deliver current and point-in-time attribute values
- Type 5: add mini-dimension, plus type 1 attributes/outrigger
- Type 6: dual type 1 and type 2 attributes in same dimension
- Type 7: dual type 1 and type 2 dimension tables
Credit Card Design Workshop
- Complementary transaction and periodic snapshot schemas
- Design considerations for one dimension versus two dimensions
- Bridge tables for multivalued dimension attributes
- Fact table normalization with measurement type dimension
- Tagging rows after the fact
Insurance Case Study
- Review of design patterns and techniques
- Development of bus matrix from extended case study
- Comparison of fact table grains
- Detailed implementation bus matrix
Course Details – Day 3
Lifecycle Overview, Requirements, and Dimensional Modeling Process
- Kimball Lifecycle method overview
- Readiness factors and scoping
- Requirements gathering best practices and prioritization
- Dimensional modeling participants and process flow
Financial Case Study
- General ledger schemas
- Fact table surrogate keys
- Audit dimensions
- Fact value banding
- Timespan transaction, periodic, and accumulating snapshot fact tables
- Forcing slightly ragged hierarchies into fixed depth
- Bridge tables for ragged variable depth hierarchies, plus pathstring option
- More on multiple currencies
- Multiple time zones
- Supertypes and subtypes
Human Resources Multivalued Dimension Exercise
- Column versus row trade-offs
- “Many-to-many” dimension examples and design alternatives, including bridges
- Reports-to challenges
Customer Case Study
- Aggregated facts as dimension attributes
- Time series of dimension tags
- Outriggers
- Cohorts
- Big data, predictive analytics, and prescriptive analytics
- Complementary conventional DW and analytic sandboxes