5 Understanding Data Quality Management

Phases in the Data Quality Lifecycle

Ensuring data quality involves the following phases:

• Quality Assessment

• Quality Design

• Quality Transformation

• Quality Monitoring

Figure 5-1 shows the phases involved in providing high quality information to your business users.

Figure 5-1 Phases Involved in Providing Quality Information

Description of "Figure 5-1 Phases Involved in Providing Quality Information"

Benefits of Data Profiling

Using the data profiling functionality in Warehouse Builder enables you to:

• Profile data from any source or combination of sources that Warehouse Builder can access.

• Explore data profiling results in tabular or graphical format.

• Drill down into the actual data related to any profiling result.

• Derive data rules, either manually or automatically, based on the data profiling results.

• Attach any data rule to a target object and select an action to perform if the rule fails.

• Create a data auditor from a data rule to continue monitoring the quality of data being loaded into an object.

• Derive quality indices such as six-sigma valuations.

• Profile or test any data rules you want to verify before putting in place.

Types of Data Profiling

Following the selection of data objects, determine the aspects of your data that you want to profile and analyze. Data profiling offers three main types of analysis: attribute analysis, functional dependency, and referential analysis. You can also create custom profiling processes using data rules, allowing you to validate custom rules against the actual data and get a score of their accuracy.

Figure 5-2 displays a representation of the types of data profiling and how you can perform each type.

Figure 5-2 Data Profiling Overview

Description of "Figure 5-2 Data Profiling Overview"

About Six Sigma

Warehouse Builder provides Six Sigma results embedded within the other data profiling results to provide a standardized approach to data quality.

About the Match-Merge Operator

Duplicate records can obscure your understanding of who your customers and suppliers really are. Eliminating duplicate records is an important activity in the data correction process. The Match-Merge operator enables you to identify matching records and merge them into a single record. You can define the business rules used by the Match-Merge operator to identify records in a table that refer to the same data. Master data management working on various systems will make use of this operator to ensure that records are created and matched with a master record.

The Match-Merge operator provides the following:

• Enables you to use weights to determine matches between records.

• Uses built-in algorithms, including the Jaro-Winkler and edit distance algorithms, to determine matches.

• Enables cross referencing of data to track and audit matches.

• Enables you to create custom rules combining built-in rules for matching and merging.

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 could refer to the same person. You can then 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 5-7 shows records that refer to the same person prior to using the Match-Merge operator.

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

Restrictions on Using the Match-Merge Operator

• Because the Match-Merge operator only accepts SQL input, you cannot map the output of the Name and Address operator directly to the Match-Merge operator. You must use a staging table.

• Because the Match-Merge generates only PL/SQL, you cannot map the Merge or XREF output groups of the Match-Merge operator to a SQL only operator such as a Sort operator or another Match-Merge operator.

Overview of the Matching and Merging Process

Matching determines which records refer to the same logical data. Warehouse Builder provides a variety of match rules to compare records. Match rules range from an exact match to sophisticated algorithms that can discover and correct common data entry errors.

Merging consolidates matched records into a single record that is free from duplicate records, omissions, misspellings, and unnecessary variations. You can define merge rules to select the preferred data values for use in the consolidated record.

Warehouse Builder uses the following in the matching and merging process.

Match Bins 

Match bins are containers for similar records and are used to identify potential matches. The match bin attributes are used to determine how records are grouped into match bins. While performing matching, only records within the same match bin are compared. Match bins limit the number of potential matches in a data set, thus improving performance of the match algorithm.

Match Bin Attributes 

Before performing matching, Warehouse Builder divides the source records into smaller groups of similar records. Match bin attributes are the source attributes used to determine how records are grouped. Records having the same match bin attributes reside in the same match bin. Match bin attributes also limit match bins to manageable sets.

Select match bin attributes carefully to fulfill the following two conflicting needs:

• Ensure that any records that match reside in the same match bin.

• Keep the size of the match bin as small as possible.

  A small match bin is desirable for efficiency.

Match Record Sets 

A match record set consists of one or more similar records. After matching records, a match record set is created for each match bin. You can define the match rules that determine if two records are similar.

Merged Records 

A merged record contains data that is merged using multiple records in the match record set. Each match record set generates its own merged record.

Matching and Merging Records

You use the Match-Merge operator to match and merge records. This operator accepts records from an input source, determines the records that are logically the same, and constructs a new merged record from the matched records.

Figure 5-3 describes the matching and merging process. The high-level tasks involved in the process are:

• Constructing Match Bins

• Constructing Match Record Sets

• Constructing Merge Records

Figure 5-3 Match-Merge Process

Description of "Figure 5-3 Match-Merge Process"

Constructing Match Bins

The match bin is constructed using the match bin attributes. Records with the same match bin attribute values will reside in the same match bin. A small match bin is desirable for efficiency. For more information about match rules, see "Match Rules".

Constructing Match Record Sets

Match rules are applied to all the records in each match bin to generate one or more match record sets. Match rules determine if two records match. A match rule is an n X n algorithm where all records in the match bin are compared.

