Dimensional Modeling in Depth (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. This course gives you the opportunity to learn directly from the industry’s dimensional modeling thought leaders.
The word “Kimball” is synonymous with dimensional modeling. Ralph has 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 basic to advanced issues and patterns. Concepts are taught through a combination of lectures, class exercises, small group workshops, and individual problem solving illustrated by real-world industry scenarios. Our goal is for students to 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 course is designed for data warehouse architects, data modelers, database administrators, business analysts, and ETL or BI application developers and system designers. It’s appropriate for anyone interested in an A to Z coverage of dimensional modeling.
Instructors
Margy Ross and Ralph Kimball, co-authors of The Data Warehouse Toolkit, 3rd Edition
Course Overview
Day 1
- Dimensional Modeling Fundamentals
- Retail Sales Case Study
- Order Management Design Workshop
Day 2
- Inventory Case Study
- Billing Design Review Exercise
- Slowly Changing Dimensions
- Credit Card Design Workshop
- Insurance Case Study
Day 3
- Financial Applications, General Ledger, and Budgeting Value Chain
- Retail Bank Account Tracking Workshop
- ETL Backroom Dimensional Designs
Day 4
- Customer Behavior Patterns and Modeling Challenges
- Real Time Tracking
Course Details
Day 1
Dimensional Modeling Fundamentals
- Publishing responsibilities of DW/BI professionals
- Role of dimensional modeling in the Kimball, Corporate Information Factory (CIF), and hybrid architectures
- Fact and dimension table characteristics
- Surrogate key for dimensions
- Fact table granularity
- Degenerate dimensions
- Benefits of dimensional modeling
- 4-step design process
Retail Sales Case Study
- Transaction fact tables
- Denormalized dimension table hierarchies
- Dealing with nulls
- Dimension role-playing
- Date and time-of-day dimension considerations
- Centipede fact tables with too many dimensions
- Star versus snowflake schemas
- Factless fact tables
Order Management Design Workshop
- Complications with operational header/line data
- Allocated facts at different levels of detail
- Abstract, generic dimensions
- Freeform text comments
- Junk dimensions for miscellaneous transaction indicators
- Multiple currencies
Day 2
Inventory Case Study
- Implications of business processes on data architecture
- Semi-additive facts
- Three types of fact tables – transaction, periodic snapshot and accumulating snapshot
- Conformed dimensions – identical and shrunken roll-ups
- Enterprise Data Warehouse Bus Architecture and matrix for master data and integration
- Drilling across fact tables
- Consolidated cross-process fact tables
- Individual exercise: Translate business requirements into DW bus matrix
Billing Design Review Exercise
- Common design flaws and mistakes to avoid
- Checklist for conducting design reviews
Slowly Changing Dimensions
- Basic Type 1, 2 and 3 techniques
- Advanced techniques to deliver current and point-in-time attribute values
- Mini-dimensions for large, rapidly changing dimensions
- Multiple mini-dimensions and outriggers
Credit Card Design Workshop
- Complementary transaction and periodic snapshot schemas
- Design considerations for one dimension versus two dimensions
- Bridge tables for many-valued dimension attributes
- Fact table normalization
Insurance Case Study
- Review of design patterns and techniques
- Development of bus matrix from extended case study
- Complex, unpredictable accumulating snapshots
- Detailed implementation bus matrix
Dimensional Modeling Process
- Process flow, tasks and deliverables
Day 3
Financial Applications – Profit Equation
- Allocating costs to the same grain as revenue
- Profit margin point analysis and value banding
Financial Applications – General Ledger
- Tracking instantaneous balances
- Multiple time zones
- Drilling down in the general ledger to a document
Financial Applications – Budgeting Value Chain
- Budgets, commitments and expenditures
- Bridge tables for variable-depth ragged hierarchies
- Shared ownership and time-varying ragged hierarchies
- Pathstring alternative for ragged hierarchies
- Tracking the “age of the book”
- Calculating the “policy loss triangle”
Retail Bank Account Tracking Workshop
- Multiple account types with hundreds of potential attributes and facts
- Many-to-many account to customer map and weighted versus “impact” reports
- Tagging accounts as “about to go bankrupt”
- Super-types and sub-types
Automobile Options Exercise
- Column versus row trade-offs based on usability and scalability
Compliance-Enabled Data Warehouses
- Eliminating Type 1 and Type 3 updates
ETL Back Room Dimensional Designs
- Tracking data quality with error event fact table
- Column, structure, and business rule tests for data quality
- Reporting data quality with audit dimension
Day 4
Customer Relationship Management Payoffs Discussion
- Business users’ expectations and bottom line impact?
- Data sources needed? Common quality/integration problems?
Complex Customer Behavior Case Studies
- Building study groups
- Sequential time dependent study groups
- Applying study groups to marketing panels and medical outcomes
Building Custom User Interfaces
- Examples: car option selection, value band definition, study group creation
Customer Dimension Modeling Challenges
- Sparse but wide demographics attributes
- Finding detailed customer profile at random times in the past
- Tricky time span queries
- Simultaneous facts and dimensions
- Relationship between prospects and customers
Real Time Customer Tracking
- Hot partitions
- Handling unresolved customer identities in real time
Modeling Sequential Behavior
- Step dimension for describing sequential behavior
- RFID and web page challenges
- Modeling product purchase sequences
Big Data Analytic Use Cases
- Competing DBMS and Hadoop architectures
- Attaching dimensions to big data
- Drilling across conventional and big data sources
Final Customer-Centric Topics
- “Text” facts for customer cluster identification
- Structured questionnaires
