构建Oracle 11gR2数据仓库基础指南

需积分: 10 133 浏览量更新于2024-07-31 收藏 1.04MB PDF 举报

"Oracle 11gR2 2 Day Data Warehouse Guide 是一本关于如何在 Oracle 11gR2 数据库系统上构建数据仓库的基础教程。本书旨在为读者提供构建 Oracle 数据仓库所需的关键知识和技术，适合初学者和有一定经验的数据库管理员。" Oracle 11gR2 是 Oracle 公司的一个重要数据库版本，它提供了许多针对数据仓库优化的功能和改进。数据仓库是用于存储和分析大量历史数据的系统，通常用于商业智能和决策支持。在 Oracle 11gR2 中，你可以利用以下核心知识点来构建高效的数据仓库： 1. **数据仓库架构**：理解数据仓库的基本组件，如数据源、ETL（提取、转换、加载）工具、事实表、维度表和星型/雪花型模式。 2. **表分区**：Oracle 11gR2 引入了多种分区策略，如范围分区、列表分区、哈希分区和组合分区，以提高查询性能和管理大规模数据的效率。 3. **物化视图**：通过预计算和存储查询结果，物化视图可以加速复杂的查询响应，尤其适用于汇总数据和定期更新的情况。 4. **OLAP（在线分析处理）功能**：包括快速立方体生成、多维数据集和高级分析函数，用于支持复杂的分析查询和报告。 5. **自动段空间管理**：简化了数据存储的管理，自动处理表和索引的段创建、扩展和收缩。 6. **高级压缩**：Oracle 11gR2 提供了列存压缩和行存压缩，能够显著减少存储需求，同时保持高性能的查询。 7. **数据泵（Data Pump）**：高速数据传输工具，用于ETL过程，能快速导出和导入大量数据，支持并行执行，提高数据迁移效率。 8. **索引**：包括位图索引、函数索引和分区索引，用于优化查询性能，特别是针对聚合操作和等值查询。 9. **SQL优化**：Oracle 11gR2 提供了更强大的 SQL 优化器，如成本基础优化器和统计信息收集，确保查询计划的有效性。 10. **数据仓库工作负载管理**：通过设置资源计划和调度，可以优先处理关键的分析查询，确保数据仓库系统的响应速度。此外，这本书可能还会涵盖数据库设计原则、性能调优技巧、安全管理和维护最佳实践等内容。通过学习，读者将掌握构建、维护和优化 Oracle 11gR2 数据仓库所需的关键技能。

What Is a Data Warehouse?

1-2 Oracle Database 2 Day + Data Warehousing Guide

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.

See Also: Oracle Database Data Warehousing Guide for more details

regarding multidimensional data warehouses

Tasks Illustrated in This Guide

Introduction to Data Warehousing 1-3

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 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:

1. 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.

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.

Tools for Administering the Data Warehouse

1-4 Oracle Database 2 Day + Data Warehousing Guide

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:

■ 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

剩余129页未读，继续阅读

jimqian

粉丝: 0
资源: 36

构建Oracle 11gR2数据仓库基础指南

Data WareHouse Guide Oracle11g

Oracle Data Warehouse Management

Oracle 11gR2 concept中英文对照版

Oracle E-Business Intelligence Embedded Data Warehouse Implement

Oracle E-Business Intelligence Embedded Data Warehouse Install

Oracle10g Data Warehouse

Oracle E-Business Intelligence Embedded Data Warehouse User Guid

Oracle_data_warehouse.rar_ 数据仓库_Data Warehouse orac_oracle_wareh

data warehouse 2

Oracle Warehouse Builder Sources and Targets Guide 11g Release 2

最新资源