| Oracle® Database 2 Day + Data Warehousing Guide 11g Release 1 (11.1) Part Number B28314-01 |
|
|
View PDF |
| Oracle® Database 2 Day + Data Warehousing Guide 11g Release 1 (11.1) Part Number B28314-01 |
|
|
View PDF |
As someone responsible for administering, designing, and implementing a data warehouse, you are responsible for the overall operation of the Oracle data warehouse and maintaining its efficient performance.
This section contains the following topics:
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 the 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, Oracle Warehouse Builder, and SQL*Plus.
Before using this guide, you must perform the following:
Become familiar with the Oracle Database 2 Day DBA Guide.
Obtain the necessary tools described in "Tools for Administering the Data Warehouse".
Oracle Database 2 Day + Data Warehousing Guide is task-oriented. The objective is to describe why and when tasks need to be performed. Where appropriate, it describes the concepts necessary for understanding and completing the current task. This guide is not an exhaustive discussion of implementing a data warehouse on Oracle. For complete conceptual information about these features and detailed instructions for using them, see the appropriate Oracle documentation:
Oracle Database Administrator's Guide for a discussion of administrative tasks
Oracle OLAP Reference for a discussion of OLAP
Oracle Data Mining Concepts for a discussion of data mining
Also, this guide discusses using some of the features available in Oracle Enterprise Manager, but this guide does not provide exhaustive information about Enterprise Manager. For information about Enterprise Manager, see:
The Enterprise Manager online help
A data warehouse is a relational or multidimensional database that is designed for query and analysis. They are not optimized for transaction processing, which is the domain of OLTP systems. Data warehouses usually consolidate historical and transactional data derived from multiple sources. Data warehouses separate analysis workload from transaction workload and enable an organization to consolidate data from several sources.
Data in 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.
End 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. See Oracle Database Data Warehousing Guide for more details regarding multidimensional data warehouses.
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, high data throughput is the key to a successful data warehouse.
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) process
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.
This guide, the Oracle Database 2 Day + Data Warehousing Guide, illustrates the following tasks:
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.
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.
Consider performance issues and advanced ETL concepts.
Chapter 6, "Improving Loading Performance" provides conceptual information for designing complex mappings and improving ETL performance.
Deploy to target schemas and execute ETL logic.
Chapter 7, "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.
Analyze metadata dependencies.
Read and complete the tasks in Chapter 8, "Analyzing Metadata Dependencies".
Write efficient SQL.
Read and complete the tasks in Chapter 9, "SQL for Reporting and Analysis". This section describes how to write efficient SQL.
Refresh the data warehouse.
Read and complete the tasks in Chapter 10, "Refreshing a Data Warehouse".
Optimize operations.
Read and complete the tasks in Chapter 11, "Optimizing Data Warehouse Operations".
Eliminate performance bottlenecks.
Read and complete the tasks in Chapter 12, "Eliminating Performance Bottlenecks".
Review some basics of data warehouse backup and recovery.
Chapter 13, "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 14, "Securing a Data Warehouse" describes some considerations for how to create a secure data warehouse.
The intent of this guide is to enable you to quickly and efficiently create and administer an Oracle data warehouse. The following are some of the products, tools, and utilities you can use to achieve your goals with your data warehouse:
Oracle Universal Installer
Oracle Universal Installer installs your Oracle software and options. It can automatically start the Database Configuration Assistant (DBCA) to install a database.
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.
Oracle Warehouse Builder
The primary product for populating and maintaining a data warehouse, Oracle Warehouse Builder provides ETL, data quality management, and metadata management functionality in a single product.
Warehouse Builder includes a unified repository hosted on an Oracle Database. Warehouse Builder leverages Oracle Database functionality to generate code optimized for loading into and maintaining Oracle Database targets.
Database Tuning Pack
Oracle Database Tuning Pack offers a set of new technologies that automate the entire database tuning process, which significantly lowers database management costs and enhances performance and reliability. The key features of Oracle Database Tuning Pack that will be used in this guide are the SQL Access and SQL Tuning Advisors.
