Oracle Database 10g: Implement and Administer a Data Warehouse

What you will learn

This course is intended for database administrators, system administrators, and database application developers who design, maintain, and use data warehouses. Before attending this course, you should be familiar with relational database concepts, basic data warehouse theory, Oracle server concepts including application and server tuning, and the operating system environment under which you are running the Oracle Database Server.



Audience
 Data Warehouse Administrator
Database Administrators
Database Designers

Prerequisites
 Knowledge of Database Administration
Oracle Database 10g: 2 Day DBA Release 2
Oracle Database 10g: New Features for Administrators Release 2
Oracle Database 10g: Administration Workshop I
Oracle Database 10g: SQL Tuning Workshop
Oracle Database 10g: Administration Workshop II

Course Objectives
 Understand and describe the features inherent in an Oracle 10g Data Warehouse
Demonstrate how to implement parallel operations
Demonstrate extraction, transformation, and loading processes
Describe and demonstrate effective usage of Materialized Views in a data warehouse
Describe star, snowflake and 3NF schemas
Implement an effective partitioning schema for your data warehouse tables
Develop an effective backup and recovery strategy
Describe security challenges in a data warehouse

Course Topics
Data Warehouse Design
 Logical Versus Physical Design in Data Warehouses
Data Warehousing Schemas
Data Warehousing Objects
Physical Design in Data Warehouses
Hardware and I/O Considerations

Data Warehousing Schemas
 Star Schema Model
Snowflake Schema Model
Tuning Star Queries
Star Transformation Hints
Star Transformation Hints
Static Partition Pruning And Star Query
Dynamic Partition Pruning And Star Query
Dynamic Partition Pruning Determination

Partitioning Basics
 Supported partition types
Rolling window operations
Partition pruning
Partitioning Using a Template

Extraction, Transportation, and Loading (Extraction and Transportation)
 ETL Tools
Extraction Methods
Logical Extraction Methods
Physical Extraction Methods
Change Data Capture
Transportation Using Transportable Tablespaces
Loading Mechanisms

ETL-Loading
 Load a formatted flat file into an existing table with SQL*Loader
Performing basic transformations while loading with SQL*Loader
External tables using oracle_loader driver
External tables using oracle_datapump driver
Loading data with OCI and Direct-path APIs

ETL-Transformation
 Transformation Using SQL
Transformation Using PL/SQL
Transformation Using Table Functions
Error Logging and Handling Mechanisms

Parallelism Concepts
 Parallel Operations
Degree Of Parallelism
Parallel Execution Plan
Operations That Can Be Parallelized
The PARALLEL Clause
Parallel Query
Parallel DDL
Performance Benefits of Parallel DML

Parallel Operations in Data Warehouses
 Automated Parallel Query Tuning
Data Distribution And V$PQ_TQSTAT
Object Statistics And V$PQ_TQSTAT

Materialized Views
 Materialized Views Overview
Types Of Materialized Views
Nested Materialized Views
Partitioned Materialized Views
Refresh Methods and Modes
Altering/Dropping Materialized Views
Dimensions and Hierarchies
Dimensions and the Data Dictionary

Dimensions
 Creating dimensions
Validate dimensions data
View dimension definition information
Distinguish between dimensions and constraints

Materialized Views Refresh
 Refresh Methods and Modes
Identifying Dependent MVs
Conditions For Complete/Fast Refreshes
Materialized View Logs
Parallel Refreshes

Query Rewrite
 What Can Be Rewritten?
Enabling Query Rewrite
Join Compatibility Checks
Common Joins
PCT and Query Rewrite
Query Rewrite using Multiple MVs
Index Materialized Views

SQL Access Advisor
 DBMS_OLAP Advisory Procedures
Workload Management and Sources
Recommendations
Workload Filters and Attribute Types
Tuning of Manually Created Materialized Views
Fast-Refreshable Materialized Views
RECOMMEND_MVIEW_STRATEGY procedure

Data Warehousing System Management
 Statistics Collection
Resumable Sessions
Backup and Recovery
Table Compression
Security