Xml and More: Book Review: "Oracle Warehouse Builder 11g R2: Getting Started 2011"

What's Data Warehouse

A data warehouse is a database used for reporting. The main source of the data is cleaned, transformed, cataloged and made available for use by managers and other business professionals for data mining, online analytical processing, market research and decision support (Marakas & OBrien 2009).

The data warehouse must handle large amounts of data, and must be simple to query and understand by the end users. A data warehouse maintains its functions in three layers:

Staging— used to store raw data for use by developers (analysis and support).
Integration—used to integrate data and to have a level of abstraction from users.
Access— for getting data out for users

Data Warehouse vs. Transactional Database

A data warehouse (DW) is different from a transactional database (TD). A DW is a database used for reporting where the focus is getting data out of the system. On the other hands, TD is concerned with daily operational processing where the focus is on getting data into the system.

In the design, TD uses normalized approach while DW chooses dimensional approach.

The normalized structure divides data into entities, which creates several tables in a relational database. When applied in large enterprises the result is dozens of tables that are linked together by a web of joins.

In a dimensional approach, transaction data are partitioned into either facts (actual numerical measures) and dimensions (descriptive data about those measures) that place the facts in a context that is understandable to the end-user decision maker.

In this article, we will review a book named Oracle Warehouse Builder 11g R2: Getting Started 2011, in which it addresses Oracle Warehouse Builder (i.e., one of the Oracle's data mining solutions).

Introduction of the Book

If you are new to data warehousing and need a way to get started, this book offers great help. The task of designing a data warehouse is nontrivial and you can use some helps from a good tool. On this, I agree with the author:

Oracle Warehouse Builder is a great application to use to build your warehouse.

This book provides good coverage on both the theory of data warehousing and practical ways of implementing a data warehouse that can be directly applied in the real world. It's the final product of author's 27 years' working experience in the database industry and his thorough and careful research on the Oracle Warehouse Builder.

The following books are perused and referenced by the author:

Oracle Warehouse Builder Data Modeling, ETL and Data Quality Guide
Oracle Warehouse Builder Concepts Guide
Oracle Database SQL Language Reference
Oracle Warehouse Builder Users Guide
Oracle Database Error Messages 11g Release 2(11.2)
The Data Warehouse Toolkit: The Complete Guide to Dimensional Modeling

Oracle Warehouse Builder (OWB)

The Oracle Warehouse Builder is a tool provided by Oracle, which can be used at every stage of the implementation of a data warehouse, from initial design and creation of the table structure to the extract, transform, and load (ETL) process and data-quality management.

OWB has the ability to design the objects logically using cubes and dimensions in a dimensional design. It also has the ability to implement them physically in the underlying database as either a relational structure or a dimensional structure simply by checking a box. For a relational implementation (physical implementation), OWB actually provides us two options for implementing the database:

Pure relational option
Relational OLAP option

In the Figure below, it shows OWB components and its architecture:

ACME Toys and Gizmos

In this book, a fictional organization named ACME Toys and Gizmos is used to demonstrate practical ways of implementing a data warehouse that can be directly applied in the real world. This book provides explanations throughout on how to use the Oracle Warehouse Builder tool to build a data warehouse within the context of this invented company, which is involved in storefront and online Internet sales.

For instance, a customer makes a purchase of a toy with ACME Toys and Gizmos on a particular day over the Internet, which results in a dollar amount of the transaction. The dollar amount becomes the fact and the toy purchased, the customer, and the location of the purchase (the Internet in this case) become the dimensions that provide a scope of the fact measurement and give it a meaning.

The design of the data warehouse is drawn out in a star schema configuration showing the cube, which is surrounded by the dimensions with the individual items of information (attributes) . It looks like the following:

With Star Schema, managers can easily use the data for data mining, online analytical processing, market research and decision support. For example, if a manager for ACME Toys and Gizmos needs to know what products sold well in the last quarter, the query will only involve two tables—the main fact table containing the data on number of items sold and the product dimension table that contains all the information about the product.

Summary

The purpose of this book is to introduce the Oracle Warehouse Builder and use it to design and build your first data warehouse. By following author's carefully designed components and instructions, you will easily achieve that goal.

Saturday, July 9, 2011

Book Review: "Oracle Warehouse Builder 11g R2: Getting Started 2011"