Training

How We're Different

Course Summaries

Course Logistics

Client-site Classes

Virtual Classes

FAQ

Cancellation Policy

Request Information

Course Surveys

Overview

This four-day hands-on course covers the three stages of data warehouse physical design implementation: Staging, ETL (extraction, transformation, and loading), and Presentation.  After a brief discussion of multi-dimensional design, this course will focus on the tasks and Oracle tools used to physically implement an Oracle data warehouse and present the data. 

Course Objectives

The course will introduce the student to the following:

• Oracle database constructs:  tablespaces, schemas, indexes, materialized views, and privileges. 

• Oracle database performance parameters

• Oracle partitioning and parallel operations

• Oracle OLAP dimensions, cubes and hierarchies

• Oracle ETL/DML tools:  Insert All, Merge Cubes, Rollup and Grouping Sets ETL strategies

• Oracle analytic functions

• Oracle products: SQL Developer, SQL Developer Data Modeler, Discoverer, Oracle Warehouse Builder and Oracle Data Integrator.

Course Agenda

I.  Review of Data Warehousing Terms and Concepts

• The data warehouse 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. historic data
• What is a star schema?
• What is a snowflake schema?
• Normalization vs. denormalization
• What are hierarchies?
• What is dimensional modeling?
• What is the Data Warehouse Bus Architecture (DWB)?
• What are surrogate keys?
• What is extract, transform, and load (ETL)?
• What are slowly changing dimensions (SCD)?
• What is metadata?
• What are materialized views (MV’s)?
• How does logical design differ from physical design?

II. Oracle Database Architecture – A Summary

• Memory structures
• Logical storage structures
• Processes
• SQL tools and extensions
• Oracle RAC clusters

III. Logical/Physical Design Process

• Data warehouse database objects
• Setting up schemas and privileges
• Hardware and I/O considerations

IV. Index Design Options

• Why use indexes?
• Composite indexes
• Types of indexes
• B-tree index
• Bitmap indexes
• Determining bitmap index candidates
• Bitmap join index
• Tuning star queries
• Using star transformation
• Star transformation restrictions
• Function based indexes
• Reverse key indexes
• Descending indexes
• Invisible indexes (11g)
• Index monitoring
• Foreign key indexing
• Why indexes may not be used

V.  DW Partitions, parallel operation, and compression

• Compression options
• Partition options
• Dropping partitions
• Truncating partitioned tables
• Rebuilding index partitions
• Splitting partitions
• Parallelism options
• Parallel query
• Parallel DML
• Parallel DDL
• Parallel data loading
• Recovery
• Partition pruning
• Merging partitions
• Partition-wise joins
• Local vs global partitioned indexes
• Local partitioned indexes
• Global range partitioned indexes
• Global hash partitioned indexes
• Global non-partitioned indexes
• Guidelines for using partitioned indexes in OLTP applications
• Guidelines for using partitioned indexes in data warehousing and DSS applications
• Partitioned indexes on composite partitions

VI. Joins

• Join types
• Nested loop join
• Hash join
• Sort merge join
• Driving tables
• Caching tables

VII. Constraints, referential integrity, sequences, and synonyms

• Maintaining the integrity of the database
• The Check constraint
• The Default option
• The Not Null constraint
• The Unique Constraint
• The Primary Key constraint
• The Foreign Key constraint
• Modifying constraints
• When to use constraints
• Synonyms
• Sequences

VIII. Materialized views

• When to user materialized views
• Types of materialized views
• Materialized views with aggregates
• Materialized views containing only joins
• Nested materialized views
• Build methods
• Enable query rewrite
• Query rewrite restrictions
• General query rewrite restrictions
• Refresh modes and options
• General restrictions on fast refresh
• Restrictions on fast refresh on materialized views with joins only
• Restrictions on fast refresh on materialized views with aggregates
• Restrictions on fast refresh on materialized views with the Union All operator
• Timing the refresh
• The Dbms_mview package
• The Dbms_refresh package

IX. Dimensions, levels and hierarchies

• Multidimensional structures
• Cubes
• Measures
• Joins
• Dimensions
• Levels
• Hierarchies
• Value based hierarchy
• Skip level hierarchy
• Ragged hierarchy
• Working with hierarchies – parents and children
• Dimensional query conditions
• Oracle dimensions
• Creating dimensions and cubes
• Oracle OLAP option

X. Extraction, transformation, and loading

• The ETL process
• Extraction types
• Data integration techniques
• Data cleansing
• Data cleansing methodology
• Surrogate keys
• The Insert-All statement
• The Merge statement

XI. Oracle Text

• Oracle Text
• Oracle Text indexes
• Context index
• Ctxcat index

XII. Analytic functions, cube, rollup, grouping sets, and Oracle data warehouse tools

• Rank() and over
• Dense_rank()
• Handling Nulls
• Top N and Bottom N queries
• The partition option
• Percentage ranks
• The Percent_rank() function
• The Cume_dis() function
• The Ntile() function
• The Row_number() function
• Windowing
• The Cumulative Aggregate function
• Moving averages
• Moving averages with a partitioned outer join
• Centered moving averages
• The Ratio_to_report function
• The Lag and Lead functions
• Statistical functions
• Linear regression functions
• Predicting the future with linear regression
• Grouping sets
• The Rollup option
• The Cube option

XIII. Oracle data warehouse performance considerations

• DML error logging
• Pipeline functions, external tables, and ETL

XIV. Oracle Discoverer

• Product overview
• Creating a business area using the Administrative edition
• Join tables
• Creating hierarchies
• Creating lists
• Creating a work sheet with the Desktop edition
• Analyzing a star schema

Prerequisites

• SQL Background is helpful

Lab Work and Software Requirements

This course includes hands-on workshops that comprise approximately 50% of the class.

• Desktop or laptop
• Oracle Database Evaluation software (Oracle 10g or 11g - client’s preferred version)
• Oracle Tools (evaluation versions of client’s preferred tool, such as Data Modeler, Data Developer, Toad, or any other tool)
• Adobe Acrobat Reader or Acrobat 4.0 or later

Delivery