Search icon CANCEL
Subscription
0
Cart icon
Your Cart (0 item)
Close icon
You have no products in your basket yet
Arrow left icon
Explore Products
Best Sellers
New Releases
Books
Videos
Audiobooks
Learning Hub
Conferences
Free Learning
Arrow right icon
Oracle Warehouse Builder 11g: Getting Started
Oracle Warehouse Builder 11g: Getting Started

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

Arrow left icon
Profile Icon Bob Griesemer
Arrow right icon
Free Trial
Full star icon Full star icon Full star icon Full star icon Half star icon 4.6 (5 Ratings)
Paperback Aug 2009 370 pages 1st Edition
eBook
NZ$14.99 NZ$71.99
Paperback
NZ$89.99
Subscription
Free Trial
Arrow left icon
Profile Icon Bob Griesemer
Arrow right icon
Free Trial
Full star icon Full star icon Full star icon Full star icon Half star icon 4.6 (5 Ratings)
Paperback Aug 2009 370 pages 1st Edition
eBook
NZ$14.99 NZ$71.99
Paperback
NZ$89.99
Subscription
Free Trial
eBook
NZ$14.99 NZ$71.99
Paperback
NZ$89.99
Subscription
Free Trial

What do you get with a Packt Subscription?

Free for first 7 days. $19.99 p/m after that. Cancel any time!
Product feature icon Unlimited ad-free access to the largest independent learning library in tech. Access this title and thousands more!
Product feature icon 50+ new titles added per month, including many first-to-market concepts and exclusive early access to books as they are being written.
Product feature icon Innovative learning tools, including AI book assistants, code context explainers, and text-to-speech.
Product feature icon Thousands of reference materials covering every tech concept you need to stay up to date.
Subscribe now
View plans & pricing
Table of content icon View table of contents Preview book icon Preview Book

Oracle Warehouse Builder 11g: Getting Started

Chapter 2. Defining and Importing Source Data Structures

The Warehouse Builder software and Oracle database have been installed, and we're ready to begin building our data warehouse. The first thing we have to do is define what our sources of data will be. If we are going to build a useful data warehouse, we have to know what kinds of information our users are going to need out of the warehouse. To know that, we have to know the following:

  • The format in which the data is currently stored and where it is stored.

  • Whether there is a transactional database currently in use or not, which supports day-to-day operations and from which we'll be pulling the data.

    A transactional database is different from a data warehouse database in that it is designed to support the day-to-day transactions that keep an organization running.

  • Whether the database is an Oracle database or another vendor's database such as Microsoft SQL Server.

  • Whether there are any flat files of information saved from database tables or...

Preliminary analysis


In any data warehouse project, we are going to need to do some up-front analysis to determine what data will need to be captured into our warehouse. The analysis will tell us where the data is located, and in what format, so that we can begin to define our source data structures in the Warehouse Builder. In our case, we will presume that we have interviewed the management at the ACME Toys and Gizmos company and they have indicated the following:

  • The high-priority information that they would like to see from this data warehouse project is sales-related data for all their stores

  • They don't have an idea about the comparative sales in the various stores, so they need some way to view all that data together to do an analysis that shows how well, or poorly, the stores are doing

  • In the future, they would also like to be able to compare store sales with their web site sales, but that will not be required for this first data warehouse we build

We are doing a very simple analysis...

An overview of Warehouse Builder Design Center


The Design Center is the main graphical interface that we will be using to design our data warehouse, but we also use it to define our data sources. So let's take some time at this point to go over the user interface and familiarize ourselves with it. We launch Design Center from the Start menu under the Oracle menu entry, as shown in the following image:

The Design Center must connect to a workspace in our repository. To review briefly, we discussed the architecture of the Warehouse Builder in Chapter 1. This included the repository in which we created a workspace and a user, who would be the owner of the workspace. We used the Repository Assistant application to configure our repository and create that user. The repository is located in the OWBSYS schema that was the pre-installed schema the database installation provided for us. The user name chosen was acmeowb and the workspace name was acme_ws. Now it's time to make use of this user and...

