Dimensional Modeling: Kimball Fundamentals (Download PDF version)

Why Attend

Excellence in dimensional modeling remains the keystone of a well-designed DW/BI presentation area, regardless of architecture. The Data Warehouse Toolkit (Kimball/Ross, 2013) established the industry’s portfolio of dimensional techniques, including conformed dimensions, slowly changing dimensions, periodic snapshot fact tables, and the list goes on.

In this course, you will learn practical dimensional modeling techniques covering fundamental patterns and best practices. Concepts are illustrated through real-world scenarios via a combination of lectures, class exercises, small group workshops, and individual problem solving. In addition, students will receive an overview of the Kimball Lifecycle approach for DW/BI design and development.

Bringing DecisionWorks 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 travels cost and lost productivity, plus less downstream “tire spinning” within the team.

Who Should Attend    

This on-site education class is appropriate for anyone interested in dimensional modeling, whether they’re project managers, data warehouse architects, data modelers, database administrators, business analysts, and ETL or BI application developers and designers.

Instructor               

Margy Ross, co-author of The Data Warehouse Toolkit, Third Edition, and several other Toolkit books with Ralph Kimball. She’s 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 DW/BI architectures
  • Fact and dimension table characteristics

Retail Sales “Basics” Case Study

  • 4-step process for designing dimensional models
  • Fact table granularity
  • Transaction fact tables
  • Degenerate dimensions
  • Date dimension considerations
  • 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
  • Mini-dimensions for large, rapidly changing dimensions (Type 4)
  • Advanced techniques to deliver current and point-in-time attribute values (Type 5, 6 and 7)

Order Management Design Workshop

  • Drilling across fact tables
  • Consolidated cross-process fact tables
  • Dimension table role-playing
  • Allocated facts at different levels of detail
  • Simultaneous facts and dimensions
  • Complications with operational header/line data and patterns to avoid
  • Multiple currencies
  • Junk dimensions for miscellaneous transaction indicators
  • Accumulating snapshot fact tables
  • Comparison of three fundamental fact table grains

Billing “Design Review” Exercise

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

Kimball Lifecycle Approach for DW/BI Development

  • Best practices for project launch, requirements and prioritization
  • Common challenges and pitfalls
  • Dimensional modeling process flow, tasks, and deliverables

Transportation “Design Enhancement” Case Study

  • Schema enhancements to embellish design for changing requirements
  • Multiple time zones
  • Design trade-offs

Credit Card Design Workshop

  • Complementary transaction and periodic snapshot schemas
  • Modeling many-valued dimension attributes with bridge tables
  • Generic abstract dimensions
  • Freeform text comments

Insurance “Pulling it all Together” Case Study

  • Review of design patterns and techniques
  • Development of bus matrix from extended case study
  • Detailed implementation bus matrix

Client-Specific Workshop      

  • Development of client-centric preliminary data warehouse bus matrix