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

Topics

This tutorial covers the following topics:

Creating a Flat File Source Module

Importing Flat File Metadata

Examining an Oracle Database Module

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.)

Overview

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 the drive D on your computer's hard drive

2.

Completed lesson 1 and remained logged in as rep_owner/rep_owner

Back to Topic List

Creating a Flat File Source Module

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.

Place the cursor over this icon to see the image

The Create Module Wizard: Welcome page appears. Click Next.

Place the cursor over this icon to see the image

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.

Place the cursor over this icon to see the image

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.

Place the cursor over this icon to see the image

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.

Place the cursor over this icon to see the image

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.

 

5.

In the Edit File System Location: SOURCE_LOCATION window, examine the path and click OK.

Place the cursor over this icon to see the image

 

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.

Place the cursor over this icon to see the image

The Create Module: Summary page appears.

 

7.

On the Summary page, review the module settings. Click Finish.

Place the cursor over this icon to see the image

The flat file module definition is complete. In the Design Center, notice the source module created in the Files node of the Project Explorer. The Import Metadata Wizard is launched automatically.

 

Back to Topic List

Importing Flat File Metadata

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.

Using the Import Metadata Wizard

Using the Flat File Sample Wizard

Back to Topic List

Using the Import Metadata Wizard

1.

On the Import Metadata Wizard: Welcome page, click Next.

Place the cursor over this icon to see the image

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.

Place the cursor over this icon to see the image

The Import Metadata Wizard: Object Selection page appears.

 

3.

On the Import Metadata Wizard: Object Selection page, expand the D:\newowbdemo\sourcefiles node (the location where you unzipped the setup files). A list of files is displayed. Use >> (Add All) to move all file definitions from the Available list to the Selected list. Click Next.

Place the cursor over this icon to see the image

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.

Place the cursor over this icon to see the image

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.

Place the cursor over this icon to see the image

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.

Place the cursor over this icon to see the image

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.

Place the cursor over this icon to see the image

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.

Place the cursor over this icon to see the image

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.

Place the cursor over this icon to see the image

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.

Place the cursor over this icon to see the image

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.

Place the cursor over this icon to see the image

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.

Place the cursor over this icon to see the image

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.

Place the cursor over this icon to see the image

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.

Place the cursor over this icon to see the image

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.

Place the cursor over this icon to see the image

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.

Place the cursor over this icon to see the image

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.

Place the cursor over this icon to see the image

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 expense_categories.csv file is an example of a single record type file. Select Single Record for Record Type and click Next.

Place the cursor over this icon to see the image

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 files first record is a header.

Place the cursor over this icon to see the image

Notice that the field names have changed.

Place the cursor over this icon to see the image

 

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).

Place the cursor over this icon to see the image

 

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.

Place the cursor over this icon to see the image

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.

Place the cursor over this icon to see the image

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.

Place the cursor over this icon to see the image

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.

Place the cursor over this icon to see the image

In Project Explorer, expand Files > SOURCE. You see the two tables (EXPENSE_CATEGORIES_CSV and EXPORT_CSV) you just sampled and imported.

Place the cursor over this icon to see the image

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

Examining an Oracle Database Module

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 see 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.

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.

Place the cursor over this icon to see the image

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.

Place the cursor over this icon to see the image

 

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.

Place the cursor over this icon to see the image

 

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.

Place the cursor over this icon to see the image

 

4.

In the Connection Information page, ensure that the Import after finish option is selected to proceed to the Import Metadata Wizard.

Place the cursor over this icon to see the image

Click Next.In Summary page, click Finish.

Place the cursor over this icon to see the image

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.

Place the cursor over this icon to see the image

Click Next. In the Object Selection page, you select the specific objects you want to import.

Place the cursor over this icon to see the image

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.

Place the cursor over this icon to see the image

Click Advanced Import Options to examine various import options, as shown in the screenshot.

Place the cursor over this icon to see the image

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.

Place the cursor over this icon to see the image

 

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