Dimensional Modeling: Kimball Fundamentals & Advanced Techniques (Download PDF version)
Why Attend
Excellence in dimensional modeling remains the keystone of a well-designed data warehouse presentation area, regardless of your architecture. The Data Warehouse Toolkit (Kimball/Ross, 2013) established the industry’s portfolio of dimensional techniques , including conformed dimensions, slowly changing dimensions, junk dimensions, bridge tables, accumulating snapshot fact tables, and the list goes on.
In this course, you will learn practical dimensional modeling techniques covering fundamental to advanced patterns and best practices from the industry’s dimensional modeling thought leader. Concepts are illustrated through real-world scenarios via a combination of lectures, class exercises, small group workshops, and individual problem solving.
Bringing DecisionWorks training onsite enables everyone on the team to get on the same page with a common vocabulary and understanding. The result is more effective and efficient education with lower travel cost and lost productivity, plus less downstream “tire spinning” within the team.
Who Should Attend
This on-site education class is primarily intended for DW/BI team members who have had prior exposure to dimensional modeling. The first day is appropriate for anyone on the team, including project managers, data warehouse architects, data modelers, database administrators, business analysts, and ETL or BI application developers and designers. The pace picks up on the second day when more advanced concepts are discussed.
Instructor
Margy Ross, co-author of The Data Warehouse Toolkit, 3rd Edition and several other Toolkit books with Ralph Kimball. She has focused on dimensional 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.
Introductions
- Course agenda and assumptions
Dimensional Modeling Fundamentals
- Role of dimensional modeling in various architectures
- Fact and dimension table characteristics
Retail Sales “Basics” Case Study
- 4-step process for designing dimensional models
- Importance of business requirements and data realities
- Fact table granularity
- Transaction fact tables
- Degenerate dimensions
- Denormalized dimension table hierarchies
- Dealing with null values
- Surrogate keys for dimensions
- Date and time-of-day dimension considerations
- Centipede fact tables with too many dimensions
- Star versus snowflake schemas
- Factless fact tables
Inventory “Beyond the 1st Business Process” Case Study
- Implications of business processes on data architecture
- Periodic snapshot fact tables
- Semi-additive facts
- Conformed dimensions – identical and shrunken roll-ups
- Enterprise Data Warehouse Bus Architecture and matrix for master data and integration
- Exercise: Translate business requirements into DW bus matrix
- Opportunity/stakeholder matrix
Slowly Changing Dimensions
- Basic Type 1, 2 and 3 techniques
Order Management Design Workshop
- Drilling across fact tables
- Consolidated cross-process fact tables
- Dimension table role-playing
- Complications with operational header/line data and design patterns to avoid
- Junk dimensions for miscellaneous transaction indicators
- Accumulating snapshot fact tables
- Comparison of three fundamental fact table grains
Course Registration Design Review Exercise
- Common design flaws and mistakes to avoid
- Checklist for conducting design reviews
- Bridge tables for multivalued dimension attributes
More on Dimension Tables
- Type 0 slowly changing dimension attributes
- Type 4 mini-dimension for large, rapidly changing dimensions
- Advanced techniques to deliver current and point-in-time attribute values
- Type 5 with Type 4 mini-dimension plus Type 1 outrigger
- Type 6 with Type 1 and Type 2 attributes
- Type 7 with dual Type 1 and Type 2 dimension tables
- More multivalued dimension attributes with bridge tables
- Bridge tables for correctly weighted versus “impact” reports
- Bridge table alternatives for multivalued dimension attributes
- Handling slightly ragged dimension hierarchies
- Bridge tables for variable depth ragged hierarchies
- Freeform text comments
- Generic abstract dimensions
- Aggregated facts as dimension attributes
- Time series of dimension tags
- Outrigger dimension tables
- Supertypes and subtypes for heterogeneous products
- Audit dimensions
Transportation “Design Enhancement” Case Study
- Exercise: Schema enhancements for changing requirements
- Multiple time zones
- Design trade-offs
More on Fact Tables
- Fact table surrogate keys
- Allocated facts at different levels of detail
- Multiple currencies or units of measure
- Timespan fact tables with row effective and expiration dates
- Simultaneous facts and dimension attributes
- Detailed implementation bus matrix
Dimensional Modeling Process
- Business process prioritization
- Process flow, tasks and deliverables
Client-Specific Exercise
- Development of client-centric preliminary data warehouse bus matrix or discussion of client-specific design questions and issues