Importing/defining source metadata


Now that we've been introduced to the Design Center, it's time to make use of it to import or define our source metadata. Metadata is data that describes our data. We are going to tell the Warehouse Builder what our source data looks like and where it is located, so that it can build the code necessary to retrieve that data when we design and run mappings to populate our data warehouse. The metadata is represented in the Warehouse Builder as objects corresponding to the type of the source object. So if we're representing tables in a database, we will have tables defined in the Warehouse Builder.

We have a couple of options for defining the source metadata. We can manually input the definitions into Design Center Project Explorer ourselves, or we can choose to have the Warehouse Builder automatically import the descriptions of our data for us. As we like having the computer do the work for us whenever possible, we will choose the second option whenever...

Summary


That was a lot of information presented in this chapter. We began with a brief discussion about the source data for our scenario using the ACME Toys and Gizmos company. We then went through the process of importing metadata about our sources, and saw how to import metadata from an Oracle database as well as a flat file. Because we are dealing with a non-Oracle database, we were also exposed to configuring the Oracle database to communicate with a non-Oracle database using Oracle Heterogeneous Services. We also saw how to configure it for generic connectivity using the supplied ODBC agent. We worked through the process of manually creating table definitions for source tables for a SQL Server database.

At this point, you should run through these procedures to import or define the remaining tables that were identified in the source databases. For this, you can use the procedures we walked through above for practice. We'll be using the SQL Server database tables for the POS transactional...

Left arrow icon Right arrow icon

Key benefits

  • Build a working data warehouse from scratch with Oracle Warehouse Builder.
  • Cover techniques in Extracting, Transforming, and Loading data into your data warehouse.
  • Learn about the design of a data warehouse by using a multi-dimensional design with an underlying relational star schema.
  • Written in an accessible and informative style, this book helps you achieve your warehousing goals, and is loaded with screenshots, numerous tips, and strategies not found in the official user guide.

Description

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.

Who is this book 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.

What you will learn

  • Install the most recent Oracle Database version including Warehouse Builder.
  • Learn basic data warehousing concepts and analyze and design your own data warehouse.
  • Configure your Oracle database to communicate with a non-Oracle database using Oracle Heterogeneous Services.
  • Build, view, and edit a cube and its dimensions, using Warehouse Builder Wizards and the Data Object Editor.
  • Discover the underlying star schema relational structure that Warehouse Builder will build to implement your cube and dimensions.
  • Recognize what a staging area is, and build a staging table and mapping to load it from SQL Server database tables.
  • Learn various operators for use in mappings, for the transformation and flow of data.
  • Build and execute mappings to load dimensions and a cube using the Warehouse Builder Mapping Editor and Control Center Manager.

Product Details

Country selected
Publication date, Length, Edition, Language, ISBN-13
Publication date : Aug 05, 2009
Length: 370 pages
Edition : 1st
Language : English
ISBN-13 : 9781847195746
Vendor :
Oracle
Category :

What do you get with a Packt Subscription?

Free for first 7 days. $19.99 p/m after that. Cancel any time!
Product feature icon Unlimited ad-free access to the largest independent learning library in tech. Access this title and thousands more!
Product feature icon 50+ new titles added per month, including many first-to-market concepts and exclusive early access to books as they are being written.
Product feature icon Innovative learning tools, including AI book assistants, code context explainers, and text-to-speech.
Product feature icon Thousands of reference materials covering every tech concept you need to stay up to date.
Subscribe now
View plans & pricing

Product Details

Publication date : Aug 05, 2009
Length: 370 pages
Edition : 1st
Language : English
ISBN-13 : 9781847195746
Vendor :
Oracle
Category :

Packt Subscriptions

