Data Integration Architecture

This integration between HR and IACUC will be implemented in different phases, each moving one step closer to the aspirational architecture. The phases broadly categorized are

• Straight point-to-point integration with change generation abstracted by a service
• Loosely couple change generation from transformation and delivery by putting the changes on a queue. But it's still point-to-point from queue to the end point.
• Loosely couple transformation and delivery by introducing a queue.
• Generalizing both transformation and delivery

Change Generation:

Change generation will be achieved by running a difference between data set sent during the previous run with the current data set. The data sent in the previous run will be stored in a materialized view. The process will roughly be:.

Database Objects Involved (e.g.)

    Source View:             PER_PEOPLE_V

    Materialized View:     PER_PEOPLE_LAST_MV

    Delta Table:               PER_PEOPLE_DELTA_TBL

• truncate table per_people_delta_tbl
• insert into per_people_delta_tbl  ( select * from per_people_v  minus  select * from per_people_last_mv ) (Assuming no deletion). This is a simplification since we will also somehow note what columns have changed.
• refresh materialized view per_people_last_mv

The change generation data service that will be used by the rest of the solution will be based on per_people_delta_tbl. The service will roughly be as follows

• Web Methods JDBC adapter will call a stored procedure, that will populate the delta table.
• Web Methods JDBC adapter will run a query against the delta table.
• The document list returned will be transformed into a XML document, that will consist of XML fragments corresponding to each changed record. Each fragment will have all the new values and will also mark the changed attributes.

Initial Implementation (point-to-point):

Initial implementation will be straight forward point-to-point implementation. 

• Obtain a composite change document from the appropriate change data service (which is described in previous section)
• Split the composite change document into series of individual change documents.
• For each atomic change document
  • If the changed attribute list matches the IACUC requirements
    • Use Web Methods mapper to map the change XML document into vendor compliant XML document.
    • Push the transformed XML document to the vendor web service.
    • Update the corresponding record's status in the delta table