4
Importing Data Definitions

Figure 4-15 Workarea Selection List

Text description of the illustration nmwworka.gif

10. To specify the Application System, click Change and choose one from the selection list, as shown in Figure 4-16.

Figure 4-16 Application Systems Selection List

Text description of the illustration nmwappli.gif

11. Click Next.

    The wizard displays the Finish page. This page summarizes the information you entered on each of the wizard pages. Verify the information.

12. Click Finish.

    The wizard creates the source module and inserts its name in the project navigation tree.

To import definitions into this source module from a Designer 6i source, you must follow the steps outlined for importing definitions from database sources.

For related information, see the following sections:

• Importing Definitions from a Database

• Re-Importing Definitions from an Oracle Database

About Flat File Modules

A project may need to extract data from or write data to flat files. You can access the flat file directly or by using the External Table operator. This section focuses on the basics of creating a flat file module. This information also serves as the basis for creating an external table from Warehouse Builder. For additional information on external tables, see "Using External Tables".

When you use a flat file, understanding both flat files and external tables will help you determine which feature to use. If you are loading large volumes of data, loading to a flat file allows you to use the DIRECT PATH SQL*Loader option, which results in better performance. If you are not loading large volumes of data, you can benefit from many of the relational transformations that you can apply to an external table. Refer to "External Tables versus Flat File Operators" for more information.

If you are accessing the data files directly, and if the Warehouse Builder client and the data files reside on different types of operating systems, contact your system administrator to establish the required connectivity through NFS or other network protocol. If the Warehouse Builder client and data files reside on a Windows operating system, store the data files on any drive locally accessed from the Warehouse Builder client machine.

Creating Flat File Modules

Once you create a flat file module, you can import flat file definitions into Warehouse Builder. For more information on importing flat files into Warehouse Builder, see "About Flat File Sources and Targets".

1. Right click on the Files node in the Warehouse Builder Console and select Create Flat File Module, as shown in Figure 4-17.

Figure 4-17 Creating a Flat File Module

Text description of the illustration flatfile.gif

Warehouse Builder displays the welcome page for the New Module Wizard.

2. Click Next.

   The New Module Wizard displays the Name page.

3. On the Name page, type a name for the module and indicate the status of the module. Specify the status as Development, Quality Assurance, or Production. This status is for descriptive purposes only. Optionally provide a description for the module.

4. Click Next.

   The wizard displays the Connection Information page.

5. Enter the fully qualified directory, including the drive letter, if appropriate, that contains the file.

6. Click Next.

   The Logical Location page displays.

   Locations define information about the database schema or target tool where you will be deploying objects. Locations are specific to a type of module such as Oracle Database, SAP, or flat file. When you create a location, a logical definition containing location type and version will be stored. When the location is deployed, the physical connection information is obtained from the Runtime Instance it is deployed to and that information is stored in the Runtime Repository.

7. Use this page to specify a location for the module. Click New to create a new location or choose from a list of previously specified locations in the drop-down menu. This step is optional. You can choose to create a location for this module later when you are deploying the object.

8. Click Next.

   The Finish page summarizes the information you provided on each of the wizard pages. Check the checkbox if you want to immediately start the Import Metadata Wizard.

9. Click Finish.

   The wizard creates the flat file module and inserts its name in the project navigation tree. If you checked the checkbox, Warehouse Builder starts the Import Metadata Wizard.

About Flat File Sources and Targets

Before you can create a definition for a flat file, you must first create a file module and a file location. To create a definition for a flat file, use the following wizards:

• The Import Metadata Wizard. Use the Import Metadata Wizard to select the flat files you wish to import.

• The Flat File Sample Wizard. When you finish selecting flat files in the Import Metadata Wizard, the Import Metadata Wizard launches the Flat File Sample Wizard. Use the Flat File Sample Wizard to view a sample of the flat file and define record organization and file properties.

