Oracle Training :Oracle Database 11g: Data Warehousing & Oracle Warehouse Builder (

Oracle Database 11g: Data Warehousing & Oracle Warehouse Builder

Oracle Training Overview

This Oracle 11g courseware training guide book will highlight the features within the Oracle database specifically intended to support data warehouses and data mining operations. It is presented as a two-volume set. The first volume considers data warehousing concepts and the requirements to initially configure a data warehouse installation. The second volume discusses how the Oracle Warehouse Builder (OWB) product may be used to implement and maintain both transactional and warehouse databases, and the movement of data between those two different types of installations.

Oracle Training Course Objectives

Among the specific topics considered within this course are:

Understanding star and snowflake schemas and other data warehouse physical and logical database objects.
Understanding and encouraging optimization of star queries.
Creating and maintaining materialized views to enhance ad-hoc query performance against enormous volumes of transactional data.
Manage materialized views to effectively and efficiently maintain their structure over the course of time.
Creating and maintaining dimensions and hierarchies to enhance ad-hoc query performance and support sophisticated data mining.
Performing dimensional analysis of data warehouse information and building cubes.
Configuration of the Oracle Warehouse Builder (OWB) repository and consideration of OWB capabilities.
Implementing data quality assurance using data rules and the OWB Data Profiler to evaluate data quality within transactional source databases and warehouse target databases.
Using mappings and process flows for data correction and augmentation, as well as building Extraction, Transformation and Transformation (ETT) or Loading (ETL) processes within OWB.

Oracle Training Audience

Target audience for this course are database administrators, data warehouse administrators and application developers who will be responsible for implementing applications using data warehouse technology.

Prerequisites

A mandatory prerequisite to this course is the course ORACLE DATABASE 11g: IMPLEMENT PARALLEL SQL & PARTITIONING FOR DATA WAREHOUSES.
Additionally, it is recommended that one have attended one of the following advanced courses provided for either database developers or database administrators:

ORACLE DATABASE 11g: NEW & ADVANCED FEATURES FOR DEVELOPERS

ORACLE DATABASE 11g: NEW FEATURES FOR ADMINISTRATORS

Oracle Training Course duration

5 Days

Oracle Training Course outline

DATA WAREHOUSE DESIGN & SCHEMAS

DATA WAREHOUSE CONCEPTS
ETT / ETL
DATA WAREHOUSE SCHEMAS
The EQUITIES Data Model
EQUITIES Logical Data Model
EQUITIES Physical Model
PHYSICAL DESIGN CONSIDERATIONS

CREATING MATERIALIZED VIEWS

ABOUT MATERIALIZED VIEWS
CREATE MATERIALIZED VIEWS
ENABLE QUERY REWRITE
STORAGE & TABLESPACE Clauses
PARALLEL & PARTITION BY Clauses
BUILD Clause
NESTED MATERIALIZED VIEWS

MAINTAINING MATERIALIZED VIEWS

ALTER MATERIALIZED VIEW
DROP MATERIALIZED VIEW
DATA DICTIONARY STORAGE
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
Refreshing With REFRESH_ALL_MVIEWS()
Refreshing With REFRESH()
Refreshing With REFRESH_DEPENDENT()
Using EXPLAIN_MVIEW()
DATA DICTIONARY REFRESH METADATA
USING EM

CONTROLLING THE QUERY REWRITE FACILITY

ENABLING QUERY REWRITE
VIEWING EXECUTION PLANS
Create PLAN_TABLE
Using AUTOTRACE
Using EM
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
USING EM FOR "WHAT-IF?" SCENARIOS

DIMENSIONS

WHAT ARE DIMENSIONS?

CREATING & MAINTAINING DIMENSIONS
ALTER DIMENSION
DROP DIMENSION
DIMENSION METADATA & VALIDATION

DIMENSIONAL ANALYSIS OF DATA

DATA SAMPLING
DIMENSION AGGREGATION TECHNIQUES
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
STAR_TRANSFORMATION Hint

ETL: LOADING FROM EXTERNAL TABLES

ABOUT THE EXTRACTION OPTIONS
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 ORACLE WAREHOUSE BUILDER

OWB & THE DATA WAREHOUSE
OWB CAPABILITIES
OWB ARCHITECTURE
OWB Repository
OMB*Plus

CONFIGURE ORACLE WAREHOUSE BUILDER

REPOSITORY DATABASE CONFIGURATION
MANAGE THE CONTROL CENTER SERVICES
CONFIGURE THE REPOSITORY & WORKSPACE

USING THE DESIGN CENTER & BUILDING THE INFRASTRUCTURE

LAUNCH THE DESIGN CENTER
DEFINING THE INFRASTRUCTURE
DEFINING LOCATIONS
DEFINING CONTROL CENTERS
DESIGN CENTER PREFERENCES

DEFINE MODULES & IMPORT METADATA

DEFINE A PROJECT CONTAINER
DEFINE MODULES
IMPORT METADATA

MAINTAINING THE RELATIONAL MODEL

NAVIGATING THE DATA OBJECT EDITOR
WORKING WITH A DETAIL VIEW
DEFINING A NEW OBJECT
DEPLOYING OBJECTS TO A TARGET DATABASE

DATA QUALITY ASSURANCE & MANAGEMENT

ABOUT DATA ANALYSIS PRINCIPLES
CREATE DATA PROFILES
USE THE DATA PROFILE EDITOR

DATA RULES & CORRECTION MAPPINGS

CREATING DATA RULES
APPLYING DATA RULES
CORRECTION MAPPINGS
DATA AUDITING MONITORS

ETL MAPPINGS

USING THE MAPPING EDITOR
ABOUT THE OPERATORS
WORKING WITH STAGING TABLES

PROCESS FLOWS & SCHEDULES

USING PROCESS FLOWS
USING SCHEDULES

About us
Contact us
Careers at Wintrac
Our Clients
Why Wintrac

Wintrac Inc.
16523 SW McGwire Ct.
Beaverton OR 97007

Site Map | Terms of Use | Privacy Policy