21 Data Quality Operators

Example of Matching and Merging Customer Data

Consider how you could utilize the Match-Merge operator to manage a customer mailing list. Use matching to find records that refer to the same person in a table of customer data containing 10,000 rows. For example, you can define a match rule that screens records that have similar first and last names. Through matching you may discover that 5 rows refer to the same person. You can merge those records into one new record. For example, you can create a merge rule to retain the values from the one of the five matched records with the longest address. The newly merged table now contains one record for each customer.

Table 21-1 shows records that refer to the same person prior to using the Match-Merge operator.

Table 21-2 shows the single record for Jane Doe after using the Match-Merge operator. Notice that the new record retrieves data from different rows in the sample.

Understanding Matching Concepts

When you use Warehouse Builder to match records, you can define a single match rule or multiple match rules. If you create more than one match rule, Warehouse Builder determines two rows match if those rows satisfy any of the match rules. In other words, Warehouse Builder evaluates multiple match rules using OR logic.

Designing Mappings with a Match-Merge Operator

Figure 21-1 shows a mapping you can design using a Match-Merge operator. Notice that the Match-Merge operator is preceded by a Name and Address operator, NAMEADDR, and a staging table, CLN_CUSTOMERS. You can design your mapping with or without a Name and Address operator. Preceding the Match-Merge operator with a Name and Address operator provides clean and standardized data before launching time consuming match and merge operations.

Figure 21-1 Match-Merge Operator in a Mapping

Description of "Figure 21-1 Match-Merge Operator in a Mapping"

Whether you include a Name and Address operator or not, be aware of the following considerations as you design your mapping:

• Operating modes: Warehouse Builder operators may accept either set-based or row-based input and generate either set-based or row-based output. SQL is set-based, so a set of records is processed at one time. PL/SQL is row-based, so each row in processed separately. When the Match-Merge operator matches records, it compares each row with the subsequent row in the source and generates row-based code only. A mapping that contains a Match-Merge operator can only run in row-based mode. For more information about set-based and row-based operators, refer to Chapter 8, "Understanding Performance and Advanced ETL Concepts".

• SQL based operators before Match-Merge: The Match-Merge operator accepts set-based SQL input, but generates only row-based PL/SQL output. Any operators that generate only SQL code must precede the Match-Merge operator. For example, the Joiner, Key Lookup, and Set operators generate set-based SQL output, so they must precede Match-Merge. If set-based operators appear after Match-Merge, then the mapping is invalid.

• PL/SQL input: The Match-Merge operator requires SQL input except from another Match-Merge operator, as described in "Using Two Match-Merge Operators". If you want to precede a Match-Merge with an operator that generates only PL/SQL output such as the Name and Address operator, you must first load the data into a staging table.

• Refining Data from Match-Merge operators: To achieve greater data refinement, map the XREF output from one Match-Merge operator into another Match-Merge operator. This scenario is the one exception to the SQL input rule for Match-Merge operators. With additional design elements, the second Match-Merge operator accepts PL/SQL. For more information, see "Using Two Match-Merge Operators".

Using Two Match-Merge Operators

Most match-merge operations can be performed by a single match-merge operator. However, if you are directing the output to two different targets, then you may need to use two match-merge operators in succession.

For example, when householding name and address data, you may need to merge the data first for addresses and then again for names. Assuming you map the MERGE output to a target table, you can map the XREF group to another Match-Merge operator. Although you could map the XREF group to a staging table, this practice can lead to significant loss of performance.

Figure 21-2 shows a mapping that uses two match-merge operators. The XREF group from MM is mapped directly to MM_1. For this mapping to be valid, you must assign the Match ID generated for the first XREF group as the Match Bin rule on the second Match-Merge operator.

Figure 21-2 Householding Data: XREF Group Mapped to Second Match-Merge Operator

Description of "Figure 21-2 Householding Data: XREF Group Mapped to Second Match-Merge Operator"

Match-Merge Wizard and Editor: Name

Use the Name page to specify a name and optional description for the operator. By default, the wizard names the Match-Merge operator MATCHMERGE.

Match-Merge Wizard and Editor: Groups

Use the Groups page to enter customized names and descriptions of the input and output groups.

Group

Lists the predefined input and output groups. You can rename the groups, but you cannot add or delete groups in the Match-Merge operator.

Direction

Identifies whether the group is for input or output. The Match-Merge operator accepts one SQL input group and generates two PL/SQL output groups. The MERGE group has the merged data. The XREF group is an optional group for documenting the merge process.

Description

Enter an optional description.

