Skip Headers
Oracle® Database 2 Day + Data Warehousing Guide
11g Release 2 (11.2)

Part Number E10578-02
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

1 Introduction to Data Warehousing

As the person responsible for administering, designing, and implementing a data warehouse, you also oversee the overall operation of Oracle data warehousing and maintenance of its efficient performance within your organization.

This section contains the following topics:

About This Guide

Oracle Database 2 Day + Data Warehousing Guide teaches you how to perform common day-to-day tasks necessary to implement and administer a data warehouse. The goal of this guide is to introduce you to the data warehousing solutions available in Oracle Database. This guide teaches you how to perform common administration and design tasks needed to keep the data warehouse operational, including how to perform basic performance monitoring tasks.

The primary interfaces used in this guide are Oracle Enterprise Manager (EM), Oracle Warehouse Builder (OWB), and SQL*Plus.

Before Using This Guide

Before using this guide, you should perform the following preparations:

What This Guide Is Not

Oracle Database 2 Day + Data Warehousing Guide is not an exhaustive discussion of implementing a data warehouse on Oracle. The objective for this guide is to describe why and when tasks must be performed in a task-oriented way. Where appropriate, it describes the concepts necessary for understanding and completing the current task.

For complete conceptual information about these features and detailed instructions for using them, see the appropriate Oracle documentation as follows:

What is a Data Warehouse?

A data warehouse is a relational or multidimensional database that is designed for query and analysis. Data warehouses are not optimized for transaction processing, which is the domain of OLTP systems. Data warehouses usually consolidate historical and analytic data derived from multiple sources. Data warehouses separate analysis workload from transaction workload and enable an organization to consolidate data from several sources.

A data warehouse usually stores many months or years of data to support historical analysis. The data in a data warehouse is typically loaded through an extraction, transformation, and loading (ETL) process from one or more data sources such as OLTP applications, mainframe applications, or external data providers.

Users of the data warehouse perform data analyses that are often time-related. Examples include consolidation of last year's sales figures, inventory analysis, and profit by product and by customer. More sophisticated analyses include trend analyses and data mining, which use existing data to forecast trends or predict futures. The data warehouse typically provides the foundation for a business intelligence environment.

This guide covers relational implementations, including star schemas.

The Key Characteristics of a Data Warehouse

The key characteristics of a data warehouse are as follows:

  • Some data is denormalized for simplification and to improve performance

  • Large amounts of historical data are used

  • Queries often retrieve large amounts of data

  • Both planned and ad hoc queries are common

  • The data load is controlled

In general, fast query performance with high data throughput is the key to a successful data warehouse.

Common Oracle Data Warehousing Tasks

As an Oracle data warehousing administrator or designer, you can expect to be involved in the following tasks:

  • Configuring an Oracle database for use as a data warehouse

  • Designing data warehouses

  • Performing upgrades of the database and software to new release levels

  • Managing schema objects, such as tables, indexes, and materialized views

  • Managing users and security

  • Developing routines used for the extraction, transformation, and loading (ETL) processes

  • Creating reports based on the data in the data warehouse

  • Backing up the data warehouse and performing recovery when necessary

  • Monitoring the data warehouse's performance and taking preventive or corrective action as required

In a small-to-midsize data warehouse environment, you might be the sole person performing these tasks. In large, enterprise environments, the job is often divided among several DBAs and designers, each with their own specialty, such as database security or database tuning.

Tasks Illustrated in this Guide

This guide illustrates the following tasks:

  1. Configure an Oracle database for use as a data warehouse.

    Refer to Chapter 2, "Setting Up Your Data Warehouse System". This section also includes instructions on how to access a demonstration that is referenced in exercises throughout this guide.

  2. Take the initial steps in consolidating data.

    Follow the instructions in Chapter 3, "Identifying Data Sources and Importing Metadata".

  3. Begin to define the target objects in the warehouse.

    Chapter 4, "Defining Warehouses in Oracle Warehouse Builder" describes how to define external tables, dimensions, and cubes for the target warehouse.

  4. Define strategies for extracting, transforming, and loading data into the target.

    Chapter 5, "Defining ETL Logic" describes how to define ETL logic to extract data from the source you identified in step 2, transform the data, and then load it into the target you designed in step 3.

  5. Deploy to target schemas and execute ETL logic.

    Chapter 6, "Deploying to Target Schemas and Executing ETL Logic" describes how to prepare a target schema with code from mappings and also describes how to subsequently execute that code.

  6. Write efficient SQL.

    Read and complete the tasks in Chapter 7, "SQL for Reporting and Analysis". This section describes how to write efficient SQL.

  7. Refresh the data warehouse.

    Read and complete the tasks in Chapter 8, "Refreshing a Data Warehouse".

  8. Optimize operations.

    Read and complete the tasks in Chapter 9, "Optimizing Data Warehouse Operations".

  9. Eliminate performance bottlenecks.

    Read and complete the tasks in Chapter 10, "Eliminating Performance Bottlenecks".

  10. Review some basics of data warehouse backup and recovery.

    Chapter 11, "Backing up and Recovering a Data Warehouse" describes some considerations for how to back up and recover a data warehouse.

  11. Review some basics of data warehouse security.

    Chapter 12, "Securing a Data Warehouse" describes some considerations for how to create a secure data warehouse.

Tools for Administering the Data Warehouse

The procedures in this guide refer to and sometimes require the following products, tools, and utilities to achieve your goals with your data warehouse:

Note:

OUI and OWB listed in this section are included with Oracle Database. Some data quality features of OWB require addtionial licensing. Oracle Tuning Pack requires additional licensing.