Data Warehouse Design

Audience

Data Architects, Database Administrators, Data Analysts, Application Developers, and System Designers.

Prerequisites

Participants should have a reasonable knowledge of data modelling and database design.

Duration

3 days. Workshops and Lecture based.

Course Objectives

Data Warehousing has evolved into a unique and key component of IT strategy for many organisations. Dimensional modelling is the proven technique for developing understandable, high-performance data warehouses and data marts. This course introduces data warehouses, demonstrates the techniques involved in developing dimensional models and shows how these models fit in to the process of building data warehouses. All techniques are illustrated by real world examples drawn from a range of industries.

After taking this course, students should be able to:

• Define the constituent parts of a data warehouse.
• State the limitations of classic entity-relationship models in data warehouse design.
• State the strengths of dimensional models and understand how such models are used in the development of data warehouses and data marts.
• Gather relevant requirements and place these requirements into a proper context for use in dimensional model development.
• Develop appropriate dimensional models using the approach given in the course.
• Understand what metadata is and its importance in data warehouse design.
• Make decisions regarding the implementation of dimensional models to produce efficient data marts and data warehouses.
• Develop appropriate dimensional models using the approach given in the course.
• Understand what metadata is and its importance in data warehouse design.

Course Content

Introduction to Data Warehousing
What is a data warehouse?
Characteristics of a data warehouse.
Online Analytical Processing (OLAP)
Data warehouse architectures.
Data marts
Development life cycles

Data Modelling
Entity/relationship modelling, definitions, notation
Normalisation
What data models are used for
Steps in design

Introduction to Dimensional Modelling
On line transaction processing (OLTP)
OLTP advantages and disadvantages
Why dimensional modelling is used in data warehouse design
Facts and dimensions
Star Schemas
Snowflaking
Fact tables and fact granularity
Strengths of dimensional modelling

Developing Dimensional Models
Requirements gathering
Conformed dimensions
Identifying facts, dimensions and data marts
Aggregation
Documenting facts and dimensions
Identifying data sources

Dimensional Modelling Techniques
Drilling up and down
When snowflaking is useful
Handling dates and times
Slow and fast changing attributes
Degenerate dimensions
Factless fact tables

Metadata
What is metadata?
Metadata for data warehouse constituent parts.
Reverse engineering
Gathering metadata
Extract transform and load (ETL)
Tool based and hand coded ETL

Architecture and Physical Design
Development lifecycle
Data warehouse architecture
Physical design process