See our plans and pricing
Modal Close icon
$19.99 billed monthly
Feature tick icon Unlimited access to Packt's library of 7,000+ practical books and videos
Feature tick icon Constantly refreshed with 50+ new titles a month
Feature tick icon Exclusive Early access to books as they're written
Feature tick icon Solve problems while you work with advanced search and reference features
Feature tick icon Offline reading on the mobile app
Feature tick icon Simple pricing, no contract
$199.99 billed annually
Feature tick icon Unlimited access to Packt's library of 7,000+ practical books and videos
Feature tick icon Constantly refreshed with 50+ new titles a month
Feature tick icon Exclusive Early access to books as they're written
Feature tick icon Solve problems while you work with advanced search and reference features
Feature tick icon Offline reading on the mobile app
Feature tick icon Choose a DRM-free eBook or Video every month to keep
Feature tick icon PLUS own as many other DRM-free eBooks or Videos as you like for just NZ$7 each
Feature tick icon Exclusive print discounts
$279.99 billed in 18 months
Feature tick icon Unlimited access to Packt's library of 7,000+ practical books and videos
Feature tick icon Constantly refreshed with 50+ new titles a month
Feature tick icon Exclusive Early access to books as they're written
Feature tick icon Solve problems while you work with advanced search and reference features
Feature tick icon Offline reading on the mobile app
Feature tick icon Choose a DRM-free eBook or Video every month to keep
Feature tick icon PLUS own as many other DRM-free eBooks or Videos as you like for just NZ$7 each
Feature tick icon Exclusive print discounts

Frequently bought together


Stars icon
Total NZ$ 256.97
Oracle Warehouse Builder 11g: Getting Started
NZ$89.99
Data Engineering with AWS
NZ$94.99
Azure Data Engineering Cookbook
NZ$71.99
Total NZ$ 256.97 Stars icon
Banner background image

Table of Contents

9 Chapters
An Introduction to Oracle Warehouse Builder Chevron down icon Chevron up icon
Defining and Importing Source Data Structures Chevron down icon Chevron up icon
Designing the Target Structure Chevron down icon Chevron up icon
Creating the Target Structure in OWB Chevron down icon Chevron up icon
Extract, Transform, and Load Basics Chevron down icon Chevron up icon
ETL: Putting it Together Chevron down icon Chevron up icon
ETL: Transformations and Other Operators Chevron down icon Chevron up icon
Validating, Generating, Deploying, and Executing Objects Chevron down icon Chevron up icon
Extra Features Chevron down icon Chevron up icon

Customer reviews

