5
Defining Source Modules

This chapter describes how to create definitions for data sources. This chapter also shows you how to modify definitions, generate and display diagrams for the source objects, and print diagrams.

This chapter includes the following topics:

• Creating Source Modules

• Importing Definitions from Database Sources

• Creating Definitions for Flat File Sources

• Importing Definitions for Pure Extract and Pure Integrate

• Importing Definitions for SAP Data Sources

Creating Source Modules

To access a source, you create a source module. You can create source modules for:

• Oracle databases

• Non-Oracle databases

• Designer repositories

• Flat files

• SAP

Warehouse Builder uses software integrators to read definitions and extract data from source systems. The New Module Wizard determines the correct integrator for a specific case from the source type and the version or system type. You can configure a module for an Oracle system, a non-Oracle database, or an Oracle Designer repository.

Table 5-1 summarizes application types and their corresponding integrators.

Table 5-1 Applications and Corresponding Software Integrators  

Type of Source	Application Version or System Type	Integrator
Oracle Database	Oracle Database 7.3, 8.0, 8i, 9i	OWB Integrator for Oracle DB & Apps 3.0
Non-Oracle Database	Oracle Generic Gateway Connectivity	OWB Integrator for Oracle DB & Apps 3.0
SAP R/3 3.x, 4.x	SAP Application Server	OWB Integrator for SAP Applications 3.0
Flat File System	Generic File System	OWB Integrator for Flat Files

Configuring Connection Information for Database Sources

When you create a source module for a database source, you create or select a database link in the Warehouse Builder repository that points to the source. Warehouse Builder uses the link to access the data dictionary of the source.

You specify the database link from the Connections page of the New Module Wizard. Select an existing database link from the drop-down list and verify the link owner, user name, and connect string. Create a new database link using the New Database Link dialog. Database administrators usually prefer to create and control database links to avoid security issues.

Figure 5-1 New Database Link Dialog

Text description of the illustration newdblin.gif

To create a new database link, specify the information in Table 5-2. You can specify the connection information as a connect string or as individual values.

Table 5-2 New Database Link Parameters  

Parameter	Description
DB Link Name	A database link name can be a maximum of 128 bytes and can include periods (.) and the at sign (@).
SQL*Net Connect String	A connect string for the database system. For a non-Oracle database: include '(HS=OK)' in the connect_data clause.
Host Name Information	Alternate specification of values for database link parameters.
Host Name Port Number Oracle SID Heterogeneous Services	Alias for the IP address of the host machine. Configured port for the Oracle Listener. SID for an Oracle Instance or an Oracle Transparent Gateway. For a non-Oracle database: check this box. This includes systems accessed via ODBC, OLE DB or an Oracle Transparent Gateway.
User Name & Password	User name and password for the database system. Case sensitive names and passwords need to be double-quoted.

For more information on database links and connect strings, see Oracle8i/9i Distributed Database Systems and Oracle8i/9i SQL Reference.

Warehouse Builder stores database link properties in the repository. After you create a database link, you can edit the link information in the module property sheet. For more information, see "Updating a Source Module".

Warehouse Builder communicates with non-Oracle systems using Oracle8i/9i Heterogeneous Services and a complementary agent. Heterogeneous Services makes a non-Oracle system appear as a remote Oracle database server. The agent can be an Oracle Transparent Gateway or the generic connectivity agent included with Oracle8i/9i.

• A transparent gateway agent is a system-specific source. For example, for a Sybase data source, the agent is a Sybase-specific transparent gateway. You must install and configure this agent to support the communication between the two systems.

• Generic connectivity is intended for low-end data integration solutions and the transfer of data is subject to the rules of specific ODBC or OLE DB drivers installed on the client system. In this case, you do not need to purchase a separate transparent gateway; you can use the generic connectivity agent included with the Oracle8i/9i database server. You must still create and customize an initialization file for your generic connectivity agent.

For additional information on distributed processing systems, see Oracle8i/9i Distributed Database Systems.

Note: The init<sid>.ora parameter OPEN_LINKS of an Oracle database determines the number of simultaneously open links during a session. If the value is too small, the Oracle8i/9i instance returns an ORA-2020 error.

Creating a Database Source Module

This section describes how to create a source module that connects with an application based on a database system.

To create a database source module:

1. Select MODULES and then Create Module from the Warehouse Builder Console menu or right-click MODULES and select Create Module.

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

2. Click Next.

   The wizard displays the Name page.

Figure 5-2 Name Page

Text description of the illustration newmodul.gif

3. On the Name page, type:
   • Name of the module

   • Status of the module

     Specify the status as Development, Quality Assurance, or Production. This status is for descriptive purposes only.

   • Source as the Module Type

   • Description (optional)

4. Click Next.

   The wizard displays the Data Source Information page.