One important point of this algorithm is the transitive matching. Consider three records A, B, and C. If record A is equal to record B and record B is equal to record C, this means that record A is equal to record C.

Constructing Merge Records

A single merge record is constructed from each match record set. You can create specific rules to define merge attributes by using merge rules. For more information about merge rules, see "Merge Rules".

Match Rules

Match rules are used to determine if two records are logically similar. Warehouse Builder enables you to use different types of rules to match source records. You can define match rules using the MatchMerge Wizard or the MatchMerge Editor. Use the editor to edit existing match rules or add new rules.

Match rules can be active or passive. Active rules are generated and executed in the order specified. Passive rules are generated but not executed.

Table 5-9 describes the types of match rules.

Conditional Match Rules

Conditional match rules specify the conditions under which records match.

A conditional match rule allows you to combine multiple attribute comparisons into one composite rule. When more than one attribute is involved in a rule, two records are considered to be a match only if all comparisons are true.

You can specify how attributes are compared using comparison algorithms.

Weight Match Rules

A weighted match rule allows you to assign an integer weight to each attribute included in the rule. You must also specify a threshold. For each attribute, the Match-Merge operator multiplies the weight by the similarity score, and sums the scores. If the sum equals or exceeds the threshold, the two records being compared are considered a match.

Weight match rules are most useful when you need to compare a large number of attributes, without having a single attribute that is different causing a non-match, as can happen with conditional rules.

Weight rules implicitly invoke the similarity algorithm to compare two attribute values. This algorithm returns an integer, percentage value in the range 0-100, which represents the degree to which two values are alike. A value of 100 indicates that the two values are identical; a value of zero indicates no similarity whatsoever.

Person Match Rules

Built-in Person rules provide an easy and convenient way for matching names of individuals. Person match rules are most effective when the data has first been corrected using the Name and Address operator.

When you use Person match rules, you must specify which data within the record represents the name of the person. The data can come from multiple columns. Each column must be assigned an input role that specifies what the data represents.

To define a Person match rule, you must define the Person Attributes that are part of the rule. For example, you can create a Person match rule that uses the Person Attributes first name and last name for comparison. For each Person Attribute, you must define the Person Role that the attribute uses. Next you define the rule options used for the comparison. For example, while comparing last names, you can specify that hyphenated last names should be considered a match.

Firm Match Rules

Built-in Firm match rules provide an easy and convenient way for matching business names. Firm match rules are most effective when the data has first been corrected using the Name and Address operator. Similar to the Person rule, this rule requires users to set what data within the record represents the name of the firm. The data can come from multiple columns and each column specified must be assigned an input role that indicates what the data represents.

Note that you need not assign a firm role to every attribute, and not every role needs to be assigned to an attribute. The attributes assigned to firm roles are used in the match rule to compare the records. The attributes are compared based on the role they have been assigned and other comparison options have you set. For a complete list of firm roles and how each role is treated in a firm match rule, see "Firm Roles".

Address Match Rules

Address Match rules provide a method of matching records based on postal addresses. Address match rules are most effective when the data has first been corrected using a Name and Address operator.

Address Rules work differently depending on whether the address being processed has been corrected or not. Generally, corrected addresses have already been identified in a postal matching database, and are therefore not only syntactically correct, but are legal and existing addresses according to the Postal Service of the country containing the address. Corrected addresses can be processed more quickly, since the match rule can make certain assumptions about their format.

Uncorrected addresses may be syntactically correct, but have not been found in a postal matching database. Addresses may have not been found because they are not in the database, or because there is no postal matching database installed for the country containing the address. Address match rules determine whether an address has been corrected based on the Is_found role. If Is_found role is not assigned, then the match rule performs the comparisons for both the corrected and uncorrected addresses.

To create an Address match rule, assign address roles to the various attributes. The attributes assigned to address roles are used in the match rule to compare the records. Attributes are compared depending on which role they have been assigned, and what other comparison options have been set.

Custom Match Rules

Custom match rules enable you to write your own comparison algorithms to match records. You can use any input attributes or match functions within this comparison. You can use an active custom rule to control the execution of passive rules.

Consider the following three passive built-in rules:

• NAME_MATCH: built-in name rule.

• ADDRESS_MATCH: built-in address rule.

• TN_MATCH: built-in conditional rule.

You can create a custom rule to specify that two records can be considered a match if any two of these rules are satisfied. Example 5-1 describes the PL/SQL code used to create the custom match rule that implements this example.

BEGIN
   RETURN(
       (NAME_MATCH(THIS_,THAT_) AND ADDRESS_MATCH(THIS_,THAT_))
     OR
       (NAME_MATCH(THIS_,THAT_) AND TN_MATCH(THIS_,THAT_))
     OR
       (ADDRESS_MATCH(THIS_,THAT_) AND TN_MATCH(THIS_,THAT_))
         );
END;

Merge Rules

Matching produces a set of records that are logically the same. Merging is the process of creating one record from the set of matched records. A Merge rule is applied to attributes in the matched record set to obtain a single value for the attribute in the merged record.

