Dimensional Modelling for Business Intelligence

Dimensional models provide the underlying building blocks for a Business Intelligence (BI) initiative which means that Dimensional Modelling skills are one of the critical factors that contribute to the successful outcomes of BI projects.

This two-day course gives participants the skills required to develop Dimensional Models that can be applied to real business problems.

The course is based on Ralph Kimball’s approach to Dimensional Modelling which has become widely adopted to the point that it is now considered the “standard” approach to Dimensional Modelling.

The course is taught by an experienced Dimensional Modeller who offers an independent point of view that is free from vendor bias and tool preferences.

At the end of the course participants will be equipped to make a valuable contribution to BI projects.

Course Features

• Assumes no prior knowledge of dimensional modelling or business intelligence.
• Independent perspective of dimensional modelling free of vendor influence and preferred tool bias.
• Based on Ralph Kimball’s widely adopted approach to dimensional modelling.

Participant Benefits

• Ability to describe the principles of dimensional modelling and develop dimensional models that solve real business problems.
• Ability to design a series of data marts conforming to a planned architectural approach.
• Capable of making an informed and valuable contribution to Business Intelligence (BI) projects.
• Understand the relationship of dimensional modelling to business planning, measurement, and IT architecture.

Who Should Attend

• Anyone acting (or planning to act) in the role of Data Architect, Data Analyst, Business Systems Analyst, Systems Analyst, Business Analyst or Business Consultant.
• Experienced Data Architects who need to update their skills, attend a “refresher”, or simply get some new ideas.
• Other IT professionals and business intelligence stakeholders who need to understand dimensional modelling.

Course Duration

• 2 days full-time

Course Agenda

Business Concepts

• Business processes
• Asset lifecycles
• Management life-cycles
• Planning

Measurement

• Management “instrument panels
• The “Balanced Scorecard”
  • Financial perspective
  • Customer perspective
  • Internal business process perspective
  • Learning and growth perspective
  • Lagging and leading indicators

Dimensional Concepts

• Entity Relationship modelling (ER)
• Dimensional Modelling (DM)
• Relationships between DM and ER
• Why dimensional modelling?

The Dimensional Model

• The Dimensional Model
  • Facts
  • Attributes
  • Dimensions
• Primary, foreign and surrogate keys
• Keys
  • Primary
  • Foreign
  • Surrogate
• Granularity of facts

Inside Dimension Tables

• Drilling down
• High quality verbose attributes
• Degenerate dimensions
• Time dimension
  • Time dimension hierarchy
  • Time dimension granularity
• Location dimension
• Party-role dimensions
• Large dimensions
• Mini dimensions
• Slowly changing dimensions
• Multi-valued facts
• Snowflake Schemas

Inside Fact Tables

• Type of fact
  • Additive
  • Semi-additive
  • Non additive
• “Fact-less” fact tables
• Fact table families
  • Value chains
  • Heterogeneous product schema
  • Aggregates

An Architectural Approach

• Data marts
  • Conformed dimensions
  • Conformed fact definitions
  • Data mart granularity
• The data mart matrix

Building Dimensional Models

• Building the data mart matrix
• Four steps to define a data mart
  • Step 1: Choose the data mart
  • Step 2: Declare the grain
  • Step 3: Choose the dimensions
  • Step 4: Choose the facts
• Design principles

Documenting Dimensional Models

• The data mart matrix
• Facts
  • Fact table diagram
  • Fact table detail
• Dimensions
  • Dimension table diagram
  • Dimension table detail

Identifying Source Data

• Candidate data sources
• Surveying the data
• Mapping source to target data
• Defining aggregations

Extract, Transform, Load (ETL)

• High level schematics
• The ETL process
  • Source systems
  • Data staging and transform
  • Load
  • Presentation data area
• The Operational Data Store (ODS)

Data Quality

• Data quality improvement
• Data quality assurance

The Data Warehouse

• Hardware platforms
• Software platforms
• Data stores
• Back end functions
• Front end functions
• Meta data repository
• Data warehouse project lifecycle