You can update a file definition after you import it into Warehouse Builder. You can also use data from a flat file as a source or a target after you import its structure into Warehouse Builder.

This section includes:

• Using the Import Metadata Wizard Flat Files

• Using the Flat File Sample Wizard

For related information, see the following sections:

• Creating Flat File Modules

• Updating a File Definition

• Mapping Flat File Operator

• Reconciling Operators and Repository Objects

Using the Import Metadata Wizard Flat Files

The following section describes how to create a definition for a flat file using the Import Metadata Wizard.

1. Right-click on a file module and select Import.

   Warehouse Builder displays the welcome page for the Import Metadata Wizard.

2. Click Next.

   The wizard displays the Filter Information page. This page gives you the option to filter file names.

   All Data Files: This option returns all the data files available for the directory you specified for the flat file module.

   Data files matching this pattern: Use this option to select only data files that match the pattern you type. For example, if you select this option and enter (*.dat), only files with .dat file extensions will be displayed on the next wizard page. If you type % as part of a filter string, it is interpreted as a wild card match for multiple characters. If you type '_' as part of a filter string, it is interpreted as a wild card match for a single character.

3. Click Next.

   The wizard displays the Object Selection page, as shown in Figure 4-18.

   Because Warehouse Builder does not provide inbound reconciliation for flat files, the available objects will never appear in bold like other objects when they are reimported. When you reimport flat files, you always need to resample the flat file objects again

Figure 4-18 Object Selection Page

Text description of the illustration mdffsdir.gif

4. Move the name of the files to be described from Available Objects on the left to the Selected Objects window pane on the right.

5. Click Next.

   The wizard displays the Summary and Import page. The left-most column of this page contains a status ball which indicates if Warehouse Builder has the metadata for the file, as shown in Figure 4-19.

   If the status ball is red, Warehouse Builder does not have the metadata. Proceed to the next step.

6. For each file on the Summary and Import page, either click Sample or select a file with a matching format from the Same As list box. If the format for a file matches that of another file on the Summary and Import page, you can select the file name from the Same As list box.

Figure 4-19 Summary and Import Page Showing File Status

Text description of the illustration wzmfffb3.gif

7. Select a file that has a red status ball and click Sample at the bottom of the Summary and Import page.

   The wizard displays the welcome page for the Flat File Sample Wizard. Complete the Flat File Sample Wizard. For more information on the Sample Wizard, see Using the Flat File Sample Wizard.

8. After you complete the Flat File Sample Wizard for one file, Warehouse Builder returns to the Summary and Import page. The file you sampled is now displayed with a green status ball.

9. If the status ball is green for all the files you want to import, click Finish. Warehouse Builder creates a definition for file, stores the definition in the source module, and inserts the format name in the source module navigation tree.

Using the Flat File Sample Wizard

Each time you use the Import Metadata Wizard to sample data from existing flat files, the Import Metadata Wizard launches the Flat File Sample Wizard. Use the Flat File Sample Wizard as an aid in defining metadata for flat files. The Flat File Sample Wizard stores the metadata you define in the Warehouse Builder repository.

To utilities the sampled flat file object in a mapping, you must run the Create External Table Wizard and create an external table based on this flat file. This new external table is then available for use in mappings as the external table operator. For information on using external tables versus using flat file operators, see "External Tables versus Flat File Operators".

The Flat File Sample Wizard guides you in completing the following steps:

• Describing the Flat File

• Selecting the Record Organization

• Selecting the File Layout

• Selecting the File Format

• Selecting Record Types (Multiple Record Type Files Only)

• Specifying Field Lengths (Fixed-Length Files Only)

• Specifying Field Properties

Describing the Flat File

Use the Name page shown in Figure 4-20 to describe the flat file you are sampling.

Figure 4-20 Name Page for the Flat File Sample Wizard

Text description of the illustration 1_name.gif

• Name: This is the name by which Warehouse Builder will refer to the file after it is imported. By default, the wizard creates a name based on the name of the source file. You can change the file name on this page.

  If you rename the file, do not include a space or any punctuation in the name. You can include an underscore. You can use upper and lower case letters. Do not start the name with a digit. Do not use a Warehouse Builder reserved word. For a list of reserved words, see Appendix B, "Reserved Words".

• Description: You can type in an optional description for the file.

• Character set: Character sets determine what languages can be represented in database objects and files. In the Warehouse Builder client, the default National Language Support (NLS) character set matches the character set defined for the machine hosting Warehouse Builder. If the character set differs from that of the source file, the data sample might appear unintelligible. You can display the data sample in the character set native to the source by selecting it from the drop-down list. For complete information on NLS character sets, see the Oracle9i Globalization Support Guide.

• Number of rows to sample: You can indicate the number of rows for the wizard to sample from the data file. By default, the wizard samples the first 200 rows. To determine an optimum value for this field, see "Example: Flat File with Multiple Record Types".

After you have completed the file set up information, click Next to continue with the wizard.

Selecting the Record Organization

Use the Record Organization page shown in Figure 4-21 to indicate how records are organized in the file you are sampling.

Figure 4-21 Record Organization Page for the Flat File Sample Wizard

Text description of the illustration 2_record.gif

Select between the two options to indicate how the length of each record in the file is determined:

• Records delimited by: Select this option if the end of each record is designated by a delimiter. Then specify that record delimiter. You can accept the default record delimiter, carriage return (<CR>), or you can type in a new value.

• Record length (in characters): Select this option if each record in the file is the same length. Then specify the number of characters in each record. For files with multibyte characters, count a multibyte character as one character.

Specifying Logical Records

The Flat File Sample Wizard allows you to sample files composed of logical records that correspond to multiple physical records. If the file contains logical records, click File contains logical records. Then select one of the options to describe the file.

The wizard updates the display of the logical record in the lower panel to reflect your selection. The default selection is one physical record per logical record.

After you complete the logical record information, click Next to continue with the wizard.

• Number of physical records per logical record: The data file contains a fixed number of physical records for each logical record.

  PHYSICAL_RECORD1
  PHYSICAL_RECORD2
  PHYSICAL_RECORD3
  PHYSICAL_RECORD4
  
  

  In the above example, if the number of physical records per logical record is 2, then PHYSICAL_RECORD1 and PHYSICAL_RECORD2 form one logical record and PHYSICAL_RECORD3 and PHYSICAL_RECORD4 form a second logical record.

• End character of the current physical record: The data file contains a variable number of physical records with a continuation character at the end that signifies that the record is associated with the next physical record.

  In the following example, the continuation character is a percentage sign (%) at the end of the record.

  PHYSICAL_RECORD1%
  PHYSICAL_RECORD2       end log rec 1
  PHYSICAL_RECORD3%
  PHYSICAL_RECORD4       end log rec 2
  
  

• Start character of the next physical record: The data file contains a variable number of physical records with a continuation character at the beginning of each physical record that signifies that the record is associated with the previous physical record.

  The following example shows two logical records with a continuation character at beginning of the record.

  PHYSICAL_RECORD1
  %PHYSICAL_RECORD2      end log rec1
  PHYSICAL_RECORD3
  %PHYSICAL_RECORD4      end log rec 2
  
  

  More than two records can be joined with this technique. The following example shows four physical records per logical record using continuation at beginning.

  PHYSICAL_RECORD1
  %PHYSICAL_RECORD2
  %PHYSICAL_RECORD25
  %PHYSICAL_RECORD26   (end log record 1)
  PHYSICAL_RECORD3
  %PHYSICAL_RECORD4
  %PHYSICAL_RECORD45
  %PHYSICAL_RECORD46 (end log record 2)
  

Selecting the File Layout

Use the File Layout page shown in Figure 4-22 to specify the number of rows to skip and to select between a single record type versus multiple record types.

Figure 4-22 File Layout Page for the Flat File Sample Wizard

Text description of the illustration 3_filela.gif

Indicate the number of records to skip in Skip rows.This is useful for skipping over unwanted header information. If one of the records includes field names, skip the preceding header records so that the record containing field names is first in the file. Later in the wizard, on the Field Properties page, you can instruct the wizard to use that record for field names if you are defining a single record file type.

Indicate whether the file contains a single record type or multiple record types.

• If the file contains only one type of record, select Single.

• If the file contains more than one record type, select Multiple. Later in the wizard you can instruct the wizard to scan the file for the record types. For more information on multiple record types, see "Selecting Record Types (Multiple Record Type Files Only)".

Selecting the File Format

Use the File Format page shown in Figure 4-23 to select between Fixed Length and Delimited formats for the file.

Figure 4-23 File Format Page for the Flat File Sample Wizard

Text description of the illustration 4_filefo.gif

When you select a file format, the wizard updates the sample displayed at the bottom of the wizard page. You can use the scroll bars to navigate the sample data.

When your file is delimited, specify the following properties:

• Field delimiter: Field delimiters designate where one field ends and another begins. You can type in a field delimiter or select one from the drop-down list. The drop-down list displays common field delimiters. However, you may type in any character as a delimiter except the ones used for enclosures. The default is the comma (,).

• Enclosures (Left and Right): Some delimited files contain enclosures that denote text strings within a field. If the file contains enclosures, enter an enclosure character in the text box or select one from the drop-down list. The drop-down list displays common enclosures. However, you may type in any character. The default for both the left and right enclosure is the double quotation mark (").

Selecting Record Types (Multiple Record Type Files Only)

Use the Record Types wizard page to scan the flat file for record types, add or delete record types, and assign type values to the record types.

Example: Flat File with Multiple Record Types

In files with multiple record types, one of the fields distinguishes one record type from the next. Figure 4-24 shows an example of a comma delimited file with two record types, "E" and "P". When you use the Flat File Sample Wizard, you instruct the wizard to scan a specified field of every record for the record type values. In this case, instruct the wizard to scan the first field. The wizard returns "E" and "P" as the type values.

Figure 4-24 Example of a File with Multiple Record Types

Text description of the illustration multirec.gif

When you use the wizard to sample flat files with multiple record types, ensure that the sample size you specified on the Name page is large enough to include each record type at least once.

Because sampling cannot be cancelled after it has been started, make sure you pick a "reasonable" number of rows for optimum performance. If all record types do not appear within a reasonable number of rows, you can mock up a sample file with rows selected from different parts of the master file to provide a representative set of data. If you do not know your data well, you may choose sample the entire file. If you know your data well, you can scan a representative sample and then manually add new record types.

Defining Multiple Record Organization in a Delimited File

When a delimited flat file contains several different types of records, you can use the scanning feature within the Flat File Sample Wizard to search and label record types.

Figure 4-25 shows the first field position selected for scanning for multiple record types.

Figure 4-25 Record Types Page for Delimited Files

Text description of the illustration 5del_rec.gif

1. Select the one field that identifies the record types in the file.

   The wizard displays all the fields in a sample in the lower panel of the page. Select the field from the sample box. Or, in Field position, you can type in the position as it appears in the sample. Unless you specify otherwise, the wizard defaults to the first field in the file.

   The wizard scans the file for the field and displays the type values. The wizard assigns default record names (RECORD1, RECORD2...) to each type value.

2. You can edit the record names.

   Click on a record name to rename it or select a different record name from the drop down list. You can associate a record name with multiple record type values. You can also add or delete type values using the New and Delete buttons.

3. Click Next to continue with the wizard.

Defining Multiple Record Organization in a Fixed-Length File

When a fixed-length flat file contains several different types of records, you can use the scanning feature within the Flat File Sample Wizard to search for record types and assign a type value to each record type.

Figure 4-26 shows the results from scanning for record types based on the first field position.

Figure 4-26 Record Types Page for Fixed Length Files

Text description of the illustration 5fx_reco.gif

1. Specify the one field that identifies the record types in the file. Type in values for the Start position and End position. If you want to scan for records based on the first field, enter 0 for Start Position.

   The wizard indicates the selected field with a red tick mark in the ruler in the file sample in the lower panel of the page.

2. Click Scan.

   The wizard scans the file field and displays the type values. The wizard assigns default record names (RECORD1, RECORD2...) to each type value.

3. You can edit the record names.

   Click on a record name to rename it or select a different record name from the drop down list. You can associate a record name with multiple record type values. You can also add or delete type values using the New and Delete buttons.

4. Click Next to continue with the wizard.

Specifying Field Lengths (Fixed-Length Files Only)

When you use the Flat File Sample Wizard to define a fixed-length flat file, you also need to define the length of each field in the file.

You can define field lengths by typing in the field lengths or by using the ruler.

Figure 4-27 displays the Field Lengths page from the Flat File Sample Wizard.

Figure 4-27 Field Lengths Page for the Flat File Sample Wizard

Text description of the illustration 6_fieldl.gif

If you know the length of each field, type in the field length in Field Lengths. Separate each length by commas. The wizard displays the changes to the sample at the bottom of the wizard page.

To use the ruler, click on any number or hash mark on the ruler. The wizard displays a red tick mark on top of the ruler and marks the boundary with a red line. If you make a mistake, double-click the marker to delete it or move the marker to another position. Use the ruler to create markers for each field in the file.

Note that when you specify a field length using the ruler, your tick markers indicate the starting and ending borders for each field. From this information, Warehouse Builder determines the positions occupied by each field. For example, a three-character field occupying positions 6, 7, and 8 is internally identified with the beginning and ending values of '5,8'.

If you define field positions for a file in OMB Plus and later look at the properties of that same file in the Warehouse Builder user interface Flat File Properties screen, the field definition appears different. The same three-character record that you defined in OMB Plus with the beginning and ending values of '6,8' appears with the positions '5.8' in the Flat File Properties screen.

Specifying Field Lengths for Multiple Record Files

You can select the record type by name from Record Name. Or, you can select Next Record Type from the lower right corner of the wizard page. The number of records with unspecified field lengths is indicated on the lower left corner of the wizard page.

If the flat file contains multiple record types, the wizard prompts you to specify field lengths for each record type before continuing.

Figure 4-28 shows the Field Lengths page for a fixed length file with multiple record types.

Figure 4-28 Field Lengths for Multiple Record Files page

Text description of the illustration 5fx_reca.gif

Specifying Field Properties

Use the Properties page in the Flat File Sample Wizard to define properties for each field.

The wizard deactivates properties that do not apply to a given data type. For example, you can edit the length for a CHAR, but precision and scale are not available. Deactivated properties are grayed out.

Figure 4-29 shows the Properties page for the Flat File Sample Wizard.

Figure 4-29 Properties Page for the Flat File Sample Wizard

Text description of the illustration 7_prop.gif

For each field, the wizard displays the following two sets of properties:

• SQL*Loader Field Properties: SQL*Loader properties refer to data types supported in flat files using the SQL*Loader utility. In Warehouse Builder this implies every mapping that uses flat file as a source. The wizard displays this set of properties first. SQL*Loader properties include Name, Type, Mask, NULLIF, DEFAULTIF, Start, and Length. The Precision and Scale fields are reserved for future use.

• SQL Properties: SQL Properties refer to relational data type defaults associated with a given flat file field. You can choose a default data type value that will be automatically updated with any changes to the flat file, or the SQL Loader, data type. You can also override these defaults with any value. These values will then become independent of the flat file data type. These properties will be the default column attributes when you map a flat file to an unbound table or when you create an external table. The wizard displays this set of properties second. SQL properties include SQL Type, SQL Length, SQL Precision, and SQL Scale.

  Note: Once you complete the Field Properties page, verify your selections on the Summary page and select Finish. The Flat File Sample Wizard returns you to the Import Metadata Wizard. You can select more files to sample or select Finish to begin the import. For information on how to continue sampling files, see step 8 in "Using the Import Metadata Wizard Flat Files".

SQL*Loader Properties

By default, the wizard displays these properties as they appear in the source file. Edit these properties or accept the defaults to specify how the fields should be handled by the SQL*Loader.

You can edit the following SQL* Loader properties:

Name

The wizard assigns a name to each field. It assigns `C1'to the first field, `C2' to the second, etc. To rename fields, click on a field and type a new name.

For single record file types, you can instruct the wizard to use the first record in the file to name the fields. Indicate this by checking the box entitled Use the first record as the field names. If you choose this option, all the field data type attributes will default to CHAR.

Type

Describes the data type of the field for the SQL*Loader. You can use the Flat File Sample Wizard to import the following data types: CHAR, DATE, DECIMAL EXTERNAL, FLOAT EXTERNAL, INTEGER EXTERNAL, ZONED , AND ZONED EXTERNAL. For complete information on SQL*Loader field and data types, refer to Oracle9i Utilities. Currently, only portable datatypes are supported.

Mask

The SQL*Loader uses dd-mon-yy as its default date mask. You can override this default by entering a valid date mask when you describe the file. For example, if the input data has the format DD-Mon-YYYY rather than the SQL*Loader default, you can enter the true format as a mask.

NULLIF

You can override the default action of the SQL*Loader by placing a NULLIF condition on a field. For example, when a character field contains all blanks, you can direct SQL*Loader to mark the field as null rather than storing the blanks. Valid syntax for this field is: =BLANKS, ='quoted string', =X'ff' (hex value), != (not equal) is also allowed.

DEFAULTIF

You can override the default action of the SQL*Loader by placing a DEFAULTIF condition on a field. For example, when a numeric or DATE field contains all blanks, SQL*Loader rejects the entire record. To override this action, type BLANKS in the DEFAULTIF property. When SQL*Loader evaluates this condition, it sets the numeric field to zeros and loads the record. Valid syntax for this field is: =BLANKS, ='quoted string', =X'ff' (hex value), != (not equal) is also allowed.

Start

In fixed length files, indicates the field start position.

Length

Specifies the length of the field to be used by SQL* Loader. For delimited files, the field length is not populated, but you can manually edit it if you know the maximum length of the field.

Precision

Defines precision for DECIMAL and ZONED data types. This field is reserved for future use.

SQL Properties

The Flat File Sample Wizard assigns default values to these properties based on their corresponding SQL*Loader properties. Warehouse Builder can use the SQL properties in one of the following ways:

• External table: After you use the Flat File Sample Wizard to import metadata, you can create an external table based on a single flat file record type. When you use the external table in a mapping, the columns properties are based on the SQL properties you defined for the flat file. For more information about external tables, see "Using External Tables".

• Populating an Empty Mapping Table. After you use the Flat File Sample Wizard to import metadata, you can populate an empty relational table with the metadata. The table inherits the SQL properties you defined for the flat file source.

• Flat file target: After you use the Flat File Sample Wizard to import metadata, you can use the flat file as a target in a mapping. Flat file targets do not inherit SQL properties, everything defaults to character data type. This option is not used here. For more information about using a flat file operator as a target, see "Mapping Flat File Operator".

Edit the SQL properties or accept the defaults to specify how the fields with SQL* Loader data types should be mapped to the columns with SQL data types. While default values adjust to SQL Loader values, user specific changes remain constant irrespective of the SQL Loader data type values. These SQL properties are used in mapping, validation, and generation.

SQL Type

Describes the SQL data type. Warehouse Builder supports the following set of portable SQL data types:

• CHAR

• DATE

• FLOAT

• NUMBER

• VARCHAR

• VARCHAR2

SQL Length

Defines the field length to be used in SQL.

SQL Precision

Defines the field precision to be used in SQL for NUMBER and FLOAT datatypes.

SQL Scale

Defines the field scale to be used in SQL for NUMBER and FLOAT datatypes.

Updating a File Definition

You can update the definition of the file format by editing its property sheet.

1. Select the file definition in the navigation tree.

2. Right-click the file name and select Properties.

   Warehouse Builder displays the Flat File property sheet with the following tabs:

   General: Use this tab to edit the name and description of the definition. You can also change the global properties, such as the physical record size, the number of physical records per logical record, and the delimiter and enclosure characters.

   Record: This tab is available only for flat files with multiple record types. Use this tab to redefine fields or add, delete, or edit record types.

   Structure: Use this tab to edit field level attributes, SQL Loader and SQL Properties.

General Tab

Use this tab to edit the name and description of the definition. You can also change the global properties, such as the physical record size, the number of physical records per logical record, and the delimiter and enclosure characters.

For delimited records, the General tab contains the following fields:

• Records delimited by: Select this option if the end of each record is designated by a delimiter. Then specify that record delimiter. You can accept the default record delimiter, carriage return (<CR>), or you can type in a new value.

• Record length (in characters): Select this option if each record in the file is the same length. Then specify the number of characters in each record. For files with multibyte characters, count a multibyte character as one character.

If the file contains logical records, click File contains logical records. Then select one of the following options to describe the file:

• Number of physical records per logical record: The data file contains a fixed number of physical records for each logical record.

• End character of the current physical record: The data file contains a variable number of physical records with a continuation character at the end that signifies that the record is associated with the next physical record.

• Start character of the next physical record: The data file contains a variable number of physical records with a continuation character at the beginning of each physical record that signifies that the record is associated with the previous physical record.

Record Tab

If the file contains multiple record types, you can select each record type from the Record Name field. Warehouse Builder displays the Record sheet and you can edit the record type information.

Record type is in column number: This field displays the column that contains the record type indicator. You can change this value. For example, if you have a flat file with two record types that are distinguished from each other by the first character in the third column as shown below, then the value in this field is 3:

• Record Type 1: 2002 0115 E 4564564

• Record Type 2: 2003 1231 D 659871 Q HKLIH

Record type values: This table displays each record type, the value that distinguishes it from the other record types, and the name you have given to the record type. Table 4-2 shows an example of what the record type values for the two sample records above might be:

Table 4-2 Example of Record Type Values  

• To add new record types, click New and enter a Type Value and a Record Name describing the record type.

• To delete record types, select the checkbox to the left of each record type you want to remove and click Delete.

After you have identified and defined the sources for our target system, you are ready to model your target schema.

Structure Tab

Use the Structure tab to edit a field name, data type, mask, and SQL properties. You can add or delete a field. You can also add a field mask, NULLIF condition, or DEFAULTIF condition.

If the file contains multiple record types, you can select each record type from the Record Name field. Warehouse Builder displays the Record sheet and you can edit the record type information.

The wizard deactivates properties that do not apply to a given data type. For example, you can edit the length for a CHAR, but precision and scale are not available.

For each field, the wizard displays the following two sets of properties:

• SQL* Loader Properties: The wizard displays this set of properties first. SQL*Loader properties include Name, Type, Mask, NULLIF, DEFAULTIF, Start, and Length. The Precision and Scale fields are reserved for future use.

• SQL Properties: The wizard displays this set of properties second. SQL properties include SQL Type, SQL Length, SQL Precision, and SQL Scale.

Figure 4-30 shows the Structure tab for a single record type data file.

Figure 4-30 Structure Tab for a Single Record Type Data File

Text description of the illustration teproprp.gif

Figure 4-31 displays the Structure tab for a multiple record type data file.

Figure 4-31 Structure Tab for a Multiple Record Type Data File

Text description of the illustration ffmultif.gif