Dimensional modeling is the proven technique for developing understandable, high-performance data warehouses. Our course offers introductory to advanced dimensional modeling techniques and the practical detailed steps of the data warehouse ETL system including extracting, cleaning, conforming, and delivering data and its associated metadata.  You'll learn to successfully design, obtain, prepare and publish data in a dimensional data warehouse.

Training Schedule
Improve your data warehouse skills!
Our Data Warehouse and ETL Design and Development Training Courses are offered across the globe, find one near you.


Class sizes are limited, so call and reserve your seat today!

Course Outline

A useful summary of the objectives, goals and content of our comprehensive data warehouse and ETL classes.

Data Warehouse and ETL Design and Development Training Course

This 5-day training course combines two unique design workshops from international data warehousing experts Joe Caserta and Lawrence Corr.

• Dimensional Modeling, Analysis and Design Workshop
• ETL Architecture and Design Workshop

Why take this course:
Dimensional modeling is the proven technique for developing understandable, high-performance data warehouses and data marts. Dimensional analysis and design closes the gap between business requirements and traditional dimensional modeling. The rigorous and practical use of dimensional analysis and design improves productivity and communication between business users and IT by supporting incremental development and more fully capturing analytical requirements.

Additionally, the data warehouse is best known for taking disparate and disordered transaction data and presenting it in a cohesive, orderly way for the business to make intelligent, fact-based decisions. The data warehouse ETL designer is charged with the task of applying a set of consistent techniques for delivering conformed dimensional data. Precisely designing and building reusable processes to extract, clean, conform and deliver dimensional data is the foundation for a successful, reduced cost, data warehouse implementation.

Who should attend:
This course is appropriate for anyone involved or interested in learning the latest techniques for planning, designing and managing dimensional data warehouses and ETL processes.

Beginner, intermediate and experienced data warehouse practitioners, data architects, DBA’s and ETL designers & developers will benefit from this course.

Materials:
In addition to the course material all students attending the ETL workshop will receive a copy of Ralph Kimball and Joe Caserta’s book, The Data Warehouse ETL Toolkit (Wiley, 2004).

About the Instructors:
Lawrence Corr is a leading data warehouse design specialist and highly experienced educator. He has taught data warehouse design courses across Europe, North America and Africa. In the dimensional modeling workshop Lawrence covers the latest techniques for aligning database design with business analytical requirements.

Joe Caserta is the co-author with Ralph Kimball of the first in-depth book on data warehouse ETL design and development, The Data Warehouse ETL Toolkit (Wiley 2004). He is an influential data warehousing veteran whose expertise is shaped by years of industry experience. In the ETL workshop Joe presents best practices for designing, deploying and managing the data warehouse ETL process.

Part 1: Dimensional Modeling, Analysis and Design Workshop

Duration: 2.5 days
Instructor: Lawrence Corr

DMA Overview:
This workshop covers introductory to advanced dimensional modeling techniques focusing on real-world design challenges typical of large, complex multinational data warehouse projects. Topics are taught through a combination of lecture, instructor-led examples, case studies and team exercises that review existing dimensional designs, model dimensional solutions to common business problems and plan the design of entire data warehouses.

DMA Objectives:
Upon completion students will be able to:

• Participate in rapid data warehouse design sessions
• Establish clear and concise communication with business users
• Maximize the usability and performance of their data warehouse designs
• Find supporting articles and DMA template documents on the web

DMA Contents:
Dimensional Modeling Fundamentals:

• Modeling for measurement – the case for dimensional modeling
• Fundamentals of stars, snowflakes, facts and dimensions
• Slowly changing dimensions – accurately reflecting history, supporting current, historically correct and alternative views
• Common dimensional modeling techniques – Time, multi-role and degenerate dimensions, surrogate keys, value chains and other common multi-star design patterns
• The Data Warehouse Bus Architecture of conformed dimensions and facts, how data marts can enable incremental data warehouse development

Dimensional Analysis:

• Gathering Analytical Requirements – how to ask the right questions
• Identifying business events and processes that must be measured
• Identifying business dimensions by classification (Who, What, Where, When and Why or People, Things, Places, Timestamps and Reasons)
• Identifying and documenting the relationships between business events and dimensions – the dimensional matrix (reloaded)
• Identifying Key Performance Indicators (KPIs) and Metrics - aggregates, comparisons and exceptions
• Defining granular facts – additive, semi-additive and non-additive measures
• Identifying and classifying dimensional attributes and hierarchies – completeness checks

Advanced Dimensional Modeling:

• Rules for combining and separating dimensions
• Flexible date handling, ad-hoc ranges and multiple simultaneous events
• Dealing with Very Large Dimensions – B2C customers
• Hot-swappable and ultra-hot-swappable dimensions
• Multi-valued dimensions – allocation problems, impact and correctly weighted analysis
• Multi-level dimensions
• Bitmap dimensions – supporting complex combination constraints
• Advanced slowly changing dimension techniques – multiple alternate realities, hybrid type 2/3 dimensions
• Variable-depth hierarchies – organization structures, bill of materials recursive relationships, dynamic hierarchies, and generic hierarchy maps
• CRM measures – using recency, frequency and intensity
• Affinity grouping – market basket analysis
• Multinational support – multiple languages, currencies and units of measure

Part 2: ETL Design Workshop

Duration: 2.5 days
Instructor: Joe Caserta

ETL Overview:
This workshop offers an in-depth understanding of extract, transform and load (ETL) techniques essential for building dimensional data warehouses. It focuses on proven methods and best practices to successfully implement, manage and maintain the most challenging task of any data warehouse project – the ETL.

ETL Objectives:
This workshop teaches the practical detailed steps of data warehouse ETL including extracting, cleaning, conforming, and delivering data and its associated metadata. You’ll learn the design and development, architecture, operations and management aspects of scheduled and real-time ETL. Upon completion you’ll have learned each of the steps, and sub-steps, required to successfully obtain, prepare and publish data in a dimensional data warehouse.

ETL Contents:
Functional Practices:

• Planning and designing your ETL system
• Choosing the appropriate architecture
• Managing the implementation
• Managing the day to day operations
• Building the development/test/production suite of ETL processes
• Building a data cleaning subsystem
• Understanding the tradeoffs of various staging data structures, including flat files, normalized schemas, XML, and dimensional schemas
• Analyzing and extracting source data
• Creating the logical data mapping

Technical Practices:

• Structuring the data into dimensional schemas for the most effective delivery to end users
• Conforming heterogeneous data from multiple sources into standardized dimension tables and fact tables
• Building ETL modules for handling the three distinct types of slowly changing dimensions (SCDs)
• Building ETL modules for multi-valued dimensions and hierarchical dimensions
• Running high-performance surrogate key pipelines
• Loading the three fundamental fact table grains – transaction, periodic snapshot and accumulating snapshot
• Handling late arriving dimensions and facts
• Optimizing ETL processes to fit into highly constrained load windows
• Structuring and presenting metadata
• Converting batch and file-oriented processes into continuously streaming real-time ETL systems