Audience
This course is intended for:
- Data Architects.
- Database Administrators.
- Data Analysts.
- Application Developers.
- System Designers.
Prerequisites
Delegates should have a reasonable knowledge of data modelling, database design and Structured Query Language.
Attending our Data Modelling and Database Design course will provide the idea prerequisite knowledge for this class.
Duration
3 days. Lecture presentations are supported by examples and a realistic case study to reinforce learning and enhance understanding.
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 will 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.
- Use appropriate techniques in the design of systems to perform the functions of extract, transform and load.
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
Classical 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.
Snowflake Schemas.
Fact tables and fact granularity.
Strengths of dimensional modelling.
Basic Dimensional Modelling Techniques
Fact tables and dimension tables.
Drilling up and down.
Snowflaking.
Date dimensions.
Attributes of dimensions.
Types of dimension - slowly changing, rapidly changing.
Degenerate dimensions.
Factless fact tables.
Developing Dimensional Models
Development Lifecycle.
Requirements gathering.
Conformed dimensions.
Identifying facts, dimensions and data marts.
Aggregates.
Fact and dimension table detail.
Identifying data sources.
Advanced Dimensional Modelling
Drilling across.
Many-to-many dimensions.
Handling time of day.
Time stamping.
Snapshots.
Transactions.
Extract Transform and Load
Steps in the ETL process.
What is metadata?
Source, data staging, DBMS and client metadata.
Source identification.
Types of data extraction.
Types of data transformation.
ETL software.
Architecture and Physical Design
Data warehouse structure.
Data acquisition architecture.
Reverse engineering.
Data access architecture.
Physical design process.