Audience
Data Architects, Database Administrators, Data Analysts, Business Analysts, Application Developers, and System Designers.
Prerequisites
Participants should have a reasonable knowledge of data modelling. Some knowledge of Structured Query Language would be useful. Attendance on the Data Modelling and Database Design course would be ideal.
Duration
Three days. Lecture presentations are supported by examples and a realistic case study to reinforce learning and enhance understanding.
Course Objectives
After taking this course, participants should be able to:
- State the strengths of dimensional models and understand how such models are used in the development of data warehouses.
- Develop appropriate dimensional models using the approach given in the course.
- Convert a data model into a dimensional model.
- Analyse and potentially improve a given dimensional model.
- Carry out multi-dimensional analysis and understand how the results may be presented to business managers.
- Understand the data mining process and common data mining tasks.
Course Content
Data Warehouses
What is a data warehouse?
Characteristics of a data warehouse
Online Analytical Processing (OLAP)
Data warehouse architectures
Data marts
Development life cycles
Introduction to Dimensional Modelling
Online Transaction Processing (OLTP)
OLTP advantages and disadvantages
Why dimensional modelling is used in data warehouse design
Facts and dimensions
Star Schemas
Snowflake schemas
Fact tables and fact granularity
Strengths of dimensional modelling
Developing Dimensional Models
Development lifecycle
Requirements gathering, the Sun model
Conformed dimensions
Identifying facts, dimensions and data marts
Aggregates and aggregate navigators
Fact and dimension table detail
Identifying data sources
Converting a Data Model to a Dimensional Model
Identify the business process
Identify many-to-many relationships
Identify the fact table
Denormalise remaining entities
Basic Modelling Patterns
Fact tables and dimension tables
Snowflaking
Date dimensions
Attributes of dimensions
Types of dimension - slowly changing, rapidly changing
Degenerate dimensions
Factless fact tables
Further Dimensional Modelling
Many-to-many dimensions
Balanced and unbalanced hierarchies
Hot-swappable dimensions
Handling time of day
Time across international time zones
Time stamping
Snapshots
Transactions
Analysing a Dimensional Model
Reviewing business needs
Identification of user needs
Model review guidelines
Data Analysis
Querying data and reporting
Multi-dimensional analysis techniques:
• Slicing and dicing
• Pivoting
• Drilling up and down
• Rolling up and down
Data visualisation
Introduction to Data Mining
Definition of data mining
Crisp-DM
Supervised and unsupervised learning
Association and correlation
Classification and prediction
Cluster analysis