Dimensional Modeling

Outline and Detailed Agenda for Dimensional Modeling Course

Introduction to Data Warehousing
• Why the need for a data warehouse
• What is a data warehouse?
• The main characteristics
• The benefits of a data warehouse
• Current trends in reporting and analysis
• Successes with data warehousing
• The OLTP model
• The DW or dimensional model
• The Data Mart Model
• The levels of data in a data warehouse

Data Warehouse Architectures
• The natural progression of data warehousing
• The current state of an organization
• The future state
• Getting there incrementally
• Four main data warehouse architectures
• Factors influencing choice of architecture
• Conformed dimension basics
• Topologies for data warehousing
     • Centralized
     • Decentralized
     • Federated
     • Virtual
     • Self-service

Gathering Information Requirements
• The differences in requirements for data warehouses
• The principles of information gathering
• The main methods of information gathering:
• Goals
• Top 10 questions
• Scenario
• Metrics
• Analyzing reporting artifacts

Introduction to Dimensional Modeling
• Definition of a dimensional model
• Characteristics of a dimensional model
• Facts
• Types of facts
• Dimensions
• Characteristics of dimensions
• The star schema
• Its pros and cons
• The snowflake schema
• Its pros and cons
• The steps of dimensional modeling
• Dimensional models and detailed data
• Dimensional model and aggregate data
• Application of dimensional modeling to different levels of data
• Querying outside-in and inside-out
• Dimensional modeling exercise

Creating the Central Warehouse
• Definition of the central data warehouse (CDW)
• Dimensional model and the CDW
• The levels of data in the CDW
• The 12 steps for creating the CDW:
• Remove pure operational data
• Eliminate private data
• Add time and date
• Summarize data
• Declare the granularity of the data
• Embed relationships into the data
• Move necessary details for drill down
• Value band serial data into dimensions
• Separate data according to its stability
• Degenerate certain dimensions
• Add external data
• Add roll-up hierarchies
• CDW modeling exercise

Designing Data Marts
• Definition of a data mart
• Types of data marts in terms of data content
• Types of data marts mart in terms of data sources
• OLAP vs. ROLAP
• The structure of a dimension
• Dimension hierarchies
• Multiple hierarchies
• Leveled dimensions
• Regular or symmetric dimensions
• Ragged dimensions
• Modeling hierarchies
• Denormalization and the dimensional model
• One large fact vs. multiple smaller facts
• Data mart design exercise

Advanced Dimensional Topics
• Conformed dimensions in depth
• Many-to-Many Dimension Relationships
• Very deep and wide dimensions
• Partitioning dimensions
• Dimensional roles
• Heterogeneous facts
• The Unity dimension
• Fact-to-fact joins
• Dimension-to-dimension joins
• Outrigger tables
• Cartesian dimensions

Designing Aggregations
• Definition of aggregates
• Seven reasons for aggregates
• Finding the right combination of summary levels
• Types of summary data:
• Vertical summaries
• Aggregates
• Pre-joined data
• Aggregates in same or different tables
• Aggregate design exercise

Designing For Time and History
• History versus time-series
• Types of history
• Current Occurrence
• Simple History: history as rows
• Delimited History
• Complex History
• Fixed History: history as attributes
• The four types of changing dimensions
• Type 1: overwrite
• Type 2: continuous history
• Type 3: limited history
• Type 4: point-in-time history
• Rapidly changing dimensions
• Creating the calendar
• Exercise in Modeling Time and History

Data Extraction and Transformation
• The overall process of ETL
• The types of extract
• ETL and aggregate data
• The steps of ETL

The Data Warehouse Project
• The data warehouse program
• Selecting data warehouse projects
• Criteria for a data warehouse project
• Characteristics of Rapid Application Development
• Data warehousing as a RAD project
• Selecting scope
• Key project learnings
• Six classic failures
• Conclusions

Metadata
• Definition of metadata
• Importance of metadata
• When to collect metadata
• Sources of metadata
• How to publish metadata

The Operational Data Store
• Definition of an ODS
• Characteristics of an ODS
• Relationship of ODS to data warehouse
• Comparison of OLTP, ODS, data warehouse, data mart

Data Warehouse Technology
• Types of technology needed
• Assessment of major technologies
• Assessment of major vendors