Home > Kimball University DW/BI Training > Dimensional Modeling Fundamentals and Advanced Topics

Dimensional Modeling Fundamentals and Advanced Topics (PDF version)

Why Attend

Excellence in dimensional modeling remains the keystone of a well-designed data warehouse presentation area, regardless of whether you’ve adopted a Kimball, Corporate Information Factory (CIF), or hybrid architecture.

The world “Kimball” is synonymous with dimensional modeling. Ralph Kimball 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 course, you will learn practical dimensional modeling techniques covering fundamental to advanced patterns and best practices. Concepts are illustrated through real-world industry scenarios, conveyed through a combination of lectures, class exercises, small group workshops, and individual problem solving.

Bringing Kimball University onsite enables everyone on the team to get on the same page with a common vocabulary and understanding of core techniques. The result is more efficient education with lower travels cost and lost productivity, plus less downstream “tire spinning” within the team.

Who Should Attend    

This course is primarily intended for team members who’ve 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 system designers. The pace picks up on the second day when more advanced concepts are discussed.

Instructors               

Margy Ross, co-author of The Data Warehouse Toolkit, 2nd Edition

Course Overview                             

Day 1

  • Introductions
  • Dimensional Modeling Fundamentals
  • Retail Sales “Basics” Case Study
  • Inventory “Beyond the 1st Business Process” Case Study
  • Slowly Changing Dimensions
  • Order Management Design Workshop

Day 2

  • Billing Design Review Exercise
  • More on Dimension Tables
  • Transportation “Design Enhancement” Case Study
  • More on Fact Tables
  • Dimensional Modeling Process
  • Client-Specific Workshop

Course Details

Day 1      

Introductions       

  • Course agenda and assumptions

Dimensional Modeling Fundamentals            

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

Retail Sales “Basics” Case Study

  • 4-step process for designing dimensional models
  • Fact table granularity
  • Transaction fact tables
  • Degenerate dimensions
  • Denormalized dimension table hierarchies
  • Dealing with nulls
  • Surrogate keys for dimensions
  • 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

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
  • Simultaneous facts and dimensions
  • Junk dimensions for miscellaneous transaction indicators
  • Accumulating snapshot fact tables

Day 2

Billing Design Review Exercise

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

More on Dimension Tables

  • Series of predictable dimension attribute changes
  • Advanced techniques to deliver current and point-in-time attribute values
  • Mini-dimensions for large, rapidly changing dimensions
  • Multiple mini-dimensions and outriggers
  • Modeling many-valued dimension attributes with bridge tables – correctly weighted versus “impact” reports
  • Alternatives to bridge tables for handling many-valued dimension attributes
  • Slightly ragged dimension hierarchies
  • Bridge tables to drill up/down variable-depth ragged hierarchies
  • Date and time-of-day dimension considerations
  • Freeform text comments
  • Generic abstract dimensions
  • Outrigger dimensions
  • Time series of dimension tags
  • Super-types and sub-types 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
  • Fact table effective and expiration dates
  • Complex, unpredictable accumulating snapshots
  • Value banding facts
  • Fact table normalization
  • Detailed implementation bus matrix

Dimensional Modeling Process

  • Process flow, tasks and deliverables

Client-Specific Workshop      

  • Development of client-centric preliminary data warehouse bus matrix or discussion of client-specific design questions and issues