23 Understanding Data Quality Management

Quality Assessment

In the quality assessment phase, you determine the quality of the source data. The first step in this phase is to import the source data, which could be stored in different sources, into Warehouse Builder. You can import metadata and data from both Oracle and non-Oracle sources.

After you load the source data, you use data profiling to assess its quality. Data profiling is the process of uncovering data anomalies, inconsistencies, and redundancies by analyzing the content, structure, and relationships within the data. The analysis and data discovery techniques form the basis for data monitoring.

Quality Design

The quality design phase consists of designing your quality processes. You can specify the legal data within a data object or legal relationships between data objects using data rules.

You can also correct and augment your data using data quality operators.

As part of the quality design phase, you also design the transformations that ensure data quality. These transformations could be mappings that are generated by Warehouse Builder as a result of data profiling or mappings you create.

Quality Transformation

The quality transformation phase consists of running the correction mappings you designed to correct the source data.

Quality Monitoring

Quality monitoring is the process of examining warehouse data over time and alerting you when the data violates business rules set for the data.

Attribute Analysis

Attribute analysis seeks to discover both general and detailed information about the structure and content of data stored within a given column or attribute. Attribute analysis looks for information about patterns, domains, data types, and unique values.

Attribute analysis consists of pattern analysis, domain analysis, data type analysis, and unique key analysis.

Pattern Analysis 

Pattern analysis attempts to discover patterns and common types of records by analyzing the string of data stored in the attribute. It identifies the percentages of your data that comply with a certain regular expression format pattern found in the attribute. Using these pattern results, you can create data rules and constraints to help clean up current data problems. Some commonly identified patterns include dates, e-mail addresses, phone numbers, and social security numbers.

Table 23-1 shows a sample attribute, Job Code, that could be used for pattern analysis.

Table 23-2 shows the possible results from pattern analysis, where D represents a digit and X represents a character. After looking at the results and knowing that it is company policy for all job codes be in the format of DDD-X-DD, you can derive a data rule that requires all values in this attribute to conform to this pattern.

Domain Analysis 

Domain analysis identifies a domain or set of commonly used values within the attribute by capturing the most frequently occurring values. For example, the Status column in the Customers table is profiled and the results reveal that 90% of the values are among the following: "MARRIED", "SINGLE", "DIVORCED". Further analysis and drilling down into the data reveal that the other 10% contains misspelled versions of these words with few exceptions. Configuration of the profiling determines when something is qualified as a domain, so review the configuration before accepting domain values. You can then let Warehouse Builder derive a rule that requires the data stored in this attribute to be one of the three values that were qualified as a domain.

Datatype Analysis 

Data type analysis enables you to discover information about the data types found in the attribute. This type of analysis reveals metrics such as minimum and maximum character length values as well as scale and precision ranges. In some cases, the database column is of data type VARCHAR2, but the values in this column are all numbers. Then you may want to ensure that you only load numbers. Using data type analysis, you can have Warehouse Builder derive a rule that requires all data stored within an attribute to be of the same data type.

Unique Key Analysis 

Unique key analysis provides information to assist you in determining whether or not an attribute is a unique key. It does this by looking at the percentages of distinct values that occur in the attribute. You might determine that attributes with a minimum of 70% distinct values should be flagged for unique key analysis. For example, using unique key analysis you could discover that 95% of the values in the EMP_ID column are unique. Further analysis of the other 5% reveals that most of these values are either duplicates or nulls. You could then derive a rule that requires that all entries into the EMP_ID column be unique and not null.

Functional Dependency

Functional dependency analysis reveals information about column relationships. This enables you to search for things such as one attribute determining another attribute within an object.

Table 23-3 shows the contents of the Employees table in which the attribute Dept. Location is dependent on the attribute Dept. Number. Note that the attribute Dept. Number is not dependent on the attribute Dept. Location.

Referential Analysis

Referential analysis attempts to detect aspects of your data objects that refer to other objects. The purpose behind this type of analysis is to provide insight into how the object you are profiling is related or connected to other objects. Because you are comparing two objects in this type of analysis, one is often referred to as the parent object and the other as the child object. Some of the common things detected include orphans, childless objects, redundant objects, and joins. Orphans are values that are found in the child object, but not found in the parent object. Childless objects are values that are found in the parent object, but not found in the child object. Redundant attributes are values that exist in both the parent and child objects.

Table 23-4 and Table 23-5 show the contents of two tables that are candidates for referential analysis. Table 23-4 is the child object and Table 23-5 is the parent object.

Referential analysis of these two objects would reveal that Dept. Number 15 from the Employees table is an orphan and Dept. Numbers 18, 20, and 55 from the Department table are childless. It would also reveal a join on the Dept. Number column.

Based on these results, you could derive referential rules that determine the cardinality between the two tables.

Data Rule Profiling

In addition to attribute analysis, functional dependency, and referential analysis, Warehouse Builder offers data rule profiling. Data rule profiling enables you to create rules to search for profile parameters within or between objects.

This is very powerful as it enables you to validate rules that apparently exist and are defined by the business users. By creating a data rule, and then profiling with this rule you can verify if the data actually complies with the rule, and whether or not the rule needs amending or the data needs cleansing.

For example, you could create a rule that Income = Salary + Bonus for the Employee table shown in Table 23-6. You can then catch errors such as the one for employee Alison.

What is Six Sigma?

Six Sigma is a methodology that attempts to standardize the concept of quality in business processes. It achieves this goal by statistically analyzing the performance of business processes. The goal of Six Sigma is to improve the performance of these processes by identifying the defects, understanding them, and eliminating the variables that cause these defects.

Six Sigma metrics give a quantitative number for the number of defects for each 1,000,000 opportunities. The term "opportunities" can be interpreted as the number of records. The perfect score is 6.0. The score of 6.0 is achieved when there are only 3.4 defects for each 1,000,000 opportunities. The score is calculated using the following formula:

• Defects Per Million Opportunities (DPMO) = (Total Defects / Total Opportunities) * 1,000,000

• Defects (%) = (Total Defects / Total Opportunities)* 100%

• Yield (%) = 100 - %Defects

• Process Sigma = NORMSINV(1-((Total Defects) / (Total Opportunities))) + 1.5

  where NORMSINV is the inverse of the standard normal cumulative distribution.

Six Sigma Metrics for Data Profiling

Six Sigma metrics are also provided for data profiling in Warehouse Builder. When you perform data profiling, the number of defects and anomalies discovered are shown as Six Sigma metrics. For example, if data profiling finds that a table has a row relationship with a second table, the number of records in the first table that do not adhere to this row-relationship can be described using the Six Sigma metric.

Six Sigma metrics are calculated for the following measures in the Data Profile Editor:

• Aggregation: For each column, the number of null values (defects) to the total number of rows in the table (opportunities).

• Data Types: For each column, the number of values that do not comply with the documented data type (defects) to the total number of rows in the table (opportunities).

• Data Types: For each column, the number of values that do not comply with the documented length (defects) to the total number of rows in the table (opportunities).

• Data Types: For each column, the number of values that do not comply with the documented scale (defects) to the total number of rows in the table (opportunities).

• Data Types: For each column, the number of values that do not comply with the documented precision (defects) to the total number of rows in the table (opportunities).

• Patterns: For each column, the number of values that do not comply with the common format (defects) to the total number of rows in the table (opportunities).

• Domains: For each column, the number of values that do not comply with the documented domain (defects) to the total number of rows in the table (opportunities).

• Referential: For each relationship, the number of values that do not comply with the documented foreign key (defects) to the total number of rows in the table (opportunities).

• Referential: For each column, the number of values that are redundant (defects) to the total number of rows in the table (opportunities).

• Unique Key: For each unique key, the number of values that do not comply with the documented unique key (defects) to the total number of rows in the table (opportunities).

• Unique Key: For each foreign key, the number of rows that are childless (defects) to the total number of rows in the table (opportunities).

• Data Rule: For each data rule applied to the data profile, the number of rows that fail the data rule to the number of rows in the table.

Types of Corrections for Source Data

You can perform the following types of corrections on source data objects:

• Schema correction

  Schema correction creates scripts that you can use to create a corrected set of source data objects with data rules applied to them. The corrected data objects adhere to the data rules derived from the results of data profiling.

  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.

• Data Correction

  Data correction is the process of creating correction mappings to remove anomalies and inconsistencies in the source data before loading it into the corrected data objects. Correction mappings enforce the data rules defined on the data objects. 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.

About Performing Data Correction

To perform data correction on source data, you must specify the following:

• Data Correction Actions

• Cleansing Strategies for Data Correction

Data Correction Actions

Based on the data profiling results, Warehouse Builder derives a set of data rules that you can use to cleanse the source data. You can automatically generate corrections based on these data rules by performing data correction actions.

For each data rule derived as a result of data profiling, you must choose a correction action that specifies how data values that are not accepted due to data rule enforcement should be handled. The correction actions you can choose are:

• Ignore: The data rule is ignored and, therefore, no values are rejected based on this data rule.

• Report: The data rule is run only after the data has been loaded for reporting purposes. It is similar to the Ignore option, except that a report is created that contains the values that do not adhere to the data rules. This action can be used for some rule types only.

• Cleanse: The values rejected by this data rule are moved to an error table where cleansing strategies are applied. When you select this option, you must specify a cleansing strategy.

  See "Cleansing Strategy" for details about specifying cleansing strategies.

Cleansing Strategies for Data Correction

When you decide to automatically generate corrected objects based on data profiling results, you must specify how inconsistent data from the source should be cleansed before being stored in the corrected object. To do this, you specify a cleansing strategy for each data rule that is applied to the correction object. Error tables are used to store the records that do not conform to the data rule.

The cleansing strategy you use depends on the type of data rule and the rule configuration. Table 23-7 describes the cleansing strategies and lists the types of data rules for which each strategy is applicable.

Using Attribute Sets to Profile a Subset of Columns from a Data Object

You can use an attribute set to restrict a data profiling operation to a subset of columns from a table, view or materialized view. Reasons to use an attribute set include:

• You can decrease profiling time by excluding columns for which you do not need profiling results.

• Data profiling can only profile up to 165 columns from a table, view or materialized view at a time. You can use an attribute set to select a set of 165 or fewer columns to profile from the object.

Data profiling, using attribute sets, consists of the following high-level steps:

1. Defining Attribute Sets

2. Profiling the Columns in an Attribute Set

Defining Attribute Sets Use the following steps to define an attribute set in a table, view, or materialized view.

1. In the Project Explorer, double-click the table, view, or materialized view.

   The Data Object Editor for the selected object is opened.