You can define one Merge rule for all the attributes in the Merge record or define a rule for each attribute.

Table 5-17 describes the types of merge rules.

Using a Match-Merge Operator

The Match-Merge operator has one input group and two output groups, Merge and Xref. The source data is mapped to the input group. The Merge group contains records that have been merged after the matching process is complete. The Xref group provides a record of the merge process. Every record in the input group will have a corresponding record in the Xref group. This record may contain the original attribute values and the merged attributes.

The Match-Merge operator uses an ordered record stream as input. From this stream, it constructs the match bins. From each match bin, matched sets are constructed. From each matched set, a merged record is created. The initial query will contain an ORDER BY clause consisting of the match bin attributes.

To match and merge source data using the Match-Merge operator:

1. Drag and drop the operator the operators representing the source data and the operator representing the merged data onto the mapping editor canvas:

   For example, if your source data is stored in a table, and the merged data will be stored in another table, drag and drop two Table operators that are bound to the tables onto the canvas.

2. Drag and drop a Match-Merge operator onto the mapping editor canvas.

   The MatchMerge wizard is displayed.

3. On the Name and Address page, the Name field contains a default name for the operator. You can change this name or accept the default name.

   You can enter an optional description for the operator.

4. On the Groups page, you can rename groups or provide descriptions for them.

   This page contains the following three groups:

   • INGRP1: Contains input attributes.

   • MERGE: Contains the merged records (usually this means fewer records than INGRP1).

   • XREF: Contains the link between the original and merged data sets. This is the tracking mechanism used when a merge is performed.

5. On the Input Connections page, select the attributes that will be used as input to the Match-Merge operator.

   The Available Attributes section of this page displays nodes for each operator on the canvas. Expand a node to display the attributes contained in the operator, select the attributes, and use the shuttle arrows to move selected attributes to the Mapped Attributes section.

   Note: The Match-Merge operator requires an ordered input data set. If you have source data from more than one operators, use a Set Operation operator to combine the data and obtain an ordered data set.

6. On the Input Attributes page, review the attribute data types and lengths.

   In general, if you go through the wizard, you need not change any of these values. Warehouse Builder populates them based on the output attributes.

7. On the Merge Output page, select the attributes to be merged from the input attributes.

   These attributes appear in the Merge output group (the cleansed group). The attributes in this group retain the name and properties of the input attributes.

8. On the Cross Reference Output page, select attributes for the XREF output group.

   The Source Attributes section contains all the input attributes and the Merge attributes you selected on the Merge Output page. The attributes from the Merge group are prefixed with MM. The other attributes define the unmodified input attribute values. Ensure that you select at least one attribute from the Merge group that will provide a link between the input and Merge groups.

9. On the Match Bins page, specify the match bin attributes. These attributes are used to group source data into match bins.

   After the first deployment, you can choose whether to match and merge all records or only new records. To match and merge only the new records, select Match New Records Only.

   You must designate a condition that identifies new records. The match-merge operator treats the new records in the following way:

   • No matching is performed for any records in a match bin unless the match bin contains new record.

   • Old records will not be compared with each other.

   • A matched record set will not be presented to the merge processing unless the matched record set contains a new record.

   • An old record will not be presented to the Xref output unless the record is matched to a new record.

   For more information about match bin attributes and match bins, see "Overview of the Matching and Merging Process".

10. On the Define Match Rules page, define the match rules that will be used to match the source data.

    Match rules can be active or passive. A passive match rule is generated but not automatically invoked. You must define at least one active match rule.

    For more information about the match rules, the types of match rules you can define, and the steps used to define them, see "Match Rules".

11. On the Merge Rules page, define the rules that will be used to merge the sets of matched records created from the source data.

    You can define Merge rules for each attribute in a record or for the entire record. Warehouse Builder provides different types of Merge rules.

    For more information about the type of Merge rules and the steps to create Merge rules, see "Merge Rules".

12. On the Summary page, review your selections. Click Back to modify any selection you made. Click Next to complete creating the Match-Merge operator.

13. Map the Merge group of the Match-Merge operator to the input group of the operator that stores the merged data.

About the Name and Address Operator

After matching and merging records, you can further validate information about your customers and suppliers, and discover additional errors and inconsistencies. Warehouse Builder parses the names and addresses, and uses methods specific to this type of data, such as matching common nicknames and abbreviations. You can compare the input data to the data libraries supplied by third-party name and address cleansing software vendors, which can augment your records with information such as postal routes and geographic coordinates.

Successful delivery and lower postage rates are not the only reasons to cleanse name and address data. You will get better results from data analysis when the results are not skewed by duplicate records and incomplete information.

Warehouse Builder enables you to perform name and address cleansing on data using the Name and Address operator. The Name and Address operator identifies and corrects errors and inconsistencies in name and address source data by comparing input data to the data libraries supplied by third-party name and address cleansing software vendors. You can purchase the data libraries directly from these vendors.

