Web Age Solutions Inc
Providing Technology Training and Mentoring For Modern Technology Adoption
Web Age Aniversary Logo
US Inquiries / 1.877.517.6540
Canadian Inquiries / 1.877.812.8887
Course #:WA1818

Dimensional Modeling Workshop Training

This 3-day course introduces students to best industry practices for designing data warehouse data structures and databases.

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 of Dimensional Modeling Workshop Training

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

We regularly offer classes in these and other cities. Atlanta, Austin, Baltimore, Calgary, Chicago, Cleveland, Dallas, Denver, Detroit, Houston, Jacksonville, Miami, Montreal, New York City, Orlando, Ottawa, Philadelphia, Phoenix, Pittsburgh, Seattle, Toronto, Vancouver, Washington DC.
US Inquiries / 1.877.517.6540
Canadian Inquiries / 1.877.812.8887