Verhoef Training USA

Training Courses and On-Line Classes Preferred By IT Pros

Visit us at:   http://www.verhoef-training.com

103 Hillside Ave., PO Box 1515
West Caldwell, NJ 07006
Main Office:  800.631.0410
Sales Office:  800.533.3893

Oracle Database 11g: Data Warehousing and Oracle Warehouse Builder

Course:   OR11DWB
Duration:   5 Days
Level:   III
On our website at:   http://www.verhoef-training.com/courses/OR11DWB.html
 
Course Summary

This course focuses on the features within the Oracle database that support data warehouses and data mining operations. The students will learn about data warehousing concepts and the requirements to initially configure a data warehouse installation. They will also learn how the Oracle Warehouse Builder (OWB) product may be used to implement and maintain both transactional and warehouse databases, and the movement of data between those two different types of installations.

Topics Covered In This Course

Data Warehouse Design & Schemas

  • Data Warehouse Concepts
  • ETT / ETL
  • Data Warehouse Schemas
  • The EQUITIES Data Model
  • EQUITIES Logical Data Model
  • EQUITIES Physical Model
  • Physical Design Considerations

Creating Materialized Views

  • About Materialized Views
  • Create Materialized Views
  • Enable Query Rewrite
  • STORAGE & TABLESPACE Clauses
  • PARALLEL & PARTITION BY Clauses
  • BUILD Clause
  • Nested Materialized Views

Maintaining Materialized Views

  • Alter Materialized View
  • Drop Materialized View
  • Data Dictionary Storage
  • Using EM

Materialized View Refresh

  • About Materialized View Refresh
  • Refresh Methods
  • Create Materialized View Log
  • Alter Materialized View Log
  • Drop Materialized View Log
  • Refresh Modes
  • ON COMMIT Considerations
  • Performing Refresh Operations
  • Refreshing With REFRESH_ALL_MVIEWS()
  • Refreshing With REFRESH()
  • Refreshing With REFRESH_DEPENDENT()
  • Using EXPLAIN_MVIEW()
  • Data Dictionary Refresh Metadata
  • Using EM

Controlling the Query Rewrite Facility

  • Enabling Query Rewrite
  • Viewing Execution Plans
  • Create PLAN_TABLE
  • Using AUTOTRACE
  • Using EM
  • Controlling Query Rewrite
  • NOREWRITE
  • REWRITE
  • Utilizing Constraints with Query Rewrite
  • ENABLE VALIDATE Constraint Option
  • ENABLE NOVALIDATE Constraint Option
  • DISABLE NOVALIDATE Constraint Option
  • DISABLE VALIDATE Constraint Option
  • RELY Constraint Option
  • ENFORCED Level
  • TRUSTED Level
  • STALE_TOLERATED Level
  • Query Rewrite Influences
  • Using EM for "WHAT-IF?" Scenarios

Dimensions

  • What Are Dimensions?
  • Creating & Maintaining Dimensions
  • Alter Dimension
  • Drop Dimension
  • Dimension Metadata & Validation

Dimensional Analysis of Data

  • Data Sampling
  • Dimension Aggregation Techniques
  • The GROUPING () Function
  • Using CUBE ()
  • Building the Data Warehouse Cube
  • The EQUITIES Cube
  • GROUPING_ID () Function
  • CUBE () vs. GROUPING SETS ()

Star Queries & the Optimizer

  • What Is A Star Query?
  • A Star Transformation Scenario
  • Encouraging Star Transformation
  • Star Transformation Hints
  • STAR_TRANSFORMATION Hint

ETL: Loading from External Tables

  • About the Extraction Options
  • Using External Tables
  • ORACLE_LOADER Access Parameters
  • RECORDS Parameter
  • BADFILE Parameter
  • LOGFILE Parameter
  • DISCARDFILE Parameter
  • LOAD WHEN Parameter
  • SKIP Parameter
  • The LOCATION Clause
  • REJECT LIMIT Clause
  • FIELDS TERMINATED BY Parameter
  • MISSING FIELD VALUES Parameter
  • ORACLE_DATAPUMP Access Driver
  • Maintaining External Tables
  • USER_EXTERNAL_TABLES
  • USER_EXTERNAL_LOCATIONS
  • Using ALTER TABLE

ETL: Transformation with Table Functions

About Oracle Warehouse Builder

  • OWB & the Data Warehouse
  • OWB Capabilities
  • OWB Architecture
  • OWB Repository
  • OMB*Plus

Configure Oracle Warehouse Builder

  • Repository Database Configuration
  • Manage the Control Center Services
  • Configure the Repository & Workspace

Using the Design Center & Building the Infrastructure

  • Launch the Design Center
  • Defining the Infrastructure
  • Defining Locations
  • Defining Control Centers
  • Design Center Preferences

Define Modules & Import Metadata

  • Define a Project Container
  • Define Modules
  • Import Metadata

Maintaining the Relational Model

  • Navigating the Data Object Editor
  • Working with a Detail View
  • Defining a New Object
  • Deploying Objects to a Target Database

Data Quality Assurance & Management

  • About Data Analysis Principles
  • Create Data Profiles
  • Use the Data Profile Editor

Data Rules & Correction Mappings

  • Creating Data Rules
  • Applying Data Rules
  • Correction Mappings
  • Data Auditing Monitors

ETL Mappings

  • Using the Mapping Editor
  • About the Operators
  • Working with Staging Tables

Process Flows & Schedules

  • Using Process Flows
  • Using Schedules
What You Can Expect

At the end of this course, students will be able to:

Who Should Take This Course

This course is designed for database administrators, data warehouse administrators and application developers who will be responsible for implementing applications using data warehouse technology.

Recommended Prerequisites

Data Warehouse Overview Class or equivalent, and good SQL experience.

Training Style

Instructor led with 50% lecture and 50% lab.

Related Courses
Code Course Title Duration Level
ODI11G
Oracle Data Integrator 11g
4 Days
I
Details
ORPTDW
Oracle 11g SQL Performance Tuning for Data Warehouses
4 Days
II
Details
ORDWA
Oracle Database 11g: Administer a Data Warehouse
5 Days
II
Details
OBIBR
Oracle BI Enterprise Edition (OBIEE) 11g: Building Repositories
5 Days
II
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.