ORACLE DATA WAREHOUSE DESIGN - ETL

Course:   OWHETL
Duration:   3 Days
Level:   I
On our website at:   http://www.verhoef-training.com/courses/OWHETL.html
 
Course Summary

This course focuses on the extract, transform and load (ETL) phase of the data warehouse (DW) development life cycle. After discussing ETL as it relates to all DBMSs (DB2, MS SQL Server, Sybase, etc.) we elaborate on Oracle's ETL offering:

Topics Covered In This Course

REVIEW OF DATA WAREHOUSING (DW) TERMS AND CONCEPTS

  • The DW environment
  • What is a data warehouse?
  • What is a data mart?
  • What is Business Intelligence (BI)?
  • How do OLTP and OLAP differ?
  • What is data mining?
  • Operational vs. historical data
  • What is a star schema?
  • What is a snowflake schema?
  • Normalization vs. denormalization
  • What are hierarchies?
  • What is dimensional modelling?
  • What is the Data Warehouse Bus Architecture (DWB)?
  • What are surrogate keys?
  • What is Extract, Transform, Load (ETL)?
  • What are Slowly Changing Dimensions (SCD)?
  • What is Metadata?
  • What Materialized Views (MV)?
  • How does logical design differ from physical design?

EXTRACT, TRANSFORM, LOAD (ETL) TERMS and CONCEPTS

  • Options
  • Extraction options
  • Transformation options
  • Loading options
  • Change Data Capture and publishing
  • Staging areas

EXTRACTING

  • Logical-to-physical data mapping
  • Disparate (heterogeneous) data sources
  • Extracting changes data – delta or other

DATA CLEANING and CONFORMING

  • Data quality criteria
  • Design methods and alternatives
  • Cleaning deliverables
  • Conforming dimension tables
  • Conforming fact tables

DIMENSION TABLE DELIVERY

  • Dimension table structure
  • Surrogate key generation
  • Dimension table grain
  • Flat (denormalized) or snowflake?
  • Data and time dimensions
  • 'Big' vs. 'small' dimensions
  • Dimensional roles
  • Dimensions as subdimensions
  • Degenerate dimensions

SLOWLY CHANGING DIMENSIONS

  • Type 1
  • Type 2
  • Type 3
  • Hybrid
  • Late arrivals

MULTIVALUED DIMENSIONS

  • Definition
  • Bridge tables

FACT TABLE DELIVERY

  • Fact table structure
  • Referential integrity (RI)
  • Surrogate key derivation and flow
  • Fundamental grain
  • Transaction fact tables
  • Factless fact tables
  • Periodic snapshots
  • Accumulating snapshots

FACT TABLE LOAD CONSIDERATIONS

  • Index management
  • Partition management
  • Updates, deletes and inserts
  • Recovery
  • Summary tables
  • Parallelism

ETL TOOLS

ORACLE'S DATA EXTRACTION

  • Synchronous Change Data Capture (CDC)
  • Asynchronous Change Data Capture (CDC)

ORACLE'S DW LOADING OPTIONS

  • SQL*Loader
  • Optimising SQL*Loader performance
  • SQL*Loader Direct Path Load
  • SQL*Loader partitioning considerations
  • SQL*Loader and data constraints (e.g., RI)
  • SQL*Loader and parallelism
  • SQL*Loader transformation options
  • SQL*Loader and index optimisation
  • Oracle's Data Pump
  • External tables
  • Transportable tablespaces
  • Using SQL MERGE
  • Multiple table INSERTs

ORACLE DATA TRANSFORMATIONS and CLEANING

  • SQL updates
  • Regular expressions
  • Data validation
  • DW key lookups
  • Table functions
  • Moving data from staging to fact
  • Exchanging partitions
  • Direct path inserts
  • CREATE TABLE AS ...

DW PERFORMANCE and GOOD PRACTICES

  • Query rewrite and MVs
  • Star vs. snowflake schema
  • SQL aggregates (e.g., ROLLUP)
  • Parallelism
  • Partitioning options
  • Index options
What You Can Expect

Upon completion of this presentation, the participant should understand the ETL phase of the data warehouse development life cycle.

Who Should Take This Course

Data Warehouse administrators, DBAs, data architects, DW PL/SQL developers.

Recommended Prerequisites

Completion of our course Data Warehouse Terms, Concepts and Architecture or equivalent knowledge.

Training Style

Lecture and hands-on.

Related Courses
Code Course Title Duration Level
DWHTCA
Data Warehousing Terms, Concepts and Architecture
1 Day
I
Details
OWHDWD
ORACLE DATA WAREHOUSE DESIGN
5 Days
I
Details
DWHFUN
Data Warehousing Fundamentals
3 Days
I
Details

Every student attending a Verhoef Training class will receive a certificate good for $100 toward their next public class taken within a year.

You can also buy "Verhoef Vouchers" to get a discounted rate for a single student in any of our public or web-based classes. Contact your account manager or our sales office for details.