Publish Processor Results into Custom Log

Purpose: Configuring OEDQ to publish results to an external source which will act as a log. This will allow the user to view the results of a process without taking it down. Innately, OEDQ does not create/store any logs that document results.

Components Needed:
- Database table reserved for logging
- Purge mechanism for database table reserved for logging

Steps:
1. Add new data store to database table reserved for logging
2. Add an additional writer processor
3. Configure writer to write to database table reserved for logging

Things to consider:
1. Add an additional Date/Time processor to record a timestamp. This will help you index and catalog the log
2. In the case of transformations or standardizations to data, be sure to add the original values to track the changed made by OEDQ
3. Setup a purge on the database table reserved for logging to prevent this log from growing to large. These custom made logs can grow very quick and take up much more space than traditional logging.

Calling external web services

Purpose: Configuring OEDQ to invoke external web services, send payloads and publish respons from web services.

Components Needed:
- Custom Script Processor
- Web  Service WSDL
- Web Service Endpoint URL

Steps:
1. Add OEDQ Script processor
2. Create script using Goovy to do the following:
     a. Try/Catch statement
     b. Instantiate new XMLHttpReqest
     c. Create xml payload as variable
     d. Send xml payload variable
     e. Extract value of response tag(s)
     f. Publish extracted response tag value(s) as output value

Example Script:

addLibrary("http");

try {
  var url = "http://xxxx";
  var xmlHttp = new XMLHttpRequest();
  var params = "<?xml version='1.0' encoding='UTF-8' ?>" + "<soapenv:Envelope xmlns:v1='http://xxxx'>" + "<soapenv:Body>" + "<RootTag>" + "<Tag1>Iutput1[0]</Tag1>" + "<Tag2>Input1[1]</Tag2>" ...etc + "</RootTag>" + "</soapenv:Body>" + "</soapenv:Envelope>";
  var request = params;
  var result = new String();
  xmlHttp.open("POST", url, false);
  xmlHttp.send(request);
  request = xmlHttp.responseText;
  request = JSON.parse(request);
  result = document.getElementById("ResponseTag1").value
  }

catch (e) {
  result = "Error:";
}
output1 = result;

Extending OEDQ to Oracle DB AQ

OEDQ can be characterized as a re-active application. Subscribing or Publishing systems interact through web services. The problem with web services is that they are synchronous. This means that a request is sent and a response is recieved by the same application. There are scenarios when data quality results need to be pulbished to multiple systems, not just the application calling the web service. The below article describes how to publish the results of a request payload to multiple applications through the use of an Advanced Queue (AQ).


Purpose: Configuring OEDQ to publish results to an Oracle Database Advanced Queue (AQ)

Components Needed:
- Oracle Data Base Advanced Queue
- Custom Script Processor
- JDBC connection string to Oracle DB

Steps:
1. Create PL/SQL script to insert into Oracle DB AQ. Script needs to do the following:
     a. Recieve Input as part of call
     b. Create payload with input
     c. Insert payload to AQ
     d. Commit

2. Add OEDQ Script processor
3. Create script using Goovy to do the following:
     a. Import groovy.sql syntax/library
     b. Define input variable
     c. Create new instance to db using JDBC connection
     d. Call PL/SQL package and/or stored procedure
     e. Pass input variable as part of previous step

Example PL/SQL to AQ Script:

create or replace
PROCEDURE ( p_payload IN  CLOB ) as
 o_payload         XXX;
 enq_msgid          RAW(16);
 eopt               dbms_aq.enqueue_options_t;
 mprop              dbms_aq.message_properties_t;
 err_message        varchar2(2000);
 v_payload         VARCHAR2(5000);
 len               BINARY_INTEGER;
Begin
  o_payload := XXX( p_payload  );
  eopt.sequence_deviation := NULL;
  mprop.priority :=1;
  dbms_aq.enqueue( 'AQ Name', eopt, mprop, o_payload  , enq_msgid);
  Commit;
END;


Example Groovy to AQ Script:

#! language : groovy
import groovy.sql.Sql
import groovy.xml.MarkupBuilder
def inputs =
    '<?xml version="1.0" encoding="UTF-8" ?>' + '<Payload xmlns="http.xxx.xxx.com' + "<Root Node>" + "<Node1>" + input1[0] + "</Node1>" + "<Node2>" + input1[1] + "</Node2>"  + ...etc. +"</RootNode>"
sql = Sql.newInstance("jdbc:oracle:thin:@hostname:port:SID, "Username", "Password", "oracle.jdbc.OracleDriver" )
sql = sql.call("call Procedure to insert into AQ('$inputs')")

Overview of Oracle Enterprise Data Quality (OEDQ)

For those of you who are new to Oracle Enterprise Data Quality (OEDQ). Rittman Mead Consulting has published a great article which provides an overview of the tool and functionality. In addition to my blog I would strongly encourage subscribing to a few other blogs on the web to build a holistic understanding and knowledge base of OEDQ and Data Quality. Provided below are links to the Rittman Mead Consulting article as well as a few of the blogs I subscribe to. Enjoy!

Rittman Mead Consulting OEDQ Overview: http://www.rittmanmead.com/2012/08/introducing-edq/

OEDQ Overview and Tutorial Blog: http://oracleedq.com/

Oracle Master Data Management Official Blog: https://blogs.oracle.com/mdm/

Oracle Data Integration Official Blog: https://blogs.oracle.com/dataintegration/