You are previewing Oracle Warehouse Builder 11gR2: Getting Started 2011.
O'Reilly logo
Oracle Warehouse Builder 11gR2: Getting Started 2011

Book Description

Extract, Transform, and Load data to build a dynamic, operational data warehouse with Oracle Warehouse Builder 11g R2 with this book and eBook

  • Build a working data warehouse from scratch with Oracle Warehouse Builder

  • Cover techniques in Extracting, Transforming, and Loading data into your data warehouse

  • Use a multi-dimensional design with an underlying relational star schema

  • This second edition covers great new features of 11gR2 such as the new user interface and a whole new chapter on code templates that implement knowledge module functionality from Oracle Data Integrator

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 that have all been updated for the new Fusion Client Platform interface in 11gR2, alongside numerous tips and hints not covered by the official documentation.

You’ll finish up with a brand new chapter on code templates where you’ll implement a complete mapping using JDBC connectivity and code template mappings.

Table of Contents

  1. Oracle Warehouse Builder 11gR2: Getting Started
    1. Oracle Warehouse Builder 11gR2: Getting Started
    2. Credits
    3. About the Author
    4. About the Reviewers
      1. Support files, eBooks, discount offers, and more
        1. Why Subscribe?
        2. Free Access for Packt account holders
        3. Instant Updates on New Packt Books
    6. Preface
      1. What this book covers
      2. What you need for this book
      3. Who this book is for
      4. Conventions
      5. Reader feedback
      6. Customer support
        1. Downloading the example code
        2. Errata
        3. Piracy
        4. Questions
    7. 1. An Introduction to Oracle Warehouse Builder
      1. Introduction to data warehousing
        1. Introduction to our fictional organization
        2. What is a data warehouse?
        3. Where does OWB fit in?
      2. Installation of the database and OWB
        1. Downloading the Oracle software
        2. A word about hardware and operating systems
        3. Installing Oracle database software
        4. Configuring the listener
        5. Creating the database
        6. Installing the OWB standalone software
        7. OWB components and architecture
        8. Configuring the repository and workspaces
      3. Summary
    8. 2. Defining and Importing Source Data Structures
      1. Preliminary analysis
        1. ACME Toys and Gizmos source data
        2. The POS transactional source database
        3. The website order management database
      2. An overview of Warehouse Builder Design Center
      3. Importing/defining source database object metadata
        1. Creating a project
        2. Creating a module
          1. Creating an Oracle Database module
          2. Creating a SQL Server database module
            1. Creating a SQL Server database connection
            2. Configuring Oracle to connect to SQL Server
              1. Creating a heterogeneous service configuration file
              2. Editing the listener.ora file
            3. Creating the Warehouse Builder ODBC module for SQL Server
        3. Importing source database objects from a database
        4. Defining source metadata manually with the Table Editor
        5. Importing source metadata from files
      4. Summary
    9. 3. Designing the Target Structure
      1. Data warehouse design
        1. Dimensional design
          1. Cube and dimensions
        2. Implementation of a dimensional model in a database
          1. Relational implementation (star schema)
          2. Multidimensional implementation (OLAP)
        3. Designing the ACME data warehouse
          1. Identifying the dimensions
          2. Designing the cube
      2. Data warehouse design in OWB
        1. Creating a target user and module
          1. Creating a target user
          2. Create a target module
        2. OWB design objects
      3. Summary
    10. 4. Creating the Target Structure in OWB
      1. Creating dimensions in OWB
        1. The Time dimension
          1. Creating a Time dimension with the Time Dimension Wizard
        2. The Product dimension
          1. Product attributes (attribute type)
          2. Product levels
          3. Product hierarchy (highest to lowest)
          4. Creating the Product dimension with the new Dimension Wizard
        3. The Store dimension
          1. Store attributes (attribute type), data type and size, and (Identifier)
          2. Store levels
          3. Store hierarchy (highest to lowest)
          4. Creating the Store dimension with the New Dimension Wizard
      2. Creating a cube in OWB
        1. Creating a cube with the wizard
      3. Using the data object editors
      4. Summary
    11. 5. Extract, Transform, and Load Basics
      1. ETL
        1. Manual ETL processes
      2. Staging
        1. To stage or not to stage
        2. Configuration of a staging area
      3. Mappings and operators in OWB
        1. OWB Mappings
          1. The canvas layout
        2. OWB operators
          1. Source and target operators
          2. Transformations (data flow operators)
          3. Other operators
      4. Summary
    12. 6. ETL: Putting it Together
      1. Designing our staging area
        1. Designing the staging area contents
        2. Building the staging area table with the Table Editor
      2. Review of the Mapping Editor
      3. Creating a mapping
        1. Adding source tables
        2. Adding a target table
        3. Connecting source to target
          1. Joiner operator attribute groups
          2. Connecting operators to the Joiner
          3. Defining operator properties for the JOINER
          4. Adding an Aggregator operator
      4. Summary
    13. 7. ETL: Transformations and Other Operators
      1. STORE mapping
        1. Adding source and target operators
        2. Adding Transformation Operators
        3. Using a Lookup operator
          1. Creating an external table
          2. Creating and loading a lookup table
          3. Retrieving the key to use for a Lookup operator
          4. Adding a SUBSTR Transformation operator
          5. Adding a Constant operator
          6. Adding a TO_NUMBER transformation
          7. Adding a Lookup operator
      2. PRODUCT mapping
      3. SALES cube mapping
        1. Dimension attributes in the cube
        2. Measures and other attributes in the cube
        3. Mapping values to cube attributes
          1. Mapping measures' values to a cube
          2. Mapping PRODUCT and STORE dimension values to the cube
          3. Mapping DATE_DIM values to the cube
          4. Mapping an Expression operator
      4. Features and benefits of OWB
      5. Summary
    14. 8. Validating, Generating, Deploying, and Executing Objects
      1. Validating
        1. Validating in the Design Center
        2. Validating using the toolbar icon in the Mapping Editor
      2. Generating
        1. Generating in the Design Center
        2. Generating using the icon from the mapping editor
        3. Default operating mode of the mapping
        4. Selecting the generation style
      3. Deploying
        1. The Control Center Service
        2. Deploying in the Design Center and Data Object Editor
        3. The Control Center Manager
          1. The Control Center Manager window overview
            1. The Object Details window
            2. The Control Center Jobs window
          2. Deploying in the Control Center Manager
      4. Executing
      5. Deploying and executing remaining objects
        1. Deployment order
        2. Execution order
      6. Summary
    15. 9. Extra Features
      1. Metadata change management
        1. Metadata Loader (MDL) exports and imports
        2. Recycle bin
        3. Cut, copy, and paste
        4. Snapshots
      2. Synchronizing objects
        1. Changes to tables
          1. Updating object definitions
          2. Synchronizing
            1. Inbound or outbound
            2. Matching and synchronizing strategy
            3. Viewing the synchronization plan
        2. Changes to dimensional objects and binding
      3. Warehouse Builder online resources
      4. Summary
    16. 10. Code Template Mappings
      1. Code templates
        1. Code template description
        2. Types of code templates
        3. Pre-defined code templates
      2. Connecting to SQL server using a JDBC database connection
        1. Downloading the JDBC driver
        2. Creating a SQL server module
          1. Configuring SQL server TCP/IP port
          2. Creating SQL server module
          3. Importing metadata
      3. Building a code template mapping
        1. Starting the Control Center Agent
        2. Defining a template mapping module
        3. Creating a code template mapping
          1. Copying a mapping
          2. Editing a code template mapping
            1. Working in the logical view
            2. Working in the execution view
              1. Creating an execution unit
              2. Moving an operator between execution units
        4. Deploying and executing a code template mapping
      4. Summary