2. In the Details panel, select the Attribute Sets tab.

3. In the Attribute Sets section, click a blank area in the Name column and enter the name of the attribute set to create.

4. Close the Data Object Editor.

5. Double-click the data object in which you created an attribute set in step 3.

   The Data Object Editor for the selected object is displayed.

6. On the Attributes Sets tab, select the name of the attribute set created in step 3.

   The Attributes of the selected attribute set section displays the attributes in the data object.

7. Select Include for all the attributes that you want included in the attribute set.

8. Save your changes and close the Data Object Editor.

Profiling the Columns in an Attribute Set Use the following steps to profile columns contained in the attribute set.

1. In the Project Explorer, right-click the Data Profiles node and select New.

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

2. On the Welcome page, click Next.

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, select the data object that you want to profile and use the shuttle arrows to move the data profile to the Selected list.

5. When the selected data object contains attribute sets, the Choose Attribute Set dialog box is displayed.

6. Select the attribute set that you want to profile and click OK.

   The Select Objects page is displayed.

7. On the Select Objects page, click Next.

8. On the Summary page, review the options you choose on the previous wizard pages and click Finish.

   The data profile is created and added to the Navigator tree.

Steps to Profile Data

After you have created a data profile, you can open it in the Data Profile Editor to profile the data or review profile results from a previous run. The objects you selected when creating the profile are displayed in the object tree of the Data Profile Editor. You can add objects to the profile by selecting Profile and then Add.

To profile the data:

1. Expand the Data Profiles node in the Project Explorer, right-click a data profile, and select Open Editor.

   The Data Profile Editor opens the selected data profile.

2. To specify the types of analysis to be performed on the data, configure the data profile.

   See "Configuring Data Profiles".

3. From the Profile menu, select Profile.

   • (Optional) If this is the first time you are profiling data, the Data Profile Setup dialog box is displayed. Enter the details of the profiling workspace. For more information about the information to be entered, click Help.

   Warehouse Builder begins preparing metadata for profiling. The progress window containing the name of the object being created to profile the data is displayed. After the metadata preparation is complete, the Profiling Initiated dialog box is displayed informing you that the profiling job has started.

4. On the Profiling Initiated dialog box, click OK.

   Once the profiling job starts, the data profiling is asynchronous and you can continue working or even close the client. Your profiling process will continue to run until it is completed.

5. View the status of the profiling job in the Monitor Panel of the Data Profile Editor.

   You can continue to monitor the progress of your profiling job in the Monitor panel. After the profiling job is complete, the status displays as complete.

6. After the profiling is complete, the Retrieve Profile Results dialog box is displayed and you are prompted to refresh the results. Click OK to retrieve the data profiling results and display them in the Data Profile Editor.

   You can use this option if you have previously profiled data in the same data profile. It allows you to control when the new profiling results become visible in the Data Profile Editor.

Configuring Data Profiles

You can, and should, configure a data profile before running it if there are specific types of analysis you do, or do not, want to run.

You can configure a data profile at one of the following levels:

• The entire profile (all the objects it contains)

• An individual object in the data profile

  For example, the data profile contains three tables. If you want to perform certain specific types of analysis on one table, configure this table only.

• An attribute within an object

  For example, if you know that you only have one problematic column in a table and you already know that most of the records should conform to values within a certain domain, then you can focus your profiling resources on domain discovery and analysis. By narrowing down the type of profiling necessary, you use less resources and obtain the results faster.

Data Profile

The Data Profile tab contains general information about the data profile. Use this tab to store any notes or information about the data profile.

Profile Object

The Profile Object tab contains two sub-tabs: Object Data and Object Notes. The Object Data tab lists the data records in the object you have selected in the Profile Objects tab of the object tree. The number of rows that were used in the sample are listed. You can use the buttons along the top of the tab to execute a query, get more data, or add a WHERE clause.

Aggregation

The Aggregation tab displays all the essential measures for each column, such as minimum and maximum values, number of distinct values, and null values. Some measures are available only for specific data types. These include the average, median, and standard deviation measures. Information can be viewed from either the Tabular or Graphical sub-tabs.

Table 23-10 describes the various measurement results available in the Aggregation tab.

A hyperlinked value in the aggregation results grid indicates that you can click the value to drill down into the data. This allows you to analyze the data in the sample that produced this result.

For example, if you scroll to the SALARY column, shown in Figure 23-4, and click the value in the Maximum cell showing 24000, the Data Drill Panel on the bottom changes to show you all the distinct values in this column with counts on the left. On the right, the Data Drill can zoom into the value you select from the distinct values and display the full record where these values are found.

Figure 23-4 Aggregation Tabular Results

Description of "Figure 23-4 Aggregation Tabular Results"

The graphical analysis displays the results in a graphical format. You can use the graphical toolbar to change the display. You can also use the Column and Property menus to change the displayed data object.

Data Type

The Data Type tab provides profiling results about data types. This includes metrics such as length for character data types and the precision and scale for numeric data types. For each data type that is discovered, the data type is compared to the dominant data type found in the entire attribute and the percentage of rows that comply with the dominant measure is listed.

One example of data type profiling would be finding a column defined as VARCHAR but is actually storing only numeric values. Changing the data type of the column to NUMBER would make storage and processing more efficient.

Table 23-11 describes the various measurement results available in the Data Type tab.

Domain