5. Select the following:
   • Generic Oracle Database Application for the Application

   • An Application Version or System Type

   The wizard determines the correct integrator based on your selections.

   Figure 5-3 Data Source Information Page

   

   Text description of the illustration newmodda.gif

   Note: For a non-Oracle Database, select Oracle Generic Gateway Connectivity as the Database Version.

   6. Click Next.

      The wizard displays the Connection Information page.

   7. Select Oracle Data Dictionary as the metadata source.

   8. Select the name of the database link from the list.

      Click New DB Link to create the link if it not does not exist in the Warehouse Builder repository. For more information, see "Configuring Connection Information for Database Sources".

      When the database link points to a heterogeneous services agent, the page contains an additional box for the gateway agent.

      The wizard displays the link information and the name of the schema owner.

   Figure 5-4 Connection Information Page

   

   Text description of the illustration newmodco.gif

9. To change the schema owner name, click Change Schema. The wizard displays a list of users.

10. Select a schema and then click OK.

    The wizard updates the schema owner name in the Connection page.

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.

    Creating a Source Module for an Oracle Designer Repository

    You can create a source module that connects with an Oracle Designer repository. When the definitions for an application are stored and managed in an Oracle Designer repository, you can reduce the amount of time you need to connect with the application itself.

    A property on the New Module Wizard's Connection page determines whether a module can import definitions from an Oracle Database or from an Oracle Designer Repository.

    Figure 5-5 Oracle Designer Repository Option

    

    Text description of the illustration wzsordes.gif

    Check the button and select a database link that connects with the host where the Oracle Designer Repository resides. Otherwise, create the source module using the procedure described for the previous example.

    Creating a Source Module for Designer 6i

    This section describes how to create a source module that connects with an Oracle Designer 6i repository.

    Designer 6i includes versioning for accessing repository objects. Designer 6i uses workareas to control versions of an object. By selecting a workarea, you specify a version of a repository object. The workarea acts as an object qualifier that you specify before you can access it.

    With Designer 6i, you can group objects into Application Systems within workareas. An Application System contains definitions for namespace and ownership of objects and enables you to view objects even though they are owned by a different user. Because Designer 6i Application Systems are controlled by workareas, they have version control. Consult the Designer 6i documentation for more information about workareas and application systems.

    All visible objects of an Application System are available for use as data sources in Warehouse Builder. To select Designer 6i objects as Warehouse Builder sources:

    • Specify a workarea

    • Specify the application system in the workarea

    The New Module Wizard detects the Designer version available in a database link. If it finds Designer 6i, the Connection Information page changes to show the Workarea and the Application System fields along with a change button for each.

    After you click Change, the New Module Wizard displays a selection list from which you choose either a workarea or an application system. The list of repository objects available for import is determined by the following criteria:

    • The object type must be supported by Warehouse Builder (Table, View, Sequence, and Synonyms).

    • The object must be accessible in the specified workarea. This determines the version of objects accessed.

    • The object must be visible within the specified application system. The list displays objects owned by the specified application system and other objects shared by the specified application system but not owned by it.

    To create a Designer 6i source module:

    1. Select MODULES and then Create Module from the Warehouse Builder Console menu or right-click MODULES and select Create Module.

       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, enter:
       • Name of the module

       • Status of the module

         Specify the status as Development, Quality Assurance, or Production. This status is for descriptive purposes only.

       • Source as the Module Type

       • Description (optional)

    4. Click Next.

       The wizard displays the Data Source Information page.

    5. Select the following:
       • Generic Oracle Database Application for the Application

       • An Application Version or System Type

       The wizard determines the correct integrator based on your selections.

    6. Click Next.

       The wizard displays the Connection Information page.

    7. Select Oracle Designer Repository as the metadata source.

    8. Select the name of the database link to a Designer 6i repository from the list.

       Click New DB Link to create the link if it not does not exist in the Warehouse Builder repository. For more information, see "Configuring Connection Information for Database Sources".

    Figure 5-6 Connection Information Page

    

    Text description of the illustration nmwconne.gif

    If the New Module Wizard detects a Designer 6i repository, the Workarea field, Application System field, and Change button is enabled. If the New Module Wizard detects a Designer 6.0 repository, an alert displays and the Workarea field and Change button are disabled.

    You must specify a workarea before Warehouse Builder can select objects in the Designer 6i repository.

