15 Working with Oracle Data Quality Products

15.2.1 Create a Quality Input File

Oracle Data Quality uses as a source for the Quality project a flat file which contains the data to cleanse. This Quality input file can be created from Data Integrator and loaded from any source datastore using interfaces. This file should be a FILE datastore with the following parameters defined on the Files tab:

For more information on creating a FILE datastore, refer to the Chapter 5, "Creating and Reverse-Engineering a Model". For more information on loading flat files, see "Files" in the Oracle Fusion Middleware Connectivity and Knowledge Modules Guide for Oracle Data Integrator.

15.2.2 Create an Entity

To import a data source into Oracle Data Quality for Data Integrator means to create an entity based on a delimited source file.

15.2.3 Create a Profiling Project

You can now run a Data Profiling Project with Oracle Data Profiling to find quality problems. Profiling discovers and analyzes the quality of your enterprise data. It analyzes data at the most detailed levels to identify data anomalies, broken filters and data rules, misaligned data relationships, and other concerns that allow data to undermine your business objectives.

For more information on Data Profiling see "Working with Oracle Data Profiling" in the Online Help for Oracle Data Profiling and Oracle Data Quality.

15.2.4 Create a Oracle Data Quality Project

You can now create an Oracle Data Quality Project to validate and transform your data, and resolve data issues such as mismatching and redundancy.

Oracle Data Quality for Data Integrator is a powerful tool for repairing and correcting fields, values and records across multiple business contexts and applications, including data with country-specific origins. Oracle Data Quality for Data Integrator enables data processing for standardization, cleansing and enrichment, tuning capabilities for customization, and the ability to view your results in real-time.

A Quality Project cleanses input files and loads cleansed data into output files. At the end of your Oracle Data Quality project this input file may be split into several output files, depending on the data Quality project.

Important Note: A Data Quality project contains many temporary entities, some of them not useful in the integration process. To limit the Entities reversed-engineered for usage by Oracle Integrator, a filter based on entities name can be used. To use this filter efficiently, it is recommended that you rename in your quality project the entities that you want to use in Oracle Data Integrator in a consistent way. For example rename the entities ODI_IN_XXX and the output (and no-match) files ODI_OUT_XXX , where XXX is the name of the entity.

For more information on Data Quality projects see "Working with Oracle Data Quality" in the Online Help for Oracle Data Profiling and Oracle Data Quality.

15.2.5 Export the Data Quality Project

Oracle Data Integrator is able to run projects exported from Oracle Data Quality. Once the Data Quality project is complete, you need to export it for Oracle Data Integrator. The exported project contains the data files, Data Dictionary Language (DDL) files, settings files, output and statistics files, user-defined tables and scripts for each process module you in the project. An exported project can be run on UNIX or Windows platforms without the user interface, and only requires the Oracle Data Quality Server.

To create a batch script:

1. In the Explorer or Project Workflow, right-click the Oracle Data Quality project and select Export... > ODQ Batch Project > No data.

2. In Browse for Folder, select or make a folder where you want the project to be exported.

3. Click OK. A message window appears indicating that the files are being copied. This export process creates a folder named after the metabase (<metabase_name>) at the location that you specified. This folder contains a projectN sub-folder (where N is the project identifier in Oracle Data Quality). This project folder contains the following folders among others:

   • data: This folder is used to contain input and output data as well as temporary data files. These files have a .DAT extension. As you specified No data for the export, this folder is empty.

   • ddl: This folder contains the entities metadata files (.DDX and .XML). These files describe the data files' fields. They are prefixed with eNN_ , where NN is the Entity number. Each entity is described in two metadata files. eNN_<name of the entity>.ddx is the description of the entity with possible duplicated columns (suitable for fixed files). enNN_<name of the entity_csv.ddx is the description of the entity with non-duplicated columns (suitable for fixed and delimited files). It recommended to use these files for the reverse-engineering process.

   • scripts: This folder contains the batch script runprojectN. This script runs the quality process and is the one that will be triggered by Oracle Data Integrator.

   • settings: This folder contains settings files (.ddt, .sto, .stt, .stx) and the configuration file config_batch.tbl.