The Domain tab displays results about the possible set of values that exist in a certain attribute. Information can be viewed from either the Tabular or Graphical subtabs.

Figure 23-5 displays the Domain tab of the Data Profile Editor.

Figure 23-5 Domain Discovery Results

Description of "Figure 23-5 Domain Discovery Results"

The process of discovering a domain on a column involves two phases. First, the distinct values in the column are used to determine whether that column might be defined by a domain. Typically, there are few distinct values in a domain. Then, if a potential domain is identified, the count of distinct values is used to determine whether that distinct value is compliant with the domain. The properties that control the threshold for both phases of domain discovery can be set in the Property Inspector.

If you find a result that you want to know more about, drill down and use the Data Drill panel to view details about the cause of the result.

For example, a domain of four values was found for the column REGION_ID: 3,2,4, and 1. If you want to see which records contributed to this finding, select the REGION_ID row and view the details in the Data Drill panel.

Table 23-12 describes the various measurement results available in the Domain tab.

Pattern

The Pattern tab displays information discovered about patterns within the attribute. Pattern discovery is the profiler's attempt at generating regular expressions for data it discovered for a specific attribute. Note that non-English characters are not supported in the pattern discovery process.

Table 23-13 describes the various measurement results available in the Pattern tab.

Unique Key

The Unique Key tab provides information about the existing unique keys that were documented in the data dictionary, and possible unique keys or key combinations that were detected by the data profiling operation. The uniqueness % is shown for each. The unique keys that have No in the Documented? column are the ones that are discovered by data profiling.

For example, a phone number is unique in 98% of the records. It can be a unique key candidate, and you can then cleanse the noncompliant records. You can also use the drill-down feature to view the cause of the duplicate phone numbers in the Data Drill panel. Table 23-14 describes the various measurement results available in the Unique Key tab.

Functional Dependency

The Functional Dependency tab displays information about the attribute or attributes that seem to depend on or determine other attributes. Information can be viewed from either the Tabular or Graphical sub-tabs. You can use the Show list to change the focus of the report. Note that unique keys defined in the database are not discovered as functional dependencies during data profiling.

Table 23-15 describes the various measurement results available in the Functional Dependency tab.

For example, if you select Only 100% dependencies from the Show list, the information shown is limited to absolute dependencies. If you have an attribute that is always dependent on another attribute, it is recommended that it be a candidate for a reference table. Suggestions are shown in the Type column. Removing the attribute into a separate reference table normalizes the schema.

The Functional Dependency tab also has a Graphical sub-tab so that you can view information graphically. You can select a dependency and property from the lists to view graphical data.

For example, in Figure 23-6, you select the dependency where DEPARTMENT_ID seems to determine COMMISSION_PCT. (DEPARTMENT_ID->COMMISSION_PCT). In a majority of cases, COMMISION_PCT is null. Warehouse Builder therefore determines that most DEPARTMENT_ID values determine COMMISION_PCT to be null. By switching the Property to Non-Compliant, you can view the exceptions to this discovery. Figure 23-6 shows that for the DEPARTMENT_ID values of 80, the COMMISION_PCT values are not null. This makes sense after you discover that the department with DEPARTMENT_ID 80 is Sales department.

Figure 23-6 Graphical Functional Dependency

Description of "Figure 23-6 Graphical Functional Dependency"

Referential

The Referential tab displays information about foreign keys that were documented in the data dictionary, as well as relationships discovered during profiling. For each relationship, you can see the level of compliance. Information can be viewed from both the Tabular and Graphical subtabs. In addition, two other sub-tabs are available only in the Referential tab: Joins and Redundant Columns.

Table 23-16 describes the various measurement results available in the Referential tab.

For example, if you are analyzing two tables for referential relationships: the Employees table and the Departments table. Using the Referential data profiling results shown in Figure 23-7, you discover that the DEPARTMENT_ID column in the Employees table is related to DEPARTMENT_ID column in the Departments table 98% of the time. You can then click the hyperlinked Yes in the Discovered? column to view the rows that did not comply with the discovered foreign key relationship.

Figure 23-7 Referential Results

Description of "Figure 23-7 Referential Results"

You can also select the Graphical subtab to view information graphically. This view is effective for viewing noncompliant records, such as orphans. To use the Graphical sub-tab, make a selection from the Reference and Property lists.

The Joins sub-tab displays a join analysis on the reference selected in the Reference list. The results show the relative size and exact counts of the three possible outcomes for referential relationships: joins, orphans, and childless objects.

For example, both the EMPLOYEES and DEPARTMENTS tables contain a DEPARTMENT_ID column. There is a one-to-many relationship between the DEPARTMENT_ID column in the DEPARTMENTS table and the DEPARTMENT_ID column in the EMPLOYEES table. The Joins represent the values that have values in both tables. Orphans represent values that are only present in the EMPLOYEES table and not the DEPARTMENTS table. And Childless values are present in the DEPARTMENTS table and not the EMPLOYEES table. You can drill into values on the diagram to view more details in the Data Drill panel.

Figure 23-8 displays the Joins subtab of the Referential tab.

Figure 23-8 Join Results

Description of "Figure 23-8 Join Results"

The Redundant Columns sub-tab displays information about columns in the child table that are also contained in the primary table. Redundant column results are only available when perfectly unique columns are found during profiling.