The errors and inconsistencies corrected by the Name and Address operator include variations in address formats, use of abbreviations, misspellings, outdated information, inconsistent data, and transposed names. The operator fixes these errors and inconsistencies by:

• Parsing the name and address input data into individual elements.

• Standardizing name and address data, using standardized versions of nicknames and business names and standard abbreviations of address components, as approved by the postal service of the appropriate country. Standardized versions of names and addresses facilitate matching and householding, and ultimately help you obtain a single view of your customer.

• Correcting address information such as street names and city names. Filtering out incorrect or undeliverable addresses can lead to savings on marketing campaigns.

• Augmenting names and addresses with additional data such as gender, postal code, country code, apartment identification, or business and consumer identification. You can use this and other augmented address information, such as census geocoding, for marketing campaigns that are based on geographical location.

  Augmenting addresses with geographic information facilitates geography-specific marketing initiatives, such as marketing only to customers in large metropolitan areas (for example, within an n-mile radius from large cities); marketing only to customers served by a company's stores (within an x-mile radius from these stores). Oracle Spatial, an option with Oracle Database, and Oracle Locator, packaged with Oracle Database, are two products that you can use with this feature.

The Name and Address operator also enables you to generate postal reports for countries that support address correction and postal matching. Postal reports often qualify you for mailing discounts. For more information, see "About Postal Reporting".

Example: Correcting Address Information

This example follows a record through a mapping using the Name and Address operator. This mapping also uses a Splitter operator to demonstrate a highly recommended data quality error handling technique.

Example Steps

This example uses a mapping with a Name and Address operator to cleanse name and address records, followed by a Splitter operator to load the records into separate targets depending on whether they were successfully parsed. This section explains the general steps required to design such a mapping.

To make the listed changes to the sample record:

1. In the Mapping Editor, begin by adding the following operators to the canvas:

   • A CUSTOMERS table from which you extract the records. This is the data source. It contains the data in Table 5-18.

   • A Name and Address operator. This action starts the Name and Address Wizard. Follow the steps of the wizard.

   • A Splitter operator. For information on using this operator, see "Splitter Operator" in the Warehouse Builder Online Help.

   • Three target operators into which you load the successfully parsed records, the records with parsing errors, and the records whose addresses are parsed but not found in the postal matching software.

2. Map the attributes from the CUSTOMERS table to the Name and Address operator ingroup. Map the attributes from the Name and Address operator outgroup to the Splitter operator ingroup.

   You are not required to use the Splitter operator, but it provides an important function in separating good records from problematic records.

3. Define the split conditions for each of the outgroups in the Splitter operator and map the outgroups to the targets.

Figure 5-4 shows a mapping designed for this example. The data is mapped from the source table to the Name and Address operator, and then to the Splitter operator. The Splitter operator separates the successfully parsed records from those that have errors. The output from OUTGRP1 is mapped to the CUSTOMERS_GOOD target. The split condition for OUTGRP2 is set such that records whose Is Parsed flag is False are loaded to the NOT_PARSED target. That is, the Split Condition for OUTGRP2 is set as INGRP1.ISPARSED='F'. The Records in the REMAINING_RECORDS group are successfully parsed, but their addresses are not found by the postal matching software. These records are loaded to the PARSED_NOT_FOUND target.

Figure 5-4 Name and Address Operator Used with a Splitter Operator in a Mapping

Description of "Figure 5-4 Name and Address Operator Used with a Splitter Operator in a Mapping"

Handling Errors in Name and Address Data

Name and Address parsing, like any other type of parsing, depends on identification of keywords and patterns containing those keywords. Free-form name and address data difficult to parse because the keyword set is large and it is never 100% complete. Keyword sets are built by analyzing millions of records, but each new data set is likely to contain some undefined keywords.

Because most free-form name and address records contain common patterns of numbers, single letters, and alphanumeric strings, parsing can often be performed based on just the alphanumeric patterns. However, alphanumeric patterns may be ambiguous or a particular pattern may not be found. Name and Address parsing errors set parsing status codes that you can use to control data mapping.

Since the criteria for quality vary among applications, numerous flags are available to help you determine the quality of a particular record. For countries with postal matching support, use the Is Good Group flag, because it verifies that an address is a valid entry in a postal database. Also use the Is Good Group flag for U.S. Coding Accuracy Support System (CASS) and Canadian Software Evaluation and Recognition Program (SERP) certified mailings.

Unless you specify postal reporting, an address does not have to be found in a postal database to be acceptable. For example, street intersection addresses or building names may not be in a postal database, but they may still be deliverable. If the Is Good Group flag indicates failure, additional error flags can help determine the parsing status.

The Is Parsed flag indicates success or failure of the parsing process. If Is Parsed indicates parsing success, you may still wish to check the parser warning flags, which indicate unusual data. You may want to check those records manually.

If Is Parsed indicates parsing failure, you must preserve the original data to prevent data loss.

Use the Splitter operator to map successful records to one target and failed records to another target.

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)

About Data Auditors