Match-Merge Wizard and Editor: Input Connections

Use the Input Connections page to select attributes for the input group (INGRP1).

To complete the Input connections page for an operator:

1. Select complete groups or individual attributes from the left panel.

   To search for a specific attribute or group by name, type the text in Search for and click Go. To find the next match, click Go again.

   Hold the Shift key down to select multiple groups or attributes. If you want to select attributes from different groups, you must first combine the groups with a Joiner or Set operator.

2. Use the left to right arrow button in the middle of the page to move your selections to the right side of the wizard page.

   You can use the right to left arrow to move groups or attributes from the input connections list. Warehouse Builder removes the selection from the input group and removes the mapping line between the source operator and the current operator.

Match-Merge Wizard and Editor: Input Attributes

Use the Input Attributes page to assign input roles to each input attribute.

Attribute

Automatically lists the attributes that you selected on the Input Connections page. Click Add to add a new input attribute.

Input Role

Input roles indicate what kind of information resides in a line of data. For each attribute, select the input role that most closely matches the data contained in the source attribute.

Data Type, Length, Precision, Scale, Seconds Precision

Attributes are set automatically to NUMBER.

Description

Enter an optional description of the input attributes.

Add

Adds a row so that you can define a new attribute.

Delete

Deletes the selected attribute.

Match-Merge Wizard and Editor: Merge Output

Use this page to specify the attributes for the output MERGE group. The MERGE group produces a consolidated record from the attributes you selected.

Source Attributes

Lists all attributes defined for this Match-Merge operator. Use the shuttle buttons to move selected attributes to the Output Attributes field.

Output Attributes

Lists attributes selected for the output MERGE group.

Match-Merge Wizard and Editor: Cross-Reference Output

Use the Cross-Reference Output page to optionally select attributes for the XREF group. Although the Match-Merge operator creates the XREF group by default, you have the option of adding attributes to the group or leaving it empty.

You can use the XREF group to document the merge process. Create a foreign key relationship between the original data set and the new merged data set. Then send the attributes from the XREF group to a table that records the corresponding source row for each merged row.

Alternatively, you can use the XREF group as input to a second Match-Merge operator. By using two operators, you can direct the merged output from a set of attributes to two different target. For an example, refer to "Using Two Match-Merge Operators".

Source Attributes

Lists the input attributes (INGRP1) and an XREF output attribute for each MATCH output attribute. The XREF attributes are distinguished by a prefix, which has a default value of MM_.

Output Attributes

The attributes that you want to cross-reference. To move selected attributes between the Source Attributes and Output Attributes lists, use the shuttle keys.

Merge Prefix

The prefix used to distinguish cross-reference output from data output.

Set Prefix

Changes the prefix on XREF attributes in both lists to the value displayed in the Merge Prefix field.

Match-Merge Wizard and Editor: Match Bins

Use the Match Bins page to limit the number of rows to be compared. When Warehouse Builder matches the rows, it compares each row with the subsequent row for all rows within the same grouping. Limiting the number of rows can greatly enhance performance, because Warehouse Builder searches for matches only within a bin and not throughout the entire data set.

Ideally, keep the number of rows in each grouping under 2000. The number of comparisons Warehouse Builder performs is based on the following formula:

n=(b*(b-1))/2 

where n is the number of comparisons, and b is the number of records in a bin.

For example, matching 5 records requires 10 comparisons, matching 50 records requires 1,225 comparisons, and matching 500 records requires 124,750 comparisons.

While you want to define Match Bins that separate rows into manageable groupings, you also want to avoid separating rows that should be matched. The attributes you select for grouping similar rows depends on your data. For example, if you have a table of customer addresses with a million rows, you may want to group the data by partial street name, city name, and zip code.

Available Attributes

Lists all input attributes, from which you can select the ones to use for binning.

Selected Attributes

One or more attributes that must match for a row to be included in a particular bin. To move attributes between the Source Attributes and Output Attributes lists, select one or more attributes and click the arrow keys located between the two lists. Use the arrow keys at the right to order the attributes from the more general at the top (such as Country) to the more specific at the bottom (such as Street).

Match New Records Only

After the first deployment, you can choose whether to match and merge all records or only new records. You do not want to match and merge the same data twice because of the impact on performance. Instead, you can just match and merge the new, uncleansed data. This option enables you to add uncleansed data into the data warehouse.

New Record Condition

Displays the conditional expression used to identify new records. Click the Ellipsis button to display the Match New Record Condition Editor (also known as the Expression Builder User Interface).

Match-Merge Wizard and Editor: Match Rules