For example, consider two tables EMP and DEPT, shown in Table 23-17 and Table 23-18, having the following foreign key relationship: EMP.DEPTNO (uk) = DEPT.DEPTNO (fk).

In this example, the Location column in the EMP table is a redundant column because you can get the same information from the join.

Data Rule

The Data Rule tab displays the data rules that are defined as a result of data profiling for the table selected in the object tree. The details for each data rule include the following:

• Rule Name: Represents the name of the data rule.

• Rule Type: Provides a brief description about the type of data rule.

• Origin: Represents the origin of the data rule. For example, a value of Derived indicates that the data rule is derived.

• % Compliant: Percent of rows that comply with the data rule.

• # Defects: Number of rows that do not comply with the data rule.

The data rules on this tab reflect the active data rules in the Data Rule panel. You do not directly create data rules on this tab.

Steps to Derive Data Rules

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.

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

6. On the Welcome page, click Next.

7. On the Name and Description page, the Name field displays a default name for the data rule. To specify a new name, select the name, enter the new name, and click Next.

8. On the Define Rule page, provide details about the data rule parameters and click Next.

   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. On the Summary page, review the options you set in the wizard using this page. Click Back if you want to change any of the selected values. Click Finish to create the data rule.

   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.

Steps to Automate Data Correction

1. Generate correction objects based on data profiling results.

   See "Steps to Create Corrections".

   You can view the corrected objects as described in "Viewing the Correction Tables and Mappings".

2. Deploy the generated corrections to create the corrected objects in the target schema.

   See "Steps to Deploy Correction Objects".

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.

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

   • To store the corrections in an existing target module, choose Select an existing module and select the module from the Available list.

   • To store correction in a new target module, select Create a new target module. The Create Module Wizard guides you through the steps of creating a new target 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 and click Next.

   The Filter list enables you to filter the objects that are available for selection. The default selection is All Objects. You can display only particular types of data objects such as tables or views.

5. On the Select Data Rules and Data Types page, perform schema correction by selecting the corrections that you want to implement for each object. Click Next.

   See "Selecting Data Rules and Data Types for Corrected Objects".

6. (Optional) On the Data Rules Validation page, note down the validation errors, if any, and correct them before proceeding.

   If correction objects from a previous data correction action exist for the objects selected for correction, this page displays a message. Click Next to remove previously created correction objects.

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

   Se e "Selecting the Objects to be Corrected".

8. On the Choose Data Correction Actions page, specify the correction actions to be performed for objects and click Next.

   See "Choosing Data Correction and Cleansing Actions".

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

The correction schema is created and added to the Project Explorer. The correction objects and mappings are displayed under the module that you specify as the target module on the Select Target Module page of the Create Correction Wizard. Depending on the data correction actions specified, correction objects may include transformations used to cleanse and correct data.

Selecting Data Rules and Data Types for Corrected Objects

Use this page to select the data rules that should be applied to the selected objects. The objects selected for correction are on the left side of the page and are organized into a tree by modules. The right panel contains two tabs: Data Rules and Data Types.

Data Rules The Data Rules tab displays the available data rules for the object selected in the object tree. Specify the data rules that you want to apply to that corrected object by selecting the check box to the left of the data rule. Warehouse Builder uses these data rules to create constraints on the tables during the schema generation.

The Bindings section contains details about the table column to which the rule is bound. Click a rule name to display the bindings for the rule.

The method used to apply the data rule to the correction table depends on the type of data rule you are implementing.

Data Types The Data Types tab displays the columns that are selected for correction. The change could be a modification of the data type, precision, or from fixed-length to variable-length. The Documented Data Type column displays the existing column definition and the New Data Type column displays the proposed correction to the column definition.

To correct a column definition, select the check box to the left of the column name.

Selecting the Objects to be Corrected

Use the Verify and Accept corrected Tables page to confirm the objects that you want to correct. This page lists the tables that have been modified with the applied rules and the data type changes.

Perform the following actions on this page:

1. To create a data object in the corrected schema, select Create to the left of the object.

   The lower part of the page displays the details of the selected object using tabs. The Columns tab displays the details of columns that will be created in the corrected data object. The Constraints tab displays details of constraints that will be created on the corrected data object. The Data Rules tab displays details of data rules that will be created on the corrected data object.

2. On the Columns tab, perform the following actions:

   • To create a displayed column in the corrected data object, select Create to the left of the column name.

   • To remove a displayed column from the corrected object, deselect Create to the left of the column name.

   • To modify a displayed column in the corrected object, edit the Data Type, Length, Precision, Seconds Precision, and Scale attributes. However, you cannot modify a column name.

3. On the Constraints tab, perform the following actions:

   • To add additional constraints, click Add Constraint.

   • To remove a constraint from the corrected data object, select the constraint and click Delete.

4. On the Data Rules tab, perform the following actions:

   • To apply a data rule, that was derived based on data profiling results, to the corrected object, select the check box to the left of the data rule.

     Ensure that the Bindings column contains the column to which the data rule should be applied.

   • To apply a new data rule to the corrected object, click Apply Rule. The Apply Data Rule wizard guides you through the process of applying a data rule.

Choosing Data Correction and Cleansing Actions

Use the Choose Data Correction Actions page to select the actions to perform to correct the source data. This page contains two sections: Select a Corrected Table and Choose Data Correction Actions. The Select a Corrected Table section lists the objects that you selected for corrections. Select a table in this section to display the affiliated data rules in the Choose Data Correction Actions section.