4. After the message window has disappeared, examine the folder you have specified and check that all folders and files are correctly created.

5. Move the exported project to a folder on the run-time machine. This machine must have the Oracle Data Quality Server installed at it will run the quality project.

6. Open with a text Editor the batch script (runprojectN) and the configuration file (config_batch.tbl) in the /batch/settings sub-folder of your projectN folder.

7. Perform the following changes to configure the run-time directory in the project.

   • In config_batch.tbl, specify the location (absolute path) of the directory containing the projectN folder for the DATABASE parameter.

   • In runprojectN, specify the location (absolute path) of the projectN directory for the TS_PROJECT parameter.

   For example, if you have the config_batch.tbl and runproject2.* files located in C:\oracle\oracledq\metabase_ data\metabase\oracledq\project2\batch\, you should specify

   • in \settings\config_batch.tbl: DATABASE = C:\oracle\oracledq\metabase_ data\metabase\oracledq\project2\batch

   • in \scripts\runprojectN.*: set TS_PROJECT=C:\oracle\oracledq\metabase_ data\metabase\oracledq\project2\batch

8. Save and close the config_batch.tbl file.

9. In runprojectN uncomment the very last line of the file (remove the :: character at the beginning of the last line).

10. Save and close the runprojectN file.

11. Oracle Data Integrator uses CSV formatted files (typically, comma-delimited with one header line) to provide the data quality project with input data, and expects output data to be in the same format.

    In the /settings directory, open with an Editor the settings file corresponding to the first process of your project. This file is typically named eN_transfmr_p1.stx (where N is the internal ID of the entity corresponding to the quality input file) if the first process is a transformer.

12. Change the following input parameters in the settings file:

    • In DATA_FILE_NAME, specify the name and location (absolute path) of your quality input file in run-time.

    • In FILE_DELIMITER, specify the delimiter used in the quality input file.

    • In START_RECORD, specify the line number where data starts. For example, if there is a 1 line header, the value should be 2.

    For example, if you have the customer_master.csv quality input file (comma-separated with one header line) located in C:/oracle/oracledq/metabase_data/metabase/oracledq/Data/, you should edit the following section:

    <CATEGORY><INPUT><PARAMETER><INPUT_SETTINGS>
      <ARGUMENTS>
      <ENTRY>
        <ENTRY_ID>1</ENTRY_ID>
        <FILE_QUALIFIER>Customer_Master(1)</FILE_QUALIFIER>
        <DATA_FILE_NAME>$(INPUT)/e1_customer_master.dat</DATA_FILE_NAME>
        <DDL_FILE_NAME>$(DDL)/e1_customer_master.ddx</DDL_FILE_NAME>
        <FILE_DELIMITER/>
        <USE_QUOTES_AS_QUALIFIER/>
        <START_RECORD/>
    

    as shown below:

    <CATEGORY><INPUT><PARAMETER><INPUT_SETTINGS>
       <ENTRY>
         <ENTRY_ID>1</ENTRY_ID>
         <FILE_QUALIFIER>Customer_Master(1)</FILE_QUALIFIER>
    <DATA_FILE_NAME>C:\oracle\oracledq\metabase_data\metabase\oracledq\Data\customer_master.csv</DATA_FILE_NAME>
    <DDL_FILE_NAME>$(DDL)/e1_customer_master.ddx</DDL_FILE_NAME>
    <FILE_DELIMITER>,</FILE_DELIMITER>
    <USE_QUOTES_AS_QUALIFIER/>
    <START_RECORD>2</START_RECORD>
    

13. Save and close the settings file.