The Match Rules are used to identify duplicate records, even though some fields may have different values. You can define match rules for a single attribute or multiple attributes in the operator. On the Match Rules tab, create match rules at the top of the page. In the lower portion of Match Rules tab, specify the details for each match rule. Table 21-9 describes the match rules.

If you create more than one match rule, Warehouse Builder determines a match when those two rows satisfy any of the match rules. In other words, Warehouse Builder evaluates multiple match rules using OR logic. This is indicated by the OR icon to the left of each additional row. For more information, see "Understanding Matching Concepts".

Name

An arbitrary name for the rule. Warehouse Builder creates a default name such as MA_0 and MA_1 for each match rule. You can replace these names with meaningful ones. Meaningful names are particularly helpful when referencing the rules from a custom PL/SQL program.

Position

The order of execution. You can change the position of a rule by clicking on the row header and dragging the row to its new location. The row headers are the boxes to the left of the Name column.

Rule Type

Assign one of the rule types listed in Table 21-9. When you select a rule type, the lower portion of the Match Rules tab activates and you can enter details for the match rule.

Usage

You can designate a match rule as either active or passive.

• Active: Warehouse Builder executes a match rule if you designate it as active. If more than one match rule is active, each is evaluated in turn until a match is found or until all rules are evaluated. If any rule identifies a match, those records are considered a match.

• Passive: Warehouse Builder does not directly execute passive match rules. It only executes passive rules when they are called through an active custom match rule. All defined match rules appear in a list of available functions in the Custom Match Rule Editor.

Description

Displays an optional description, which you can enter.

Descriptions of Match Rules

Table 21-9 describes the types of match rules.

Conditional Match Rule

Use the Conditional Match Rule to combine multiple attribute comparisons into one composite rule. When you assign multiple attributes for comparison, all the comparisons must be true for the records to be considered a match. Warehouse Builder displays the AND icon in the left-most column of subsequent conditions.

Attribute

Identifies the attribute that will be tested for a particular condition. You can select from any input attribute (INGRP1).

Position

The order of execution. You can change the position of a rule by clicking on the row header and dragging the row to its new location. The row headers are the boxes to the left of the Attribute column.

Algorithm

A list of methods that can be used to determine a match. Table 21-10 describes the algorithms.

Similarity Score

The minimum similarity value required for two strings to match, as calculated by the Edit Distance, Standardized Edit Distance, Jaro-Winkler, or Standardized Jaro-Winkler algorithms. Enter a value between 0 and 100. A value of 100 indicates an exact match, and a value of 0 indicates no similarity.

Blank Matching

Lists options for handling empty strings in a match.

Defining a Conditional Match Rule

To define a Conditional match rule, complete the following steps:

1. On the top portion of the Match Rules tab, select Conditional for the rule type.

   The operator displays a Details section at the bottom of the tab.

2. Click Add to add a new row.

3. Select an attribute.

4. Select an algorithm. Refer to Table 21-10 for descriptions.

5. Specify a similarity score for the Edit Distance, Standardized Edit Distance, Jaro-Winkler, or Standardized Jaro-Winkler algorithms.

6. Select a method for handling blanks.

Algorithms for Conditional Match Rules

Table 21-10 describes the algorithms available for defining conditional match rules.

Weight Match Rule

Use this rule to match rows based on a weight value. A weighted match rule is most useful when comparing a large number of attributes. This rule can prevent a single attribute from invalidating a match, which results from the AND logic of conditional rules.

Warehouse Builder compares each attribute using a similarity algorithm that returns a score between 0 and 100 to represent the similarity between the rows. For two rows to be considered a match, the total counts must be greater than the overall score you designate.

Similarity Algorithm

The method used to determine a match. Choose from these algorithms:

• Edit Distance: Calculates the number of deletions, insertions, or substitutions required to transform one string into another.

• Jaro-Winkler: Uses an improved comparison system over the Edit Distance algorithm. It accounts for the length of the strings and penalizes more for errors at the beginning. It also recognizes common typographical errors.

Attribute

Identifies the attribute that will be tested for a particular condition. You can select from any input attribute (INGRP1).

Maximum Score

The weight value for the attribute. This value should be greater than the value of Required Score to Match.

Score When Blank

The similarity value when one of the records is empty.

Required Score to Match

A value that represents the similarity required for a match. A value of 100 indicates that the two values are identical. A value of zero indicates there is no similarity.

Using the Weight Match Rule

To use the Weight match rule, complete the following steps:

1. On the Match Rules tab, select Weight as the Rule Type.

   The Details tab is displayed at the bottom of the page.

