Oracle Warehouse Builder 11g: Getting Started电子书

Oracle Warehouse Builder 11g Getting Started

Table of Contents

Oracle Warehouse Builder 11g Getting Started

Credits

About the Author

About the Reviewers

Preface

What this book covers

What you need for this book

Who this book is for

Conventions

Reader feedback

Customer support

Downloading the example code for the book

Errata

Piracy

Questions

1. An Introduction to Oracle Warehouse Builder

Introduction to data warehousing

Introduction to our fictional organization

What is a data warehouse?

Where does OWB fit in?

Installation of the database and OWB

Downloading the Oracle software

A word about hardware and operating systems

Installing Oracle database software

Configuring the listener

Creating the database

Installing the OWB standalone software

OWB components and architecture

Configuring the repository and workspaces

Summary

2. Defining and Importing Source Data Structures

Preliminary analysis

ACME Toys and Gizmos source data

The POS transactional source database

The web site order management database

An overview of Warehouse Builder Design Center

Importing/defining source metadata

Creating a project

Creating a module

Creating an Oracle Database module

Creating a SQL Server database module

Creating a SQL Server database connection

Configure Oracle to connect to SQL Server

Creating a heterogeneous service configuration file

Editing the listener.ora file

Creating the Warehouse Builder ODBC module for SQL Server

Importing source metadata from a database

Defining source metadata manually with the Data Object Editor

Importing source metadata from files

Summary

3. Designing the Target Structure

Data warehouse design

Dimensional design

Cube and dimensions

Implementation of a dimensional model in a database

Relational implementation (star schema)

Multidimensional implementation (OLAP)

Designing the ACME data warehouse

Identifying the dimensions

Designing the cube

Data warehouse design in OWB

Creating a target user and module

Create a target user

Create a target module

OWB design objects

Summary

4. Creating the Target Structure in OWB

Creating dimensions in OWB

The Time dimension

Creating a Time dimension with the Time Dimension Wizard

The Product dimension

Product Attributes (attribute type)

Product Levels

Product Hierarchy (highest to lowest)

Creating the Product dimension with the New Dimension Wizard

The Store dimension

Store Attributes (attribute type), data type and size, and (Identifier)

Store Levels

Store Hierarchy (highest to lowest)

Creating the Store dimension with the New Dimension Wizard

Creating a cube in OWB

Creating a cube with the wizard

Using the Data Object Editor

Summary

5. Extract, Transform, and Load Basics

ETL

Manual ETL processes

Staging

To stage or not to stage

Configuration of a staging area

Mappings and operators in OWB

The canvas layout

OWB operators

Source and target operators

Data flow operators

Pre/post-processing operators

Summary

6. ETL: Putting it Together

Designing and building an ETL mapping

Designing our staging area

Designing the staging area contents

Building the staging area table with the Data Object Editor

Designing our mapping

Review of the Mapping Editor

Creating a mapping

Adding source tables

Adding a target table

Connecting source to target

Joiner operator attribute groups

Connecting operators to the Joiner

Defining operator properties for the Joiner

Adding an Aggregator operator

Summary

7. ETL: Transformations and Other Operators

STORE mapping

Adding source and target operators

Adding Transformation Operators

Using a Key Lookup operator

Creating an external table

Creating and loading a lookup table

Retrieving the key to use for a Lookup Operator

Adding a SUBSTR Transformation Operator

Adding a Constant operator

Adding a TO_NUMBER transformation

Adding a Key Lookup operator

PRODUCT mapping

SALES cube mapping

Dimension attributes in the cube

Measures and other attributes in the cube

Mapping values to cube attributes

Mapping measures' values to a cube

Mapping PRODUCT and STORE dimension values to the cube

Mapping DATE_DIM values to the cube

Mapping an Expression operator

Features and benefits of OWB

Summary

8. Validating, Generating, Deploying, and Executing Objects

Validating

Validating in the Design Center

Validating from the editors

Validating in the Data Object Editor

Validating in the Mapping Editor

Generating

Generating in the Design Center

Generating from the editors

Generating in the Data Object Editor

Generating in the Mapping Editor

Default operating mode of the mapping

Selecting the generation style

Deploying

The Control Center Service

Deploying in the Design Center and Data Object Editor

The Control Center Manager

The Control Center Manager window overview

The Object Details window

The Control Center Jobs window

Deploying in the Control Center Manager

Executing

Deploying and executing remaining objects

Deployment Order

Execution order

Summary

9. Extra Features

Additional editing features

Metadata change management

Recycle Bin

Cut, copy, and paste

Snapshots

Metadata Loader (MDL) exports and imports

Synchronizing objects

Changes to tables

Updating object definitions

Synchronizing

Inbound or outbound

Matching and synchronizing strategy

Viewing the synchronization plan

Changes to dimensional objects and auto-binding

Warehouse Builder online resources

Summary

Index