Dimensional Modeling: The Kimball Method

This is the last time Margy will be teaching this course in Australia

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


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

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

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



Rydges World Square
389 Pitt Street, Sydney NSW 2000

3rd-5th March 2020

Single Rate $0*

* prices exclude GST

Meet our Instructor

Margy Ross

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!