2. Select Add at the bottom of the page to add a new row.

3. For each row, select an attribute to add to the rule.

4. In Maximum Score, assign a weight to each attribute. Warehouse Builder compares each attribute using a similarity algorithm that returns a score between 0 and 100 to represent the similarity between the rows.

5. In Required score to match, assign an overall score for the match.

   For two rows to be considered a match, the total counts must be greater than the Required score.

Person Match Rule

Use the Person match rule to match records based on names. Matching by names is most effective when you first correct the address data using the Name and Address operator before the Match-Merge operator.

When you select the Person match rule, the Match Rules page displays the Person Attributes and Details tabs.

Person Attributes Tab

• Eligible Attributes: Lists all input attributes.

• Attributes: The attributes containing parts of names. Use the shuttle keys to move attributes from Eligible Attributes to the Attributes column of Name Roles.

• Name Roles: Lists the roles for different parts of a name. Select the appropriate role for each attribute. Table 21-12 describes the roles.

Person Details Tab

Use the Details tab to set options for determining a match. Table 21-13 lists the rule options you can select for each component of the name. For a description of the algorithms, refer to Table 21-10.

Defining a Person Match Rule

To define a Person match rule, complete the following steps:

1. On the Match Rules tab, select Person as the Rule Type.

   The Person Attributes tab and Details tab are displayed at the bottom of the page.

2. In the left panel of the Person Attributes tab, select the attributes that describe a full name and use the arrow key to move them to Name Roles Attributes.

3. For each attribute, select the role it plays in a name. You must define either the Last Name or First Name Standardized for the match rule to be effective. See Table 21-12 for the types of roles you can assign.

4. Select the Details tab and select the applicable options as listed in Table 21-13.

Firm Match Rule

Use the Firm match rule to match records by business name. This type of match is most effective when you first correct the address data using the Name and Address operator before the Match-Merge operator.

When you select the Firm match rule, the Match Rules page displays the Firm Attributes and Details tabs.

Firm Attributes tab

Use the Firm Attributes tab to identify attributes that contain business names.

• Eligible Attributes: Lists all input attributes.

• Attributes: The attributes containing the names of businesses. Move one or two attributes containing business names from Eligible Attributes to the Attributes column of Firm Roles.

• Firm Roles: Lists two roles, Firm 1 and Firm 2. If you selected one attribute, then designate Firm 1 as its role. If you selected two attributes, then designate one of them as Firm 1 and the other as Firm 2.

Firm Details Page

Use the Details tab to set options for determining a match. By default, the operator compares the values in Firm 1 for exact matches.

Table 21-14 lists the rule options you can select for each component of the name. For a description of the algorithms, refer to Table 21-10.

Defining a Firm Match Rule

To define a Firm match rule, complete the following steps:

1. On the Match Rules tab, select Firm as the Rule Type.

   The Firm Attributes tab and Details tab are displayed at the bottom of the page.

2. In the left panel of the Firm Attributes tab, select one or two attributes that represent the firm name and click the right shuttle button.

   The attributes move to the Firm Roles box.

3. For each attribute, click Role Required. From the list, select Firm 1 for the first attribute, and Firm 2 for the second attribute, if it exists.

4. On the Details tab, select the applicable options.

Address Match Rule

Use the Address match rule to match records based on postal addresses.

Matching by address is most effective when you first correct the address data using the Name and Address operator before the Match-Merge operator. The Name and Address operator identifies addresses as existing in a postal matching database and designates the records with the Is Found flag. The Match-Merge operator processes addresses with the Is Found role faster because the data is known to be syntactically correct, legal, and existing.

Address Attributes tab

Use the Address Attributes tab to identify attributes that contain addresses.

• Eligible Attributes: Lists all input attributes.

• Attributes: The attributes containing parts of addresses. Use the shuttle keys to move attributes containing address information from Eligible Attributes to the Attributes column of Address Roles.

• Address Roles: Lists the various parts of an address. Select the one that most closely matches each attribute. Table 21-15 describes the address roles.

Address Details Page

Use the Details tab to set options for determining a match. Table 21-16 describes the options you can assign to Address Roles.

Defining an Address Match Rule

To define an Address match rule, complete the following steps:

1. On the Match Rules tab, select Address as the Rule Type.

   The Address Attributes tab and Details tab are displayed at the bottom of the page.

2. In the left panel of the Address Attributes tab, select the attribute that represents the primary address. Use the right shuttle key to move it to the Address Roles Attributes column.

