Dimensional Modeling Fundamentals

• Modeling for measurement - the case for dimensional modeling
• Fundamentals of stars, snowflakes, facts and dimensions
• Slowly changing dimensions - accurately reflecting history, supporting current, historically correct and alternative views
• Common dimensional modeling techniques - Time, multi-role and degenerate dimensions, surrogate keys, value chains and other common multi-star design patterns
• The Data Warehouse Bus Architecture of conformed dimensions and facts, how data marts can enable incremental data warehouse development

Dimensional Analysis

• Gathering Analytical Requirements - how to ask the right questions
• Identifying business events and processes that must be measured
• Identifying business dimensions by classification (Who, What, Where, When and Why or People, Things, Places, Timestamps and Reasons)
• Identifying and documenting the relationships between business events and dimensions - the dimensional matrix (reloaded)
• Identifying Key Performance Indicators (KPIs) and Metrics - aggregates, comparisons and exceptions
• Defining granular facts - additive, semi-additive and non-additive measures
• Identifying and classifying dimensional attributes and hierarchies - completeness checks

ETL Functional Practices

• Planning and designing your ETL system
• Choosing the appropriate architecture
• Managing the implementation
• Managing the day to day operations
• Building the development/test/production suite of ETL processes
• Building a data cleaning subsystem
• Understanding the tradeoffs of various staging data structures, including flat files, normalized schemas, XML, and dimensional schemas
• Analyzing and extracting source data
• Creating the logical data mapping

ETL Technical Practices

• Structuring the data into dimensional schemas for the most effective delivery to end users
• Conforming heterogeneous data from multiple sources into standardized dimension tables and fact tables
• Building ETL modules for handling the three distinct types of slowly changing dimensions (SCDs)
• Building ETL modules for multi-valued dimensions and hierarchical dimensions
• Running high-performance surrogate key pipelines
• Loading the three fundamental fact table grains - transaction, periodic snapshot and accumulating snapshot
• Handling late arriving dimensions and facts
• Optimizing ETL processes to fit into highly constrained load windows
• Structuring and presenting metadata
• Converting batch and file-oriented processes into continuously streaming real-time ETL systems