Data auditors are processes that validate data against a set of data rules to determine which records comply and which do not. Data auditors gather statistical metrics on how well the data in a system complies with a rule by auditing and marking how many errors are occurring against the audited data.

Data auditors have thresholds that allow you to create logic based on the fact that too many non-compliant records can divert the process flow into an error or notification stream. Based on this threshold, the process can choose actions. In addition, the audit results can be captured and stored for analysis purposes.

Data auditors can be deployed and executed ad-hoc, but they are typically run to monitor the quality of the data in an operational environment like a data warehouse or ERP system and, therefore, can be added to a process flow and scheduled.

When executed, the data auditor sets several output values. One of these output values is called the audit result. If the audit result is 0, then there were no errors. If the audit result is 1, at least one error occurred. If the audit result is 2, then at least one data rule failed to meet the specified error threshold. Data auditors also set the actual measured values such as Error Percent and Six Sigma values.

Data auditors are a very important tool in ensuring that data quality levels are up to the standards set by the users of the system. It also helps determine spikes in bad data allowing events to the tied to these spikes.

For information about creating and using data auditors, see "Monitoring Data Quality Using Data Auditors".

Import or Select the Metadata

Data profiling requires the profiled objects to be present in the project in which you are performing data profiling. Ensure that these objects are either imported into this project or created in it. Also ensure that the data is loaded into the objects. Having the data loaded is essential to data profiling.

Also, because data profiling uses mappings to run the profiling, you must ensure that all locations that you are using are registered. Data profiling attempts to register your locations. If, for some reason, data profiling cannot register your locations, you must explicitly register the locations before you begin profiling.

Create a Data Profile

After your system is set up, you can create a data profile using the Design Center. A data profile is a metadata object in the workspace. It includes the set of data objects you want profiled, the settings controlling the profiling operations, the results returned after you profile the data, and correction information (if you decide to use these corrections).

To create a data profile:

1. From the Project Explorer, expand the project node in which you want to create a data profile.

2. Right-click Data Profiles and select New.

   The Welcome page of the Create Data Profile Wizard is displayed.

3. On the Name and Description page, enter a name and an optional description for the data profile. Click Next.

4. On the Select Objects page, specify the objects that you want to include in the data profile and click Next.

   The Available section displays the objects available for profiling. Select the objects to include in the data profile and use the shuttle buttons to move them to the Selected section. To select multiple objects, hold down the Ctrl key while selecting objects. You can include tables, views, materialized views, external tables, dimensions, and cubes in your data profile.

   When you select tables, views or materialized views that contain attribute sets, the Choose Attribute Set dialog box is displayed. The list at the bottom of this dialog box displays the attribute sets defined on the data object. You can select an attribute set to profile only the columns included in that attribute set. To profile all columns in the data object, select <all columns>.

   When you select a dimensional object in the Available section, a warning is displayed informing you that the relational objects bound to these dimensional objects will also be added to the profile. Click Yes to proceed.

5. On the Summary page, review the choices you made on the previous wizard pages. Click Back to change any selected values. Click Finish to create the data profile.

   The data profile is added to the Data Profiles node in the navigation tree.

   If this is the first data profile you have created in the current project, the Connection Information dialog box for the selected control center is displayed. Enter the connection information and click OK. The Data Profile Editor is displayed.

Profile the Data

Data profiling is achieved by performing deep scans of the selected objects. This can be a time-consuming process, depending on the number of objects and type of profiling you are running. However, profiling is run as an asynchronous job, and the client can be closed during this process. You will see the job running in the job monitor and Warehouse Builder prompts you when the job is complete.

View Profile Results

After the profile operation is complete, you can open the data profile in the Data Profile Editor to view and analyze the results. The profiling results contain a variety of analytical and statistical information about the data profiled. You can immediately drill down into anomalies and view the data that caused them. You can then determine what data must be corrected.

To view the profile results:

1. Select the data profile in the navigation tree, right-click, and select Open Editor.

   The Data Profile Editor opens and displays the data profile.

2. If you have previous data profiling results displayed in the Data Profile Editor, refresh the view when prompted so that the latest results are shown.

   The results of the profiling are displayed in the Profile Results Canvas.

3. Minimize the Data Rule and Monitor panels by clicking on the arrow symbol in the upper left corner of the panel.

   This maximizes your screen space.

4. Select objects in the Profile Objects tab of the object tree to focus the results on a specific object.

   The results of the selected object are displayed in the Profile Results Canvas. You can switch between objects. The tab that you had selected for the previous object remains selected.

The Profile Results Canvas contains the following tabs that display the results of data profiling:

• Data Profile

• Profile Object

• Aggregation

• Data Type

• Pattern

• Domain

• Unique Key

• Functional Dependency

• Referential

• Data Rule

For more information about the contents of these tabs, click the arrow on the right of the Profile Results Canvas panel and select Help.

Derive Data Rules

Based on the results of data profiling, you can decide to derive data rules that can be used to clean up your data. A data rule is an expression that determines the set of legal data that can be stored within a data object. Use data rules to ensure that only values compliant with the data rules are allowed within a data object. Data rules will form the basis for correcting or removing data if you decide to cleanse the data. You can also use data rules to report on non-compliant data.

