Course Description | Course Outline | Data Warehousing | IT 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)