9. To specify a Workarea, click Change and choose one from the selection list.

   Figure 5-7 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.

       If you have not set a workarea, a dialog box prompts you to do so. You must set a workarea before you can select an application system.

   Figure 5-8 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.

    Creating a Flat File Source Module

    A project may need to extract data from flat files. This section describes how to create a source module for a flat file source. Before you can create a file source module, the files must be local to the computer where the Warehouse Builder client is installed.

    When the files reside on a UNIX operating system, the directory containing the files must be mapped to the Warehouse Builder host, which requires third-party software on the host to support a Network File System (NFS) connection.

    The general steps for mapping a UNIX directory to a Warehouse Builder host are:

    1. Set up the UNIX host as an NFS server.

    2. Set the source directory on the UNIX machine as sharable.

    3. Install the third-party NFS-software package on the Warehouse Builder host.

    4. Map the UNIX directory to the Warehouse Builder host.

    When you create a source module using the New Module Wizard, you can point the module to the NFS directory using the Browse button or by entering the complete path name to the directory.

    To create a file source module:

    1. Select MODULES and then Create Module from the Warehouse Builder Console menu or right-click MODULES and select Create Module.

       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:
       • Name of the module

       • Status of the module

         Specify the status as Development, Quality Assurance, or Production. This status is for descriptive purposes only.

       • Source as the Module Type

       • Description (optional)

    4. Click Next.

       The wizard displays the Data Source Information page.

    5. Select Generic File Based Application for the Application.

       The wizard determines the other selections.

    6. Click Next.

       The wizard displays the Connection Information page.

       The Oracle9i Warehouse Builder integrator for flat files reads files that have fixed-length or delimited single record formats. Fixed-length files can contain logical records that have multiple physical records. Multiple record types can be classified in either fixed-length or delimited files.

    Figure 5-9 Connection Information Page

    

    Text description of the illustration filesour.gif

    7. Enter the name of the drive that contains the source directory. This drive must be mapped to the Warehouse Builder host.

    8. Enter the directory that contains the file. Click Browse to select a directory.

    9. Click Next.

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

    10. Click Finish.

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

    Create a Source Module for SAP Definitions

    This section describes how to create a source module for an SAP data source. After you create the source module, you can import the metadata definitions from SAP tables using the Import Metadata Wizard.

    To create the SAP source module:

    1. Expand the navigation tree for your project.

    2. Right-click MODULES and select Create Module.

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

    3. Click Next.

       The wizard displays the Name page.

    4. Provide the following information in the Name page:
       • Name of the module: Type a unique name for the module between 1 and 30 alphanumeric characters. Spaces are not allowed.

       • Status of the module: Select a status for the module from the drop-down list. These options can be used to document the warehouse design version.

       • Module Type: Select Data Source.

       • Description: Type a description of the module you are creating (Optional).

    5. Click Next.

       The wizard displays the Data Source Information Page.

    6. Select the correct version of your SAP application: SAP R/3 3.x or SAP R/3 4.x.

       Note: If you select the wrong SAP application, the SAP Integrator will display an error when you attempt to log on. You must then click Back to return to this page and select the correct application.

    When you select the Application Type (SAP), the wizard automatically selects the System Type and Integrator Type (SAP) for the application.

    7. Click Next.

       The wizard displays the Connection Information page for SAP applications.

    8. Provide the following information in the Connection Information page:
       • Connection Type: Select remote function call (RFC) or SAP remote function call (SAPRFC.INI File).

       • Connection Information: The fields within this box depend on the connection type you choose.

         RFC Connection type requires the following connection information:

         • Application Server: the name of the SAP application server

         • System Number: the SAP system number for SAP GUI login

         • Client: the SAP client number

         • User Name: the SAP GUI user name

         • Language: EN for English or DE for German (If you select DE, only the description text will be in German; all other text will be in English.)

         SAPRFC.INI File requires the following connection information:

         • RFC Destination: the alias for the SAP connection information

         • Client: the SAP client number

         • User Name: the SAP GUI user name

         • Language: EN for English or DE for German (If you select DE, only the description text will be in German; all other text will be in English.)

           Note: To use the SAPRFC connection type, the file SAPRFC.INI must be installed in this directory: X:\ORACLE_HOME\wbapp where X:\ORACLE_HOME is the Warehouse Builder Oracle Home path.

       9. Click Next.

          The wizard displays the Logon dialog for the SAP application.

       10. Enter the SAP GUI password for your SAP GUI user and click Logon.

           The wizard attempts to log onto the SAP application. If unsuccessful, the wizard displays an error message; otherwise, it displays the Finish page.

           If the application server version information entered on the Data Source Information page of the wizard does not match the actual application version for the source system, you are prompted with an error message. If necessary, click Back to return to the Data Source Information page and correct the application version, then logon again.

           After you log on to the SAP source, the wizard displays the Finish page.

       11. To proceed directly to the Import Metadata Wizard, check the box at the bottom of page. You can also choose to import metadata at a later time and leave the box unchecked.

       12. Review the module information, then click Finish.

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

           Note: To import metadata at a later time, right-click the source module in the project navigation tree and select Import from the pop-up menu.

       Updating a Source Module

       You can update the definition of a source module by editing its property sheet. To display the property sheet, right-click on the module and select Properties. The property sheet for a module contains the following tabs:

       • Name: Describes the type of module (Data Source or Warehouse Target) and its function.

       • Data Source: Describes the Application Type, Application Version or System Type, and the Integrator used to access the source.

       • Connection: Describes the configured connection.

       Figure 5-10 Property Sheet for a Source Module

       

       Text description of the illustration sourcemo.gif

       Importing Definitions from Database Sources

       This section describes how to import definitions from a database application and store them in a source module.

       Importing Definitions from a Database

       You use the Import Metadata Wizard to import metadata from a database into a module. You can import metadata from an Oracle database, a non-Oracle database, and a Designer repository.

       Designer Repository Notes:: You cannot import Dimension Objects. Position will be renumbered as 10, 20, 30, ....

       To import definitions from an Oracle database application into a source module:

       1. Right-click on a data source module name and select Import.

          The welcome page for the Import Metadata Wizard displays.

       2. Click Next.

          The Filter Information page displays.

          

          Text description of the illustration importfi.gif

       3. Limit the search of the data dictionary. Enter any of the following:
          • Select tables, views, or sequences.

          • Type a search pattern, for example, a warehouse project name followed by a %.

          • Type a maximum number of objects to retrieve.

       4. Click Next.

          Warehouse Builder retrieves names that meet the filter conditions from the data dictionary and displays the Object Selection page.

       5. Select items to import from the Available Objects list and click the arrow to move them to the Selected Objects list.
          • To move all items to the Selected Objects list, click the double arrow.

          • To move one name and the names of objects it references, select the name and check One Level.

          • To move a single name and names of the objects it references directly or indirectly, select its name and check All Levels.

       Figure 5-11 Import Metadata Wizard Object Selection Page

       

       Text description of the illustration impobjec.gif

       If you are re-importing definitions, previously imported objects appear in bold.

       • Click Next.

         The Summary and Import page displays. This page summarizes your selections in a spreadsheet listing the name, type of object, and whether the object will be reconciled or created. Verify the contents of this page and add descriptions for each of the objects.

       Figure 5-12 Import Metadata Wizard Summary and Import Page

       

       Text description of the illustration object_s.gif

       7. Click Finish.

          The Import Results page displays.

       8. Click OK to accept the changes. Click Undo to cancel the import.

          Warehouse Builder stores the definitions in the module.

       Figure 5-13 Source Module with Imported Objects

       

       Text description of the illustration sqltree.gif

       Re-Importing Definitions from an Oracle Database

       Re-importing your source database definitions enables you bring in changes added to the source since your original import. You do not have to remove the original definitions from the repository. You are also given advanced options that enable you to preserve some of the changes you have made to the objects since the original import. This includes any new objects, foreign keys, relationships, and descriptions you have created.

       To re-import definitions:

       1. Right-click on a data source module name and select Import.

          The welcome page for the Import Metadata Wizard displays.

       2. Click Next.

          The Filter Information page displays.

       3. Select the object types you want to re-import. You must select the same settings used in the original import to ensure that the same objects are re-imported.

       4. Click Next.

          The Object Selection page displays. The objects that were originally imported display in bold.

       5. Select the objects that you originally imported, and click the arrow to move them to the Selected Objects list.

       6. Click Next.

          The Summary and Import page displays. The Reconcile action is displayed for the objects you are re-importing.

       Figure 5-14 Summary and Import Page Showing Reconcile Action

       

       Text description of the illustration imw_sumi.gif

       Note: If the source contains new objects related to the object you are re-importing, the wizard requires that you import the new objects at the same time. The Create action displays for these objects.

       7. Click Advanced Reconcile Options to select advanced reconciliation options.

          The Advanced Reconciliation Options dialog displays.

       Figure 5-15 Advanced Reconciliation Options Dialog

       

       Text description of the illustration imw4.gif

       Select options for reconciling views:

       • Preserve existing descriptions

       • Preserve repository added columns

       Select options for reconciling tables:

       • Preserve repository added constraints

       • Preserve existing descriptions

       • Preserve repository added columns

         Note: By default, all options are checked. Uncheck boxes to have these repository objects replaced and not preserved.

       8. Click OK after selecting your options.

       9. Click Finish.

          Warehouse Builder reconciles and creates objects. When this is complete, the Import Results dialog displays.

       Figure 5-16 Import Results Dialog

       

       Text description of the illustration imw5.gif

       The report lists the actions performed by Warehouse Builder for each object.

       Click Save to save the report. Make sure to use a naming convention that is specific to the re-import.

       10. Click OK to proceed.

           Click Undo to undo all changes to your repository.

       Updating Oracle Database Source Definitions

       You can update source definitions, diagram individual definitions and their references, and print the diagrams using the Source Module Editor. To display this editor, double-click the module name.

       Figure 5-17 Source Module Editor

       

       Text description of the illustration smegccin.gif

       Update a Source Definition

       You can update a source definition by editing entries in its Property Sheet. To display a definition's property sheet, select its name in the navigation tree and then select Properties from the pop-up list.

       Figure 5-18 Columns Tab of the Table Properties Sheet

       

       Text description of the illustration smeprpli.gif

       To update an existing entry, select the entry and enter the new information. Some entries have drop-down lists that limit the range of selections. For example, when you change the data type of a column, you must select an entry from a drop-down list. You can also add new entries or remove existing ones.

       Update the Connection

       You can update the connection information for a data source by selecting another database link from the drop-down list.

       Figure 5-19 Connection Tab of the Module Properties Sheet

       

       Text description of the illustration prcongcc.gif

       When you change the connection information, Warehouse Builder displays a warning message that you may compromise the existing definitions in the source module. To change the connection, click OK.

       Diagram a Source Definition

       You can display a diagram for a definition and its references using the Source Module Editor. To display a diagram for a definition, select its name in the navigation tree and then select Edit from the pop-up list. The Source Module Editor displays a diagram of the definition.

       Figure 5-20 Table Editor

       

       Text description of the illustration smedigli.gif

       Each object in the diagram has a toolbar that you can use to sort the column names. The toolbar is divided into three rectangles. To sort the column names click one of the rectangles:

       • Left rectangle determines the position and order of the primary key columns.

       • Middle rectangle sorts all the column names.

       • Right rectangle determines the position and order of foreign key columns.

       The sort order is only for display purposes and has no bearing on the ordering of column names within the definition.

       Print a Source Definition

       To print a diagram of a source definition, display the diagram and then click the Print icon on the editor's toolbar.

       Creating Definitions for Flat File Sources

       This section shows you how to create a definition for a flat file. Using the Flat File Sample wizard you can:

       • Create format definitions for delimited and fixed-length files

       • Create format definitions for logical records within a fixed-length file that consists of one or more physical records within a file

       • Identify and create format definitions for multiple record types within a file

       After you create and store a format definition, you can use it to describe other flat files that have the same properties.

       About the Flat File Sample Wizard

       When you create a definition, the Flat File Sample Wizard opens the file, displays a sample of data, and requests detailed information about the file format. The Flat File Sample Wizard is structured as shown in Table 5-3.

       Table 5-3 Flat File Sample Wizard Pages  

       Page Name	Information Required
       Setup	File format: Fixed-length or delimited Field delimiter and enclosure characters for delimited format Terminator or physical record length for fixed-length format Character set
       Record Organization	Single or multiple record types Number of rows to sample and rows to skip before sampling Logical record definition (only available for fixed-length files that have a single record type)
       Record Types (only appears for multiple record types)	Column positions specifying the record type Name of each record type
       Column Definition (only appears for fixed-length files)	Width of each column in a fixed-length file
       Properties	Name, type, mask, NULLIF, DEFAULTIF, field length Header row

       Character set: Warehouse Builder's default NLS character set is the same as its host. If it differs from the source file's character set, the data sample might be unintelligible. You can display the data sample in the source's native character set by selecting it from the drop-down list. For complete information on NLS character sets, see the Oracle8i/9i National Language Support Guide.

       Physical record length: The length of a fixed-format record can be specified as length in characters or set to a user-defined terminator. The length specification results in greater efficiency.

       Logical records: Warehouse Builder can manage a source file of logical records. The number of physical records in a logical record can be fixed or variable. For a fixed number, you specify the number of physical records per logical record. For a variable number, you specify a continuation character at either the end or beginning of each physical record. See "Specifying Logical Records" for more information.

       Multiple record types: The Flat File Wizard can interpret a source file that contains a variety of record types. You must specify the column within the source file that contains unique record types. You then scan the column to identify unique record type values, then define the characteristics of that record type. The record types can be renamed.

       Field type: Describes the data type of the field for the SQL*Loader. Warehouse Builder supports the following set of portable data types:

       • CHAR

       • DATE

       • DECIMAL EXTERNAL

       • FLOAT EXTERNAL

       • INTEGER EXTERNAL

       • ZONED EXTERNAL

       The native numeric data is a number in character form; it is not a binary representation. The numeric data types are identical to CHAR except with respect to the DEFAULTIF and NULLIF constraints. See the discussion below on field constraints.

       You can represent FLOAT EXTERNAL data either in scientific or regular notation. The representations 5.33 and 533E-2 are both valid.

       For complete information on SQL*Loader field and data types, refer to Oracle8i/9i Utilities.

       Field 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/DEFAULTIF conditions: You can override the default action of the SQL*Loader by placing a DEFAULTIF or NULLIF condition on a field.

       • When a numeric or DATE field contains all blanks, SQL*Loader rejects the entire record. To override this action, include a DEFAULTIF = BLANKS condition on the field. When SQL*Loader evaluates this condition, it sets the field to zeros and loads the record.

       • When a character field contains all blanks, you can direct SQL*Loader to mark the column as null rather than storing the blanks by including a NULLIF = BLANKS condition on the field.

       When you describe the field using the Flat File Sample Wizard, you can choose one of these constraints.

       Creating a Definition for a Fixed-Length File

       The following procedure describes how to create a definition for a file using the Import Metadata and Flat File Sample Wizards. Each logical record of this file consists of a single physical record. The location of the file was configured in the warehouse source module.

       To create a definition for a flat file format:

       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. Use this page to filter file names.

       3. Click Next.

          The wizard displays the Object Selection page.

       Figure 5-21 Object Selection Page

       

       Text description of the illustration mdffsdir.gif

       4. Move the name of the file to be described from the Available to the Selected Objects window pane.

       5. Click Next.

          The wizard displays the Summary and Import page. The left-most column of this page contains a status ball which can be red or green. If green, then Warehouse Builder already has a definition of the file's format--proceed to step 15; if red, then you must create a format for the file using the Flat File Sample Wizard.

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

       7. Click Next.

          The wizard opens the file, reads a sample of data, and displays the File Setup page. This page displays the sample of data in a template with a few initial values set for the global properties.

       8. Verify and select the global properties:
          • File format: Fixed Length

          • Record ends at: <CR> by default.

          • Record size can be specified by length

          • NLS Character set: WE8MSWIN1252

       Figure 5-22 Flat File Sample Wizard Setup Page

       

       Text description of the illustration wzmffdel.gif

       Note: If the Warehouse Builder character set differs from the source, the sample might not be readable. If so, select the source character set from the drop-down list and Warehouse Builder translates the sample.

       9. Click Next.

          The wizard displays the Record Organization page. Use this page to specify whether the file contains single or multiple record types, or if it requires a logical record structure. You can also select how many rows of the file to sample.

       Figure 5-23 Record Organization Page

       

       Text description of the illustration wzmfffbs.gif

       10. Click Next.

           The wizard displays the Column Definition page. Use this page to specify the column widths.

       Figure 5-24 Column Definition Page

       

       Text description of the illustration wzmffcd.gif

       Define a column using one of two methods:

       • Locate where the column ends in the sample and click that position on the ruler. The wizard displays a red tick mark on top of the ruler and marks the boundary with a red line.

       • Specify the column width in the Field Widths space.

       If you make a mistake, double-click the marker to restart.

       Use the vertical and horizontal scroll bars to navigate.

       11. Click Next.

           The wizard displays the Properties page. This page defines each field of the logical record just as you marked it off in the previous step.

       12. Use the Properties page to define each of the logical record's fields:
           • Name: Can be changed.

             If your flat file supplies or contains field names, select the check box Use the first record as the field names to automatically transfer header information.

           • Type: Describes the source to SQL*Loader. Click the field data type and select a type from the drop-down list.

           • Mask: Overrides the default for DATE formats.

           • NULLIF or DEFAULTIF: Overrides the default SQL*Loader action for fields that contain all blanks. Specify DEFAULTIF or NULLIF and Warehouse Builder generates a corresponding DEFAULTIF=BLANKS or NULLIF=BLANKS condition.

           • Length: Specifies the length of the field.

           For more information on these fields, see "Creating Definitions for Flat File Sources".

           Figure 5-25 Properties Page

           

           Text description of the illustration wzmfffie.gif

           13. Click Next.

               Warehouse Builder displays the Summary page. Verify that the definition is correct. If not, click Back to navigate the wizard pages.

           14. Click Finish.

               Warehouse Builder exits the Flat File Sample Wizard and returns to the Summary and Import page of the Import Object Wizard.

               The Summary and Import page has been updated:

               • The status ball is green.

               • The File Structure Name column now has an entry.

           Figure 5-26 Summary and Import Page

           

           Text description of the illustration wzmfffb2.gif

           If you open this wizard at a later time with a file that has the same format as the one you have just set, then you can select this entry from the Same As field instead of creating a new definition with the Flat File Sample Wizard.

           15. Click Finish.

               Warehouse Builder creates a definition for file, stores the definition in the source module, and inserts the format's name in the source module's navigation tree.

           Defining Multiple Records in a Fixed-Length File

           You can define different record types within a file using the Flat File Sample Wizard.

           To define multiple record types:

           1. Open the Import Metadata Wizard, select a file, and then click Sample to open the Flat File Sample Wizard. See "Creating a Definition for a Fixed-Length File".

              The wizard displays the welcome page for the Flat File Sample Wizard.

           2. Click Next.

              The wizard displays the Setup page.

           3. Select Fixed Length records.

           4. Select a way to specify where each fixed-length record ends.
              • If you select Records end at, specify the code that terminated each record.

              • If you select Record length (characters), specify the number of characters in each record.

           5. Optionally, select a different character set.

           6. Click Next.

              The wizard displays the Record Organization page.

           7. Select Multiple record types and specify the number of rows to sample. Optionally, specify the number of rows to skip before sampling.

           8. Click Next.

              The wizard displays the Record Types page.

           9. Identify the column or columns that identify the record type in the file by using the Record Type begins at position field and the And ends at position field.

              In the following example, the first column defines the record type, so the first column begins in position 0 and ends in position 1.

           Figure 5-27 Record Types Page

           

           Text description of the illustration wzffmult.gif

           10. Click Scan.

               A list of distinct type values appears with the default record names RECORD1, RECORD2, and so on. You can edit the record names, select a different record name for any type value, and add or delete type values.

           11. Click Next.

               The wizard displays the Column Definition page.

           12. Select a record name and use the ruler to specify the length of a field, or specify the field length in numbers in Field Widths.

               The Field Width settings you define refer to the positions that contain the record type value. When you have adjusted the information for each column, either select another record name and define its columns or click Next to go to the Properties page.

           Figure 5-28 Column Definition Page

           

           Text description of the illustration wzffmula.gif

           You now need to define the data characteristics for each field, and adjust masking and constraints.

           13. Adjust the data type, mask, and constraints (NULLIF, DEFAULTIF) as needed for each record type.

           14. Click Next.

               The wizard displays the Summary page showing what is to be imported by the wizard.

           15. Click Finish.

           The source module now contains a definition for the file format, and within it, definitions for the individual records.

           Specifying Logical Records

           When the logical record for a source file contains multiple physical records, you must specify the physical records and describe the assembly method. Only fixed-length files with a single record type can have logical records that contain multiple physical records.

           To describe multiple physical records:

           1. Open the Import Metadata Wizard, select a file to describe, and then click Sample to open the Flat File Sample Wizard. See steps 1 - 5 in "Creating a Definition for a Fixed-Length File".

              The wizard displays the welcome page for the Flat File Sample wizard.

           2. Click Next.

              The wizard displays the Setup page. Use this page to define the global properties.

           3. Select the Fixed Length radio button and complete the other selections as required.

           4. Click Next.

              The wizard displays the Record Organization page.

           Figure 5-29 Logical Record Support

           

           Text description of the illustration fflogica.gif

           5. Select the radio button that describes how the logical record is assembled:
              • Fixed number of physical records per logical record.

              • Variable number of physical records with a continuation character at the end of each physical record that signifies that the record belongs with the next physical record.

              • Variable number of physical records with a continuation character at the beginning of each physical record that signifies that the record belongs with the previous physical record.

              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.

           6. You can now define the breaks for each field, click Next, and complete the definition for the format.

           Creating a Definition for a Delimited File

           The following procedure describes how to create a definition for a file using the Import Metadata and Flat File Sample Wizards. The location of the file was configured in a warehouse source module.

           To create a definition for a delimited file format:

           1. Select the warehouse module.

           2. Right-click the module name and select Import.

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

           3. Click Next.

              The wizard displays the Filter Information page which you can use to filter the file names.

           4. Click Next.

              The wizard displays the Object Selection page.

           5. Move the file name from the Available to the Selected Objects list using the single arrow key.

           6. Click Next.

              The wizard displays the Summary and Import page. The left-most column of this page contains a status ball that can be red or green. If green, then Warehouse Builder already has a definition of the file format. If red, you must create a definition for the file using the Flat File Sample Wizard.

           Figure 5-30 Summary and Import Page Showing File Status

           

           Text description of the illustration wzmfffb3.gif

           If the ball is red and you cannot describe the file with the format, you must create a definition for the file's format.

           To create a format:

           1. Click Sample at the bottom of the page.

              The wizard displays the welcome page for the Flat File Sample Wizard.

           2. Click Next.

              The wizard opens the file, reads a sample of data, and displays the Setup page. This page displays a sample of data in a template with a few initial values set for the file's global properties.

              • The Field Delimiter default is the comma (,).

              • The Enclosures defaults are double quotation marks (") for both the left and right enclosures.

           3. Select the file format as delimited and an NLS character set. See the discussion on NLS character sets.

              Use the text box for the left and right enclosure characters to define text strings in the data. You can enter an enclosure character in the text box or select one from the drop-down list.

           4. Click Next.

              The wizard displays the Record Organization page.

           5. Specify single record type and the number of rows to sample. You cannot enable logical record support for a delimited format file. For a file that has multiple record types, see "Defining Multiple Record Types in a Delimited File".

           6. Click Next.

              The wizard displays the Properties page. Use this page to describe each field.

              See the discussion for each field property in Step 12.

           7. Click Next.

              The wizard displays the Summary page. Verify that the format definition is correct. If not, navigate the wizard pages by clicking Back and correct the definition.

           8. Click Finish.

              The Flat File Sample Wizard returns to the Summary and Object page of the Import Object Wizard.

           The Summary and Object page has been updated. The status ball is now green and the File Structure Name column now has an entry.

           Figure 5-31 Summary and Import Page Showing File Status

           

           Text description of the illustration wziobsta.gif

           You can open this wizard at a later time and use the file format you created to describe any flat that has the properties described by this format. Instead of sampling the new file you can select this format from the Same As field.

           7. Click Finish.

              Warehouse Builder creates a definition for file, stores it in the source module, and inserts its name in the source module navigation tree.

           Defining Multiple Record Types in a Delimited File

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

           To associate multiple record types within a flat file:

           1. Open the Import Metadata Wizard, select a file to describe, and then click Sample to open the Flat File Sample Wizard.

              The wizard displays the welcome page for the Flat File Sample Wizard.

           2. Click Next.

              The wizard displays the Setup page. Use this page to define the file's global properties such as whether records are delimited by a character or space.

           3. Click Next.

              Define whether the file contains single or multiple record types.

           Figure 5-32 Record Organization Page

           

           Text description of the illustration selectmu.gif

           4. Click Next.

              Selecting multiple record types opens the Record Types page.

           Figure 5-33 Record Types Page

           

           Text description of the illustration selectma.gif

           5. Identify the column that contains unique record information. For delimited files, the program assumes the column to scan is the first column of the record unless you specify a different column. Click Scan. All unique values appear.

              Records identified are named RECORD1, RECORD2, and so on. You can rename them by typing the new name in the field.

              When you select a record type in the list, the lower panel shows data only for that record type.

           6. Click Next.

              The wizard displays the Properties page.

           7. For each record type, select its record name and adjust the data types, mask, and constraint information.

              See the detailed discussion for each field property.

           8. Select the remaining record type and adjust the date types and other information for that record type. When you have the record type definition in the proper structure, click Next.

              The wizard processes your records.

           9. The Summary page displays all record information to be imported.

           10. Click Finish to import the file or click Back to return to a previous page to make changes.

           Updating a File Definition

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

           To update a file definition:

           1. Select the file definition in the navigation tree.

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

              Warehouse Builder displays the General tab of the Flat File property sheet. You can edit the name and description of the definition. You can also change the global properties ascribed to the file, such as the physical record size, the number of physical records per logical record, and the delimit and enclosure characters.

           3. Select Structure.

              Warehouse Builder displays the Structure page.

           Figure 5-34 Structure Page

           

           Text description of the illustration teproprp.gif

           4. Use this page to:
              • Edit a field name, data type, mask.

              • Add a field mask.

              • Add a NULLIF condition.

              • Add a DEFAULTIF condition.

              • Add or delete a field.

           5. After completing your changes, click OK.

           Figure 5-35 Structure Page

           

           Text description of the illustration ffmultif.gif

           6. If the file contains multiple record types, select Record.

              Warehouse Builder displays the Record sheet. You can edit the record type information, for example, you can delete a record type or add a new one.

           Importing Definitions for Pure Extract and Pure Integrate

           You can import definitions that describe source data that can be extracted using Pure Extract and Pure Integrate. These imported definitions can then be the subject of mappings defined within your project.

           You can also export definitions with Pure Extract and import them into a Warehouse Builder project. For a complete description of how to use the Oracle Warehouse Builder MetaData Loader Utility, refer to "About the Warehouse Builder Metadata Loader".

           Importing Definitions for SAP Data Sources

           You can import definitions from an SAP application and store them in an SAP source module in your project. See "Create a Source Module for SAP Definitions" for instructions on how to create an SAP source module.

           Business Components

           SAP application systems logically group numerous data tables under different business components. Examples of business components are FI - Finance and CO - Controlling.

           The business component filter on the Import Metadata Wizard Filter Information page enables you to filter metadata from a business area of your interest within the SAP application. To select the appropriate business component, you can navigate to the SAP business component tree, locate a specific business component, and view a list of tables organized under that component.

           Import Metadata from SAP sources

           To import metadata from an SAP application using the Import Metadata Wizard:

           1. Fully expand the navigation tree for your project.

           2. Right-click the name of your and select Import from the pop-up menu.

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

           3. Click Next.

              The wizard displays the Filter Information page.

           4. Use this page to select how you want to filter tables to be displayed in the Object Selection page.
              • Choose Business Component option if you want to filter the tables by business areas within the SAP application.

                When you click Browse, the wizard displays the SAP R/3 Business Component Hierarchy Dialog, a hierarchical navigation mechanism for locating data objects in SAP applications. After you select a business component from this dialog, your selection displays on the blank Business Component field on the import wizard page.

              • Choose the Name matches entry field or the Description matches entry field to enter a string and obtain matching tables from the SAP data source. The following rules apply:
                • Although the Name matches field is not case sensitive, the Description matches field is case sensitive.

                • You must enter a text string in the selected Text String entry field; it cannot be left empty.

                • You can create a filter for object selection by using the wildcard characters (%) for zero or more matching characters and (_) for a single matching character.

                • The SAP Integrator allows you to import metadata for transparent tables, cluster tables, or pool tables.

           5. Specify the number of tables you want to import in the Maximum number of objects displayed field.

           6. Click Next.

              The wizard displays the Object Selection page with a description of each table.

           7. Select tables from the Available Objects list and move them to the Selected Objects list using the arrow buttons.

              For a description of the keys, see the example for a database source module in this chapter.

              Note: The SAP Integrator does not currently support importing definitions for views.

8. If the radio button for the foreign key level is set to One Level or All Levels, the Confirm Import Selection dialog appears.

   Note: If you select All Levels, you will import hundreds of tables that are related to each other through foreign key constraints.

9. Click OK.

   The selected objects appear in the right pane of the Object Selection page.

10. Click Next.

    The wizard imports definitions for the selected tables from the SAP Application Server and stores them in the SAP source module, then displays the Summary and Import page.

11. Review the information on the Summary and Import page.

    If you are re-importing metadata, the tables to be re-imported are displayed in bold. Click Advanced Reconcile Options to set your re-import options.

12. Click Finish.

    The SAP Integrator reads the table definitions from the SAP Application Server and creates the metadata objects in the Warehouse Builder repository.