Although you can create data rules and apply them manually to your data profile, derived data rules allow you to move quickly and seamlessly between data profiling and data correction.

For example, if you have a table called Employees with the following columns: Employee_Number, Gender, Employee_Name. The profiling result shows that 90% of the values in the Employee_Number column are unique, making it a prime candidate for the unique key. The results also show that 85% of the values in the Gender column are either 'M' or 'F', making it a good candidate for a domain. You can then derive these rules directly from the Profile Results Canvas.

To derive a data rule:

1. Select a data profile in the navigation tree, right-click, and select Open Editor.

   The Data Profile Editor is displayed with the profiling results.

2. Review the profiling results and determine which findings you want derived into data rules.

   The types of results that warrant data rules vary. Some results commonly derived into data rules include a detected domain, a functional dependency between two attributes, or a unique key.

3. Select the tab that displays the results from which you want to derive a data rule.

   For example, to create a data rule that enforces a unique key rule for the EMPLOYEE_NUMBER column, navigate to the Unique Key tab.

4. Select the cell that contains the results you want derived into a data rule and then from the Profile menu select Derive Data Rule. Or click the Derive Data Rule button.

   For example, to create a Unique Key rule on the EMPLOYEE_NUMBER column, select this column and click Derive Data Rule.

   The Derive Data Rule Wizard opens and displays the Welcome page.

5. Click Next.

   The Name and Description page is displayed.

6. The Name field displays a default name for the data rule. You can either accept the default name or enter a new name.

7. Click Next.

   The Define Rule page is displayed.

8. Provide details about the data rule parameters.

   The Type field that represents the type of data rule is populated based on the tab from which you derived the data rule. You cannot edit the type of data rule.

   Additional fields in the lower portion of this page define the parameters for the data rule. Some of these fields are populated with values based on the result of data profiling. The number and type of fields depends on the type of data rule.

9. Click Next.

   The Summary page is displayed. Review the options you set in the wizard using this page. Click Back if you want to change any of the selected values.

10. Click Finish.

    The data rule is created and it appears in the Data Rule panel of the Data Profile Editor. The derived data rule is also added to the Derived_Data_Rules node under the Data Rules node in the Project Explorer. You can reuse this data rule by attaching it to other data objects.

Generate Corrections

After you have derived data rules from the profiling results, you can automate the process of correcting source data based on profiling results. You can create the schema and mapping corrections.

The schema correction creates scripts that you can use to create a corrected set of source data objects with the derived data rules applied. The mapping correction creates new correction mappings to take your data from the source objects and load them into new objects.

As part of the correction process, the following are created:

• Corrected tables that adhere to the newly derived data rules

  The correction tables have names that are prefixed with TMP__. For example, when you profile the EMPLOYEES table, the correction table will be called TMP__EMPLOYEES.

• Correction mappings that you use to cleanse the data

  The correction mappings enforce the data rules. While moving data from the old "dirty" tables in the profile source tables into the corrected tables, these mappings correct records that do not comply with the data rules. The name of the correction mapping is the object name prefixed with M_. For example, the correction mapping for the EMPLOYEE table is called M_EMPLOYEE.

Steps to Create Corrections

Use the Data Profile Editor to create corrections based on the profiling results.

To create corrections:

1. If the Data Profile is not already open, open it by right-clicking the data profile in the Project Explorer and selecting Open Editor.

2. From the Profile menu, select Create Correction.

   The Create Correction Wizard is displayed. Click Help on any wizard page for more information about the page.

3. On the Select Target Module page, specify the target module that will contain the corrections. You can create a new module or select and existing module.

   If you choose to create a new target module, the Create Module Wizard is displayed that enables you to create a new module.

   You can remove correction objects created as a result of previous corrections by selecting Remove previous correction objects.

4. On the Select Objects page, select the objects that you want to correct by moving them to the Selected list.

5. On the Select Data Rules and Data Types page, specify the corrections that you want to implement for each object. The navigation tree on the left displays the objects. Select each object in the navigation tree and specify corrections for that object on the Data Rules and Data Types tabs.

   The Data Rules tab displays the data rules that will be applied to the corrected object. Select the rules to be applied. For each rule, in the Bindings section at the bottom of the page, select the column to which the rule must be bound.

   The Data Types tab displays the new data type and the documented data type for each column. To use the new data type determined as a result of the data correction actions, select the column by clicking the box to the right of the column. Columns which are not selected will retain their existing data types.

6. On the Verify and Accept Corrected Tables page, select the objects that you want to correct.

   On the top of this page, the objects selected for corrections are listed. Select Create to the right of the table name to generate corrected objects for the object.

   The bottom part of this page contains the columns, Constraints, and Data Rules tabs. These tabs contain the definitions used for the corrected objects. You can make modifications to these tabs, if required.

