Oracle Database 11g: Administer a Data Warehouse
Course: ORDWA
Duration: 5 Days
Level: II
Course Summary
This course details the tools available to the Oracle database administrator/warehouse developers to develop and administer a data warehouse consisting of staging area, object tables, and data marts (dimensional models). The course will describe the best tools and strategies to use for extracting data from OLTP systems, why you should use partitioning, how to use partitioning, when to use parallelism, and a wealth of other tools. The course will also cover data cleansing strategies. The student will also have the opportunity to actually build a data warehouse consisting of a star schema and several object tables. This exercise can and has been used as a template for future development by student DBA/developers.
« Hide The Details
Topics Covered In This Course
Introduction
- Development Tools
- Oracle SQL Developer
- Enterprise Manager
- Oracle Warehouse Builder
- Oracle Data Integrator
- Practice schemas
Data Warehouse Design: Overview
- What is a Data Warehouse?
- Overview of Data Warehouse Terms
- Characteristics of a Data Warehouse
- Comparing OLTP and Data Warehouses
- Data Warehouse Architectures
- Data Warehouse Design
- Data Warehouse objects
- Data Warehouse Schemas
- Star Transformation
- Techniques For Successfully Designing Dimension/Data Marts
Oracle Database Architecture
- Memory Structures and Recommended Settings
- Data, Temp, and Undo tablespaces
- Fixing the ORA-01555 Error
- Determine Whether Sorts Are Performed on Disk or Memory
Partitioning Basics
- Partitioned Tables and Indexes
- Partitioning Methods
- Partitioning Types
- Partition Pruning and Star queries
Parallelism Concepts
- Operations That Can Be Parallelized
- How Parallel Execution Works
- Degree of Parallelism
- Parallel execution plan
- Parallel Operations in Data Warehouses
- Parallel Query
- Parallel DDL ??? Reindexing and table creation
- Parallel DML
- Tuning Parameters for Parallel Execution
- Which Data Warehouse Objects That Should Be Parallelized
ETL: Extraction and Transportation
- Extraction Methods
- Capturing Data With Change Data Capture
- Sources and Modes of Change Data Capture
- Publish and Subscribe Model: The Publisher and the Subscriber
- Synchronous and Asynchronous CDC
- Asynchronous AutoLog Mode and Asynchronous HotLog Mode
- Transportation in a Data Warehouse
- Transportable Tablespaces
ETL: Loading
- Loading Mechanisms
- Applications of External Tables
- Defining external tables with SQL*Loader
- Populating external tables with Data Pump
- Other Loading Methods
ETL: Transformation
- Data transformation
- Transformation Mechanisms
- Transformation Using SQL
- Table Functions
- DML error logging
- Merge statement
- Insert All statement
- Grouping sets, cube, and rollup options
- Data cleansing strategies
Materialized Views
- The Need for Summary Management
- Types of Materialized Views
- Using Materialized Views for Summary Management
- Fast vs Complete Refresh
- Using Refresh Groups
- Other Materialized View Refresh Options
- Materialized View Logs
- When not to use materialized views
Working With Dimensions, Fact Tables, and Cubes
- What Are Dimensions, Fact Tables, Cubes, Star Schemas, and Snow Flaked Star Schema
- Creating Dimensions and Hierarchies
- Dimensions and Privileges
- Dimension Restrictions
- Verifying Relationships in a Dimension
- Dimension Invalidation
Query Rewrite
- Query Rewrite: Overview
- What Can be Rewritten
- Conditions Required for Oracle to Rewrite a Query
- Query Rewrite guidelines
- Setting Initialization Parameters for Query Rewrite
- Query Rewrite Methods
- Partition Change Tracking (PCT) and Query Rewrite
- Query Rewrite Enhancement to Support Queries Containing Inline Views
Tuning and Performance Options
- Types of indexes
- Identifying Long Running Queries
- Compression and percent free
- End user tuning methodologies
- Benefit of ANSI SQL 99 in tuning queries
Other
- Global Temporary Tables
- Referential Integrity and Data Marts
- How a Data Warehouse Can Be the One Source of Truth
- Oracle Text
- Analytic Functions
What You Can Expect
At the end of this course the student should understand the data warehouse stages, how to populate the data warehouse, and manage its day-to-day operation. In addition, the student will have a template that can be used for developing actual data marts.
Who Should Take This Course
Data warehouse DBA's, data administrators, and developers
Recommended Prerequisites
Students should have a basic understanding of OLTP and OLAP Database Design, and experience with Oracle's products.
Training Style
Instructor led with 60% lecture and 40% lab.
« Hide The Details
Related Courses
| Code |
Course Title |
Duration |
Level |
|
OWPDP |
Oracle Physical Data Warehouse Design and Implementation |
4 Days |
I |
Details |
OWHDWD |
ORACLE DATA WAREHOUSE DESIGN |
5 Days |
I |
Details |
ORDBD |
Oracle OLTP and OLAP Database Design |
5 Days |
I |
Details |
ODI11G |
Oracle Data Integrator 11g |
4 Days |
I |
Details |
OBIEE |
Oracle BI Enterprise Edition (OBIEE) 11g: Reports and Dashboards |
4 Days |
I |
Details |
DWHFUN |
Data Warehousing Fundamentals |
3 Days |
I |
Details |
OBIBC |
Oracle BI Enterprise Edition (OBIEE) 11g: Boot Camp |
5 Days |
I |
Details |
DWHWPT |
DB2 Data Warehousing Performance And Tuning Workshop |
4 Days |
II |
Details |
OBIBR |
Oracle BI Enterprise Edition (OBIEE) 11g: Building Repositories |
5 Days |
II |
Details |
OWHPSQ |
Advanced PL/SQL (and SQL) for Data Warehousing ETL |
4 Days |
III |
Details |
OR11DWB |
Oracle Database 11g: Data Warehousing and Oracle Warehouse Builder |
5 Days |
III |
Details |
Every student attending a Verhoef Training class will receive
a certificate good for $100 toward their next public class taken
within a year.
You can also buy "Verhoef Vouchers" to get a discounted rate for a
single student in any of our public or web-based classes.
Contact your account manager or our sales office for details.
|
