Oracle® Database 2 Day + Data Warehousing Guide
Oracle® Database 2 Day + Data Warehousing Guide
Introduction to Data Warehousing
“Introduction to Data Warehousing”
About This Guide
“About This Guide”
Before Using This Guide
“Before Using This Guide”
What This Guide Is Not
“What This Guide Is Not”
What is a Data Warehouse?
“What is a Data Warehouse?”
The Key Characteristics of a Data Warehouse
“The Key Characteristics of a Data Warehouse”
Common Oracle Data Warehousing Tasks
“Common Oracle Data Warehousing Tasks”
Tasks Illustrated in this Guide
“Tasks Illustrated in this Guide”
Tools for Administering the Data Warehouse
“Tools for Administering the Data Warehouse”
Setting Up Your Data Warehouse System
“Setting Up Your Data Warehouse System”
General Steps for Setting up a Data Warehouse System
“General Steps for Setting up a Data Warehouse System”
To set up a data warehouse system, complete the following steps:
“To set up a data warehouse system, complete the following steps:”
Preparing the Environment
“Preparing the Environment”
Balanced Hardware Configuration
“Balanced Hardware Configuration”
How Many CPUs and What Clock Speed Do I Need?
“How Many CPUs and What Clock Speed Do I Need?”
How Much Memory Do I Need?
“How Much Memory Do I Need?”
How Many Disks Do I Need?
“How Many Disks Do I Need?”
How Do I Determine Sufficient I/O Bandwidth?
“How Do I Determine Sufficient I/O Bandwidth?”
Throughput Performance Conversion
“Throughput Performance Conversion”
About Automatic Storage Management (ASM)
“About Automatic Storage Management (ASM)”
Verifying the Data Warehouse Hardware Configuration
“Verifying the Data Warehouse Hardware Configuration”
About the dd Utility
“About the dd Utility”
Example: Using the dd Utility
“Example: Using the dd Utility”
To use the dd utility:
“To use the dd utility:”
About the Orion Utility
“About the Orion Utility”
To download Orion software, point your browser to the following:
“To download Orion software, point your browser to the following:”
Example: Using the Orion Utility
“Example: Using the Orion Utility”
Setting Up a Database for a Data Warehouse
“Setting Up a Database for a Data Warehouse”
How Should I Set the Memory Management Parameters?
“How Should I Set the Memory Management Parameters?”
Example: Setting an Initialization Parameter
“Example: Setting an Initialization Parameter”
What Other Initialization Parameter Settings Are Important?
“What Other Initialization Parameter Settings Are Important?”
Accessing Oracle Warehouse Builder
“Accessing Oracle Warehouse Builder”
To enable Warehouse Builder, complete the following steps:
“To enable Warehouse Builder, complete the following steps:”
Installing the Oracle Warehouse Builder Demonstration
“Installing the Oracle Warehouse Builder Demonstration”
To perform the Warehouse Builder exercises presented in this guide, complete the following steps:
“To perform the Warehouse Builder exercises presented in this guide, complete the following steps:”
Identifying Data Sources and Importing Metadata
“Identifying Data Sources and Importing Metadata”
Overview of Data Sources
“Overview of Data Sources”
General Steps for Importing Metadata from Sources
“General Steps for Importing Metadata from Sources”
To import metadata, complete the following steps:
“To import metadata, complete the following steps:”
About Workspaces, Projects, and other Devices in Warehouse Builder
“About Workspaces, Projects, and other Devices in Warehouse Builder”
Example: Importing Metadata from Flat Files
“Example: Importing Metadata from Flat Files”
To import metadata from flat files:
“To import metadata from flat files:”
Specifying Locations for the Flat Files
“Specifying Locations for the Flat Files”
Creating Modules in the Project
“Creating Modules in the Project”
Launching the Import Metadata Wizard
“Launching the Import Metadata Wizard”
Using the Flat File Sample Wizard
“Using the Flat File Sample Wizard”
Importing the Flat File Data
“Importing the Flat File Data”
Defining Warehouses in Oracle Warehouse Builder
“Defining Warehouses in Oracle Warehouse Builder”
General Steps for Defining a Relational Target Warehouse
“General Steps for Defining a Relational Target Warehouse”
To define a relational target warehouse, complete the following steps:
“To define a relational target warehouse, complete the following steps:”
Identifying the Warehouse Target Schema
“Identifying the Warehouse Target Schema”
To designate a schema as the data warehouse target schema:
“To designate a schema as the data warehouse target schema:”
About Flat File Sources in Warehouse Builder
“About Flat File Sources in Warehouse Builder”
Exercise: Adding External Tables to the Target Module
“Exercise: Adding External Tables to the Target Module”
To add external tables to the target warehouse module:
“To add external tables to the target warehouse module:”
About Dimensions
“About Dimensions”
Exercise: Understanding Dimensions
“Exercise: Understanding Dimensions”
To become familiar with the dimensions:
“To become familiar with the dimensions:”
About Levels
“About Levels”
Defining Level Attributes
“Defining Level Attributes”
Defining Hierarchies
“Defining Hierarchies”
Dimension Roles
“Dimension Roles”
Level Relationships
“Level Relationships”
Dimension Example
“Dimension Example”
Products Dimension Level Details
“Products Dimension Level Details”
Control Rows
“Control Rows”
Control Rows Created for the Products Dimension
“Control Rows Created for the Products Dimension”
Implementing a Dimension
“Implementing a Dimension”
Star Schema
“Star Schema”
Binding
“Binding”
Auto Binding
“Auto Binding”
To perform auto binding:
“To perform auto binding:”
Manual Binding
“Manual Binding”
To perform manual binding for a dimension:
“To perform manual binding for a dimension:”
About Cubes
“About Cubes”
Defining a Cube
“Defining a Cube”
Cube Measures
“Cube Measures”
Cube Dimensionality
“Cube Dimensionality”
To define a dimension reference, specify the following:
“To define a dimension reference, specify the following:”
Cube Example
“Cube Example”
Dimensionality of the Sales Cube
“Dimensionality of the Sales Cube”
Implementing a Cube
“Implementing a Cube”
Relational Implementation of a Cube
“Relational Implementation of a Cube”
To implement a cube:
“To implement a cube:”
Binding
“Binding”
Auto Binding
“Auto Binding”
Manual Binding
“Manual Binding”
To perform manual binding for a cube:
“To perform manual binding for a cube:”
Defining ETL Logic
“Defining ETL Logic”
About Mappings and Operators
“About Mappings and Operators”
Instructions for Defining Mappings
“Instructions for Defining Mappings”
To define a mapping, refer to the following sections:
“To define a mapping, refer to the following sections:”
Subsequent Steps
“Subsequent Steps”
Creating a Mapping
“Creating a Mapping”
To create a mapping:
“To create a mapping:”
To open a previously created mapping:
“To open a previously created mapping:”
Types of Operators
“Types of Operators”
Adding Operators
“Adding Operators”
To add an operator to a mapping:
“To add an operator to a mapping:”
Adding Operators that Bind to Workspace Objects
“Adding Operators that Bind to Workspace Objects”
Create Unbound Operator with No Attributes
“Create Unbound Operator with No Attributes”
Select from Existing Workspace Object and Bind
“Select from Existing Workspace Object and Bind”
Editing Operators
“Editing Operators”
To edit an operator, group, or attribute:
“To edit an operator, group, or attribute:”
Connecting Operators
“Connecting Operators”
Connecting Attributes
“Connecting Attributes”
To connect attributes:
“To connect attributes:”
Connecting Groups
“Connecting Groups”
Example: Using the Mapping Editor to Create Staging Area Tables
“Example: Using the Mapping Editor to Create Staging Area Tables”
To map a source table to a staging table:
“To map a source table to a staging table:”
Setting Mapping Properties
“Setting Mapping Properties”
Target Load Order
“Target Load Order”
To specify the loading order for multiple targets:
“To specify the loading order for multiple targets:”
Reset to Default
“Reset to Default”
Setting Operator, Group, and Attribute Properties
“Setting Operator, Group, and Attribute Properties”
Synchronizing Operators and Workspace Objects
“Synchronizing Operators and Workspace Objects”
Synchronizing An Operator
“Synchronizing An Operator”
To synchronize an operator, complete the following steps:
“To synchronize an operator, complete the following steps:”
Synchronizing From a Workspace Object to an Operator
“Synchronizing From a Workspace Object to an Operator”
Synchronizing Operators based on Workspace Objects
“Synchronizing Operators based on Workspace Objects”
Operators Synchronized with Workspace Objects
“Operators Synchronized with Workspace Objects”
Synchronizing from an Operator to a Workspace Object
“Synchronizing from an Operator to a Workspace Object”
Deploying to Target Schemas and Executing ETL Logic
“Deploying to Target Schemas and Executing ETL Logic”
About Deployment
“About Deployment”
What is a Control Center?
“What is a Control Center?”
To create a new Control Center:
“To create a new Control Center:”
To make a Control Center active:
“To make a Control Center active:”
Configuring the Physical Details of Deployment
“Configuring the Physical Details of Deployment”
Deployment Actions
“Deployment Actions”
The Deployment Process
“The Deployment Process”
Deploying Objects
“Deploying Objects”
To deploy from the Project Explorer:
“To deploy from the Project Explorer:”
To open the Control Center Manager:
“To open the Control Center Manager:”
Starting ETL Jobs
“Starting ETL Jobs”
To start ETL from the Project Explorer:
“To start ETL from the Project Explorer:”
Viewing the Data
“Viewing the Data”
To view the data:
“To view the data:”
SQL for Reporting and Analysis
“SQL for Reporting and Analysis”
Use of SQL Analytic Capabilities to Answer Business Queries
“Use of SQL Analytic Capabilities to Answer Business Queries”
How to Add Totals to Reports Using the ROLLUP Function
“How to Add Totals to Reports Using the ROLLUP Function”
When to Use the ROLLUP Function
“When to Use the ROLLUP Function”
Example: Using the ROLLUP Function
“Example: Using the ROLLUP Function”
How to Separate Totals at Different Levels Using the CUBE Function
“How to Separate Totals at Different Levels Using the CUBE Function”
When to Use the CUBE Function
“When to Use the CUBE Function”
Example: Using the CUBE Function
“Example: Using the CUBE Function”
To use the CUBE function:
“To use the CUBE function:”
How to Add Subtotals Using the GROUPING Function
“How to Add Subtotals Using the GROUPING Function”
When to Use the GROUPING Function
“When to Use the GROUPING Function”
Example: Using the GROUPING Function
“Example: Using the GROUPING Function”
To use the GROUPING function:
“To use the GROUPING function:”
How to Combine Aggregates Using the GROUPING SETS Function
“How to Combine Aggregates Using the GROUPING SETS Function”
When to Use the GROUPING SETS Function
“When to Use the GROUPING SETS Function”
Example: Using the GROUPING SETS Function
“Example: Using the GROUPING SETS Function”
To use the GROUPING SETS function:
“To use the GROUPING SETS function:”
How to Calculate Rankings Using the RANK Function
“How to Calculate Rankings Using the RANK Function”
When to Use the RANK Function
“When to Use the RANK Function”
Example: Using the RANK Function
“Example: Using the RANK Function”
To use the RANK function:
“To use the RANK function:”
How to Calculate Relative Contributions to a Total
“How to Calculate Relative Contributions to a Total”
Example: Calculating Relative Contributions to a Total
“Example: Calculating Relative Contributions to a Total”
To calculate relative contributions to a total:
“To calculate relative contributions to a total:”
How to Perform Interrow Calculations with Window Functions
“How to Perform Interrow Calculations with Window Functions”
Example: Performing Interrow Calculations
“Example: Performing Interrow Calculations”
To perform interrow calculations:
“To perform interrow calculations:”
How to Calculate a Moving Average Using a Window Function
“How to Calculate a Moving Average Using a Window Function”
Example: Calculating a Moving Average
“Example: Calculating a Moving Average”
Use of Partition Outer Join to Handle Sparse Data
“Use of Partition Outer Join to Handle Sparse Data”
When to Use Partition Outer Join
“When to Use Partition Outer Join”
Example: Using Partition Outer Join
“Example: Using Partition Outer Join”
Use of the WITH Clause to Simplify Business Queries
“Use of the WITH Clause to Simplify Business Queries”
When to Use the WITH Clause
“When to Use the WITH Clause”
Example: Using the WITH Clause
“Example: Using the WITH Clause”
Refreshing a Data Warehouse
“Refreshing a Data Warehouse”
About Refreshing Your Data Warehouse
“About Refreshing Your Data Warehouse”
Example: Refreshing Your Data Warehouse
“Example: Refreshing Your Data Warehouse”
To refresh a materialized view:
“To refresh a materialized view:”
Using Rolling Windows to Offload Data
“Using Rolling Windows to Offload Data”
Example: Using a Rolling Window
“Example: Using a Rolling Window”
To use a rolling window:
“To use a rolling window:”
Optimizing Data Warehouse Operations
“Optimizing Data Warehouse Operations”
Avoiding System Overload
“Avoiding System Overload”
Monitoring System Performance
“Monitoring System Performance”
Monitoring Parallel Execution Performance
“Monitoring Parallel Execution Performance”
To monitor parallel execution performance:
“To monitor parallel execution performance:”
Monitoring I/O
“Monitoring I/O”
To monitor I/O performance:
“To monitor I/O performance:”
Using Database Resource Manager
“Using Database Resource Manager”
Optimizing the Use of Indexes and Materialized Views
“Optimizing the Use of Indexes and Materialized Views”
Example: Optimizing Indexes and Materialized Views Using the SQL Access Advisor
“Example: Optimizing Indexes and Materialized Views Using the SQL Access Advisor”
To optimize an index and materialized view:
“To optimize an index and materialized view:”
Optimizing Storage Requirements
“Optimizing Storage Requirements”
Using Data Compression to Improve Storage
“Using Data Compression to Improve Storage”
To use data compression:
“To use data compression:”
Eliminating Performance Bottlenecks
“Eliminating Performance Bottlenecks”
Verifying That SQL Runs Efficiently
“Verifying That SQL Runs Efficiently”
Analyzing Optimizer Statistics
“Analyzing Optimizer Statistics”
Analyzing an Execution Plan
“Analyzing an Execution Plan”
Example: Analyzing Explain Plan Output
“Example: Analyzing Explain Plan Output”
To analyze EXPLAIN PLAN output:
“To analyze EXPLAIN PLAN output:”
Using Hints to Improve Data Warehouse Performance
“Using Hints to Improve Data Warehouse Performance”
Example: Using Hints to Improve Data Warehouse Performance
“Example: Using Hints to Improve Data Warehouse Performance”
To use a hint to improve data warehouse performance:
“To use a hint to improve data warehouse performance:”
Using Advisors to Verify SQL Performance
“Using Advisors to Verify SQL Performance”
Example: Using the SQL Tuning Advisor to Verify SQL Performance
“Example: Using the SQL Tuning Advisor to Verify SQL Performance”
Improving Performance by Minimizing Resource Consumption
“Improving Performance by Minimizing Resource Consumption”
Improving Performance: Partitioning
“Improving Performance: Partitioning”
Improving Performance: Partition Pruning
“Improving Performance: Partition Pruning”
Improving Performance: Partitionwise Joins
“Improving Performance: Partitionwise Joins”
Example: Evaluating Partitioning with the SQL Access Advisor
“Example: Evaluating Partitioning with the SQL Access Advisor”
Improving Performance: Query Rewrite and Materialized Views
“Improving Performance: Query Rewrite and Materialized Views”
Improving Performance: Indexes
“Improving Performance: Indexes”
Improving Performance: Columnar Compression
“Improving Performance: Columnar Compression”
Improving Performance: DBMS_COMPRESSION Package
“Improving Performance: DBMS_COMPRESSION Package”
Improving Performance: COMPRESS Clause of CREATE TABLE and ALTER TABLE
“Improving Performance: COMPRESS Clause of CREATE TABLE and ALTER TABLE”
Using Resources Optimally
“Using Resources Optimally”
Optimizing Performance with Parallel Execution
“Optimizing Performance with Parallel Execution”
How Parallel Execution Works
“How Parallel Execution Works”
Setting the Degree of Parallelism
“Setting the Degree of Parallelism”
Example: Setting the Degree of Parallelism
“Example: Setting the Degree of Parallelism”
To set the degree of parallelism:
“To set the degree of parallelism:”
About Wait Events
“About Wait Events”
Backing up and Recovering a Data Warehouse
“Backing up and Recovering a Data Warehouse”
How Should I Handle Backup and Recovery for a Data Warehouse?
“How Should I Handle Backup and Recovery for a Data Warehouse?”
Strategies and Best Practices for Backup and Recovery
“Strategies and Best Practices for Backup and Recovery”
Best Practice A: Use ARCHIVELOG Mode
“Best Practice A: Use ARCHIVELOG Mode”
Is Downtime Acceptable?
“Is Downtime Acceptable?”
Best Practice B: Use RMAN
“Best Practice B: Use RMAN”
Best Practice C: Use Read-Only Tablespaces
“Best Practice C: Use Read-Only Tablespaces”
Best Practice D: Plan for NOLOGGING Operations
“Best Practice D: Plan for NOLOGGING Operations”
Extraction, Transformation, and Loading
“Extraction, Transformation, and Loading”
The ETL Strategy and NOLOGGING Operations
“The ETL Strategy and NOLOGGING Operations”
Sizing the Block Change Tracking File
“Sizing the Block Change Tracking File”
Incremental Backup
“Incremental Backup”
The Incremental Approach
“The Incremental Approach”
Best Practice E: Not All Tablespaces are Equally Important
“Best Practice E: Not All Tablespaces are Equally Important”
Securing a Data Warehouse
“Securing a Data Warehouse”
Overview of Data Warehouse Security
“Overview of Data Warehouse Security”
Why is Security Necessary for a Data Warehouse?
“Why is Security Necessary for a Data Warehouse?”
Using Roles and Privileges for Data Warehouse Security
“Using Roles and Privileges for Data Warehouse Security”
Using Virtual Private Database in Data Warehouses
“Using Virtual Private Database in Data Warehouses”
How Virtual Private Database Works
“How Virtual Private Database Works”
Overview of Oracle Label Security
“Overview of Oracle Label Security”
How Oracle Label Security Works
“How Oracle Label Security Works”
How Data Warehouses Benefit from Labels
“How Data Warehouses Benefit from Labels”
Overview of Fine-Grained Auditing in Data Warehouses
“Overview of Fine-Grained Auditing in Data Warehouses”
Overview of Transparent Data Encryption in Data Warehouses
“Overview of Transparent Data Encryption in Data Warehouses”