| 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 |
After you create and import data object definitions in Warehouse Builder, you can define extraction, transformation, and loading (ETL) operations that move data from sources to targets. In Warehouse Builder, you design these operations in a mapping.
This chapter contains the following topics that describe how to create, edit, and use mappings:
Mappings describe a series of operations that extract data from sources, transform it, and load it into targets. They provide a visual representation of the flow of the data and the operations performed on the data.
When you design a mapping in Warehouse Builder, you use the Mapping Editor interface. Alternatively, you can create and define mappings using OMB Plus, the scripting interface for Warehouse Builder. For information on how to create and define mappings in OMB Plus, see the Oracle Warehouse Builder Scripting Reference .
Before you can begin designing mappings, you must define an Oracle warehouse module. Warehouse Builder contains several types of modules. However, the Oracle warehouse module is the only module that stores mapping logic. For more information about Oracle warehouse modules, see "Creating Warehouse Modules".
When you define a mapping, you create a container that holds the operators defining the ETL logic. To define a mapping, complete the following steps:
After you define the mapping you can validate the code for the mapping. For information, see Chapter 12, "Validating Objects". You can also interrelate mappings by creating process flows. For more information, see Chapter 10, "Designing Process Flows".
The first step in defining a mapping is to create a mapping using the Mapping Wizard.
To create a mapping:
Navigate to the Mappings node in a project. The Mappings node is located under an Oracle warehouse module which is contained under the Oracle folder under the Databases node.
Figure 6-1 shows the Mappings node containing maps MAP1, MAP2, MAP3, and MAP4. The Oracle warehouse module in this example is named ORCL_MOD.
Right-click Mappings and then select Create Mapping.
Warehouse Builder opens the New Mapping Wizard.
Click Next.
The New Mapping Wizard displays the Name page.
Enter a name and description for the new mapping.
Click OK.
Warehouse Builder stores the definition for the mapping and inserts its name in the warehouse module navigation tree. Warehouse Builder opens a Mapping Editor for the mapping you created. The Mapping Editor displays the name of the mapping in the title bar.
The Mapping Editor is the interface for designing, and editing mappings. The Mapping Editor includes a variety of operators that you add and connect to design a mapping. Figure 6-2 shows the Mapping Editor with three connected operators.
The Mapping Editor has the following components:
Menu Bar: The menu bar provides access to the Mapping Editor commands.
Toolbar: The toolbar provides access to commonly used commands.
Toolbox: The toolbox contains operator icons. To include an operator, drag an operator icon on to the Mapping Editor canvas.
Mapping Editor Canvas: The canvas provides the work space where you can create and modify mappings.
To open the Mapping Editor:
From the navigation tree, locate an Oracle warehouse module.
These modules are located under the Oracle database folder in a project or collection, as shown in Figure 6-1. If your project or collection does not have a warehouse module, create one using the instructions in "Creating Warehouse Modules".
Expand the Mappings node.
Open the Mapping Editor in one of the following ways:
The basic design element for a mapping is the operator. When you design a mapping in Warehouse Builder, you select operators from the Mapping Editor toolbox and drag them onto the canvas.
In Warehouse Builder, there are the following types of operators:
Source and Target Operators: Source and target operators represent relational database objects and flat file objects in the mapping. For details on using SAP source data, see "Using SAP R/3 Data in Warehouse Builder".
Data Flow Operators: Data flow operators transform data.
Use source and target operators to represent relational database objects and flat file objects. Table 6-1 lists each source and target operator alphabetically, gives a brief description, and shows the associated icon in the Mapping Editor.
Table 6-1 Source and Target Operators
| Icon | Operator | Description |
|---|---|---|
|
|
Mapping Advanced Queue Operator |
Represents an Advanced Queue you previously imported. |
 |
Mapping Cube |
Represents a cube that you previously defined. |
 |
Mapping Dimension |
Represents a dimension that you previously defined. |
 |
Mapping External Table |
Represents an external table that you previously defined or imported. |
 |
|
Represents a flat file that you previously defined or imported. |
 |
Mapping Materialized View |
Represents a materialized view that you previously defined. |
 |
Mapping Table |
Represents a table that you previously defined or imported. |
 |
Mapping View |
Represents a view that you previously defined or imported. |
Use data flow operators to transform data in a mapping. Table 6-2 lists each data flow operator alphabetically, gives a brief description, and shows the associated icon in the Mapping Editor. For more information on operators, see Chapter 8, "Using Mapping Operators".
Table 6-2 Data Flow Operators
| Icon | Operator | Description |
|---|---|---|
 |
|
Performs data aggregations, such as SUM and AVG, and provides an output row set with aggregated data. |
 |
|
Produces a single output group that can contains one or more constant attributes. |
 |
|
Provides information such as record number, system date, and sequence values. |
 |
|
Removes duplicate data in a source by placing a DISTINCT clause in the select code represented by the mapping. |
 |
|
Enables you to write SQL expressions that define non-procedural algorithms for one output parameter of the operator. The expression text can contain combinations of input parameter names, variable names, and library functions. |
 |
|
Conditionally filters out rows from a row set. |
 |
|
Joins multiple row sets from different sources with different cardinalities and produces a single output row set. |
 |
|
Performs a lookup of data from a lookup object such as a table, view, cube, or dimension. |
 |
Mapping Input Parameter Operator |
Passes parameter values into a mapping. |
 |
Mapping Output Parameter Operator |
Sends values out of a mapping. |
 |
|
Generates sequential numbers that increment for each row. |
 |
