Defining and Importing Source Data Structures (Oracle Warehouse Builder 11gR2)

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 other files that users keep, which might be a source of information.

A flat file is a file in text format that stores data in some kind of delimited format. The most common example of this kind of file is a CSV file, or a comma-separated file, that can be saved from a spreadsheet or extracted from a database table. It is called a flat file because it is in a text-only format and doesn’t need to be interpreted by another program or application to read it.

The Warehouse Builder can help us with importing data from any (or all) of these formats into our data warehouse, and we’re going to see how to do that in this topic by covering the following major topics:

• Analysis of the source systems for the data warehouse we’ll be building

• The Point of Sale transactional database

• A website orders database

• An overview of the Warehouse Builder Design Center

• Importing and defining source database object metadata

• Creating projects and modules in OWB, including Oracle and SQL Server modules

• Creating a SQL Server database connection using an ODBC gateway

• Configuring Oracle Heterogeneous Services for the ODBC gateway

• Defining source metadata manually with table editor

• Importing source metadata from a file

Next post:

Previous post: