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
What Is a Data Warehouse?
Tasks Illustrated in This Guide
Tools for Administering the Data Warehouse

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 (Enterprise Manager), Oracle Warehouse Builder (Warehouse Builder), and SQL*Plus.

Before Using This Guide

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

Become familiar with using Oracle Enterprise Manager (EM) to administer Oracle Database, as described in Oracle Database 2 Day DBA.
Obtain the necessary tools described in "Tools for Administering the Data Warehouse".

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:

Oracle Database Data Warehousing Guide
Oracle Warehouse Builder Sources and Targets Guide
Oracle Database Administrator's Guide for a discussion of administrative tasks
Oracle Data Mining Concepts for a discussion of data mining

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 O racle 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 data warehousing software to new releases
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:

Configure an Oracle database for use as a data warehouse.

See 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.
Take the initial steps in consolidating data.

Follow the instructions in Chapter 3, "Identifying Data Sources and Importing Metadata".
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.
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.
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.
Write efficient SQL.

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

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

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

Read and complete the tasks in Chapter 10, "Eliminating Performance Bottlenecks".
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.
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:

Oracle Universal Installer

Oracle Universal Installer (OUI) installs your Oracle software and options. It can automatically start the Database Configuration Assistant (DBCA) to install a database. OUI and DBCA are included with Oracle Database. See Oracle Universal Installer User's Guide for Windows and UNIX for optional information.
Oracle Enterprise Manager

The primary tool for managing your database is Oracle Enterprise Manager, a Web-based interface. After you have installed the Oracle software, created or upgraded a database, and configured the network, you can use Oracle Enterprise Manager for managing your database. In addition, Oracle Enterprise Manager also provides an interface for performance advisors and for Oracle utilities such as SQL*Loader and Recovery Manager. See Oracle Enterprise Manager Administrator's Guide if you want more detailed information than what is discussed in this guide.
Oracle Warehouse Builder

The primary product for populating and maintaining a data warehouse, Oracle Warehouse Builder provides ETL, data quality management, and metadata management in a single product.

Warehouse Builder includes a unified repository hosted on Oracle Database. Warehouse Builder leverages Oracle Database functionality to generate code that is optimized for loading into and maintaining Oracle Database targets. See Oracle Warehouse Builder Data Modeling, ETL, and Data Quality Guide for more details and comprehensive procedures.
Oracle Tuning Pack

Oracle Tuning Pack offers a set of technologies that automate the entire database tuning process, which significantly lowers database management costs and enhances performance and reliability. The key features of Oracle Tuning Pack that will be used in this guide are the SQL Access and SQL Tuning Advisors. See Oracle Database Licensing Information and Oracle Database Performance Tuning Guide.

Note:

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