3. Click Role Required and designate that attribute as the Primary Address.

   You must perform this step. If you do not assign the Primary Address role, the match rule is invalid.

4. Add other attributes and designate their roles as necessary. See Table 21-15 for the types of roles you can assign.

5. Select the Details tab and select the applicable options as listed in Table 21-16.

Custom Match Rule

Use the Custom match rule to execute your own PL/SQL program as a comparison algorithm.

Match Rules Detail

Displays the PL/SQL code composing your custom algorithm. You can edit code directly in this field or use the Custom Match Rule Editor.

Edit

Displays the Custom Match Rule Editor.

Custom Match Rule Editor

Use the Custom Match Rule Editor to develop custom PL/SQL programs to use as comparison algorithms.

The editor provides basic program development support. It consists of these components:

• Menu Bar

• Search For Field

• Navigation Tree

• Implementation Field

• Definition Tab

• Messages Tab

Menu Bar

The menu bar contains the following menus:

• Code: Enables you to read from and write to files on your local computer network, save your editing changes, and so forth.

• Edit: Provides basic cut, copy, and paste functionality.

• Search: Provides basic search and replace functionality.

• Test: Checks for syntax errors in the code entered in the Implementation field.

• Help: Displays this topic.

Search For Field

All or part of a function name you wish to find in the navigation tree. Click Go to search for the first instance, click Go again to find the next instance, and so forth.

Navigation Tree

Lists input parameters and transformations that you can include in your program. The navigation tree contains these folders:

• Match Functions: List both active and passive rules that you have defined. You can call these rules the same as any other PL/SQL function.

• Parameters: Lists all input attributes under two subfolders named THIS_ and THAT_. Your program can compare two rows in the same attribute or different ones.

• Transformation Library: Lists all Warehouse Builder transformations.

To insert a function, parameter, or transformation into your code at the cursor, double-click or drag-and-drop it into the Implementation field.

Implementation Field

Displays your program code.

Definition Tab

Displays the signature of the selected function.

Messages Tab

Displays information about the success or failure of validating the code.

Defining a Custom Match Rule

To define a Custom match rule, complete the following steps:

1. On the Match Rules tab, select Custom as the Rule Type.

   A Details field is displayed at the bottom of the page with the skeleton of a PL/SQL program.

2. Click Edit to open the Custom Match Rules Editor.

3. To enter PL/SQL code, do any combination of the following:

   • To read in a file, choose Open File from the Code menu.

   • To enter text, first position the cursor using the mouse or arrow keys, then begin typing. You can also use the commands on the Edit and Search menus.

   • To reference any function, parameter, or transformation in the navigation tree, first position the cursor, then double-click or drag-and-drop the object onto the Implementation field.

4. To validate your code, choose Validate from the Test menu.

   The validation results appear on the Messages tab.

5. To save your code, choose Save from the Code menu.

6. To close the Custom Match Rules Editor, choose Close from the Code menu.

Merge Rules Page

Use the Merge Rules page to select values for the attributes in the merged record.

Name

An arbitrary name for the rule. Warehouse Builder creates a default name such as ME_0 for each merge rule. You can replace these names with meaningful ones. Meaningful names are particularly helpful when calling rules from a PL/SQL custom rule.

Position

The order of execution. You can change the position of a rule by clicking on the row header and dragging the row to its new location. The row headers are the boxes to the left of the Name column.

Rule Type

Assign one of the rule types listed in Table 21-17. When you select a rule type, the lower portion of the Merge Rules tab activates and you can enter details for the merge rule.

Attribute

Identifies the attribute whose values are selected by the merge rule. Provides a list of all merged attributes.

When you select a Record rule, Warehouse Builder disables the list of attributes. You select multiple attributes on the Attributes tab, which appears on the lower portion of the page.

Description

An optional description of the rule, which you can enter.

Descriptions of Merge Rules

When you define a merge rule, you can define one rule for all the attributes in the merged record or define a rule for each attribute. For instance, if the merged record is a customer record, it may have attributes such as ADDRESS1, ADDRESS2, CITY, STATE, and ZIP. You can write five rules that select the value of each attribute from up to five different records, or one Record rule that selects that values of all five attributes from one record. Use record rules when multiple attributes compose a logical unit, such as an address. For example, City, State, and Zip Code might be three different attributes, but the data for these attributes should all come from the same record.

Table 21-17 describes the types of merge rules.

Match ID Merge Rule

Use the Match ID merge rule to merge records that have already been output in the XREF group from another Match-Merge operator. No other operator is valid for this type of input. For more information, refer to "Using Two Match-Merge Operators".