Rating distribution
Full star icon Full star icon Full star icon Full star icon Half star icon 4.6
(5 Ratings)
5 star 60%
4 star 40%
3 star 0%
2 star 0%
1 star 0%
J. Northrup Aug 12, 2009
Full star icon Full star icon Full star icon Full star icon Full star icon 5
Griesemer has provided a very useful and easy-to-read introduction to building data warehouses using Oracle tools. By providing realistic examples and thorough explanations, this book will help anybody who has basic database experience get up-to-speed quickly.
Amazon Verified review Amazon
Vamseedhar R. Sane Oct 22, 2009
Full star icon Full star icon Full star icon Full star icon Full star icon 5
The book is larger compared to regular books released by PackT Publishing. Instead of the regular 250-page range, the book is now more than 350 pages. The book looks massive as it deals with very specific information and wide range of possibilities and scenario with Oracle's data warehousing application. The screenshots can be noticed while going through random pages which means it provides an actual interaction with the application.The book was written by Bob Griesemer. Although he's not a recognized name in the industry, he has been working with various database solutions for more than 25 years. His experience with various industries and even in some government institutions have provided the needed knowledge in getting this book done. He is currently the Senior Database Engineer in Northrop Grumman Corporation. His profile also indicates that he has worked with Oracle since its 6th version which means he has monitored almost every major change of Oracle since its inception.The book is intended for those who want to learn more about data warehousing from scratch. The introduction on the necessity of data warehousing is very interesting since it provides a closer look on how database administrators should understand data manipulation in servers. The book slowly moves to complicated subjects as it tries to help readers fully understand each step.Even though the book is intended on those starting in data warehousing, the book can be a good refresher for experts. The later chapter can be a good reference in case special functions in data warehousing should be used.
Amazon Verified review Amazon
Kenneth L. Feb 23, 2011
Full star icon Full star icon Full star icon Full star icon Full star icon 5
This book is an excellent resource for all of those that would like to learn Oracle Warehouse Builder and data warehousing.
Amazon Verified review Amazon
Serafeim Karapatis Jan 26, 2010
Full star icon Full star icon Full star icon Full star icon Empty star icon 4
The book fulfills its purpose as a starter kit for Oracle Warehouse Builder (OWB) 11g R1. It covers both the OWB basics as well as the ETL process fundamentals and following the Packt philosophy, it is accompanied with lots of illustrations and examples. In overall, it is a very good starting point for OWB, as advanced topics such as PL/SQL extensions, auditing or data quality are beyond the scope of the book.
Amazon Verified review Amazon
Uli Bethke Oct 21, 2009
Full star icon Full star icon Full star icon Full star icon Empty star icon 4
Bob Griesemer's Oracle Warehouse Builder 11G - Getting Started is the first and so far (as of Oct 2009) only book published on Oracle Warehouse Builder.It aims to introduce data warehousing in general and Oracle Warehouse Builder in particular, to absolute beginners in the field.In chapter 1 the author guides us through the installation process for OWB 11G. A typical installation of Warehouse Builder in a client/server environment is explained. More complex architectures with the Control Center service on a different server are not explained.In chapter 2 we are introduced to the data model of the source system for the data warehouse. We are shown how to import or create the metadata for our source system. What I found useful here is that the author explains how we can use a non-Oracle RDBMS (in this case SQL Server) as a source system via heterogenous services. This can be a pain to set up so it is helpful to have a step by step walkthrough for this.Chapter 3 gives an introduction to the basics of data warehouse design. It then explains how we can implement such a design as a target structure in Oracle Warehouse Builder.In chapter 5 the author gives an overview on the basics of ETL processes and introduces us to some of the more important OWB operators that will allow us to implement ETL process in Warehouse Builder. I found that more space should have been given to explain the operators in detail. Some more detailed examples for each would have been useful also.Chapters 6 and 7 then show us how we can bring the source metadata from chapter 2, the target metadata from chapter 3, and the operators from the previous chapter together in a Warehouse Builder mapping to extract, transform, and load data from source to target. What is completely missing here though is an introduction to process flows. Process flows are fundamental to glue ETL mappings in Warehouse Builder together and should have been part of an introduction to the subject.Chapter 8 gives an overview on the deployment and execution of objects via the OWB Control Center. It also includes a good troubleshooting section with regards to deployment. As expected from a beginner's book, more advanced topics such as deployment via OMB+ and TCL scripts is not explained. What is also missing is an explanation on how to schedule objects once they have been deployed.In chapter 9 the author introduces us to various OWB features. The book gives a good overview on version management via snapshots and export/import of metadata.The book does pretty much what it says on the tin and gives a good introduction to novices in the area of data warehousing and Oracle Warehouse Builder. So if you have never used Oracle Warehouse Builder this book is for you. If you have used OWB before you will not learn anything new here. Also this book can only be a starting point for your OWB career. A lot more OWB features than are outlined in this book need to be learned to become a master in the area (if you think about it the OWB user manual in PDF format has about 1000 pages and in some areas only scratches at the surface). Also the timing of the publication of the book is a bit unfortunate as only recently OWB 11GR2 was released with a lot of important new features and a redesigned User Interface. Hopefully there will be a 2nd edition soon that addresses this shortcoming.[...].
Amazon Verified review Amazon
Get free access to Packt library with over 7500+ books and video courses for 7 days!
Start Free Trial

FAQs

What is included in a Packt subscription? Chevron down icon Chevron up icon

