Datawarehousing : Two Days Workshop

Day 1

Prerequisites

Knowledge of relational database design.

Content

This course has been designed to show the student the differences between designing data warehouses and traditional systems for the OLTP environment.  It will emphasize the need for understanding business requirements and developing an appropriate warehouse architecture as the foundation for the warehouse.  An enterprise or subject area data model serves as the starting point for designing the warehouse.  The course will then discuss the design of the various types of structures found within a typical data warehouse, such as detail data, summary data, multi-dimensional data, external data, specialized subsets, etc.   The tradeoffs in designing for efficient data retrieval, loading, and data management will be considered.  The maintenance and use of metadata throughout the life cycle will be considered.  It will also discuss partitioning, parallel processing, replication, and other issues.

Objectives  

• How to develop a data warehouse architecture.
• How to design the various types of structures that may be found in a data warehouse, including data marts, detail data, summary data, multi-dimensional data, external data, specialized data subsets, etc.
• Special design considerations, including multi-dimensional design, partitioning, use of parallel processing, replication, etc.
• How to design for efficient data management and database administration. 

Topics

• Review of Terminology (including data warehouse, data mart, operational data store, staging area, etc.)
• The Design process
• Comparison of Operational and Data Warehouse design
• Building an architecture
• Design objectives and their tradeoffs
• Metadata throughout the life cycle
• Normalization vs. Denormalization
• Designing detail data
• Partitioning issues
• Parallel processing
• Referential integrity
• Indexing
• Summary levels
• Replication
• Extracts
• Personal data issues
• Dataset placement
• Multi-dimensional modeling
• Design reviews
• ETL architecture
• Critical success factors
• Summary

Data Warehousing Advanced Topics: Day 2

Contents

In the process of building a data warehouse, approximately 60-80% of the time is usually spent in the analysis of data and design of the extract, transformation, and loading process. The course will discuss the development of an architecture and design of an efficient ETL process. Since the ETL process offers the potential of serious performance problems in loading data efficiently, a set of guidelines will be discussed that should help to provide good performance and data integrity. Although users need accurate data definitions to understand the data in the warehouse, managing the metadata process across the data warehouse life cycle is a challenge that will be considered in this class. The Exploration Data Warehouse, which is one of the newer issues in data warehousing, will also be discussed. The design of a data warehouse is iterative in nature and must be evaluated periodically to ensure that it is meeting changing objectives. The assessment process will be discussed and guidelines for an effective assessment will be presented.

Objectives

•  How to develop an architecture and design for an efficient extract, transformation, and load (ETL) process.
•  How to manage metadata across data warehouse life cycle. 
• How to build and manage the new concept of the Exploration Data Warehouse.
• How to perform data warehouse assessments.

Topics

Data Warehouse Architecture

• CHAOS- the most common architecture
• Data flow between data marts
• Staging area
• Hub and spoke architecture
• Distributed data warehouse
• Independent data marts
• Federated data warehouse architecture
• Using near-line storage

Metadata Architecture

• Hub and spoke metadata architecture
• Central vs. distributed data repository

Efficient Extraction, Transformation, and Loading (ETL)

• ETL design process
• Data warehouse data model
• Source-target mappings
• Types of data transformation
• Data quality
• Data reengineering
• Custom code vs. vendor tools
• Tool selection criteria
• Sample ETL guidelines
• Designing for high availability

Conducting data warehouse assessments

• Why?
• What are they?
• Scheduling assessments
• Assessment approach
• Building the right team
• Using leading questions
• Sample assessment
• Assessment topics
  • Data warehouse strategy
  • Business value
  • User satisfaction
  • Organizational issues
  • Business requirements
  • Development methodology
  • Data warehouse architecture
  • Standards & guidelines
  • Vendor relationships
  • Metadata strategy
  • Data warehouse administration
  • Data Access and Delivery
  • Database Design
  • Performance and Capacity Planning
  • Future planning

Exploration Data Warehouse

• Definition
• How business analysts
•  use the warehouse
• Using data patterns
• Patterns analysis
• Data issues
• Permanent vs. temporary exploratory warehouses
• Feeding exploration
• data warehouse
• Data mining
• Tool approaches to exploration & mining
• Token-based processing vs. in-memory processing