7. On the Choose Data Correction Actions page, specify the correction actions to be taken for objects.

   Select an object by clicking the Correct to the left of the object and use the Choose Data Correction Actions section to specify the correction action and cleansing strategy. For more information about correction actions, click Help on this page.

8. On the Summary page, click Finish to create the correction objects.

At this stage, the corrections objects are only defined and their metadata is stored in the workspace. To implement the correction objects in your target schema, you must deploy the correction tables and correction mappings.

Before you deploy a correction mapping, ensure that you do the following:

• Deploy the correction tables created as a result of data profiling.

• Grant the SELECT privilege on the source tables to PUBLIC.

  For example, your correction mapping contains the table EMPLOYEES from the HR schema. You can successfully deploy this correction mapping only if the SELECT privilege is granted to PUBLIC on the HR.EMPLOYEES table.

Viewing the Correction Tables and Mappings

You can review the correction tables in the Data Object Editor to see the data rules and constraints created as part of the design of your table.

To view the correction mappings:

1. Double-click the table or mapping to open the object in their respective editors.

2. After the mapping is open, select View and then Auto Layout to view the entire mapping.

3. Select the submapping ATTR_VALUE_1 and click the Visit Child Graph icon from the toolbar to view the submapping.

   The submapping is displayed. The submapping is the element in the mapping that performs the actual correction cleansing you specified in the Create Correction Wizard.

Define and Edit Data Rules Manually

Data rules can be derived or manually created. Before and after you have created the corrections, you can define additional data rules manually.

For more information about defining and editing data rules manually, see "Creating Data Rules".

Generate, Deploy, and Execute

Finally, you can generate, deploy, and execute the correction mappings and data rules. After you run the correction mappings with the data rules, your data is corrected. The derived data rules remain attached to the objects in the corrected schema for optional use in data monitors.

Tuning the Data Profile for Better Data Profiling Performance

You can configure a data profile to optimize data profiling results. Use the configuration parameters to configure a data profile. For more information about configuration parameters, see "Configuration Parameters for Data Profiles" in the Warehouse Builder Online Help.

Use the following guidelines to make your data profiling process faster:

• Perform only the types of analysis that you require

  If you know that certain types of analysis are not required for the objects that you are profiling, use the configuration parameters to turn off these types of data profiling.

• Analyze lesser amount of data

  Use the WHERE clause and Sample Rate configuration parameters.

If the source data for profiling is stored in an Oracle Database, it is recommended that the source schema be located on the same database instance as the profile workspace. You can do this by installing the workspace into the same Oracle instance as the source schema location. This avoids using a database link to move data from source to profiling workspace.

Tuning the Oracle Database for Better Data Profiling Performance

To ensure good data profiling performance, the computer that runs the Oracle Database must have certain hardware capabilities. In addition to this, you must optimize the Oracle Database instance on which you are performing data profiling.

For efficient data profiling, the following considerations are applicable:

• Multiple Processors

• Memory

• I/O System

Creating Data Rules

The Data Rules folder in the Project Explorer contains the data rules. Every data rule must belong to a data rule folder. The subfolder DERIVED_DATA_RULES contains the data rules derived as a result of data profiling. You can create additional data rule folders to contain any data rules that you create.

To create a data rule:

1. Right-click the Data Rule folder in which the data rule should be created and select New.

   The Create Data Rule Wizard is displayed.

2. On the Name and Description page, enter a name and an optional description for the data rule. Click Next.

3. On the Define Rule page, specify the type of data rule to create. Also specify any additional information required to create the data rule. Click Next.

   For example, when you create a Domain Range rule, you must specify the values that represent the valid domain values.

   For more information about the types of rules, see "Types of Data Rules" in the Warehouse Builder Online Help.

4. On the Summary page, review the selections you made in the wizard. Click Back to modify any selected values. Click Finish to create the data rule.

   The data rule is added to the data rule folder under which you created the data rule.

Applying Data Rules to Objects

Applying a data rule to an object binds the definition of the data rule to the object. For example, binding a rule to the table Dept ensures that the rule is implemented for the specified attribute in the table. You apply a data rule using the Data Object Editor. You can also apply a derived data rule from the Data Rule panel of the Data Profile Editor.

The Apply Data Rule Wizard enables you to apply a data rule to a data object. You can apply precreated data rules or any data rule you created to data objects. The types of data objects to which you can apply data rules are tables, views, materialized views, and external tables.

To apply a data rule to a data object:

1. In the Project Explorer, right-click the object to which the data rule must be applied and select Open Editor.

   The Data Object Editor for the data object is displayed.

2. Navigate to the Data Rules tab.

   If any data rules are bound to the data object, these are displayed on this tab.

3. Click Apply Rule.

   The Apply Data Rule wizard is displayed.

4. On the Select Rule page, select the data rule that you want to apply to the data object. Click Next.

5. On the Name and Description page, enter a name and an optional description for the applied data rule. Click Next.

6. On the Bind Rule Parameters page, bind the data rule to the column in the data object to which the data rule must be applied. Click Next.

