Oracle Warehouse Builder 11g R2: Getting Started 2011电子书

Oracle Warehouse Builder 11gR2: Getting Started 2011

Table of Contents

Oracle Warehouse Builder 11gR2: Getting Started 2011

Credits

About the Author

About the Reviewers

www.PacktPub.com

Support files, eBooks, discount offers, and more

Why Subscribe?

Free Access for Packt account holders

Instant Updates on New Packt Books

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

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 website order management database

An overview of Warehouse Builder Design Center

Importing/defining source database object metadata

Creating a project

Creating a module

Creating an Oracle Database module

Creating a SQL Server database module

Creating a SQL Server database connection

Configuring 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 database objects from a database

Defining source metadata manually with the Table 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

Creating 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 editors

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

OWB Mappings

The canvas layout

OWB operators

Source and target operators

Transformations (data flow operators)

Other operators

Summary

6. ETL: Putting it Together

Designing our staging area

Designing the staging area contents

Building the staging area table with the Table Editor

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 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 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 using the toolbar icon in the Mapping Editor

Generating

Generating in the Design Center

Generating using the icon from 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

Metadata change management

Metadata Loader (MDL) exports and imports

Recycle bin

Cut, copy, and paste

Snapshots

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 binding

Warehouse Builder online resources

Summary

10. Code Template Mappings

Code templates

Code template description

Types of code templates

Pre-defined code templates

Connecting to SQL server using a JDBC database connection

Downloading the JDBC driver

Creating a SQL server module

Configuring SQL server TCP/IP port

Creating SQL server module

Importing metadata

Building a code template mapping

Starting the Control Center Agent

Defining a template mapping module

Creating a code template mapping

Copying a mapping

Editing a code template mapping

Working in the logical view

Working in the execution view

Creating an execution unit

Moving an operator between execution units

Deploying and executing a code template mapping

Summary

Index