Oracle Database 10g Implement & Administer Data Warehouses I

Request more details:

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
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: