Overview

This course introduces the delegate to all the necessary terminology, architectures, approaches, skills, techniques, and infrastructure required to design and implement a successful Data Warehouse solution. This is largely achieved by guiding the delegate through an easy to understand process that uses a series of practicals to create an incremental and iterative process.

A Data Warehouse has the potential to revolutionise your business. Increased profitability, customer retention, and market penetration are all possible applications of an effective Data Warehouse implementation but first of all you have to build it. The approach, methodology and design techniques required to build a Data Warehouse differ significantly from those required to build a database to support on-line transaction processing.

Instead of focusing on the technology from a given vendor the entire course, including the exercises and demonstrations, is vendor neutral and focus on the generic skills that are necessary to create an effective data warehouse. So we won't teach you how to use a particular vendor's piece of software, we will teach you the skills that underpin good warehouse development no matter which software platform you use.

Objectives

At the end of this course you will be able to:

  • Make a business case, assemble a project team, and lay the infrastructure necessary for the Data Warehouse construction to begin
  • Design, implement, utilise, maintain, and administer the Data Warehouse
  • Build models using the dimensional (star schema) modelling technique
  • Extract data from one or more operational systems
  • Transform, condition, and cleanse the data
  • Populate the Data Warehouse using a variety of mechanisms
  • Query, drill-down, and report the data
  • Understand the role of technologies such as OLAP and data mining
  • Understand the importance of metadata and issues surrounding its integration
  • Assess your organisation's readiness to embark on a Data Warehousing project

Audience

This course is made up of a mix of theory and practicals that allow delegates to apply the principles they have learnt. It is ideal for developers, software engineers, database administrators, data analysts, system analysts or application designers who will be involved in designing, building or maintaining a Data Warehouse.

Prerequisites

  • Basic understanding of IT and how business systems use IT; this would be gained by at least a year's experience in IT or business systems development.
  • Some exposure to Relational Databases and Database Modelling. Systems/Application programming experience would be an advantage. Database modelling skills can be acquired on QA's Database Analysis and Design course.

Syllabus

Introduction

  • Historical background
  • What is a Data Warehouse and why do we need it? OLTP versus OLAP
  • Benefits to the business
  • Benefits to IT
  • Reasons for failure

The Project

  • Skills required
  • Top down vs. bottom up development
  • Ownership & funding
  • Methodology
  • Scoping and requirements gathering
  • Questions to ask in determining requirements

Modelling

  • Review of modelling terminology and techniques
  • How a Warehouse is different
  • Logical and physical modelling
  • 3NF vs. denormalisation

Dimensional Modelling Basics

  • The dimensional model
  • The fact table
  • The dimension tables
  • Using the dimensional model
  • Modelling Attributes in the Dimensions
  • Steps in design

Dimensional Modelling Further Considerations

  • Conformed Dimensions
  • Synonym Dimensions
  • Mini Dimensions
  • Snowflaking
  • Changing Dimension Values
  • Handling Hierarchies
  • The importance of Surrogate Keys
  • Aggregates

Hardware Architectures and Database Architectures

  • SMP
  • Clusters
  • MPP
  • RDBMS, Things to look out for
  • Database Parallelism
  • TPC benchmarks

Software Architectures

  • Centralised Data Warehouse
  • Independent or Federated Data Marts
  • Hybrid approach
  • Standards

Data Extraction

  • Extraction method
  • Tool selection guidelines
  • Metadata
  • dependencies
  • The Data Quality Assessment Process

Data Transformation

  • The case for data quality
  • Transformation
  • Cleansing
  • Conditioning
  • Specific data type issues
  • Transformation methods

Data Loading

  • How to identify what has changed
  • Snapshot vs. detail data
  • Full Refresh vs. Delta Capture
  • Load methodology
  • Load techniques

Querying the Data

  • Canned queries
  • Report writers
  • ROLAP and OLAP tools
  • Data visualisation
  • Drilldown analysis
  • End user training
  • Performance

Data Warehousing and the Internet

  • Technology overview
  • Web reporting
  • Web marts
  • The Internet as a data source
  • Extranets
  • Intelligent agents

Data Mining and Exploration

  • Data mining methodology
  • Data mining algorithms
  • Interpreting the output
  • Formulating a strategy

Operating and Maintaining the System

  • Processes and procedures
  • Change control
  • User and privilege administration
  • Service level agreements
  • Archive and recovery

Assessment

  • Being ready for a Data Warehouse
  • Establishing a plan
  • Getting the mandate
  • Training
  • Feasibility study
  • Choosing a pilot project

Training provider

Teaching mode: Classroom - Instructor Led
Duration: 4 days
Gooroo has partnered with the global leaders in IT training to give you access to quality training, personalised to you, targeted at increasing your job opportunities and salary.

Our pricing

We do not display pricing as Gooroo members qualify for special discounts not available elsewhere. You must enquire through Gooroo to get this benefit.

New courses are happening all the time

Our partner's expert training consultant will provide you with the times and all the details you need. Enquire today.

Top skills covered in this course

Analysis
Worldwide
This skill has an average salary of
US$82,833
and is mentioned in
22.87%
of job ads.
Database
Worldwide
This skill has an average salary of
US$79,140
and is mentioned in
11.13%
of job ads.
Data warehouse
Worldwide
This skill has an average salary of
US$101,195
and is mentioned in
0.81%
of job ads.
Data mining
Worldwide
This skill has an average salary of
US$97,185
and is mentioned in
0.64%
of job ads.