Oracle Database 10g Data Warehouse Administration

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, partitioned tables, materialized views, and others. Emphasis is placed on the parallel execution features of the database and how these can yield significant performance advantages.

The objective of this course is to consider present a comprehensive consideration of data warehouse features that exist within the Oracle database. Major subject areas to be explored are:

• Understanding star schemas and other data warehouse objects.
• Understanding and encouraging optimization of star queries
• Creating and maintaining materialized views to enhance ad-hoc query performance.
• Creating and maintaining dimensions to enhance ad-hoc query performance.
• Performing dimensional analysis of data warehouse information.
• Using the Summary Advisor tool for data warehouse design recommendations

Target audience

Target audience for this course is database administrators, data warehouse administrators and application developers who will be responsible for implementing and using data warehouse technology. Students should have attended the following courses or have equivalent knowledge and experience:

• Oracle Database 10g Introduction to SQL
• Oracle Database 10g: Program With PL/SQL
• Oracle Database 10g: Administration I
• Oracle Database 10g: SQL Tuning
• Oracle Database 10g: New Features for Administrators

Course content

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

About Courseware Company :: Instructor-led training courseware :: Self-study solutions :: Buy online today