Working with Flat File Transactional Data
Working with Flat File Transactional Data
Flat files are a typical data source for warehouse projects.
In this lesson, you learn how to create a flat file source module. Modules
are storage objects within projects that help you logically group source and
target object definitions. Source modules hold metadata describing source systems
from which you extract data. Target modules hold metadata describing your
target data warehouse or data marts.
A flat file source module defines a connection to a folder
that contains a number of flat file sample files. You learn how to use
the wizard to import flat file metadata into the flat file source module. In
addition to hands-on practice with flat file sources, you see a demonstration of
how OWB also handles relational table database sources.
Approximately 20 minutes
Note: This tutorial and its setup scripts support OWB 11g Release 1, only. Earlier versions of this Oracle By Example tutorial are available for OWB 10g Releases 1 and 2.
This tutorial covers the following topics:
Place the cursor over this icon to load and view all
the screenshots for this tutorial. (Caution: Because this action loads all screenshots
simultaneously, response time may be slow depending on your Internet connection.)
Warehouse Builder requires metadata to describe a source
object for use within the product. For this purpose, you group sources in specific
metadata groups, called modules. A module references an OS directory or a database
schema. So when you start, you must create a flat file module, and then import
the metadata from the source. This is a similar process whether you do this
for files or for tables in a schema.
This lesson focuses on defining a source module to
store flat file metadata. You learn how
to use the Import Metadata Wizard to select the flat files that you want to import
and how to use the Flat File Sample Wizard to inspect and document metadata for flat
files. The Flat File Sample Wizard stores the metadata you define in the Warehouse
Builder repository. With the help of screenshots and instructions, you examine
an Oracle database source module.
Back to Topic List
Before starting this tutorial, you should have:
| 1. |
Downloaded the owbdemo_files.zip
file and extracted the files to a drive such as D on your computer's hard drive |
| 2. |
Completed lesson 1 and remained logged
in as rep_owner/rep_owner |
Back to Topic List
In this topic, you learn how to create a flat file
module in which you later add metadata describing your flat file sources.
The source module that you create now eventually holds the metadata specific
to flat files; therefore, this module is located in the Files node of the
Warehouse Builder Project Explorer.
| 1. |
You are logged in to the
Design Center as rep_owner/rep_owner. In the Project Explorer panel,
expand the OWB_DEMO project. Right-click the Files
node and select New.
The Create Module Wizard: Welcome page appears. Click Next.
The Create Module Wizard: Name and Description page appears.
|
| 2. |
On the Create Module Wizard: Name and Description
page, enter SOURCE as the name for the module. Click
Next.
The Create Module Wizard: Connection Information page appears. In a
flat file source module, Location is the path or the directory
in the file system where the flat files reside.
|
| 3. |
On the Create Module Wizard: Connection
Information page, OWB creates a default location, SOURCE_LOCATION1.
Click Edit.
The Edit File System Location: SOURCE_LOCATION1 window appears.
|
| 4. |
In the Edit File System Location: SOURCE_LOCATION1
window, rename the default name SOURCE_LOCATION1 to SOURCE_LOCATION.
Click Browse.
In the Browse
Directory window, select D:\newowbdemo\sourcefiles
or the drive and directory (which
contains the source folders) where you saved the setup scripts. Click Select.
|
| 5. |
In the Edit File System Location: SOURCE_LOCATION
window, examine the path and click OK.
|
| 6. |
In the Connection Information window, ensure that Import
after finish is selected. Selecting this option launches the
Import Metadata Wizard. Click Next.
Tip: If the check box is not selected, the Import Metadata
Wizard does not launch automatically. To launch the Import Metadata
Wizard manually, in Project Explorer, expand Files,
right-click the SOURCE module, and select Import.
The Create Module: Summary page appears.
|
| 7. |
On the Summary page, review the module settings. Click
Finish.
The flat file module definition is complete. The Import Metadata Wizard is launched automatically. If you temporarily move this wizard to the side, you can see in the Design Center that the source module has been created in the Files node of the Project Explorer.
|
Back to Topic List
After you create a flat file module, you can import flat file
definitions into Warehouse Builder. The Import Metadata Wizard guides you through
the process of importing metadata objects. Each time you use the Import Metadata
Wizard to import metadata from existing flat files, the Import Metadata Wizard
launches the Flat File Sample Wizard. Use the Flat File Sample Wizard to inspect
and document metadata for flat files.
Back to Topic List
Using the Import Metadata Wizard
| 1. |
On the Import Metadata
Wizard: Welcome page, click Next.
The Import Metadata Wizard: Filter Information page appears.
|
| 2. |
On the Import Metadata Wizard: Filter Information
page, accept the default selection All Data Files and
click Next.
The Import Metadata Wizard: Object Selection page appears.
|
| 3. |
On the Import Metadata Wizard: Object Selection
page, expand the D:\newowbdemo\sourcefiles node (or whatever other location
where you unzipped the setup files in Lesson 1). A list of files is displayed.
Use >> (Add All) to move all file definitions
from the Available list to the Selected list. Click Next.
The Import Metadata Wizard: Summary and Import page
appears.
|
| 4. |
Note: On this page, you see two file definitions (expense_categories_csv
and export_csv) prefixed with a red cross. The red cross indicates that
there is no metadata information available for this file within the Warehouse
Builder repository. Before importing, Warehouse Builder needs to know
the structure of these files. Through the sampling process, information
about the structure is gathered.
On the Import Metadata Wizard: Summary and Import page, select expense_categories_csv
file , if it is not already selected. Click Sample.
The Flat File Sample Wizard appears.
|
Back to Topic
Using the Flat File Sample Wizard
| 1. |
The Flat File Sample Wizard
guides you through the definition of a flat file's structure. On the
Flat File Sample Wizard: Welcome page, click Next.
The Flat File Sample Wizard: Name page appears.
|
| 2. |
On the Flat File Sample Wizard: Name page,
accept EXPENSE_CATEGORIES_CSV as the default file's name. This is the
name by which Warehouse Builder refers to the file after it is imported.
Warehouse Builder offers a large number of character sets, which you could
use to read the data.
Accept the defaults on this page. This file has two
columns: Expense Type and Expense Categories. The wizard makes
assumptions about the structure of the flat file. The lower portion
of the dialog box displays a sample of how OWB imports the rows of the
flat file data into columns in a table. Click Next.
The Flat File Sample Wizard: Record Organization page appears.
|
| 3. |
Note: You use the Record Organization
page to indicate how records are organized in the file you are sampling.
On the Flat File Sample Wizard: Record Organization page, accept the
default option of Records delimited by carriage return (<CR>).
Click Next.
The Flat File Sample Wizard: File Format page appears.
|
| 4. |
Note: On this page, you specify the
formatting for the file structure. Fixed length files, as well as character-delimited
files, are supported.
Tip: If your files have a different delimiter, you
can enter it in the Field Delimiter drop-down list. Warehouse Builder
uses the delimiter that you entered.
On the Flat File Sample Wizard: File Format page, accept the default
option of Delimited for file format. Click Next.
The Flat File Sample Wizard: File Layout page appears.
|
| 5. |
Note: On this page, you can specify
the number of rows to skip. You can select between a single record type
and multiple record types.
On the Flat File Sample Wizard: File Layout page,
accept the default values of 0 for Skip Rows. The expense_categories.csv
file is an example of a single record type file. Select Single
Record for record type and click Next.
The Flat File Sample Wizard: Field Properties page appears.
|
| 6. |
You can use the values in the first record for the field
names, when the flat file's first record is a header. On the Flat File
Sample Wizard: Field Properties page, select Use the first record
as the field names.
In the following screenshot, notice that the field names have changed.
Tip: Notice the SQL data types to the right; these
allow you to control, for example, the external table data types based
on this file.
Click Next.
The Flat File Sample Wizard: Summary page appears.
|
| 7. |
Review the summary specifications that you have defined for
the expense_categories_csv file. On the Flat File Sample Wizard: Summary
page, click Finish.
The wizard creates the flat file. The Flat File Sample Wizard returns
you to the Import Metadata Wizard.
|
| 8. |
On the Import Metadata Wizard: Summary and Import page,
in the Status field for expense_categories_csv, the green check mark
indicates that expense_categories_csv has been sampled.
You now proceed to sample the second flat file.
|
| 9. |
Select the export_csv file if not already selected. Click Sample.
Tip: To save time, you can select Same As <FileName
> from the Same As drop-down list. (You must use the Same
As option when the file to be sampled is similar in structure to any
of the previously sampled files.) In this example, the structures of the
two files to be sampled are not similar.
The Flat File Wizard: Welcome page appears. Click Next.
|
| 10. |
On the Flat File Sample Wizard: Name page, accept EXPORT_CSV
as the default file's name. This is the name by which Warehouse Builder
refers to the file after it is imported.
Accept the defaults on this page. This file has 14
columns. Click Next.
The Flat File Sample Wizard: Record Organization page appears.
|
| 11. |
Note: You use the Record Organization
page to indicate how records are organized in the file that you are sampling.
On the Flat File Sample Wizard: Record Organization page, accept the
default option of Records delimited by carriage return (<CR>).
Click Next.
The Flat File Sample Wizard: File Format page appears.
|
| 12. |
Note: On this page, you specify the
formatting for the file structure. Fixed length files, as well as character-separated
files, are supported.
On the Flat File Sample Wizard: File Format page, accept the default
option of Delimited for file format. Click Next.
The Flat File Sample Wizard: File Layout page appears.
|
| 13. |
Note: On this page, you can specify
the number of rows to skip. You can select between a single record type
and multiple record types.
On the Flat File Sample Wizard: File Layout page,
accept the default value of 0 for Skip Rows. The export.csv
file is an example of a single record type file. Select Single
Record for Record Type and click Next.
The Flat File Sample Wizard: Field Properties page appears.
|
| 14. |
On the Flat File Sample Wizard: Field Properties page,
select Use the first record as the field names. You
can choose to use the values in the first record for the field names,
when the flat file's first record is a header.
Notice that the field names have changed.
|
| 15. |
Note: Do not click the Back button on the wizard page
unless you have finished step 15. This might result in an error because
date is a reserved word and you have not yet renamed the date field.
On the Flat File Sample Wizard: Field Properties page, select Date.
Rename Date to Expense_Date because Date is a reserved
word.
From the Type drop-down list, select Date. In the Mask
column, enter dd-mon-yy (without single quotation marks).
|
| 16. |
On the Flat File Sample Wizard: Field Properties page,
select Reimbursable_Amount_(USD).
Rename Reimbursable_Amount_(USD) to Reimbursable_Amount, because '('
')' are both reserved characters.
From the Type drop-down list, select Decimal External.
Note: Scroll to the right in this dialog box to view
SQL Properties. The wizard creates default SQL Properties on the basis of your
selection in the Type column.
Click Next. The Flat File Sample Wizard: Summary page
appears.
|
| 17. |
Review the summary specifications that you defined for the
EXPORT_CSV file. On the Flat File Sample Wizard: Summary
page, click Finish.
The wizard creates the flat file. The Flat File Sample Wizard returns
you to the Import Metadata Wizard.
|
| 18. |
On the Import Metadata Wizard Summary and Import page, the green
check mark in the Status field for both EXPENSE_CATEGORIES_CSV and
EXPORT_CSV indicates that both the files have been sampled. On the Import
Metadata Wizard: Summary and Import page, click Finish.
The import begins. Importing Progress Dialog shows the
import progress.
In Project Explorer, expand Files > SOURCE.
You see the two tables (EXPENSE_CATEGORIES_CSV and EXPORT_CSV) you just
sampled and imported.
In the Design Center, click Save All 
on the toolbar. In the Warehouse Builder Warning window, click Yes
to commit your work.
|
Back to Topic
In addition to flat file metadata sources, you create
a module for various other sources, such as for Oracle-based relational table
source metadata. The topics covered assumed that your source data was present in the
form of flat files.
In this subtopic, you read a demonstration of how you can
import your data from an Oracle source. In this example, you import data from
a schema in an Oracle database that stores HR data. The following steps are for
read-only and not to be performed. With the help of the instructions and screenshots,
you can assimilate the concepts.
| 1. |
Again, please note:
The following steps are for you to read only, and are not to be performed.
In this step, you see how the Oracle module is created.
In Project Explorer, expand project OWB_DEMO > Databases.
Right-click Oracle and select New. In the Create Module -Welcome page, click Next.
On the Create Module Wizard: Name and Description page,
enter HR as the name for the Oracle data source module. The module
type is Data Source. Click Next.
|
| 2. |
The wizard's next page is the Connection Information page. OWB creates
a location name for every module created. As you see in the screenshot,
the location name is HR_LOCATION1. Locations are specific to a type of
module, such as Oracle database or flat file.
|
| 3. |
Clicking Edit launches the Edit Oracle Database location
page. On the Edit Oracle Database location page, rename the default location
HR_LOCATION1 as HR_LOCATION. You also provide the connection
information (username, password, host, port, and service name) that specifies
what the source of the metadata is going to be. In this example, the
connection details provided are to connect to the HR schema.
Tip: You can use various connection types:
|
HOST:PORT:SERVICENAME |
| |
Database Link |
| |
SQL*NET Connection |
This enables you to configure this (for example, for RAC) where you use
TNS Names entries and not reference a direct single node.
Click Test Connection. The connection information that you provided
is tested, and the message "Successful" is displayed if the
connection is successful. Click OK.
|
| 4. |
In the Connection Information page, ensure that the Import after finish
option is selected to proceed to the Import Metadata Wizard.
Click Next.In Summary page, click Finish.
The Import Metadata wizard welcome page displays. Click Next.
|
| 5. |
Using the Import Metadata Wizard, you import metadata from a database
into a module. You select the objects that you want to import into the
HR Oracle source module.
In the Filter Information page, you select the type of objects you want
to import.
Click Next. In the Object Selection page, you select the specific
objects you want to import.
Warehouse Builder imports all definitions, including
constraints (if any). In this example, you selected COUNTRIES table and
Warehouse Builder identified REGIONS table related by a foreign key. In
the Confirm Import Selection dialog box, click OK to import the
dependent REGIONS table. Click Next.
|
| 6. |
In the Summary and Import page, verify that you
have selected all the objects that you want to import into Warehouse Builder.
You may edit description field of objects.
Click Advanced Import Options to examine
various import options, as shown in the screenshot.
Click OK. Click Finish.
In the Import Results page, click OK.
You have now completed the import of the Oracle
database definitions. In the Oracle Warehouse Builder client, examine
the imported metadata. Expand the HR module and examine the metadata
that is imported for the tables.
|
Back to Topic List
In this lesson, you've learned how to:
| |
Create a Flat File Source Module. A
file module defines a connection to a folder that contains a number
of flat file sample files. |
|
Create a definition for a flat file using the following
wizards: |
| |
Import Metadata Wizard to import flat files |
|
| Flat File Sample Wizard to sample flat files |
| |
Briefly examine an Oracle Database Module
|
Back to Topic List
|
Bookmark