Choose Data Correction Actions For each data rule, select an action from the menu in the Action column. The settings you select here determine how to handle data values that are not accepted due to data rule enforcement. Select one of the following actions:

• Ignore

• Report

• Cleanse

Cleansing Strategy Use the Cleansing Strategy list to specify a cleansing strategy. This option is enabled only if you select Cleanse in the Action column. The cleansing strategy depends on the type of data rule and the rule configuration. Error tables are used to store the records that do not conform to the data rule.

Steps to Deploy Correction Objects

To deploy the correction mappings created as part of the data correction process:

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

2. To create the correction objects in your target schema, deploy the correction tables created as a result of data profiling.

3. To cleanse data, deploy and execute the correction mappings.

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.

   Figure 23-9 displays a correction map generated by the Create Correction wizard.

   Figure 23-9 Generated Correction Mapping

   
   Description of "Figure 23-9 Generated Correction Mapping"
   
   

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

   Figure 23-10 displays the submapping that is displayed.

   Figure 23-10 Correction Submapping

   
   Description of "Figure 23-10 Correction Submapping"
   
   

   The submapping is the element in the mapping that performs the actual correction cleansing you specified in the Create Correction Wizard. In the middle of this submap is the DOMAINSIMILARITY transformation that was generated as a function by the Create Correction Wizard.

Adding Data Objects to a Data Profile

To add data objects to a data profile, complete the following steps:

1. Right-click the data profile in the Project Explorer and select Open Editor.

   The Data Profile Editor is displayed.

2. From the Profile menu, select Add Objects.

   The Add Profile Tables dialog box is displayed.

3. On the Add Profile Tables dialog box, select the objects that you want to add to the data profile. Select the objects and use the arrows to move them to the Selected section.

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

Load Configuration

This category contains the following parameters:

• Enable Data Type Discovery: Set this parameter to true to enable data type discovery for the selected table.

• Enable Common Format Discovery: Set this parameter to true to enable common format discovery for the selected table.

• Copy Data into Workspace: Set this parameter to true to enable copying of data from the source to the profile workspace.

• Random Sample Rate: This value represents the percent of total rows that will be randomly selected during loading.

• Sample Set Filter: This represents the WHERE clause that will be applied on the source when loading data into the profile workspace. Click the Ellipsis button on this field to display the Expression Builder. Use the Expression Builder to define the WHERE clause.

• Null Value Representation: This value will be considered as null value during profiling. You must enclose the value in single quotation marks. The default value is null, which is considered as a database null.

Aggregation Configuration

This category consists of one parameter called Not Null Recommendation Percentage. If the percentage of null values in a column is less than this threshold percent, then that column will be discovered as a possible Not Null column.

Pattern Discovery Configuration

This category contains the following parameters:

• Enable Pattern Discovery: Set this to true to enable pattern discovery.

• Maximum Number of Patterns: This represents the maximum number of patterns that the profiler will get for the attribute. For example, when you set this parameter to 10, it means that the profiler will get the top 10 patterns for the attribute.

Domain Discovery Configuration

This category contains the following parameters:

• Enable Domain Discovery: Set this to true to enable domain discovery.

• Domain Discovery Max Distinct Values Count: The maximum number of distinct values in a column in order for that column to be discovered as possibly being defined by a domain. Domain discovery of a column occurs if the number of distinct values in that column is at or below the Max Distinct Values Count property, and the number of distinct values as a percentage of total rows is at or below the Max Distinct Values Percent property.

• Domain Discovery Max Distinct Values Percent: The maximum number of distinct values in a column, expressed as a percentage of the total number of rows in the table, in order for that column to be discovered as possibly being defined by a domain. Domain Discovery of a column occurs if the number of distinct values in that column is at or below the Max Distinct Values Count property, and the number of distinct values as a percentage of total rows is at or below the Max Distinct Values Percent property.

• Domain Value Compliance Min Rows Count: The minimum number of rows for the given distinct value in order for that distinct value to be considered as compliant with the domain. Domain Value Compliance for a value occurs if the number of rows with that value is at or above the Min Rows Count property, and the number of rows with that value as a percentage of total rows is at or above the Min Rows Percent property.

• Domain Value Compliance Min Rows Percent: The minimum number of rows, expressed as a percentage of the total number of rows, for the given distinct value in order for that distinct value to be considered as compliant with the domain. Domain Value Compliance for a value occurs if the number of rows with that value is at or above the Min Rows Count property, and the number of rows with that value as a percentage of total rows is at or above the Min Rows Percent property.

Relationship Attribute Count Configuration

This category contains one parameter called Maximum Attribute Count. This is the maximum number of attributes for unique key, foreign key, and functional dependency profiling.

Unique Key Discovery Configuration

This category contains the following parameters:

• Enable Unique Key Discovery: Set this parameter to true to enable unique key discovery.

• Minimum Uniqueness Percentage: This is the minimum percentage of rows that need to satisfy a unique key relationship.

Functional Dependency Discovery Configuration

This category contains the following parameters:

• Enable Functional Dependency Discovery: Set this parameter to true to enable functional dependency discovery.

• Minimum Functional Dependency Percentage: This is the minimum percentage of rows that need to satisfy a functional dependency relationship.

Row Relationship Discovery Configuration

This category contains the following parameters:

• Enable Relationship Discovery: Set this parameter to true to enable foreign key discovery.

• Minimum Relationship Percentage: This is the minimum percentage of rows that need to satisfy a foreign key relationship.

Redundant Column Discovery Configuration

This category contains the following parameters:

• Enable Redundant Columns Discovery: Set this parameter to true to enable redundant column discovery with respect to a foreign key-unique key pair.

• Minimum Redundancy Percentage: This is the minimum percentage of rows that are redundant.

Data Rule Profiling Configuration

This category contains one parameter called Enable Data Rule Profiling for Table. Set this parameter to true to enable data rule profiling for the selected table. This setting is only applicable for a table, and not for an individual attribute.

Multiple Processors

The computer that runs the Oracle Database needs multiple processors. Data profiling has been designed and tuned to take maximum advantage of the parallelism provided by the Oracle Database. While profiling large tables (more than 10 million rows), it is highly recommended to use a multiple processor computer.

Hints are used in queries required to perform data profiling. It picks up the degree of parallelism from the initialization parameter file of the Oracle Database. The default initialization parameter file contains parameters that take advantage of parallelism.

Memory

It is important that you ensure a high memory hit ratio during data profiling. You can ensure this by assigning a larger size of the System Global Area. It is recommended that the size of the System Global Area be at least 500 MB. If possible, configure it to 2 GB or 3 GB.

For advanced database users, it is recommended that you observe the buffer cache hit ratio and library cache hit ratio. Set the buffer cache hit ratio to higher than 95% and the library cache hit ratio to higher than 99%.

I/O System

The capabilities of the I/O system have a direct impact on the data profiling performance. Data profiling processing frequently performs full table scans and massive joins. Since today's CPUs can easily out-drive the I/O system, you must carefully design and configure the I/O subsystem. Keep in mind the following considerations that aid better I/O performance:

• You need a large number of disk spindles to support uninterrupted CPU and I/O cooperation. If you have only a few disks, the I/O system is not geared towards a high degree of parallel processing. It is recommended to have a minimum of two disks for each CPU.

• Configure the disks. It is recommended that you create logical stripe volumes on the existing disks, each striping across all available disks. Use the following formula to calculate the stripe width:

  MAX(1,DB_FILE_MULTIBLOCK_READ_COUNT/number_of_disks) X DB_BLOCK_SIZE

  Here, DB_FILE_MULTIBLOCK_SIZE and DB_BLOCK_SIZE are parameters that you set in your database initialization parameter file. You can also use a stripe width that is a multiple of the value returned by the formula.

  To create and maintain logical volumes, you need a volume management software such as Veritas Volume Manager or Sun Storage Manager. If you are using Oracle Database 10g or a higher version and you do not have any volume management software, you can use the Automatic Storage Management feature of the Oracle Database to spread the workload to disks.

• Create different stripe volumes for different tablespaces. It is possible that some of the tablespaces occupy the same set of disks.

  For data profiling, the USERS and the TEMP tablespaces are normally used at the same time. So you can consider placing these tablespaces on separate disks to reduce interference.

Defining the Data Rule

Use the Define Rule page or the Define Rule tab to provide details about the data rule. The top section of the page displays the Type list that represents the type of data rule. When you are creating a data rule, expand the Type field to view the types of data rules and select the type you want to create. When you edit a data rule, the Type field is disabled as you cannot change the type of data rule once it is created. For more information about types of data rules, see "Types of Data Rules".

The bottom section of this page specifies additional details about the data rule. The number and names of the fields displayed in this section depend on the type of data rule you create.

For example, if you select Custom as the Type, use the Attributes section to define the attributes required for the rule. Use the Ellipsis button on the Expression field to define a custom expression involving the attributes you defined in the Attributes section.

If you select Domain Range as the type of data rule, the bottom section of the page provides fields to specify the data type of the range, the minimum value, and the maximum value. When you are deriving a data rule, some of these fields are populated based on the profiling results from which you are deriving the rule. You can edit these values.

Specifying Data Correction Actions

Use the Choose Actions page of the Create Data Auditor Wizard or the Choose Action tab of the Edit Data Auditor dialog box to specify data correction actions. This 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.

Manually Running Data Auditors

To check if the data in the data object adheres to the data rules defined for the object, you must run the data auditor. You can run data auditors from the Design Center or the Control Center Manager. To run a data auditor from the Design Center, right-click the data auditor and select Start. In the Control Center Manager, select the data auditor, and from the File menu, select Start. The results are displayed in the Job Details window as described in "Data Auditor Execution Results".

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 23-11 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 23-11 Data Auditor Monitor Activity in a Process Flow

Description of "Figure 23-11 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 23-12 displays the Execution Results tab of the Job Details window.

Figure 23-12 Data Auditor Execution Results

Description of "Figure 23-12 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 23-12, 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.

Run Time Parameters

Default Purge Group: This parameter is used when executing the package. Each audit record in the runtime schema is assigned to the purge group specified.

Bulk size: The number of rows to be fetched as a batch while processing cursors.

Analyze table sample percentage: The percentage of rows to be samples when the target tables are analyzed. You analyze target tables to gather statistics that you can use to improve performance while loading data into the target tables.

Commit frequency: The number of rows processed before a commit is issued.

Maximum number of errors: The maximum number of errors allowed before the execution of this step is terminated.

