Oracle Database 11g: Data Warehousing & Oracle Warehouse Builder

This course has been superseded

We suggest the following instead:
Oracle Database Administration Course

Course Description

This Oracle 11g training course will highlight the features within the Oracle database specifically intended to support data warehouses and data mining operations.
5 Days
Contact us for pricing
 

Prerequisites

A mandatory prerequisite to this course is the course ORACLE DATABASE 11g: IMPLEMENT PARALLEL SQL & PARTITIONING FOR DATA WAREHOUSES.

Additionally, it is recommended that one have attended one of the following advanced courses provided for either database developers or database administrators:
• ORACLE DATABASE 11g: NEW & ADVANCED FEATURES FOR DEVELOPERS
• ORACLE DATABASE 11g: NEW FEATURES FOR ADMINISTRATORS

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

DIMENSIONS

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

DIMENSIONAL ANALYSIS OF DATA

CREATING & MAINTAINING DIMENSIONS
ALTER DIMENSION
DROP DIMENSION
DIMENSION METADATA & VALIDATION

STAR QUERIES & THE OPTIMIZER

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()

ETL: LOADING FROM EXTERNAL TABLES

WHAT IS A STAR QUERY?
A STAR TRANSFORMATION SCENARIO
ENCOURAGING STAR TRANSFORMATION
STAR TRANSFORMATION HINTS
STAR_TRANSFORMATION Hint

ETL: TRANSFORMATION WITH TABLE FUNCTIONS

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

ABOUT ORACLE WAREHOUSE BUILDER

CONFIGURE ORACLE WAREHOUSE BUILDER

OWB & THE DATA WAREHOUSE
OWB CAPABILITIES
OWB ARCHITECTURE
OWB Repository
OMB*Plus

USING THE DESIGN CENTER & BUILDING THE INFRASTRUCTURE

REPOSITORY DATABASE CONFIGURATION
MANAGE THE CONTROL CENTER SERVICES
CONFIGURE THE REPOSITORY & WORKSPACE

DEFINE MODULES & IMPORT METADATA

LAUNCH THE DESIGN CENTER
DEFINING THE INFRASTRUCTURE
DEFINING LOCATIONS
DEFINING CONTROL CENTERS
DESIGN CENTER PREFERENCES

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

OracleOracle 11gOracle Database 11g