DW/BI Lifecycle: Kimball Methods for Launch, Requirements & Modeling (Download PDF version)

Why Attend

The data warehouse and business intelligence (DW/BI) system continues to be one of the most organizationally complex and interesting IT projects. This course prepares you to successfully implement your DW/BI environment by distilling the essential upfront elements of the popular Kimball Lifecycle approach as described in the bestselling book, The Data Warehouse Lifecycle Toolkit, Second Edition.

This course is packed with specific techniques, guidance and advice from initial project planning through dimensional modeling. It is taught through a combination of lectures, class exercises, small group workshops, and individual problem solving.

The DW/BI Lifecycle course is appropriate for anyone who is new to DW/BI and wants to learn a holistic set of best practices from the beginning, or for anyone who has been through a couple of projects and wants to refine their methods to better align with the proven, broadly-accepted Kimball approach.

Who Should Attend

This course is designed for all major roles on a DW/BI project, including project managers, business analysts, data modelers and database administrators, architects, and ETL or BI application designers/developers.

Instructor                

Margy Ross, co-author of The Data Warehouse Lifecycle Toolkit, Second Edition and several other Toolkit books with Ralph Kimball. She previously taught this course for Kimball University for over 15 years. Margy’s also taught dimensional modeling concepts to nearly 15,000 students worldwide.

 

Introduction to the Kimball Lifecycle Approach

  • Roadmap for creating the DW/BI system

Program/Project Planning and Management

  • Readiness factors
  • Risk assessment and mitigation plans
  • Scoping
  • Role of agile development

Business Requirements Definition

  • Program versus project requirements preparation
  • Requirements gathering participants
  • Techniques for gathering requirements and handling obstacles
  • Program/project requirements deliverables
  • Requirements prioritization

Dimensional Modeling

  • Role of dimensional modeling in various DW/BI architectures
  • Fact and dimension table characteristics
  • 4-step process for designing dimensional models
  • Transaction fact tables
  • Fact table granularity
  • Denormalizing dimension table hierarchies
  • Degenerate dimensions
  • Date dimension considerations
  • Dealing with nulls
  • Surrogate key for dimensions
  • Star versus snowflake schemas
  • Centipede fact tables with too many dimensions
  • Factless fact tables

Order Management Design Workshop

  • Converting requirements and source data realities into dimensional model
  • Consolidated fact tables
  • Dimension table role-playing
  • Allocated facts at different levels of detail
  • Complications with operational header/line data and patterns to avoid
  • Multiple currencies
  • Junk dimensions for miscellaneous transaction indicators
  • Periodic and accumulating snapshot fact tables

Inventory “Beyond the 1st Business Process” Case Study

  • Implications of business processes on data architecture
  • Enterprise Data Warehouse Bus Architecture and matrix for master data and integration
  • Conformed dimensions – identical and shrunken roll-ups
  • Exercise: Translate business requirements into DW Bus Matrix
  • Opportunity/stakeholder matrix

Slowly Changing Dimensions

  • Type 0
  • 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)

Course Registration “Design Review” Exercise

  • Common dimensional modeling flaws
  • Bridge table for multivalued dimension attributes

Dimensional Modeling Process

  • Process flow, tasks, and deliverables
  • Detailed implementation bus matrix

Transportation “Design Enhancement” Case Study

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