Oracle Database 11g: Data Warehousing and Oracle Warehouse Builder

Course: OR11DWB

Duration: 5 Days

Level: III

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.

« Hide The Details

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:

Recognize star and snowflake schemas and other data warehouse physical and logical database objects.
Recognize and encouraging optimization of star queries.
Create and maintain materialized views to enhance ad-hoc query performance against enormous volumes of transactional data.
Manage materialized views to effectively and efficiently maintain their structure over the course of time.
Create and maintain dimensions and hierarchies to enhance ad-hoc query performance and support sophisticated data mining.
Perform dimensional analysis of data warehouse information and building cubes.
Configure the Oracle Warehouse Builder (OWB) repository and understand OWB capabilities.
Implement data quality assurance using data rules and the OWB Data Profiler to evaluate data quality within transactional source databases and warehouse target databases.
Use mappings and process flows for data correction and augmentation, as well as build Extraction, Transformation and Transformation (ETT) or Loading (ETL) processes within OWB

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.

« Hide The Details

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.

Schedule For This Course
There are currently no public sessions scheduled for this course. We can schedule a private class for your organization just a couple of weeks from now. Or we can let you know the next time we do schedule a public session.

Notify me the next time this course is confirmed!

Can't find the course you want?
Call us at 800.533.3893, or
email us at [email protected]