Oracle Warehouse Builder 11g: Getting Started

Bob Griesemer

• Publisher: Packt Publishing
• Year: 2009
• eBook ISBN: 9781847195746
• Edition: 1
• Topic: Computer Science
• Subtopic: Information Technology
• Index: Computer Science

In Detail

In today's economy, businesses and IT professionals cannot afford to lag behind the latest technologies. Data warehousing is a critical area to the success of many enterprises, and Oracle Warehouse Builder is a powerful tool for building data warehouses. It comes free with the latest version of the Oracle database.

Written in an accessible, informative, and focused manner, this book will teach you to use Oracle Warehouse Builder to build your data warehouse. Covering warehouse design, the import of source data, the ETL cycle and more, this book will have you up and running in next to no time.

This book will walk you through the complete process of planning, building, and deploying a data warehouse using Oracle Warehouse Builder. By the book's end, you will have built your own data warehouse from scratch.

Starting with the installation of the Oracle Database and Warehouse Builder software, this book then covers the analysis of source data, designing a data warehouse, and extracting, transforming, and loading data from the source system into the data warehouse. You'll follow the whole process with detailed screenshots of key steps along the way, alongside numerous tips and hints not covered by the official documentation.

 

A hands-on guide to building a data warehouse using Oracle Warehouse Builder

Approach

This easy-to-understand tutorial covers Oracle Warehouse Builder from the ground up, and taps into the author's wide experience as a software and database engineer. Written in a relaxed style with step-by-step explanations, lots of screenshots are provided throughout the book. There are numerous tips and helpful hints throughout that are not found in the original documentation. By following this book, you can use Oracle Warehouse Builder in the best possible way and maximize your learning potential.

Who this book is for

This book is a good starting point for database engineers, administrators, and architects who are responsible for data warehouse projects and need to design them and load data into them. If you are someone who wants to learn Oracle Warehouse Builder and expand your knowledge of the tool and data warehousing, this is an ideal book for you. No prior data warehouse or database experience is presumed with all new database and data warehouse technical terms and concepts explained in clear easy-to-understand language.

Oracle Warehouse Builder 11g Getting Started

Bob Griesemer

---

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

Oracle Warehouse Builder 11g Getting Started

Copyright © 2009 Packt Publishing

All rights reserved. No part of this book may be reproduced, stored in a retrieval system, or transmitted in any form or by any means, without the prior written permission of the publisher, except in the case of brief quotations embedded in critical articles or reviews.

Every effort has been made in the preparation of this book to ensure the accuracy of the information presented. However, the information contained in this book is sold without warranty, either express or implied. Neither the author, nor Packt Publishing and its dealers and distributors will be held liable for any damages caused or alleged to be caused directly or indirectly by this book.

Packt Publishing has endeavored to provide trademark information about all of the companies and products mentioned in this book by the appropriate use of capitals. However, Packt Publishing cannot guarantee the accuracy of this information.

First published: August 2009

Production Reference: 1300709

Published by Packt Publishing Ltd.

32 Lincoln Road

Olton

Birmingham, B27 6PA, UK.

ISBN 978-1-847195-74-6

www.packtpub.com

Cover Image by Parag Kadam (<[email protected]>)

Credits

Author

Bob Griesemer

Reviewers

Anitha Kadaru

Yasodarani Venkatesan

Acquisition Editor

James Lumsden

Development Editor

Swapna V. Verlekar

Technical Editors

Arani Roy

Reshma Sundaresan

Copy Editor

Sneha Kulkarni

Editorial Team Leader

Abhijeet Deobhakta

Project Team Leader

Lata Basantani

Project Coordinators

Ashwin Shetty

Neelkanth Mehta

Indexer

Rekha Nair

Proofreader

Chris Smith

Production Coordinator

Adline Swetha Jesuthas

Cover Work

Adline Swetha Jesuthas

2323__perl...

Table of contents

• Oracle Warehouse Builder 11g Getting Started