| Oracle® Warehouse Builder User's Guide 10g Release 1 (10.1) Part Number B12146-02 |
|
|
View PDF |
| Oracle® Warehouse Builder User's Guide 10g Release 1 (10.1) Part Number B12146-02 |
|
|
View PDF |
This chapter describes how to create source modules in Warehouse Builder. This chapter also shows you how to import definitions from different data sources into the source modules.
This chapter includes the following topics:
In Warehouse Builder, you create a source modules to store definitions from different source systems. You can create source modules for:
Oracle databases
Non-Oracle databases
Flat files
SAP Applications
Warehouse Builder uses software integrators to read data definitions and extract data from source systems. Table 4-1 summarizes application types and their corresponding integrators.
Table 4-1 Applications and Corresponding Software Integrators
| Type of Source | Application Version or System Type | Integrator |
|---|---|---|
|
Oracle Database |
|
OWB Integrator for Oracle DB & Apps 3.0 |
|
Non-Oracle Database (including Sybase, Informix, ODBC) |
Oracle Generic Gateway Connectivity |
OWB Integrator for Oracle DB & Apps 3.0 |
|
SAP R/3 3.x, 4.x |
SAP |
OWB Integrator for SAP Applications 3.0 |
|
Flat File System |
File System |
OWB Integrator for Flat Files |
When you create source modules in Warehouse Builder, the New Module wizard determines the correct integrator to use based upon the source type you select. This section shows you how to choose the type of source module you want to create from the Warehouse Builder navigation tree, as shown in Figure 4-1:
Oracle Source Module: Expand the Databases node, right-click the Oracle node and select Create Oracle Module. For details, see "Creating a Database Source Module".
SAP Source Module: Expand the Applications node, then right-click the SAP node and select Create SAP R/3 Source Module. See "Creating SAP Module Definitions".
Flat File Module: Right-click the Files node and select Create Flat File Module. See "Creating Flat File Modules".
DB2 Source Module: Expand the Databases node, then expand the Others node, right-click the DB2 node and select Create DB2 Source Module.
SQL Server Source Module: Expand the Databases node, then expand the Others node, right-click the SQL Server node and select Create SQL Server Source Module.
Sybase Source Module: Expand the Databases node, then expand the Others node, right-click the Sybase node and select Create Sybase Source Module.
Informix Source Module: Expand the Databases node, then expand the Others node, right-click the Informix node and select Create Informix Source Module.
RDB Source Module: Expand the Databases node, then expand the Others node, right-click the RDB node and select Create RDB Source Module.
DRDA Source Module: Expand the Databases node, then expand the Others node, right-click the DRDA node and select Create DRDA Source Module.
ODBC Source Module: Expand the Databases node, then expand the Others node, right-click the ODBC node and select Create ODBC Source Module.
Other Gateway Source Module: Expand the Databases node, then expand the Others node, right-click the Other node and select Create Other Gateway Source Module. See the section on "Oracle Heterogeneous Services" in the following section.
Warehouse Builder communicates with non-Oracle systems using Oracle Database Heterogeneous Services and a complementary agent. Heterogeneous Services make 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 Oracle Database.
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 Oracle Database database server. You must still create and customize an initialization file for your generic connectivity agent.
For additional information on distributed processing systems, see Oracle Database Distributed Database Systems.
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 system. Warehouse Builder uses this link to access the data dictionary of the source.
You can 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 or create a new database link using the New Database Link dialog as shown in Figure 4-2.
You can create either private or public database links. Private database links can only be used by the user that created them. Public database links are available to all repository users on the same database. By default, if you have multiple users for the same repository, only the owner is given the CREATE PUBLIC DATABASE LINK privilege. If other users want to create public database links they must have the privilege granted.
To create a new database link:
Specify a name for the database link.
A database link name can be a maximum of 128 bytes and can include periods (.) and the "at" sign (@).
Check the Create public database link check box if you want the database link created in the PUBLIC schema and made available to other users.
By default, the check box is unchecked.
Select either the SQL*Net Connect String or Host Name radio button and provide the following connection information.
SQL Connect String: Specify the connect string for the database system as it exists in your TNSNAMES.ORA file. If the system is a non-oracle system, specify this by including `(HS-OK)' within the connect_data clause.
Host Name: If the connect string does not exist in your TNSNAMES.ORA file, enter the Host Name, Port Number, and Oracle Service Name.
Enter a user name and password with access to the database you are connecting to and click Create and Test.
Case sensitive names and passwords need to be double-quoted. For BIS Applications, enter your APPS account user name.
The connection information you provided is tested, and a message will display.
Click OK when you are done.
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 on database links and connect strings, see Oracle Database Distributed Database Systems and Oracle Database SQL Reference.
This section describes how to create a source module that connects with an application based on a database system. The source module is a container for data definitions imported from the database system.
The following sections contain information about working with Database Source systems:
For related information, see the following sections:
To create a database source module:
From the Warehouse Builder navigation tree, expand the Databases node.
To create a source module for Oracle data sources, right-click the Oracle node and select Create Oracle Module as shown in Figure 4-3.
The New Module Wizard determines the source type from the navigation tree node from where you launch the wizard. For example, to create an Informix source module, right-click Informix and select Create Informix Source Module. The wizard will automatically determine the correct Warehouse Builder integrator to use for this data source.
Warehouse Builder displays the welcome page for the New Module Wizard.
Click Next.
The wizard displays the Name page.
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.
Data Source as the Module Type
Description (optional)
Click Next.
The wizard displays the Data Source Information page.
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. For a non-Oracle Database, select Oracle Generic Gateway Connectivity as the Database Version.
Click Next.
The wizard displays the Connection Information page.
Select Oracle Data Dictionary or Oracle Designer Repository as the metadata source.
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 Connections for Database Sources".
When the database link points to a heterogeneous services agent, the page displays an additional field for the gateway agent if you are importing from non-Oracle databases including systems accessed through ODBC, OLE DB or an Oracle Transparent Gateway.
The wizard displays the link information and the name of the schema owner as shown in Figure 4-4.
To change the schema name, click Change Schema. The wizard displays a list of users.
Select a schema and then click OK.
The wizard updates the schema owner name in the Connection page.
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 use 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.
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.
Click Next.
The Finish page summarizes the information you provided on each of the wizard pages. Check the checkbox if you want to directly start the Import Metadata Wizard.
Click Finish.
The wizard creates the source module and inserts its name in the project navigation tree. If you checked the check box, Warehouse Builder starts the Import Metadata Wizard.
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, or a Designer repository.
To import definitions from an Oracle Data Dictionary:
Right-click a data source module name and select Import.
The welcome page for the Import Metadata Wizard displays.
Click Next.
The Filter Information page displays, as shown in Figure 4-5.
Limit the search of the data dictionary in one of the following methods:
Select tables, views, external tables, sequences, advanced queues, or PL/SQL transformation packages.
Type a search pattern. For example, you can type a warehouse project name followed by a % to import objects that begin with that project name. Use % as a wild card match for multiple characters and _ as a wild card match for a single character.
Click Next.
Warehouse Builder retrieves names that meet the filter conditions from the data dictionary and displays the Object Selection page, as shown in Figure 4-6.
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 an object and the objects it references, select the name of the object and check One Level.
To move a single object and all the objects it references directly or indirectly, select the name of the object and check All Levels.
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.
Click Finish.
The Import Results page displays.
Click OK to accept the changes. Click Undo to cancel the import.
Warehouse Builder stores the definitions in the source module.
For related information, see the following sections:
Re-importing your source database definitions enables you to import changes made to your source metadata since your previous import. You do not have to remove the original definitions from the repository. Warehouse Builder provides you with options that also enable you to preserve any changes you may have made to the definitions since the previous import. This includes any new objects, foreign keys, relationships, and descriptions you may have created in Warehouse Builder.
To re-import definitions:
Right-click a data source module name and select Import.
The welcome page for the Import Metadata Wizard displays.
Click Next.
The Filter Information page displays.
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.
Click Next.
The Object Selection page displays. The objects that were originally imported display in bold, as shown in Figure 4-7.
Select the objects that you originally imported, and click the arrow to move them to the Selected Objects list.
Click Next.
The Summary and Import page displays as shown in Figure 4-8. The Reconcile action is displayed for the objects you are re-importing.
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.
Click Advanced Reconcile Options to select advanced reconciliation options.
The Advanced Reconciliation Options dialog displays, as shown in Figure 4-9.
This dialog enables you to preserve any edits and additions made to the object definitions in the Warehouse Builder repository.
Select these options for reconciling views:
Preserve existing descriptions: The descriptions stored in the repository are preserved.
Preserve repository added columns: The columns you added in Warehouse Builder are preserved.
Select these options for reconciling tables:
Preserve repository added constraints: The constraints you added to the table in Warehouse Builder are preserved.
Preserve existing descriptions: The descriptions stored the repository are preserved.
Preserve repository added columns: The columns you added in Warehouse Builder are preserved.
By default, all options are checked. Clear boxes to have these repository objects replaced and not preserved.
For example, after importing tables or views for the first time, you manually add descriptions to the table or view definitions. If you want to make sure that these descriptions are not overwritten while reimporting the table or view definitions, you must select the Preserve Existing Definitions option. This ensures that your descriptions are not overwritten.
Click OK after selecting your options.
Click Finish.
Warehouse Builder reconciles and creates objects. When this is complete, the Import Results dialog displays, as shown in Figure 4-10.
The report lists the actions performed by Warehouse Builder for each object.
Click Save to save the report. You should use a naming convention that is specific to the re-import.
Click OK to proceed.
Click Undo to undo all changes to your repository.
The Oracle Source Module Editor enables you to view and print schema diagrams of objects in the Oracle Source Module and run Lineage and Impact Analysis reports on them.
To display the Oracle Source Module Editor:
Double-click the name of the source module.
The View Objects dialog displays as shown in Figure 4-11.
Choose the objects you want to graphically display on the Oracle Source Module Editor canvas.
Click OK.
Warehouse Builder displays the Oracle Source Module Editor, as shown in Figure 4-12.
To print a diagram of a source definition, click the Print icon on the editor toolbar.
You can update a the properties of a source definition by editing its Property Sheet. To display the property sheet, right-click the module name from the navigation tree and select Properties from the pop-up menu.
Select the Connection tab from the Module Properties window to update the connection information for a data source. You can select another database link from the drop-down list, as shown in Figure 4-13.
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.
Select the Location tab from the Module Properties window to update the location of a data source. If you have defined multiple locations for the source module, you can select a different location from the drop-down list.
For more on Locations, see "Defining Runtime Repository Connections".
In Warehouse Builder, 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.
Designer 6i/9i repositories use workareas to control versions of an object. By selecting a workarea, you can specify a version of a repository object. With Designer 6i/9i, you can also 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/9i Application Systems are controlled by workareas, they have version control. See the Designer 6i/9i documentation for more information about workareas and application systems.
All visible objects of a workarea or an Application System in Designer 6i/9i are available for use as data sources in Warehouse Builder. To select Designer 6i/9i objects as Warehouse Builder sources:
Specify a workarea or,
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/9i, 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 in 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 import definitions from a Designer 6i/9i source, you must follow the steps outlined for importing definitions from database sources.
To create a Designer6i/9i source module:
From the Warehouse Builder navigation tree, expand the Databases node.
Right-click the Oracle node and select Create Oracle Module.
Warehouse Builder displays the welcome page for the New Module Wizard.
Click Next.
The New Module Wizard displays the Name page.
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.
Data Source as the Module Type
Description (optional)
Click Next.
The wizard displays the Data Source Information page. The wizard determines the correct integrator based on the navigation tree node from where you launch the wizard.
Click Next.
The wizard displays the Connection Information page, as shown in Figure 4-14.
Select Oracle Designer Repository as the source for metadata import.
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 Connections for Database Sources".
When you connect to a Designer 6i/9i repository, you must connect as the Designer Repository owner.
If the New Module Wizard detects a Designer 6i repository, the Workarea field, Application System field, and Change button are enabled.
You must specify a workarea before Warehouse Builder can select objects in the Designer 6i repository.
To specify a Workarea, click Change and choose one from the selection list, as shown in Figure 4-15.
To specify the Application System, click Change and choose one from the selection list, as shown in Figure 4-16.
Click Next.
The wizard displays the Finish page. This page summarizes the information you entered on each of the wizard pages. Verify the information.
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:
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 enables 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.
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".
To create a flat file module:
Right-click the Files node in the Warehouse Builder Console and select Create Flat File Module, as shown in Figure 4-17.
Warehouse Builder displays the welcome page for the New Module Wizard.
Click Next.
The New Module Wizard displays the Name page.
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.
Click Next.
The wizard displays the Connection Information page.
Enter the fully qualified directory, including the drive letter, if appropriate, that contains the file.
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.
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.
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.
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.
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:
For related information, see the following sections:
The following section describes how to create a definition for a flat file using the Import Metadata Wizard.
Right-click a file module and select Import.
Warehouse Builder displays the welcome page for the Import Metadata Wizard.
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.
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
Move the name of the files to be described from Available Objects on the left to the Selected Objects window pane on the right.
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.
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.
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.
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.
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.
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:
Use the Name page shown in Figure 4-20 to describe the flat file you are sampling.
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 Globalization Support or 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 Oracle Database 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.
Use the Record Organization page shown in Figure 4-21 to indicate how records are organized in the file you are sampling.
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.
The Flat File Sample Wizard enables 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 for each logical record.
After you complete the logical record information, click Next to continue with the wizard.
Number of physical records for each 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 preceding example, if the number of physical records for each 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 for each 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)
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.
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)".
Use the File Format page shown in Figure 4-23 to select between Fixed Length and Delimited formats for the file.
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 (Ò).
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.
Note:
This step in not necessary for files with a single record type. If the data file has a single record type and fixed length file format, proceed to "Specifying Field Lengths (Fixed-Length Files Only)" . If the data file has a single record type and delimited file format, proceed to "Specifying Field Properties".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.
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.
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.
To complete the Records Type page for a delimited file:
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.
You can edit the record names.
Click 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.
Click Next to continue with the wizard.
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.
To complete the Records Type page for a fixed-length file:
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.
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.
You can edit the record names.
Click 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.
Click Next to continue with the wizard.
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.
Note:
This step is not necessary for delimited files. Proceed to "Specifying Field Properties".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.
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 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.
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.
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.
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 (UNKNOWN STEP NUMBER) "Using the Import Metadata Wizard Flat Files".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:
The wizard assigns a name to each field. It assigns 'C1'to the first field, 'C2' to the second, and so on. To rename fields, click 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.
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 Oracle Database Utilities. Currently, only portable datatypes are supported.
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.
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.
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.
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.
You can update the definition of the file format by editing its property sheet.
To update a file definition:
Select the file definition in the navigation tree.
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 for each 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.
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 for each 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 for each 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.
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 in the following list, 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 earlier might be:
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.
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-31 displays the Structure tab for a multiple record type data file.