|
Identifies and corrects errors and inconsistencies in name and address source data. |
 |
|
Transforms a single row of attributes into multiple rows. Use this operator to transform data that contained across attributes instead of rows. |
 |
|
Calls a function or procedure after executing a mapping |
 |
|
Calls a function or procedure prior to executing a mapping |
 |
|
Performs union, union all, intersect, and minus operations in a mapping. |
 |
|
Sorts attributes in ascending or descending order. |
 |
|
Splits a single input row set into several output row sets using a boolean split condition. |
 |
|
Enables you to develop custom code to manipulate a set of input rows and return a set of output rows of the same or different cardinality that can be queried like a physical table. |
 |
|
Transforms the attribute value data of rows within a row set using a PL/SQL function or procedure. |
 |
|
Converts multiple input rows into one output row. It enables you to extract from a source once and produce one row from a set of source rows that are grouped by attributes in the source data. |
For every source and target operator you add, Warehouse Builder creates and maintains a version of that object in the Warehouse Builder repository and a separate version for the Mapping Editor. For example, when you add a table to a mapping, Warehouse Builder maintains a copy of the table in the repository. Refer to it as the repository table. Refer to the table in the mapping as the mapping table.
In addition to maintaining separate repository objects and mapping objects for sources and targets, Warehouse Builder also maintains separate repository objects for the following data flow operators:
Key LookUps
Mapping Input Parameters
Mapping Output Parameters
Mapping Sequences
Transformations
Warehouse Builder maintains separate repository objects so that you can reconcile changing definitions of these objects. For example, as you make changes to a mapping table, you may want to propagate those changes back to the repository table associated with it. Conversely, if you import a new metadata definition for the repository table, you may want to propagate those changes to the mapping table. You can accomplish these tasks by a process known as reconciliation. When you reconcile, you bind one object definition to another. For more information on binding objects, see "Reconciling Operators and Repository Objects".
The concept of binding a mapping operator to a repository object is important because it affects how you add operators to a mapping.
To add an operator to a mapping:
Open the Mapping Editor.
From the Mapping menu, select Add and select an operator. Alternatively, you can drag an operator icon from the toolbox and drop it onto the Mapping Editor canvas.
If you select an operator that you can bind to a repository object, the Mapping Editor displays the Add Mapping <operator name> dialog. For details on how to use this dialog, see "Adding Bindable Operators".
If you select an operator that you cannot bind to a repository object, Warehouse Builder may display a wizard or dialog to assist you in creating the operator. For more information, refer to Chapter 8, "Using Mapping Operators", which provides an alphabetical listing of each operator and how to use them when designing mappings.
Follow any prompts Warehouse Builder displays and click OK.
The Mapping Editor displays the operator maximized on the canvas as shown in Figure 6-3. You can view each attribute name and data type. The operator name appears in the upper left corner. If you want to minimize the icon, click the arrow in the upper right corner.
Use this section for information on adding bindable operators to a mapping. Bindable operators include the following operators
listed in a columnar table that reads down the columns left to right to conserve space:
|
|
|
|
|
|
|
|
|
|
|
|
|
N/A |
When you add an operator that you can bind to a repository object, the Mapping Editor displays the Add Mapping <operator name> dialog. Figure 6-4 shows the dialog for adding a mapping table operator.
Select one of the four options. Depending on the type of operator you select, some of the options may be greyed out.
Use this option when you want to use the Mapping Editor to define a new object. After you select Create Unbound Object with No Attributes, type a name for the new object. Warehouse Builder displays the operator on the canvas without any attributes. For an example on how to quickly create a staging table using an unbound table operator and the Auto-Mapping dialog, see "Example: Using the Mapping Editor to Create Staging Area Tables".
Use this option when you want to invoke a wizard to create a new object. First select a target module and then Warehouse Builder launches a wizard that assists you in creating the new operator. After you complete the wizard, Warehouse Builder saves a copy of the operator in the repository. For example. if you select this option when adding a mapping table, Warehouse Builder launches the New Table Wizard, creates the mapping table, creates the repository table, and binds the mapping table to the repository table.
Use this option when you want to create an operator based on an object that you can import into the repository. Select a module into which you import the object.
If you select a module without a database link defined, Warehouse Builder displays the New Database Link Information dialog. See "Configuring Connections for Database Sources" for information on how to define a new database link.
If you select a module with a defined database link, Warehouse Builder displays the Import Wizard. Enter the appropriate information in the dialog.
Use this option when you want to add an operator to the mapping based on an object you previously defined or imported into the repository.
Either type the prefix to search for the object or select from the displayed list of objects within the selected module.
To select multiple items, press the Control key as you click each item. To select a group of items located in a series, click the first object in your selection range, press the Shift key, and then click the last object.
You can add operators based on repository objects within the same module as the mapping or from other modules. If you select a repository object from another module, the Mapping Editor creates a connector if one does not already exist. The connector establishes a path for moving data between the mapping location and the location of the repository object. For more information about locations and connectors, see "Defining Locations".
Each operator has an editor associated with it. Use the operator editor to specify general and structural information for operators, groups, and attributes. In the operator editor you can add, remove, or rename groups and attributes. You can also rename an operator.
To specify loading properties and conditional behaviors, use the properties windows. For information on properties windows, see "Mapping Naming Conventions".
To edit an operator, group, or attribute:
Select an operator from the Mapping Editor canvas.
Or select any group or attribute within an operator.
Right-click and select Edit.
The Mapping Editor displays the operator editor with a General tab, Groups tab, and a tab for each type of group in the operator. Figure 6-5 shows the General tab on the operator editor.
The General tab displays the operator name and an optional description. You can rename the operator and add a description. Name the operator according to the conventions listed in "Naming Conventions for Operators".
Edit group information on the Groups tab as shown in Figure 6-6.
Each group has a name, direction, and optional description. You can rename the group but you cannot change the group direction. A group can have one of these directions: Input, Output, Input/Output.
Depending on the operator, you can add and remove groups from the Groups tab. For example, you add input groups to Joiners and output groups to Splitters.
The operator editor displays a tab for each type of group displayed on the Groups tab. Each of these tabs displays the attribute name, data type, length, precision, scale, and optional description.
Edit attribute information on the each of the remaining tabs.
Figure 6-7 shows an Input/Output tab on the Operator Editor. In this example, the operator is a table and therefore has only the Input/Output tab. Other operators can have an Input tab and an Output tab.
You can add, remove, and edit attributes. The Mapping Editor greys out properties that you cannot edit. For example, if the data type is NUMBER, you can edit the precision and scale but not the length.
To assign correct values for datatype, length, precision, and scale in an attribute, follow PL/SQL rules. When you reconcile the operator, Warehouse Builder checks the attributes based on SQL rules.
When you name or rename objects in the Mapping Editor, use the following naming conventions.
Attribute and group names are logical. Although the attribute names of the object are often the same as the attribute names of the operator, their properties remain independent of the attributes of the operator to which they are bound. This protects any expression or use of an attribute from corruption if it is manipulated within the operator. You can rename groups and attributes independent of their sources.
When you add operators to a mapping, the Mapping Editor displays the physical name or the business name of the operator, depending on which naming mode you selected in the project Preferences window. See "Naming Preferences" for details on how to specify a naming mode.
Operator names display as business names if you select the business naming mode in the project Preferences window. When you add an operator to a mapping while working in the business naming mode, the Mapping Editor generates a default business name for the operator. You can change the business name to any name that meet the following requirements:
The length of the operator name can be any string of 200 characters. You must use double quotes for names containing spaces.
The operator name must be unique on its attribute group, attribute and display set level with respect to its parent.
Operator names display as physical names if you select the physical naming mode in the Project Preferences window. When you use the Warehouse Builder OMB Scripting Language, you navigate to operators based on their physical names. Because the physical name is used during scripting, physical names must meet the following requirements:
The operator name must be unique on its group, attribute and display set level with respect to its parent.
The operator name must contain no more than 28 characters. Two characters are used by Warehouse Builder.
The operator name must conform to the syntax rules for basic elements as defined in the Oracle Database SQL Reference.
In addition to physical and business names, operators also have bound names. Bound names are available as a logical and physical property to operators that can be bound. They are used to reference the object during code generation and have the following characteristics:
Bound names need not be unique.
Bound names must conform to the general Warehouse Builder physical naming rules.
Typically, you do not change bound names directly but perform an outbound reconciliation.
When you rename the logical name for an operator or attribute, Warehouse Builder propagates the new logical name as the bound name when you perform an outbound reconciliation. However, logical names can be up to 200 character while bound names are limited to 30 characters. Therefore, Warehouse Builder uses the first 30 characters of the logical name for the bound name.
A display set is a graphical representation of a subset of attributes. Use display sets to limit the number of attributes visible in an operator and simplify the display of a complex mapping.
By default, the operators contain a display set named ALL that shows all attributes contained in the repository object represented by the operator.
Operators also contain a default display set named MAPPED that includes only those attributes in a group that are connected to another operator in a mapping. The MAPPED display set updates automatically whenever attribute connections are changed in the Mapping Editor. Table 6-3 describes the default Display Sets.
Table 6-3 Default Sets
| Display Set | Description |
|---|---|
|
All |
Includes all attributes in an operator. |
|
Mapped |
Includes only those attributes in an operator that are connected to another operator. |
|
Hierarchies |
For each hierarchy in a Dimension, a display set containing all the level attributes in that hierarchy. |
See "Adding Attribute Sets" for information on attribute sets. See "Editing Operators" for information on operator attributes and groups.
You can define display sets for any operator in a mapping.
To define a display set:
Right-click a group in an operator, and select Display Set.
The Display Set dialog appears, as shown in Figure 6-8.
Click Add.
Type a name in the Name column and press Enter.
All available attributes for the operator appear in Attributes of the selected attribute set field. The Type column is automatically set to USER_DEFINED.
You cannot edit or delete a PREDEFINED Attribute Set.
In the Include column, select each attribute you want to include in the display set.
Click Select All to include all attributes and Deselect All to exclude all the attributes.
Click OK.
The group for the operator now lists only those attributes contained within the Attribute Set selected for display.
When you add a source or target operator to a mapping, all of the columns from the physical source or target are added as attributes to the operator. Some of these attributes may be unnecessary in the mapping. By compressing a mapping, any source or target mapping attribute that is not connected to any other attribute is deleted from the mapping. This simplifies the display of the mapping and improves performance when importing and exporting MDL files.
After a mapping is compressed, you can only undo the compression by performing an inbound reconciliation. Once you reconcile outbound, the compressed mapping is saved in the repository and cannot be rolled back.
To compress a mapping:
Open a mapping in the Mapping Editor.
From the Mapping menu, select Compress Mapping.
A warning message displays stating that unconnected Oracle relational source and target attributes will be deleted, as shown in Figure 6-9.
Click Yes to continue. Any source or target mapping attribute that is not connected to any other attribute is deleted from the mapping except for unconnected attributes that are part of an update or delete condition and flat file sources.
After you have selected mapping source operators, data flow operators, and target operators, you are ready to connect them. Data flow connections graphically represent how the data flows from a source, through operators, and to a target.
You can connect operators by one of the following methods:
Connecting Attributes: Connect individual operator attributes to each other.
Connecting Groups: When you connect groups, you can control which attributes are connected using the Auto-Mapping dialog.
The position of the attribute where you start your mapping and the position where you release the mouse button determines the type of data flow connection you make in the mapping.
You can draw a line from a single output attribute of one operator to a single input attribute of another operator.
To connect attributes:
Click and hold down the mouse button while the pointer is positioned over an output attribute.
Drag the mouse away from the output attribute and toward the input attribute to which you want data to flow.
As you drag the mouse, a line appears on the Mapping Editor canvas to indicate a connection, as shown in Figure 6-10.
Release the mouse over the input attribute.
Repeat steps one through three until you have created all the data flow connections appropriate for your situation.
When connecting attributes, keep the following rules in mind:
You cannot connect to the same input attribute twice.
You cannot connect attributes within the same operator.
You cannot connect out of input only attribute.
You cannot connect into an output only attribute.
You cannot create mapping lines that contradict an established cardinality. Instead, use a Join operator.
When you connect groups, the Mapping Editor assists you by either automatically copying the attributes or prompts you for more information in the Auto-Mapping dialog described in "Using the Auto-Mapping Dialog".
If you connect to a target group with no existing attributes, the Mapping Editor automatically copies the attributes and creates the mapping lines. This is useful for designing mappings such shown in "Example: Using the Mapping Editor to Create Staging Area Tables".
You can use the Mapping Editor with an unbound table operator to quickly create staging area tables.
The following instructions describe how to create a staging table based on an existing source table. You can also use these instructions to create views, materialized views, flat files, and transformations.
To map a source table to a staging table:
In the Mapping Editor, add the source table.
From the menu bar, select Mappings and Add. Use the Add Mapping Table dialog to select and bind the source table operator in the mapping.
Add a new unbound table operator.
From the menu bar, select Mappings and Add. From the Add Mapping Table dialog, select Create unbound mapping table with no attributes. The mapping should now resemble Figure 6-11 with one source table and one staging area table without attributes.
With the mouse button positioned over the group in the operator for the source table, click and hold down the mouse button.
Drag the mouse to the staging area table group.
Warehouse Builder copies the source attributes to the staging area table and creates the corresponding mapping lines.
In the Mapping Editor, select the unbound table you added to the mapping. Right click and select Reconcile Outbound. Warehouse Builder displays the Outbound Reconcile Operator dialog as shown in Figure 6-12.
Select Create a new table and specify the target module in which to create the table.
Warehouse Builder creates the new table in the target module you specify.
If you connect to a target with existing attributes, the Mapping Editor launches the Auto-Mapping dialog as shown in Figure 6-13.
Select one of the following rules for copying and creating mapping lines:
After you select one of the three options, select Go. The Auto-Mapping dialog displays a list of the source and target attributes to be mapped as shown in Figure 6-14.
You can deselect attributes by clearing the Map checkbox. The Auto-Mapping describes the results of your selections under Comments. See "About Auto-Mapping Dialog Comments" for more information about these comments.
When you select OK, Warehouse Builder copies the source attributes to the target group and creates mapping lines between the source and target group.
Use this option to copy source attributes to a target group that already contain attributes. Warehouse Builder creates mapping lines from the source attributes to the new target attributes based on the selections you make in the Auto-Mapping dialog. Warehouse Builder does not perform this operation on target groups that do not accept new input attributes such as dimension and cube target operators.
Use this option to create mapping lines between existing attributes based on the position of the attributes in their respective groups. Source and target attributes are matched in order, until all attributes for a target are matched. If a source operator contains more attributes than a target, then the remaining source attributes do not map to a target.
Use this option to create mapping lines between existing attributes with matching names, as shown in Figure 6-15. By selecting from the list of options, you can specify auto-mapping between names that do not match exactly. You can combine these options:
Ignore case differences: Enables lower-case and upper-case characters to match. For example, the attributes FIRST_NAME and First_Name will match.
Ignore special characters: Enables you to specify characters to ignore during the matching process. Enter the characters into the text field to the right of this option. For example, if you specify a hyphen and underscore, the attributes FIRST_NAME, FIRST-NAME, and FIRSTNAME will all match.
Ignore source prefix, Ignore source suffix, Ignore target prefix, Ignore target suffix: Enables you to specify prefixes and suffixes to ignore during matching. For example, if you select Ignore source prefix and enter USER_ into the text field, then the source attribute USER_FIRST_NAME will match the target attribute FIRST_NAME.
After you set the matching criteria, click Go.
If you attempt to map a source group with no attributes, or if you are matching by name and there are no matches, an error dialog displays.
If one or more of the attributes can be matched, the Displayed Mappings field displays the matches. You can verify and deselect the mapping lines before they are implemented.
In the Displayed Mapping area of the Auto-Mapping dialog, the Comments column contains information about the results from your matching criteria.
When a mapping results in duplicated attributes, the following messages appear:
Target was already mapped: A target attribute can take part in only one mapping. This target attribute is already used in a mapping.
Target may not be double-mapped: If name matching finds multiple matches to a target attribute, only one mapping can be created. The first mapping is selected, and all other mappings to that target attribute are not selected. Selecting one attribute deselects any other attribute.
Source may be double-mapped: A source attribute can be mapped to different target attributes. If name matching finds multiple matches from a source attribute, all of these mappings can be created. You can then deselect the target attributes that you do not want to map to by clicking their check boxes.
When a source group has more attributes than a target group or a target group has more attributes than a source group, the following messages appear:
Source will not be mapped: No target attributes are available for the source.
Target will not be mapped: No source attributes are available for the target.
If you open the Auto-Mapping dialog while the Mapping Editor is in read-only mode, an error displays. You must have read/write permission before you can use auto-mapping. See "Supporting Multiple Users" for more information on read/write permissions.
This section discusses how to set properties for the following operators in a mapping:
For information on setting other operator types, see the following sections:
Each operator, group, and attribute has a properties window associated with it. Use properties windows to specify loading settings and other conditional settings. You can view and set the following types of properties:
Operator Properties: Properties that affect the operator as a whole. The properties you can set depend upon the operator type.
Group Properties: Properties that affect a group of attributes. Most operators do not have properties for their groups. Examples of operators that do have group properties include the splitter operator and the deduplicator. For information on how to set properties for groups, look up the operator by name in Chapter 8, "Using Mapping Operators".
Attribute Properties: Properties that pertain to attributes in source and target operators. Examples of attribute properties are data type, precision, and scale.
To add, delete, or rename attributes, use the operator editor. For information on the operator editor, see "Editing Operators".
To open the properties windows:
Click one of the properties.
The properties window displays the name of the property you selected in the title bar.
The Mapping Editor displays a description for the property in a window at the bottom of the properties window. You can expand the window to view the description.
For objects that contain many properties, click the flashlight icon in the upper left corner and enter the property name to search for in Find.
To sort the list alphabetically, click the plus icon in the upper left corner.
By default, the properties window lists the properties by category.
Select an operator in the Mapping Editor and double-click it or right-click and select Operator Properties.
The Mapping Editor displays the properties window for the object you select. Figure 6-16 shows the properties window for a mapping table operator.
The properties window contains the following categories of parameters for source and target operators:
Operator Name: Under the operator name, you can view the Bound Name, and set Primary Source indicator, and the Loading Type.
Conditional Loading: You can set Target Filter for Update, Delete Target Condition, and Match By Constraint.
Keys (read-only): You can view the Key Name, Key Type, and Referenced Keys. If the operator functions as a source, the key settings are used in conjunction with the join operator. If the operator functions as a target, the key settings are used in conjunction with the Match By Constraint parameter.
The operator properties window displays as shown in Figure 6-17.
The name used by the code generator. If an operator is currently bound and reconciled, then this property is read-only. If an operator is not yet bound, you can edit the bound name within the Mapping Editor before you reconcile it to a repository object.
For Oracle Application Embedded Data Warehouse (EDW) users, refer to EDW documentation. For all other users, disregard this parameter.
Select a loading type for each target operator.
INSERT: The incoming row sets are inserted into the data target. Insert fails if a row already exists with the same primary or unique key.
UPDATE: The incoming row sets are used to update existing rows in the data target. If no rows exist for the specified match conditions, no changes are made.
INSERT/UPDATE: For each incoming row, an insert operation is performed first. If the insert fails, an update operation occurs. If there is no matching records for update, the insert is performed. If you select Insert/Update and the default mode is row based, you must set unique constraints on the target. If you select Insert/Update and the default operating mode is set based, Warehouse Builder generates a merge statement to Oracle Database.
UPDATE/INSERT: For each incoming row, an update operation is performed first. If you select Update/Insert and the default operating mode for the target is set-based, Warehouse Builder generates a merge statement to Oracle Database.
DELETE: The incoming row sets are used to determine which of the rows at the target get deleted.
TRUNCATE/INSERT: The data target is truncated before the incoming row set is inserted into the data target. If you choose this option, the procedure cannot be rolled back even if the execution of the mapping fails.
DELETE/INSERT: The rows in the data target are deleted before the incoming row set is inserted into the data target.
CHECK/INSERT: The data target is checked to see if it contains any rows. If not, the incoming row sets are inserted into the data target.
None: No operation is performed on the data target. This setting is useful for testing. Extraction and transformations run but have no effect on the target.
Configure SQL*Loader parameters to define SQL*Loader options for your mapping. The values chosen during configuration directly affect the content of the generated SQL*Loader and the runtime control files. SQL*Loader provides two methods for loading data:
Conventional Path Load: Executes an SQL INSERT statement to populate tables in an Oracle database.
Direct Path Load: Eliminates much of the Oracle database overhead by formatting Oracle data blocks and writing the data blocks directly to the database files. Because a direct load does not compete with other users for database resources, it can usually load data at or near disk speed.
Certain considerations such as restrictions, security, and backup implications are inherent to each method of access to database files. See Oracle Database Database Utilities for more information.
When designing and implementing a mapping that extracts data from a flat file using SQL*Loader, you can configure different properties affecting the generated SQL*Loader script. Each load operator in a mapping has an operator property called Loading Types. The value contained by this property affects how the SQL*Loader INTO TABLE clause for that load operator is generated. Although SQL*Loader can append, insert, replace, or truncate data, it cannot update any data during its processing. Table 6-4 lists the INTO TABLE clauses associated with each load type and their affect on data in the existing targets.
Table 6-4 Loading Types and INTO TABLE Relationship
| Loading Types | INTO TABLE Clause | Affect on Target with Existing Data |
|---|---|---|
|
INSERT/UPDATE |
APPEND |
Adds additional data to target. |
|
DELETE/INSERT |
REPLACE |
Removes existing data and replaces with new (DELETE trigger fires). |
|
TRUNCATE/INSERT |
TRUNCATE |
Removes existing data and replaces with new (DELETE trigger fires). |
|
CHECK/INSERT |
INSERT |
Assumes target table is empty. |
|
NONE |
INSERT |
Assumes target table is empty. |
If the condition evaluates to true, the row is included in the update loading operation.
When loading target operators with the UPDATE or the DELETE conditions, you can specify matching criteria. You can set matching and loading criteria manually or choose from several built-in options. Use Match By Constraint to indicate whether unique or primary key information on a target overrides the manual matching and loading criteria set on its attributes. When you click the property Match By Constraint, Warehouse Builder displays a drop down box listing the constraints defined on that operator and the built-in loading options, as shown in Figure 6-18.
If you select All Constraints, all manual attribute load settings are overruled and the data is loaded as if the load and match properties of the target attributes were set as displayed in Table 6-5.
Table 6-5 All Constraints Target Load Settings
| Load Setting | Key Attribute | All Other Attributes |
|---|---|---|
|
NO |
YES |
|
|
YES |
NO |
|
|
YES |
NO |
If you select No Constraints, all manual load settings are honored and the data is loaded accordingly.
If you select a constraint previously defined for the operator, all manual attribute load settings are overruled and the data is loaded as if the load and match properties of the target were set as displayed in Table 6-7.
Table 6-6 Target Load Settings for a Selected Constraint
| Load Setting | Selected Key Attributes | All Other Attributes |
|---|---|---|
|
Load Column when Updating Row |
NO |
YES |
|
Match Column when Updating Row |
YES |
NO |
|
Match Column when Deleting Row |
YES |
NO |
If you made changes at the attribute level and you want to default all settings, click Advanced. Warehouse Builder displays a drop down box listing the loading options as shown in Figure 6-19. Warehouse Builder defaults the settings based on the constraint type you select.
For example, if you want to reset the match properties for all key attributes, click Advanced, select No Constraints, and click OK. Warehouse Builder overwrites the manual load settings and loads the data based on the settings displayed in Table 6-7.
Table 6-7 Default Load Settings for Advanced No Constraints
| Load Setting | All Key Attributes | All Other Attributes |
|---|---|---|
|
Load Column when Updating Row |
YES |
YES |
|
Match Column when Updating Row |
NO |
NO |
|
Match Column when Deleting Row |
NO |
NO |
Alternatively, if you click Advanced and select All Constraints, Warehouse Builder overwrites the manual load settings and loads the data based on the settings displayed in Table 6-8
Name of the primary, foreign, or unique key.
Local columns that define this key. Each key column is comma-delimited if the operator contains more than one key column.
Type of key, either primary, foreign, or unique.
If the operator contains a foreign key, Referenced Keys displays the primary key or unique key for the referenced object.
For each attribute in a source and target operator, parameters are categorized into the following types:
Attribute Properties: Under the attribute properties, you can view the Bound Name and set the Data Type, Precision, Scale, and Length.
Loading Properties: The mapping table, mapping dimension, mapping cube, mapping view, and mapping materialized view operators have a Loading Properties category. This category contains the following settings: Load Column When Inserting Row, Load Column When Updating Row, Match Column When Updating Row, Update: Operation, and Match Column When Deleting Row.
Figure 6-20 shows the attribute parameters for mapping tables, mapping dimensions, mapping views, and mapping materialized views.
Name used by the code generator to identify this item. By default, it is the same name as the item. This is a read-only setting when the operator is unbound.
Data type of the attribute.
The maximum number of digits this attribute will have if the data type of this attribute is a number or a float. This is a read-only setting.
The number of digits to the right of the decimal point. This only applies to number attributes.
The maximum length for a CHAR, VARCHAR, or VARCHAR2 attribute.
This setting prevents data from moving to a target even though it is mapped to do so. If you select Yes (default), the data will reach the mapped target.
This setting prevents the selected attribute data from moving to a target even though it is mapped to do so. If you select Yes (default), the data reaches the mapped target attribute. If all columns of a unique key are not mapped, then the unique key is not used to construct the match condition. If no columns of a unique key are mapped, Warehouse Builder displays an error. If a column (not a key column) is not mapped, then it is not used in loading.
This setting updates a data target row only if there is a match between the source attribute and mapped target attribute. If a match is found, then an update occurs on the row. If you set this property to Yes (default), the attribute is used as a matching attribute. If you use this setting, then all the key columns must be mapped. If there is only one unique key defined on the target entity, use constraints to override this setting.
You can specify an update operation to be performed when Warehouse Builder locates a matching row and updates the target. An update operation is performed on the target attribute using the data of the source attribute. Table 6-9 lists the update operations you can specify and describes the update operation logic.
Table 6-9 Update Operations
| Operation | Example | Result If Source Value = 5 and Target Value = 10 |
|---|---|---|
|
= |
TARGET = SOURCE |
TARGET = 5 |
|
+= |
TARGET = SOURCE + TARGET |
TARGET = 15 (5 + 10) |
|
-= |
TARGET = TARGET - SOURCE |
TARGET = 5 (10 - 5) |
|
=- |
TARGET = SOURCE - TARGET |
TARGET = negative 5 (5 - 10) |
|
||= |
TARGET = TARGET || SOURCE |
TARGET = 105 (10 concatenated with 5) |
|
=|| |
TARGET = SOURCE || TARGET |
TARGET = 510 (5 concatenated with 10) |
Deletes a data target row only if there is a match between the source attribute and mapped target attribute. If a match is found, then a delete occurs on the row. If you set this property to Yes (default), the attribute is used as a matching attribute. Constraints can override this setting.
You can set properties for a flat file operator as either a source or target. You can set Loading Types and the Field Names in the First Row setting. All other settings are read-only and depend upon how you imported the flat file. The operator properties window displays as shown in Figure 6-21.
Select a loading type from the drop-down list:
Insert: Creates a new target file. If a target file already exists, then it is replaced with a new target file.
Update: Creates a new target file. If a target file already exists, then it is appended.
None: No operation is performed on the data target. This setting is useful for test runs where all transformations and extractions are run but have no effect on the target.
Set this property to True if you want to write the field names in the first row of the operator or False if you do not.
For every operator you define in a mapping, there can be a corresponding definition in the Warehouse Builder repository. When you make changes to an operator in a mapping, you may want to reconcile the operator and its corresponding repository definition.
When you reconcile operators, you ensure that the operator matches the repository object it represents. You can reconcile in one of two ways:
Inbound Reconciliation: This updates or reconciles the operator with the definition of a specified repository object.
Outbound Reconciliation: This updates a selected repository object to reflect changes you made to the operator in a mapping.
Reconciliation is different from synchronization. Synchronization ensures that you are up-to-date with changes made by other users in a multiuser environment. Reconciliation updates operators with their corresponding repository objects.
Inbound reconciliation updates the operator with the definition of a specified repository object. You can also use Inbound Reconcile to bind an unbound operator to a repository object. You can reconcile an operator by name, position, object identifier match, or any combination of these methods.
You can use inbound reconciliation for any of the following reasons:
Propagate changes: Use inbound reconciliation to propagate changes you made in a repository object to its associated operator. Changes to the repository object can include structural changes, attribute name changes, attribute data type changes.
Update an operator based on a new repository object: You can associate an operator with a new repository object. For example, if you are migrating mappings from one version of a data warehouse to a newer version and you maintain different object definitions for each version. Or, if you want to provide access to the underlying source data through views rather than directly against the base tables.
Prototype mappings using tables: When you are satisfied with the transformation logic, you can switch to other object types for production mappings such as views, materialized views, or cubes.
Unless you remove attributes, inbound reconciliation has no impact on the dependent relationship between other operators in the mapping to repository objects. Warehouse Builder preserves those dependencies.
When you perform inbound reconciliation on an external table operator, Warehouse Builder updates the operator based on the repository external table only and not its associated flat file. To update an external table based on its associated flat file, see "Reconciling an External Table Definition with a Record in a File".
Reconciliation also updates additional logical properties of an operator. For example, for a Mapping Flat File operator, information about the character set and filename are refreshed.
To perform inbound reconciliation on an operator:
Select an operator on the Mapping Editor canvas.
From the Edit menu, select Reconcile Inbound or right-click the header of the operator and select Reconcile Inbound.
The Inbound Reconcile Operator dialog displays as shown in Figure 6-22.
From Reconcile with, select the type of repository object to use to update the operator. The types of objects available depends on the operator type. See Table 6-10 for a list of available objects.
Set the Match Strategies.
Matching by Object Identifier: Use this strategy if you want to keep your operators consistent with changes to the previously bound repository object. Match by object identifier is not available if you want to reconcile to a different repository object.
Matching by Bound Name: Use this strategy if you want to maintain equivalence of operator bound names and object physical names. You can use this strategy with a different repository object if there are changes that alter the structure of the operator.
Matching by Position: Use this strategy for reconciliation with a different repository object if you want to preserve the business names of your operator attributes. This strategy is most effective when the only changes to the repository object are the addition of columns, fields, or parameters at the end of the object.
For more information, see "Match Strategies".
Click OK.
Table 6-10 describes the repository objects that you can use to update an operator by performing an inbound reconciliation.
Table 6-10 Operators Reconciled with Repository Objects
| Operator Object | Repository Objects Available for Reconciliation |
|---|---|
|
Mapping Table |
Tables, External Tables, Views, Materialized Views, Flat Files, Dimensions and Cubes |
|
Mapping External Table |
Tables, External Tables, Views, Materialized Views, Flat Files, Dimensions and Cubes |
|
Mapping View |
Tables, External Tables, Views, Materialized Views, Files, Dimensions and Cubes |
|
Mapping Materialized View |
Tables, External Tables, Views, Materialized Views, Files, Dimensions and Cubes |
|
Mapping Sequence |
Sequences only |
|
Mapping Flat File |
Tables, External Tables, Views, Materialized Views, Flat Files, Dimensions and Cubes |
|
Mapping Dimension |
Tables, External Tables, Views, Materialized Views, Flat Files, Dimensions and Cubes |
|
Mapping Cube |
Tables, Views, Materialized Views, Flat Files, Dimensions and Cubes |
|
Key Lookup Operator |
Tables only |
|
Pre Mapping Process Operator |
Transformations only |
|
Post Mapping Process Operator |
Transformations only |
|
Mapping Transformation Operator |
Transformations only |
Outbound reconciliation updates a selected repository object to reflect changes in the operator. You can perform outbound reconciliation on tables, views, materialized views, transformations, and flat file operators. You can use outbound reconciliation for any of the following reasons:
Create new repository objects: Use outbound reconciliation to create a new repository object in a target module. The new repository object must be of the same type. For example, you can use outbound reconciliation to create a new table based on an existing table, but you cannot create a new view based on an existing table.
Propagate changes: Use outbound reconciliation to propagate changes you made in an operator to its associated repository object. When you rename the logical name for an operator or attribute, Warehouse Builder propagates the first 30 characters of the logical name as the bound name.
Replace repository objects: Use outbound reconciliation to replace an existing repository object.
Outbound reconciliation has no impact on the dependent relationship between other operators in the mapping to repository objects. Table 6-11 lists the operators eligible for Outbound Reconcile.
Table 6-11 Outbound Reconcile Operators
| Mapping Objects | Create Repository Objects | Propagate Changes | Replace Repository Objects | Notes |
|---|---|---|---|---|
|
External Tables |
Yes |
Yes |
Yes |
Updates the repository external table only and not the flat file associated with the external table. See "Reconciling an External Table Definition with a Record in a File". |
|
Flat Files |
Yes |
Yes |
No |
Creates a new, comma-delimited flat file for single record type flat files only. Cannot replace an existing file. |
|
Mapping Input Parameters |
Yes |
Yes |
Yes |
Copies input attributes and data types as input parameters. |
|
Mapping Output Parameters |
Yes |
Yes |
Yes |
Copies output attribute and data types as return specification for the function. |
|
Materialized Views |
Yes |
Yes |
Yes |
Copies attributes and data types as columns. |
|
Tables |
Yes |
Yes |
Yes |
Copies attributes and data types as columns. Constraint properties are not copied. |
|
Transformations |
Yes |
Yes |
Yes |
Not applicable. |
|
Views |
Yes |
Yes |
Yes |
Copies attributes and data types as columns. |
To perform an outbound reconciliation on an existing operator:
Select an operator on the canvas.
From the Edit menu, select Reconcile Outbound or right-click the header of the operator and select Reconcile Outbound.
The Outbound Reconcile dialog displays, as shown in Figure 6-23.
Select either Create a new <object type> or Reconcile with an existing <object type>.
If you select Create a new <object type>, Warehouse Builder creates the object in the target module you specify. This option is useful for quickly creating staging tables for a data warehouse. For instructions how to use outbound reconciliation and the Auto-Mapping dialog to create staging tables, see "Example: Using the Mapping Editor to Create Staging Area Tables".
If you select Reconcile with an existing <object type>, you can make selections for either updating or replacing the object.
To update the repository object, select the associated operator from the Reconcile with list. For example, to update the S_TABLE repository object, select the S_TABLE operator. The types of changes propagated from the operator to the repository object include changes to an operator business name and physical name and changes to an attribute data type.
To replace the repository object, select the another operator from the Reconcile with list. For example, to replace the S_TABLE1 repository object, select S_TABLE2 operator. You can only replace a repository object with the same type of repository object. For instance, you can use outbound reconciliation to replace TABLE_A with TABLE_B but not VIEW_B.
Set the Match Strategies. For more information, see "Match Strategies".
Click OK.
You can specify the following strategies for reconciling an object in a mapping:
You can specify more than one reconciliation strategy. If you select more than one strategy, then Warehouse Builder performs the matches in the following order:
Match by object identifier
Match by position
Match by name
Use this strategy if you want operators to be consistent with changes to the repository object and if you want to maintain separate business names for your operator attributes despite changes to physical names in the repository object. Match by object identifier is not available if you want to reconcile with a different type of repository object.
This strategy uses the unique object identifiers to determine the correlation between the operator attributes and those of the selected repository object. Warehouse Builder removes attributes from the operator that do not correspond to an attribute in the repository object. This can happen if an attribute was added to the operator and not reconciled, or if a column was removed from the repository object before reconciliation. When an attribute is removed, any incoming or outgoing mapping lines connected to that attribute are also removed from the canvas. Attributes of the selected repository object that cannot be matched with those of the operator are added as new attributes at the end of the operator. Mapping lines for matched attributes are preserved.
Use this strategy if you want bound names in the operator to be consistent with physical names for the object. If a repository object column was renamed, it is interpreted as if the column were deleted and a new column inserted. The mapping lines for renamed attributes are removed. You can also use this strategy with a different repository object if there are changes in the repository object that would change the structure of the operator.
This strategy uses matching between the bound names of the operator attributes and the physical names of the repository object attributes. Matching is case-sensitive. On inbound reconciliation, attributes of the operator that cannot be matched with those of the repository object are removed. When an attribute is removed, any incoming or outgoing mapping line connected to that attribute is also removed from the canvas. Attributes of the selected repository object that cannot be matched with those of the operator are added as new attributes to the operator. Mapping lines for matched attributes are preserved. Because bound names are read-only after you have bound an operator to a repository object, it is not possible to manipulate them to achieve a different match result during inbound reconciliation.
This strategy matches operator attributes with columns, fields, or parameters of the selected repository object by position. The first attribute of the operator is reconciled with the first attribute of the repository object, the second with the second, and so on. If the operator has more attributes than the repository object, then the excess attributes are removed from the operator. If you remove an attribute, any incoming or outgoing mapping line connected to that attribute is also removed from the canvas. If the selected repository object has more attributes than the operator, then they are added as new attributes to the end of the operator. Mapping lines for existing attributes in the operator are preserved. Use this strategy to reconcile with a different repository object if you want to preserve the business names of your operator attributes. This strategy is most effective when the only changes to the repository object are the addition of extra columns, fields, or parameters at the end of the object.