Default Operating Mode: The operating mode used. The options you can select are Row based, Row based (target only), Set based, Set based fail over to row based, Set based fail over to row based (target only).

Default Audit Level: Use this parameter to indicate the audit level used when executing the package. When the package is run, the amount of audit information captured in the runtime schema depends on the value set for this parameter.

The options you can select are as follows:

ERROR DETAILS: At runtime, error information and statistical auditing information is recorded.

COMPLETE: All auditing information is recorded at runtime. This generates a huge amount of diagnostic data which may quickly fill the allocated tablespace.

NONE: No auditing information is recorded at runtime.

STATISTICS: At runtime, statistical auditing information is recorded.

Data Auditor Parameters

This category uses the same name as the data auditor and contains the following generic data auditor configuration parameters:

Generation comments: Specify additional comments for the generated code.

Threshold Mode: Specify the mode that should be used to measure failure thresholds. The options are PERCENTAGE and SIX SIGMA.

Language: The language used to define the generated code. The options are PL/SQL (default) and UNDEFINED. Ensure that PL/SQL (default) is selected.

Deployable: Select this option to indicate that you want to deploy this data auditor. Warehouse Builder generates code only if the data auditor is marked as deployable.

Referred Calendar: Specify the schedule to associate with the data auditor. The schedule defines when the data auditor will run.

Code Generation Options

ANSI SQL syntax: Select this option to use ANSI SQL code in the generated code. If this option is not selected, Oracle SQL syntax is generated.

Commit control: Specifies how commit is performed. The options available for this parameter are: Automatic, Automatic Correlated, and Manual. Ensure that this parameter is set to Automatic.

Enable Parallel DML: Select this option to enable parallel DML at runtime.

Analyze table statistics: Select this option to generate the statement used to collect statistics for the data auditor. If the target table is not in the same schema as the mapping and you wish to analyze the table, then you would need to grant ANALYZE ANY to the schema owning the mapping.

Optimized Code: Select this option to indicate that optimized code should be generated.

Generation Mode: Select the mode in which optimized code should be generated. The option you can select are: All Operating Modes, Row based, Row based (target only), Set based, Set based fail over to row based, and Set based fail over to row based (target only).

Use Target Load Ordering: Select this option to generate code for target load ordering.

Error Trigger: Specify the name of the error trigger procedure.

Bulk Processing code: Select this option to generate bulk processing code.

Granting Privileges on Error Tables

Before you view data stored in error tables using the Repository Browser, you must grant privileges on the error tables to the OWBSYS user. This enables the Repository Browser to access error table data.

To grant privileges on error tables:

1. In SQL*Plus, log in to the schema containing the error tables.

   The error table for an object is stored in the same schema as the object.

2. Run the SQL script OWB_ORACLE_HOME\owb\rtp\sql\grant_error_table_privileges.sql.

3. When prompted, enter the name of the error table for which you want to grant privileges.

   If you did not specify a name for the error table of an object, using the Error Table Name property, Warehouse Builder provides a default name. For objects that use error tables, the default error table name is the object name suffixed by "_ERR".

4. Repeat steps 2 and 3 for each error table to which you want to grant privileges.

Predefined Data Rules for MDM

Warehouse Builder provides a set of data rules that are commonly used in MDM applications. These include the following customer data rules that can be used in both Customer Data Hub (CDH) and Universal Customer Master (UCM) applications:

• Attribute Completeness

• Contact Completeness

• Data Type

• Data Domain

• Restricted Values

• Unique Key Discovery

• Full Name Standardization

• Common Pattern

• Name Capitalization

• Extended Phone Numbers

• International Phone Numbers

• No Access List by Name Only

• No Access List by Name or SSN

• No Email List

For more details about these data rules, refer to the Oracle Watch and Repair for MDM User's Guide.

Importing MDM Data Rules

Data rules required for Customer Data Hub (CDH) and Universal Customer Master (UCM) applications are provided in the OWB_ORACLE_HOME/misc/dwr/customer_data_rules.mdl file. To import these data rules, from the Design menu, select Import, then Warehouse Builder Metadata. In the Metadata Import dialog box, select customer_data_rules.mdl and click OK. For more information on using the Metadata Import dialog, click Help on this page.

The imported data rules are listed in the Global Explorer, under the MDM Customer Data Rules node of the Public Data Rules node.

Writing Corrected Data and Metadata to the MDM Application

The cleansed and corrected data is contained in the correction objects created as a result of data profiling.

To be more efficient, you can write back only those rows that need to be corrected. You can achieve this by modifying the generated correction mapping. Delete the branch that passes through the compliant rows unchanged (this is the branch that contains the minus filter and the minus set operators). Retain only the corrected rows processing branch in the correction mapping.

Use the following steps to write corrected data to the source MDM application:

1. Create a mapping using the Mapping Editor.

2. Drag and drop the corrected table on to the Mapping Editor. This represents the source table.

3. For UCM, drag and drop the interface table that corresponds to the base table with which you are working.

   Use the MDM application tools and documentation to determine the base table for a particular interface table.

4. Map the columns from the corrected table to the interface table.

5. Deploy and execute the mapping to write corrected data to the source MDM application.

6. Update the base table with changes made to the interface table. You can use Siebel Enterprise Integration Manager (EIM). EIM can be run using the command line or from a Graphical User Interface (GUI).

   For more details about using the EIM, refer to Siebel Enterprise Integration Manager Administration Guide.