Dimensional Modeling for the Data Warehouse Course

Course Description

training This course is for IT Professionals who are looking to design and build a corporate data warehouse. It is a 3 day intensive, instructor-led course on the fundamental techniques required to optimise the technical design, deliver business benefit and reduce the risk of the project.

Designed for those familiar with traditional RDBMS design techniques, it provides IT Professionals with the knowledge of industry standard tools and techniques necessary to achieve a 'right first time' result when embarking on one of the most challenging types of database architecture design.

The course is packed with practical aspects, tips and techniques gained over years by our consultants to ensure that you benefit from those years of knowledge and experience – not just theory. This includes recognition of;

Where a DWH fits into the business.
How surrounding tools (e.g. BI, ETL, Data Quality, Meta Data Management) can dictate the right course to take in design decisions.
How to manage projects to minimise risk and optimise business benefit.
The pitfalls of such projects and how to avoid them.

Topics Covered

Theory:

Why Star Schema designs are most popular for data warehousing? How does it compare to OLTP and MOLAP data solutions?
Dimensional Modelling - ER Models, Dimensional Models, Star and Snowflake schemas
Fact table definition and the most popular types of facts used in business.
Fact table granularities, aggregates and snapshots.
Metrics, Key Performance Indicators (KPIs) and Facts: additive, semi-additive and non-additive measures.
Industry Examples of common Star Schema definitions.

Practical:

Change Data Capture methods – including specifics around Oracle & SQL Server.
Slowly Changing Dimensions – how to define, build and report against.
Things to consider when using Very Large Dimensions.
Common dimensional modelling techniques: Time, Range Bandings, Roles, Junk, Weighting.
Managing multi-level hierarchies and overcoming issues of 'ragged hierarchies'.
Coping with poor Data Quality – tools and techniques including technical and non technical approaches.

Infrastructure:

Data Warehouse Architectures: data warehouses, operational data stores, data marts.
Physical Database Design – relational optimization of dimensional models.
ETL design & infrastructure – process control, batch, real time and near real time extraction.
Database performance management – DB Tuning/ optimisation, Indexing, partitioning, aggregation.
Meta Data Management and how it impacts the data warehouse.

Management:

The data warehouse development process, methodology and management.
Business analysis techniques and tools for gathering analytical requirements to determine the dimensional model.
Data Warehouse Evolution and conformed dimensions and facts.
Managing Change: to data, to structure and to requirements.
The Business Intelligence (BI) Layer:
The role of the BI meta data layer.
The use of analytics, scorecards and dashboards.

On completion delegates will be able to:

Design a data warehouse according to dimensional modelling design principles.
Resolve many types of complex business requirements including tracking history, aggregate tables, and hierarchical relationships.

Audience

This course is for IT Professionals who are looking to design and build a corporate data warehouse.

Course Style

This course is based around DW design theory and best practice and does not include any hands on practical elements. It does include; group discussions, paper based exercises and case studies to help with learning and provide some insight into real world design.
(Post training surgery days can be purchased to assist with client specific designs.)

Pre requisite Knowledge, Experience and Education

A good understanding of relational database concepts and SQL.

Delivery & Duration

This is a 3 day, instructor led training course. This training can be delivered either on your site or at our training suite in Birmingham.

Request More Information

For more information regarding this workshop, please call today 0845 124 9495 and speak to one of our team or complete our short form below.