A subscription provides you with full access to view all Packt and licnesed content online, this includes exclusive access to Early Access titles. Depending on the tier chosen you can also earn credits and discounts to use for owning content

How can I cancel my subscription? Chevron down icon Chevron up icon

To cancel your subscription with us simply go to the account page - found in the top right of the page or at https://subscription.packtpub.com/my-account/subscription - From here you will see the ‘cancel subscription’ button in the grey box with your subscription information in.

What are credits? Chevron down icon Chevron up icon

Credits can be earned from reading 40 section of any title within the payment cycle - a month starting from the day of subscription payment. You also earn a Credit every month if you subscribe to our annual or 18 month plans. Credits can be used to buy books DRM free, the same way that you would pay for a book. Your credits can be found in the subscription homepage - subscription.packtpub.com - clicking on ‘the my’ library dropdown and selecting ‘credits’.

What happens if an Early Access Course is cancelled? Chevron down icon Chevron up icon

Projects are rarely cancelled, but sometimes it's unavoidable. If an Early Access course is cancelled or excessively delayed, you can exchange your purchase for another course. For further details, please contact us here.

Where can I send feedback about an Early Access title? Chevron down icon Chevron up icon

If you have any feedback about the product you're reading, or Early Access in general, then please fill out a contact form here and we'll make sure the feedback gets to the right team. 

Can I download the code files for Early Access titles? Chevron down icon Chevron up icon

We try to ensure that all books in Early Access have code available to use, download, and fork on GitHub. This helps us be more agile in the development of the book, and helps keep the often changing code base of new versions and new technologies as up to date as possible. Unfortunately, however, there will be rare cases when it is not possible for us to have downloadable code samples available until publication.

When we publish the book, the code files will also be available to download from the Packt website.

How accurate is the publication date? Chevron down icon Chevron up icon

The publication date is as accurate as we can be at any point in the project. Unfortunately, delays can happen. Often those delays are out of our control, such as changes to the technology code base or delays in the tech release. We do our best to give you an accurate estimate of the publication date at any given time, and as more chapters are delivered, the more accurate the delivery date will become.

How will I know when new chapters are ready? Chevron down icon Chevron up icon

We'll let you know every time there has been an update to a course that you've bought in Early Access. You'll get an email to let you know there has been a new chapter, or a change to a previous chapter. The new chapters are automatically added to your account, so you can also check back there any time you're ready and download or read them online.

I am a Packt subscriber, do I get Early Access? Chevron down icon Chevron up icon

Yes, all Early Access content is fully available through your subscription. You will need to have a paid for or active trial subscription in order to access all titles.

How is Early Access delivered? Chevron down icon Chevron up icon

Early Access is currently only available as a PDF or through our online reader. As we make changes or add new chapters, the files in your Packt account will be updated so you can download them again or view them online immediately.

How do I buy Early Access content? Chevron down icon Chevron up icon

Early Access is a way of us getting our content to you quicker, but the method of buying the Early Access course is still the same. Just find the course you want to buy, go through the check-out steps, and you’ll get a confirmation email from us with information and a link to the relevant Early Access courses.

What is Early Access? Chevron down icon Chevron up icon

Keeping up to date with the latest technology is difficult; new versions, new frameworks, new techniques. This feature gives you a head-start to our content, as it's being created. With Early Access you'll receive each chapter as it's written, and get regular updates throughout the product's development, as well as the final course as soon as it's ready.We created Early Access as a means of giving you the information you need, as soon as it's available. As we go through the process of developing a course, 99% of it can be ready but we can't publish until that last 1% falls in to place. Early Access helps to unlock the potential of our content early, to help you start your learning when you need it most. You not only get access to every chapter as it's delivered, edited, and updated, but you'll also get the finalized, DRM-free product to download in any format you want when it's published. As a member of Packt, you'll also be eligible for our exclusive offers, including a free course every day, and discounts on new and popular titles.