Next Value of the Sequence

Identifies the sequence that will be used by the rule.

sequences list

Lists all sequences defined in the current project.

Select Sequence

Sets the sequence for the rule to the sequence currently selected in the list. Move a sequence from the sequences list to Select Sequence.

Rank and Rank Record Merge Rules

Use the Rank and Rank Record rules when merging data from multiple sources. These rules enable you to identify your preference for certain sources. Your data must have a second input attribute on which the rule is based.

For example, the second attribute might identify the data source, and these data sources are ranked in order of reliability. The most reliable value would be used in the merged record. The merge rule might look like this:

INGRP1.SOURCE = 'Order Entry'

Name

An arbitrary name for the rule. Warehouse Builder creates a default name such as RULE_0 for each rank merge rule. You can replace these names with meaningful ones.

Position

The order of execution. You can change the position of a rule by clicking on the row header and dragging the row to its new location. The row headers are the boxes to the left of the Name column.

Expression Record Selection

The custom SQL expression used in the ranking. Click the Ellipsis button to display the Rank Rule Editor (also called the Expression Builder User Interface). Use this editor to develop the ranking expression.

Sequence Merge Rule

The Sequence rule uses the next value in a sequence.

Next Value of the Sequence

Identifies the sequence that will be used by the rule.

sequences list

Lists all sequences defined in the current project.

Select Sequence

Sets the sequence for the rule to the sequence currently selected in the list.

Min Max and Min Max Record Merge Rules

The Min Max and Min Max Record rules select an attribute value based on the size of another attribute value in the record.

For example, you might select the First Name value from the record in each bin that contains the longest Last Name value.

Selecting Attribute

Lists all input attributes. Select the attribute whose values provide the order.

Attribute Relation

Select the characteristic for choosing a value in the selected attribute.

• Minimum. Selects the smallest numeric value or the oldest date value.

• Maximum. Selects the largest numeric value or the most recent date value.

• Shortest. Selects the shortest character value.

• Longest. Selects the longest character value.

Copy Merge Rule

The Copy rule uses the values from another merged attribute.

Merged Attribute

Lists the other merged attributes, which you selected on the Merge Attributes page.

Custom and Custom Record Merge Rules

The Custom and Custom Record rules use PL/SQL code that you provide to merge the records. The following is an example of a Custom merge rule, which returns the value of the TAXID attribute for record 1.

BEGIN
RETURN M_MATCHES(1)."TAXID";
END;

Following is an example of a Custom Record merge rule, which returns a record for record 1:

BEGIN
RETURN M_MATCHES(1);
END;

Merge Rules Detail

Displays the PL/SQL code composing your custom algorithm. You can edit code directly in this field or use the Custom Merge Rule Editor.

Edit

Displays the Custom Merge Rule Editor.

Custom Merge Rule Editor

Use the Custom Merge Rule Editor to develop custom PL/SQL programs to use as merging algorithms.

The editor provides basic program development support. It consists of these components:

• Menu Bar

• Search For Field

• Navigation Tree

• Implementation Field

• Definition Tab

• Messages Tab

Menu Bar

The menu bar contains the following menus:

• Code: Enables you to read from and write to files on your local computer network, save your editing changes, and so forth.

• Edit: Provides basic cut, copy, and paste functionality.

• Search: Provides basic search and replace functionality.

• Test: Checks for syntax errors in the code entered in the Implementation field.

• Help: Displays this topic.

Search For Field

All or part of a function name you wish to find in the navigation tree. Click Go to search for the first instance, click Go again to find the next instance, and so forth.

Navigation Tree

Lists input parameters and transformations that you can include in your program. The navigation tree contains these folders:

• Parameters: Lists all input attributes under the M_MATCHES folder and the merged attributes under the M_MERGE folder.

• Transformation Library: Lists all Warehouse Builder transformations.

To insert a parameter or a transformation into your code at the cursor, double-click or drag-and-drop it into the Implementation field.

Implementation Field

Displays your program code.

Definition Tab

Displays the signature of the selected function.

Messages Tab

Displays information about the success or failure of validating the code.

Name and Address Wizard and Editor: General

Use the General page to specify a name and optional description for the operator. By default, the wizard names the Name and Address operator NAMEADDR.

Name and Address Wizard and Editor: Definitions

Characterize the nature of your input data by assigning general definitions to this Name and Address operator. In the Definitions page, select a Parsing Type, Primary Country, and Dual Address Assignment.

Parsing Type

Select one of the following parsing types from the drop-down list:

• Name Only: Select this option when the input data contains only name data. Names can include both personal and business names. Selecting this option instead of the more generic Name and Address option may improve performance and accuracy, depending on the adapter.

• Address Only: Select this option when the input data contains only address data and no name data. Selecting this option instead of the more generic Name and Address option may improve performance and accuracy, depending on the adapter.

• Name and Address: Select this option when the input data contains both name and address data.

Primary Country

Select the country that best represents the country distribution of your data. The primary country is used by some providers of name and address cleansing software as a hint for the appropriate parser or parsing rules to use on the initial parse of the record. For other name and address service providers, external configuration of their installation controls this behavior.

Dual Address Assignment

A dual address contains both a Post Office (PO) box and a street address for the same address record. For records that have dual addresses, your selection determines which address becomes the normal address and which address becomes the dual address. A sample dual address is:

PO Box 2589
4439 Mormon Coulee Rd
La Crosse WI 54601-8231

Note that the choice for Dual Address Assignment affects which postal codes are assigned during postal code correction, because the street address and PO box address may correspond to different postal codes.

• Street Assignment: The street address is the normal address and the PO Box address is the dual address. This means that the Address component is assigned the street address. In the preceding example, the Address is 4439 MORMON COULEE RD. This choice corrects the postal code to 54601-8220.

• PO Box Assignment: The PO Box address is the normal address and the street address is the dual address. This means that the Address component is assigned the Post Office (PO) box address. In the preceding example, the Address is PO BOX 2589. This choice corrects the postal code to 54602-2589.

• Closest to Last Line: Whichever address occurs closest to the last line is the normal address; the other is the dual address. This means that the Address component is assigned the address line closest to the last line. In the preceding example, the Address is 4439 MORMON COULEE RD. This choice corrects the postal code to 54601-8220.

This option has no effect for records having a single street or PO box address.

Name and Address Wizard and Editor: Groups

The Groups page lists the input and output groups defined for an operator. By definition, the Name and Address operator has one input group and one output group. You cannot edit, add, or delete groups in the Name and Address operator. The input group is called INGRP1 and the output group is OUTGRP1. You can edit these names. If the input data requires multiple groups, create a separate Name and Address operator for each group.

Name and Address Wizard and Editor: Input Connections

Use the Input Connections page to select attributes from any operator in your mapping that you want to copy and map into the operator. The Available Attributes box lists the available attributes. The Mapped Attributes box lists the attributes that will be processed by the Name and Address operator. You can move an entire group of attributes, or individual attributes from a single group.

If you have not created one or more operators for the source data yet, the Available Attributes column will be empty.

To complete the Input Connections page for an operator:

1. Select complete groups or individual attributes from the Available Attributes panel. The Available Attributes panel enables you to select attributes from any operator in your mapping.

   To search for a specific attribute or group by name, type the text in Search for and click Go. To find the next match, click Go again.

   Hold the Shift key down to select multiple groups or attributes. If you want to select attributes from different groups, you must first combine the groups with a Joiner or Set operator.

2. Use the left to right arrow button between the two panels to move your selections to the Mapped Attributes panel.

Name and Address Wizard and Editor: Input Attributes

Use the Input Attributes page to assign input roles to each input attribute.

Attribute

Automatically lists the attributes that you selected on the Input Connections page. Otherwise, the list may be empty. Click Add to add each input attribute.

Map all attributes through the Name and Address operator, including those that you do not wish to process. Bypassing the Name and Address operator was valid in earlier releases of Warehouse Builder, but may cause problems now. Instead, assign these attributes the Pass Through input role in the Name and Address operator.

Input Role

Input roles indicate what kind of name or address information resides in a line of data. For each attribute, select the input role that most closely matches the data contained in the source attribute. Refer to "Input Role Descriptions" for a complete list of input roles and their descriptions.

Whenever possible, choose discrete roles (such as City, State, and Postal Code) rather than non-discrete, line-oriented roles (such as Last Line). Discrete roles give the Name and Address operator more information about the data content and result in better parsing.

Data Type, Length, Precision, Scale, Seconds Precision

Set the data type and related parameters for attributes given the Pass Through input role. Attributes with other input roles are set automatically to VARCHAR2 and cannot be changed.

Description

Enter an optional description of the input attributes.

Input Role Descriptions

Table 21-18 describes the input roles for the Name and Address Operator.

Name and Address Wizard and Editor: Output Attributes

Use the Output Attributes page to define output attributes that determine how the Name and Address operator handles parsed data. Specifically, the output attribute properties characterize the data extracted from the parser output.

The Output Attributes page is empty initially. You can create and edit attributes.

