Dimensional Data Warehouse Design Course

Course Objectives  Audience  Content  Instructor  Dates  Price

Dimensional modelling is the proven data modelling technique for developing understandable, high-performance data warehouses and data marts.

Dimensional analysis and design closes the gap between business analysis requirements and traditional data modelling. The use of dimensional techniques throughout analysis, design and ETL improves productivity and communication between IT and business users by supporting incremental development and more realistically capturing analytical requirements.

This course offers in-depth knowledge of dimensional modelling essential for successfully building and maintaining cost effective data warehouses. It focuses on proven methods and best practices for modelling, extracting, cleaning, conforming, and delivering data.

Throughout the 3 day course dimensional modelling techniques are reinforced by real-world examples and exercises that make extensive use of sample data.


Dimensional modeling and related techniques are covered within the framework described in Ralph Kimball’s book ‘The data warehouse Lifecycle Toolkit’ a copy of which is provided to each student.

Objectives

Upon completion attendees will be able to:

• Participate in rapid incremental data warehouse design
• Establish realistic analytical requirements with business users
• Maximise the usability and performance of their data warehouse or data mart designs
• Reference articles and template documents on-line that provide further support for the techniques covered

Audience

This course is for anyone 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 all benefit from this course.
 

Content

Dimensional Modelling Fundamentals

• Data warehousing requirements and challenges
• Modelling for measurement – the case for dimensional modelling
• Star schema, snowflake schema, facts and dimensions
• The four steps of dimensional modelling
• Fact table types – transactional, periodic snapshots, accumulating snapshots
• Fact additivity – additive, semi-additive and non-additive measures
• Business dimension types, the 5Ws – Who, What, Where, When and Why
• Calendar and Time dimensions
• Degenerate dimensions
• Dimensional attributes and hierarchies
• Accurately reflecting history, supporting current (as is), historically correct (as was) and alternative (as at) analysis views – slowly changing dimensions
• Modelling for change – using surrogate keys

Data Warehouse Design

• Data marts vs. enterprise data warehouses
• Dimensional Data Warehouse vs. Corporate Information Factory
• Incremental data warehouse development using data marts – the Data Warehouse Bus Architecture, dimensional matrix
• Data Warehouse reuse – conformed dimensions and facts, multi-role dimensions
• Consolidated data marts

Dimensional Analysis

• Gathering Analytical Requirements – asking the right questions
• Identifying and documenting the relationships between business events, dimensions and users – data warehouse matrices
• Identifying Key Performance Indicators (KPIs) and Metrics – aggregation level, comparisons and query by exception
• Identifying and classifying dimensional attributes and hierarchies – finding hidden data sources

Dimensional Modelling Patterns

• Combining and separating dimensions
• Flexible date handling, ad-hoc ranges and multiple simultaneous events
• Dealing with Very Large Dimensions – Individual customers – mini dimensions, 'snowflaking' and hot-swappable dimensions
• Customer relationship measurement – recency, frequency and intensity facts and dimensional attributes
• Allocation problems – multi-valued dimensions, bridge tables, weighting factors, impact reports and 'correctly weighted' analysis
• Supporting complex combination constraints – storing data as rows and columns, Bitmap dimensions
• Ragged and volatile hierarchies – organization structures, bill of materials – using hierarchy maps to cope with recursive relationships and dynamic hierarchies
• Modelling sequential behaviour – dimensional overloading, step dimensions, first and last analysis
• Multinational support – national languages reporting, multi-currencies time zones and local calendars
• Fact table performance optimization – indexing, partitioning and aggregation strategies

Instructor

This course is designed and presented by Lawrence Corr

Dates

The next course will run 22-24 March 2010 at The Corn Exchange, Leeds. For future dates check our Public Schedule or contact us to arrange an on-site class.

Price

Course fees of £1,250 per attendee includes course workbook, a copy of  'The Data Warehouse Toolkit' Ralph Kimball et al. refreshments and lunch.

Discounts
A 15% group booking discount is available for two or more attendees booking at the same time.

To book please call +44 (0)7971 964824 or email training@decisionone.co.uk.