| Course Code: |
RT 270 |
| Course Abstract: |
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.
This course is also called “Building Oracle9i Data Warehouses”. |
| Audience: |
This course is designed for Database administrators, data warehouse administrators and application developers who will be responsible for implementing and using data warehouse technology |
| Duration: |
5 days |
| Learning Outcomes: |
Upon completion of this course, the participant will be able to:
> Apply star schemas and other data warehouse objects
> Create and maintain materialized views to enhance ad-hoc query performance
> Create and maintain dimensions to enhance ad-hoc query performance
> Perform dimensional analysis of data warehouse information
> Use the Summary Advisor tool for data warehouse design recommendations |
| Course Topics: |
About Data Warehousing
Understanding Warehouse Concepts & Terms
Contrast OLTP & Warehouse Databases
Using Materialized Views
Enable Materialized Views & Query Rewrite
Create The Materialized View
Maintaining Materialized Views
Maintenance Options
About The Types Of Views
Altering And Dropping Views
Data Dictionary Storage
Refreshing Materialized Views
Specifying The Default Refresh Options
Performing A Refresh On Demand
Implementing Fast Refresh
Monitor Query Rewrite With Explain Plan
Generating The Execution Plan
Viewing The Execution Plan
Interpreting The Execution Plan
Controlling The Query Rewrite Facility
Query Rewrite Optimizer Hints
Utilizing Constraints With Query Rewrite
Query Rewrite Integrity Levels
Query Rewrite Influences
Dimensions
Creating & Maintaining Dimensions
Data Dictionary Storage
Dimension System-Supplied Packages
The Summary Advisor Tool
The DBMS_OLAP() Package
Incorporating Workload Statistics
OEM Summary Advisor Wizard
Dimensional Analysis Of Data
Data Sampling Techniques
Aggregation Techniques
Building The Data Warehouse Cube
An Introduction To The Analytic Functions
Ranking Functions
Understanding Function Execution
Incorporating Bitmap Indexes
Star Queries & The Optimizer
A Star Transformation Scenario
Encouraging Star Transformation
ETT Features (External Tables)
Creating & Accessing External Tables
Performance Considerations
Viewing & Altering Properties Of External Tables
ETT Features (Table Functions)
Implementing A Pipelined Table Function |
| Prerequisites: |
Oracle9i SQL (RT 262)
Oracle9i PL/SQL Language (RT 258)
Oracle9i Advanced SQL (RT 275)
Additional Recommended Prerequisites
In addition, the following courses are strongly recommended, although not mandatory, prerequisites:
Oracle9i Architecture For Developers OR Oracle9i Database: Administration – DBA I
Oracle9i SQL Tuning
Oracle9i New & Advanced Features for Developers OR Oracle9i New & Advanced Features for DBAs |