What you will learn

After completing this course, the student should be able to:


  • Understand and apply the concepts and principles of data warehousing
  • Understand and apply basic data warehouse architectures and development processes
  • Understand and apply basic data warehouse data design techniques
  • Ensure that business requirements are identified and included in your design
  • Gather, organize and prioritize business requirements
  • Choose between an appropriately normalized data model or a more dimensional model
  • Understand how and when to use aggregation
  • Understand and apply different modeling techniques for different data warehouse structures
  • Developers and administrators involved in data warehousing
  • Business and technical data warehouse team members
  • Our “Introduction To Data Warehousing” or equivalent knowledge
  • An understanding of data modeling, especially entity-relationship modeling
  3 days

Outline for Dimensional Modeling Workshop

1. Introduction to Data Warehousing

  • Scope and levels of modeling
  • Kinds of data
  • The framework for data modeling
  • Challenges in data management
  • Major characteristics of data warehouse
  • Types of data and data models:

2. Introduction to Dimensional Modeling

  • Definition and components
  • Entity, Attribute, Relationship
  • Keys, Derived Data
  • Levels of data models and rules by level
  • The high level and detailed data model
  • Facts and dimensions
  • Normalization

3. Building the Data Warehouse Model

  • Operational vs. informational data
  • A data controlled environment
  • Progression of data in a data controlled environment

4. Levels of Data in the Enterprise

  • Four types of data and systems
  • The warehouse and decision data model
  • Sources of warehouse and decision data
  • Definition and rules
  • The corporate model
  • The business area model

5. Information Gathering

  • Facilitated sessions
  • Interviews
  • Information gathering techniques

6. Building the Central DW Model

  • Remove pure production data
  • Support time and history
  • Add aggregates
  • Find the right granularity of data
  • Create fact tables and dimensions
  • Merge like data from different tables
  • Create arrays of data
  • Separate data based on its stability
  • Embed relationships in the data
  • Add external data

7. Modeling Aggregates

  • Types of aggregate data
  • Overall process for aggregating
  • Trade-offs with aggregate data

8. Modeling Time and History

  • Short Term And Long Term View
  • Four ways of handling time and date
  • History vs. time-series data
  • Capturing business changes
  • Importance of business time dimension

9. Building Data Marts

  • Types of data marts
  • Trade-offs with data marts
  • Definition of fact tables and dimensions
  • Dimensions and dimension hierarchies
  • Creating multidimensional arrays
  • Corporate reference tables
  • The star schema
  • The snowflake schema

10. Optimizing the Data Warehouse Design

  • Safe compromises to data
  • Merge like tables (eliminate 1:1)
  • Create arrays of data (violate 1NF)
  • Split data based on stability and usage
  • Add indices
  • Encode-decode data
  • Aggressive compromises to data
  • Store derived data
  • Summarize data
  • Add redundant data or relationship
  • Add partial or transitive dependencies (violate 2NF or 3NF)
  • Critical factors in data design
  • Number of occurrences of each table
  • The ratio of one table to another
  • The queries that use the data
  • The data accesses made by each query
  • The load factor for each query (number of times performed).
  • The steps of optimization

11. Data Warehouse Technology

  • Categories of warehouse tools
  • Review of major products

12. Summary and Conclusion

  • Selected warehouse projects
  • Critical Success Factors

13. Case Studies

  • Selected mini-exercises
  • Complete group case study (moderately sized)
  • Complete individual case study (large)

14. Glossary