Oracle Database 10g Implement & Administer Data Warehouses I

This course has been superseded

We suggest the following instead:
Oracle Database Administration Course

Course Description

This course considers how to build, implement, tune and utilize data warehouses with Oracle technology. Logical data warehouse concepts are considered such as dimension tables, fact tables and star schemas. Implementing such logical concepts using the Oracle database is then presented including defining dimensions, hierarchies, measures and other objects. Physical implementation techniques are considered such as bitmap indexes, materialized views, and others. Emphasis is placed on the parallel execution features of the database and how these can yield significant performance advantages

View other Oracle Courses available
3 Days
Contact us for pricing
 

Prerequisites

Oracle Database 10g: Implement Parallel SQL & Partitioning For Data Warehouses
Oracle Database 10g: SQL Tuning

DATA WAREHOUSE DESIGN & SCHEMAS

DATA WAREHOUSE CONCEPTS
ETT / ETL
DATA WAREHOUSE SCHEMAS
The EQUITIES Data Model
PHYSICAL DESIGN CONSIDERATIONS

CREATING MATERIALIZED VIEWS

ABOUT MATERIALIZED VIEWS
CREATE MATERIALIZED VIEWS
STORAGE & TABLESPACE Clauses
PARALLEL & PARTITION BY Clauses
BUILD Clause
Specifying The SELECT Clause
Including The ORDER BY Clause
NESTED MATERIALIZED VIEWS

MAINTAINING MATERIALIZED VIEWS

ALTER MATERIALIZED VIEW
DROP MATERIALIZED VIEW
DATA DICTIONARY STORAGE
USER_MVIEWS Example
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
DATA DICTIONARY REFRESH METADATA
USING EM

CONTROLLING THE QUERY REWRITE FACILITY

ENABLING QUERY REWRITE
VIEWING EXECUTION PLANS
Create PLAN_TABLE
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

DIMENSIONS

WHAT ARE DIMENSIONS?
CREATING & MAINTAINING DIMENSIONS
ALTER DIMENSION
DROP DIMENSION
DIMENSION METADATA & VALIDATION
Using DBMS_DIMENSION()
Using EM

DIMENSIONAL ANALYSIS OF DATA

DATA SAMPLING
DIMENSION AGGREGATION TECHNIQUES
Using ROLLUP()
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
FACT Hint

ETL: LOADING FROM EXTERNAL TABLES

ABOUT THE EXTRACTION OPTIONS
Offline Extraction Methods
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

OracleOracle 10gOracle Database 10g