14. Also in the /settings directory, open the file that corresponds to the settings of the process generating the output (cleansed) data. Typically, for a cleansing project which finishes with a Data Reconstructor process, it is named with eNN_datarec_pXX.stx. Change the following value in the settings file to give the full path of the generated output file.

    <CATEGORY><OUTPUT><PARAMETER>
    <OUTPUT_SETTINGS>
    <ARGUMENTS>
    <FILE_QUALIFIER>OUTPUT</FILE_QUALIFIER>
    <DATA_FILE_NAME>C:\oracle\oracledq\metabase_data\metabase\oracledq\Data\customer_master_cleansed.csv</DATA_FILE_NAME>
    <DDL_FILE_NAME>$(DDL)/e36_us_datarec_p11.ddx</DDL_FILE_NAME>
    

15. Save and close the settings file.

16. If you have several data quality processes that generate useful output files (for example, one data reconstructor per country). Repeat the two previous steps for each of these processes.

15.2.6 Reverse-engineer the Entities

In order to provide the Quality process with input data and use its output data in data integrator's integration processes, it is necessary to reverse-engineer these Entities. This operation is performed using a customized reverse-engineering method based on the Oracle Data Quality RKM. The RKM reads metadata from the .ddx files located in the /ddl folder of your data quality project.

To reverse-engineer the Entities of a data Quality project:

1. Import the RKM Oracle Data Quality into your Oracle Data Integrator project.

2. Insert a physical schema for the File technology in Topology Manager. Specifying for both, the Directory (Schema) and the Directory (Work Schema), the absolute path of your data folder. For example C:\oracle\oracledq\metabase_data\metabase\oracledq\projectN\data

   This directory must be accessible to the agent that will be used to run the transformations. Oracle Data Integrator will look in the schema for the source and target data structures for the interfaces. The RKM will access the output data files and reverse-engineer them.

3. Create a File model and reverse the /ddl folder.

   1. In Designer Navigator expand the Models panel.

   2. Right-click then select New Model.

   3. Enter the following fields in the Definition tab:

      Name: Name of the model used in the user interface.

      Technology: File

      Logical Schema: Select the Logical Schema on which your model will be based.

   4. In the Reverse tab and select:

      Parameter	Value/Action
      Reverse:	Customized
      Context:	Reverse-engineering Context
      Type of objects to reverse-engineer:	Table
      KM	Select the RKM Oracle Data Quality

      
      

   5. Set the RKM options as shown in Table 15-1:

   Table 15-1 KM Options for RKM Oracle Data Quality

   Parameter	Default Value	Description
   DDX_FILE_NAME	*.ddx	Mask for DDX Files to process. If you have used a naming convention in the Quality project for the Entities that you want to use, enter a mask that will return only these Entities. For example, specify the ODI*_csv.ddx mask if you have used the ODI_IN_XX and ODI_OUT_XX naming convention for your input and output entities.
   USE_FRIENDLY_NAMES	No	Set this option to Yes if you want the Reverse-Engineering process to generate user-friendly names for datastore columns based on the field name specified in the DDX file.
   USE_LOG	Yes	Set to Yes if you want the reverse-engineering process activity be logged in a log file.
   LOG_FILE_NAME	/temp/reverse.log	Name of the log file.

   
   

4. Click Apply. The model is created, but contains no datastores yet.

5. Click Reverse. Now, the model contains datastores that you can see in the Models view.

15.2.7 Use Oracle Data Quality Input and Output Files in Interfaces

You can now create in Oracle Data Integrator interfaces sourcing or targeting the data Quality input and output files.

For example, you can:

• Create interfaces to load the input file using datastores from various sources.

• Create interfaces to re-integrate the output data back into the sources after cleansing.

15.2.8 Run this Quality Project from Oracle Data Integrator

The OdiDataQuality tool executes the batch file to run the Oracle Data Quality project. This tool takes as a parameter the path to the runprojectN script file. It can run either in synchronous (the tool waits for the quality process to complete) or asynchronous mode.

For more information about the OdiDataQuality tool and its parameters, see Section A.5.3, "OdiDataQuality".

15.2.9 Sequence the Process in a Package

Create a package in Oracle Data Integrator sequencing the following process:

1. One or more Interfaces creating the Quality input file, containing the data to cleanse.

2. OdiDataQuality tool step launching the Oracle Data Quality process.

3. One or more Interfaces loading the data from the Oracle Data Quality output files into the target datastores.