7. On the Summary page, review the sections you made on the previous wizard pages. Click Back to modify selected values. Click Finish to apply the data rule.

   The data rule is bound to the data object and is listed on the Data Rules folder.

Creating Data Auditors

Use the Create Data Auditor Wizard to create data auditors. Data auditors are part of an Oracle module in a project.

To create a data auditor:

1. Expand the Oracle module in which you want to create the data auditor.

2. Right-click Data Auditors and select New.

   The Create Data Auditor Wizard is displayed.

3. On the Name and Description page, enter a name and an optional description for the data auditor. Click Next.

4. On the Select Objects page, select the data objects that you want to audit. Use the shuttle buttons to move objects to the Selected section and click Next.

   You can select multiple objects by holding down the Ctrl key while selecting objects.

5. On the Choose Actions page, specify the action to be taken for records that do not comply with data rules bound to the selected objects. Click Next.

   The Choose Actions page contains two sections, Error threshold mode and Data Rules.

   Error threshold mode

   Error threshold mode is used to determine the compliance of data to data rules in the objects. Select one of the following options:

   • Percent: The data auditor will set the audit result based on the percentage of records that do not comply with the data rule. This percentage is specified in the rule's Defect Threshold value.

   • Six Sigma: The data auditor will set the audit result based on the Six Sigma values for the data rules. If the calculated Six Sigma value for any rule is less than the specified Sigma Threshold value, then the data auditor will set the AUDIT RESULT to 2.

   Data Rules

   The Data Rules section lists the data rules applied to the objects selected on the Select Object page. For each rule, specify the following:

   • Action: The action to be performed if data in the source object does not comply with the data rule. Select Report to ensure that the data rule is audited. Select Ignore if you want the data rule to be ignored.

   • Defect Threshold: The percent of records that should comply with the data rules to ensure successful auditing. Specify a value between 1 and 100. This value is ignored if you select Six Sigma in the Error threshold mode section.

   • Sigma Threshold: The required success rate. Specify a number between 0 and 7. If you set the value to 7, no failures are allowed. This value is ignored if you select Percent in the Error threshold mode section.

6. On the Summary page, review the selections you made. Click Back to modify any selected values. Click Finish to create the data auditor.

   The created data auditor is added to the Data Auditors node. At this stage, only the metadata for the data auditor is stored in your workspace. To use this data auditor to monitor the quality of data in your data objects, you must run the data auditor.

Auditing Data Objects Using Data Auditors

After you create a data auditor, you can use it to monitor the data in your data objects. This ensures that the data rule violations for the objects are detected. When you run a data auditor, any records that violate the data rules defined on the data objects are written to the error tables.

There are two ways of using data auditors:

• Manually Running Data Auditors

• Automatically Running Data Auditors

Automatically Running Data Auditors

You can automate the process of running a data auditor using the following steps:

1. Create a process flow that contains a Data Auditor Monitor activity.

2. Schedule this process flow to run at a predefined time.

   For more information about scheduling objects, see "Process for Defining and Using Schedules".

Figure 5-5 displays a process flow that contains a Data Auditor Monitor activity. In this process flow, LOAD_EMP_MAP is a mapping that loads data into the EMP table. If the data load is successful, the data auditor EMP_DATA_AUDIT is run. The data auditor monitors the data in the EMP table based on the data rules defined for the table.

Figure 5-5 Data Auditor Monitor Activity in a Process Flow

Description of "Figure 5-5 Data Auditor Monitor Activity in a Process Flow"

Data Auditor Execution Results

After you run a data auditor, the Job Details window displays the details of the execution. The Job Details window contains two tabs: Input Parameters and Execution Results. Note that the Job Details window is displayed only when you set the deployment preference Show Monitor to true. For more information about deployment preferences, see "Deployment Preferences".

Figure 5-6 displays the Execution Results tab of the Job Details window.

Figure 5-6 Data Auditor Execution Results

Description of "Figure 5-6 Data Auditor Execution Results"

The Input Parameters tab contains the values of input parameters used to run the data auditor. The Execution Results tab displays the results of running the data auditor. This tab contains two sections: Row Activity and Output Parameters.

The Row Activity section contains details about the inserts into the error table for each step. Note that when more than one data rule is specified, multi-table insert may be used in the data auditor. In this case, the count of the number of rows will not be accurate.

In Figure 5-6, the data rule called E_NOT_NULL inserted one record into the error table.

The Output Parameters section contains the following three parameters:

• AUDIT_RESULT: Indicates the result of running the data auditor. The possible values for this parameter are as follows:

  0: No data rule violations occurred.

  1: At least one data rule violation occurred, but no data rule failed to meet the minimum quality threshold as defined in the data auditor.

  2: At least one data rule failed to meet the minimum quality threshold.

  For more information about setting the threshold, see the step on choosing actions in "Creating Data Auditors".

• EO_<data_rule_name>: Represents the calculated error quality for the specified data rule. 0 indicates all errors and 100 indicates no errors.

• SO_<data_rule_name>: Represents the Six Sigma quality calculated for the specified data rule.