Dimensional Modeling: The Kimball Method




Learn basic through advanced Kimball dimensional modeling patterns and advanced techniques.

Excellence in dimensional modeling remains the keystone of 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.

The word “Kimball” is synonymous with dimensional modeling. The Data Warehouse Toolkit (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.

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 following the training.

Who Should Attend?

This class is designed for data warehouse architects, data modelers, database administrators, business analysts, and ETL or BI application developers and designers. It’s appropriate for anyone interested in A-to-Z coverage of dimensional modeling.


Here’s an outline of what will be covered during this 3-day workshop

Day 1

Commences 9am

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

Finishes 5pm

Day 2
Commences 9am

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


Finishes 5pm

Day 3

Commences 9am

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

Finishes 5pm

16th-18th October 2019

9am – 5pm

Held at:
etc.venues Victoria
One Drummond Gate
Victoria, London
Venue details

Meet our Instructor

Margy Ross

Founder & CEO

Margy Ross, co-author of The Data Warehouse Toolkit, Third Edition, and five other books with Ralph Kimball including the most recent Kimball Group Reader, Second Edition. Margy is currently President of DecisionWorks Consulting, Inc.; she was previously President of the Kimball Group. Margy co-taught Kimball University’s public dimensional modeling course with Ralph Kimball for over 10 years, as well as Kimball University’s onsite dimensional modeling courses. Kimball University’s leadership and excellence in dimensional modeling education lives on!