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, mini-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. Margy co-taught Kimball University’s dimensional modeling course with Ralph Kimball for over 10 years. The legacy lives on!

Course Details – Day 1

Introductions

  • Course agenda and assumptions

Dimensional Modeling Fundamentals

  • Role of dimensional modeling in the Kimball, Corporate Information Factory, and hybrid architectures
  • Fact and dimension table characteristics
  • Benefits of dimensional modeling

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 Details – Day 2

Billing Design Review Exercise

  • Common design flaws and mistakes to avoid
  • Checklist for conducting design reviews

More on Dimension Tables

  • Type 0 slowly changing dimension attributes
  • Series of predictable Type 3 dimension attribute changes
  • Type 4 mini-dimension for large, rapidly changing dimensions
  • Multiple mini-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 both Type 1 and Type 2 attributes
  • Type 7 with dual Type 1 and Type 2 dimension tables
  • Modeling multivalued dimension attributes with bridge tables
  • Bridge tables for correctly weighted versus “impact” reports
  • Alternatives to bridge tables for multivalued dimension attributes
  • Slightly ragged dimension hierarchies
  • Bridge tables to drill up/down 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
  • Complex, unpredictable accumulating snapshots
  • Value banding facts
  • Simultaneous facts and dimension attributes
  • Fact table normalization
  • Detailed implementation bus matrix

Dimensional Modeling Process

  • 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