Add button

To create a new output attribute, click Add.

Attribute column

A new attribute has a default name such as OUTPUT1. This default name changes when you select an output component to a default descriptive name, such as Primary_Address. It does not change if you already replaced the name. Click the name to replace it with a new name.

Output Component column

Select an output component for every output attribute. Click the Ellipsis button to the right of the cell to open the Output Attribute Components dialog. See "Descriptions of Output Components" for a complete list of output components.

Be sure to add error handling flags, such as Is Parsed, Is Good Name, and Is Good Address. These flags can be used with the Splitter operator to separate good records from records with errors, and load them into different targets.

Data Type column

Pass Through output components retain their input data type. All other output component types are VARCHAR2. This column is read-only.

Length column

Adjust the field length to match the length of the target attribute to which you intend to map the output attribute. This practice helps prevent data truncation warnings during code generation, or errors during execution.

Output Attribute Components Dialog Box

Use the Output Attribute Components dialog to select a component for each output attribute. The output components indicate which component an attribute constitutes, such as the first name, last name, street name, city, or state.

Select an Output Component

Select a component from the navigation tree to apply to the output attribute. See "Descriptions of Output Components" for a description of these components.

Output Component

Identifies the component currently selected from the tree. If this field is empty, the current selection is a folder. Expand the folder and select a valid component. Note that some folders are valid components.

Address Type

Available only for dual addresses, and not supported by all name and address cleansing software providers. The Dual Address Assignment option you specified in the Definitions Page page determines whether the street address or the PO box address is used as the dual address. Select either NORMAL or DUAL. For more information on dual addresses, see "Dual Address Assignment".

Instance

Specify which instance of an output component to use when there are multiple occurrences of the same attribute in a single record. The instance control applies to all name components and several address components, such as Miscellaneous Address and Complex. This setting enables you to extract numerous attributes of the same nature. The number of instances allowed for various components depends on the third-party name and address cleansing software.

For example, an input record containing John and Jane Doe has two name occurrences: John Doe and Jane Doe. You can extract John Doe by assigning Instance 1 to the First Name and Last Name components. Similarly, you can extract Jane Doe by assigning Instance 2 to the First Name and Last Name components.

Descriptions of Output Components

Output components are grouped in the following categories:

• Pass Through

• Name

• Address

• Extra Vendor

• Error Status

• Country-Specific

Chobham Rd
Knaphill
Woking GU21 2TZ

USS John F. Kennedy
Shadow Green Apartments
Cedarvale Gardens
Concordia College

Oracle
39/F The Lee Gardens
33 Hysan Ave
Causeway Bay

Oracle
39/F The Lee Gardens
33 Hysan Ave
Causeway Bay

Name and Address Wizard and Editor: Postal Reporting Page

Postal reporting applies only to countries that support address correction and postal matching. Country certification varies with different vendors of name and address cleansing software. The most common country certifications are United States, Canada, and Australia. The process provides mailers a common platform to measure the quality of address-matching software, focusing on the accuracy of postal codes (in the case of the United States, of five-digit ZIP Codes and ZIP+4 Codes), delivery point codes, and carrier route codes applied to all mail. Some vendors of name and address cleansing software may ignore these parameters and require external setup for generating postal reports. For more information, see "About Postal Reporting".

Postal Report

Select Yes for a postal report for the Primary Country you chose in the Definitions Page page. Only one postal report can be active.

Processor Name

The use of this field varies with vendors of name and address cleansing software. Typically, this value appears on the United States Coding Accuracy Support System (CASS) report.

List Name

An optional reference field that appears on the United States and United Kingdom reports under the List Name section, but is not included in other reports. The list name provides a reference for tracking multiple postal reports; for example, 'July 2005 Promotional Campaign'.

Processor Address Lines

These address lines may appear on various postal reports. Various name and address cleansing software vendors use these fields differently. They often contain the full address of your company.

About Postal Reporting

All address lists used to produce mailings for discounted automation postal rates must be matched by postal report-certified software. Certifications depend on the third-party vendors of name and address software and data. The certifications may include the following:

• United States Postal Service: Coding Accuracy Support System (CASS)

• Canada Post: Software Evaluation and Recognition Program (SERP)

• Australia Post: Address Matching Approval System (AMAS)

Managing the Name and Address Server

An external Name and Address server provides an interface between Oracle Database and third-party name and address processing libraries. This section discusses methods of configuring, starting, and stopping the Name and Address Server.

TraceLevel=0
SocketTimeout=180
ClientThreads=4
Port=4040