Company Profile Newsletter IT Training Business Training Coaching Consulting Schedule Accreditations Contact Us
Popular Topics:
SharePoint Training Crystal Reports Training Photoshop Training Oracle Training Microsoft Excel Training
Project Management Training Finance Training Effective Management Training
Oracle Database 11g: SQL Fundamentals I 
Print PageOracle Database 10g: Implement and Administer Data Warehouses I
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.
- Cost: Price on application
- Duration: 3 days
- This course is not available as part of our public schedule but can be provided on a customised client specific basis.
Prerequisites:
Oracle Database 10g: Implement Parallel SQL & Partitioning For Data Warehouses and Oracle Database 10g: SQL Tuning
DATA WAREHOUSE DESIGN & SCHEMAS:
• DATA WAREHOUSE CONCEPTS
• ETT / ETL
• DATA WAREHOUSE SCHEMAS
• The EQUITIES Data Model
• PHYSICAL DESIGN CONSIDERATIONS
• 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
• 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
• 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
• 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
• 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
• 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()
• 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
• 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
• 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: