Data Warehousing Design Fundamentals

This course will cover Kimball Dimensional design and development techniques where you will gain knowledge and skills in:

• Kimball Dimensional Modelling techniques.
• Star Schemas.
• Fact & Dimension tables.
• Bridge Tables.
• Junk Dimensions. 

❑ Module 1: Approaching Dimensional Modelling

• Introduces the components of the overall DW/BI architecture, establishes the core vocabulary used during the remainder of the course and covers some of the misconceptions about dimensional modelling.

❑ Module 2: Kimball Dimensional Modelling Techniques Overview

• Overview of the dimensional modelling techniques and patterns that will emerge in the case study examples that follow.

❑ Module 3: Dimension Tables

• Introduces the fundamental building blocks of the Dimensional model. Covers the four-step process for designing dimensional models. Explores dimension tables in depth, including the date dimension, degenerate dimensions, snowflaking, and surrogate keys.

❑ Module 4: Fact Tables

• Explores the three fundamental types of fact tables: transaction, periodic snapshot, and accumulating snapshot. Introduces the enterprise data warehouse bus architecture and the bus matrix with conformed dimensions.

❑ Module 5: Slowly Changing Dimension Attributes

• Explores  a series of basic and advanced techniques for handling slowly changing dimension attributes.

❑ Module 6: Advanced Fact Table Techniques.

• Explores dimensions that play multiple roles within a schema. Covers the common challenges modelers face when dealing with order management information, such as header/line item considerations, multiple currencies or units of measure, and junk dimensions with miscellaneous transaction indicators.

❑ Module 7: Dimension Hierarchies

• Explores dimension attribute hierarchies, from simple denormalized fixed depth hierarchies to bridge tables for navigating more complex ragged, variable depth hierarchies.

❑ Module 8: Bridge Tables

• Explores the customer dimension, including address standardization and bridge tables for multivalued dimension attributes, complex customer behavior modeling patterns, as well as the consolidation of customer data from multiple sources.

❑ Module 9: Dimensional Modelling Case Study

• This module integrates many of the patterns covered earlier in a single set of interrelated schemas. It consolidates the modeling techniques which are now layered on top of one another.

❑ Module 10: Modelling Case Study

• This supplementary case study will allow further practical application of the information covered previously and allow exploration of the relational aspects.

❑ module 10.1: Planning and Preparation Through Analysis

• This module analyses a Dimensional model for the case study from a company operational capacity (what a company does for a living).

❑ module 10.2: Creating and Refining Tables During the Design Phase

• This module explores the design of a Dimensional model for the case study.

❑ module 10.3: Filling in the Details with a Detailed Design

• This module continues the design process for the case study — refining fields in tables. Field design refinement includes field content, field formatting, and indexing on fields.

❑ module 10.4: Business Rules and Field Settings

• This module is the final of four modules covering the case study design of the Dimensional model. Business rules application to design encompasses stored procedures, as well as specialized and very detailed field formatting and restrictions.