Oracle Warehouse Builder 11g R2: Getting Started 2011. Extract, Transform, and Load data to build a dynamic, operational data warehouse

Oracle Warehouse Builder 11g R2: Getting Started 2011. Extract, Transform, and Load data to build a dynamic, operational data warehouse - Opis

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. Spis treści:
Oracle Warehouse Builder 11gR2: Getting Started 2011
Table of Contents
Oracle Warehouse Builder 11gR2: (...) więcej 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 mniej