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
  • Value banding facts
  • Simultaneous facts and dimension attributes
  • Fact table normalization
  • 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