ionode a framework for interoperability shell project - etl activities october 15 th 2003 john...

ioNode

‘A Framework for Interoperability’

Shell Project - ETL Activities

October 15th 2003John BigerstaffWynne Rees

Commercial in Confidence© 2003 ETL Solutions Ltd. All rights reserved.

ETL Solutions Ltd Overview

MBO of Prismtech April 2002 Data Transformation Team and Product

Based in Blaenau Ffestiniog Wales, UK 10 member team (8 technical) Sales and Marketing Office in North East

Standards based solutions POSC, PPDM, W3C standards (XML, XSLT)

Product and services company >$4m investment in development of Extract

Transform Load (ETL) tools Principal market is large enterprises in Finance, Oil

and Gas, Education and Health ...


ETL Solutions Strengths

ETL product, Transformation Manager (TM) System Integrators of Messaging and ETL solutions Highly qualified and experienced 10 member company

with IT and Transformation Skills Requirements Capture and Analysis Data Modelling using EXPRESS, UML, Many years of Java and C++ Programming RDBMS design, development and tuning Extensive knowledge of W3C standards XML, XSD, XSLT Project Management

Transformation Manager ETL Tool Meta Data Code Generator Java components for easy deployment in client architectures


ioNode

A framework for interoperability to meet the interoperability challenges of today

Flexible Hardware and Software configuration Developed in Java to ‘run anywhere’

Software based on Open Source middleware software Low cost transformation component

Standards based SOAP [Simple Object Access Protocol] XML [eXtensible Mark up Language] ebXML [electronic business XML] JMS [Java Messaging Service]


ioNode Software

Operating System - FreeBSD

JAVA Runtime Environment - JDK 1.3+

Webserver / Servlet Container - Apache Jakarta Tomcat

SOAP Handler - AXIS

Message Handler - OpenJMS

Persistent Storage - PostgreSQL

XMLDOM Handler - JDOMParser - XalanValidator - Xerces

Transformation Manager - ETL TM

Webserver - ApacheHTTPD

Administration

Active Scripting -PHP

Storage -PostgreSQL

Native XML Storage - Apache Xindice


ETL Role in ioNode

Responsible for the design and implementation of ioNode software components: ioAgent – messaging component ioHub – message routing ioTransform – data transformation ioDB – XML persistent data store

Utilise Transformation Manager for design of transforms For SHELL project

CSV to IMS-LIP LSC to HESA code lookup and vice versa


Shell Hub and ioAgents

SHELL

HUB

SCR

SRVLE

MIS

PDP

PDP

SR

VLE

PDP

= IO Agent

SHELL

HUB

SCR

SR

MIS

PDP

PDP

SR

VLE

PDP

VLE


Scalability of ioNode and SHELL Architecture

RegionalRecordCentre

RegionalHub

INSTITUTION

VLE

PDP

SR

ioConsort

ioConsor

t

ioConsor

t

WideAreaHub

LearnerRecord

ProgressFile


SHELL Data Flow and Transforms

ioAgent ioHubLSC toIMS-LIP (HESA)

Lookup Lists

LearnerRecord

Database

StudentRecord

Database

SOAP transport over https

Add, Update, Withdraw, QueryStudent records sent as IMS-LIP record


Asynchronous message responsereturns results in IMS-LIP record

AdminDatabase

Outbound Queue

Send ResponseQueue

TransformCSV to IMS-

LIP XML

AdminDatabase

Outbound Queue

Send ResponseQueue

Asynchronous messagesent in response to

LRDB update

IMS-LIP message read,Global Unique ID

generated if new studentrecord and

LRDB updated

Plug-in accesses themessage

IMS-LIP (HESA)to LSC

Lookup Lists

TransformIMS-LIP XML

to CSV

AdminDatabase

Message Rx Queue

Inbound Queue

AdminDatabase

Plug-in createsstudent record as CSV file

entered into DBor HTPPS protocol

or file stored in directory

Message Rx Queue

Inbound Queue


Messaging Requirements

Once and once only message delivery ebXML uniquely identified messages are

acknowledged upon delivery checks for duplication are carried out

Send and re-send ebXML messages not successfully acknowledged

are re-sent a fixed number of times

Persistent storage Provides a record of the message exchanges and

allows messages to be re-sent if required


ioAgent at College

Messages posted using one of the following: Direct to outbound message queue A file drop directory A HTTP Post interface

Send Message Respond to messages posted in outbound message queue (ionodepost). Initiate transforms that have been configured

e.g. CSV to IMS-LIP and LSC to HESA code conversions Retry messages preset number of times if synchronous reply not received

Receive Message Respond to messages in ionodereceive Initiate transforms that have been configured

e.g. IMS-LIP to CSV and HESA to LSC to code conversions Place message in inbound message queue

Persistence provided by PostgreSQL


ioAgent

Messages sent andand synchronous reply

ioAgent

Send Channel

Receive Channel

Plug in

Post Messages

Configure Transforms

Delivery Results

Read Messages

Configure Transforms

Message delivery queue

Out bound message queue

TransformData validation

Data Handling Rules

Messages received andand asynchronous replies

ioHub

In bound message queue

Message receive queue


Data Handling Rules


Synchronous Message Response

ioHubioAgent

AdminDatabase

OutboundQueue

ReliableMessanging

Queue

Send ResponseQueue

Synchronous reply


LSC toIMS-LIP (HESA)

Lookup Lists


LIP XML

AdminDatabase

MessageReceiveQueue

InboundQueue

Attempt retries if not received

Transforms(if required)


ioAgent – ioNodePost

index

Field name type Description

1 index varchar(80) Unique identifier for entry (Primary Key)

2 rowcount bigint, Index of number of rows

3 messageId varchar(80) Unique message ID, set by ioNode, used for message identification

4 timestamp bigint, Timestamp set when table is read by ioNode

5 status_client varchar(20), Flag for success of failure of message use by application

6 status_message varchar(20), Flag for success or failure of message delivery

7 sourcedatatype varchar(20), Type of source data in data field eg. CSV

8 sourcedatatypeversion varchar(20), Version of source data in data field eg. v1

9 targetdatatype varchar(20), Type required of target data eg. LIP

10 targetdatatypeversion varchar(20), Version required of target data eg v1

11 senders_authorisation

varchar(80), Authorization field for sender

12 function_type varchar(80), Action type of post eg. Add

13 target varchar(80), Intended recipient of message eg. UoP.

14 domainref varchar(80), The identity of the sender eg. Cornwall

15 localidentifier varchar(80), Security identity field for the sender

16 altdomainref varchar(80), Alternative identity for the sender

17 altlocalidentifer varchar(80), Alternative security field for the sender

18 data bytea The data to be sent as payload eg. CSV


IoAgent - ioNodeReceive

index Field name type Description

1 rowcount bigint, Index of number of rows

2 messageId varchar(80) Unique message ID, set by ioNode, used for message identification

3 timestamp bigint, Timestamp set when table is read by ioNode

4 message_type varchar(80) Type of data eg. CSV

5 version varchar(20) Version of data eg v1

6 senders_authorisation varchar(80), Authorization field for sender

7 function_type varchar(80), Action type of post eg. Add

8 fromTarget varchar(80), URL of sender of message

9 domainref varchar(80), The identity of the sender eg. Cornwall

10 localidentifier varchar(80), Security identity field for the sender

11 altdomainref varchar(80), Alternative identity for the sender

12 altlocalidentifer varchar(80), Alternative security field for the sender

13 data bytea The data received


ioHub at University

Implements IoAgent send and receive of messages Sends synchronous and asynchronous messages replies May act as message router

Incoming messages received in Message Receive Queue (ionodereceive) Acts as router to other ioAgents if required

In this case makes synchronous reply only when successfully forwarded

May transform incoming messages before placing in inbound queue

Outgoing messages Responds to asynchronous messages placed in outbound message

queue May transform outgoing messages before placing in message delivery

queue Acts as message router for asynchronous replies


Hub as Router

ioHubioAgent LSC toIMS-LIP (HESA)

Lookup Lists

StudentRecord

Database

Add or UpdateStudent record

sent as IMS-LIP record


LIP XML


Lookup Lists


to CSV


Lookup Lists


to CSV

ioAgent

StudentRecord

Database

Application level processesrequests for data and

returns sets of records in XML.

SOAP transport overhttps

AdminDatabase

OutboundQueue

Send ResponseQueue

AdminDatabase

Message Rx Queue

Inbound Queue

AdminDatabase

Message Rx Queue

Inbound Queue

Route toDestination

AdminDatabase

Message Rx Queue

Inbound Queue

AdminDatabase

OutboundQueue

Send ResponseQueue

AdminDatabase

OutboundQueue

Send ResponseQueue

LSC toIMS-LIP (HESA)

Lookup Lists


LIP XML

AdminDatabase

Message Rx Queue

Inbound Queue

AdminDatabase

OutboundQueue

Send ResponseQueue

SOAP transport overhttps

Route toDestination

ioHub acts as a routersending messagesto other institutions

SOAP transportover https

SOAP transportover https


Hub as a Router – Seamless Connection

ioHub

message delivery queue

message receive queue



ioAgent




Data Handling Rules




Data Handling Rules




Data Handling Rules




Data Handling Rules

ioAgent

Send Channel

Receive Channel

ioHub




ioAgent




Data Handling Rules




Data Handling Rules




Data Handling Rules




Data Handling Rules

ioAgent

Send Channel

Receive Channel



JMS Internal Services

ioAgent

Third Party Processor

AXIS Client/ServerImplementation

TOMCAT

Servlet Agent AXIS Handler

ioHub


TOMCAT



ioAgent



TOMCAT


SOAP SOAP



TOMCAT - Provides HTTP services and a Java Servlet container.

AXIS - provides SOAP message handling and link into HTTP server

JMS - Java Messaging Service - Provides a messaging framework on which to buildreliable messaging services

Third Party Processor - Provides rules and validation of messaging.

Messaging Implementation ioAgent to ioAgent


ioTransform

Ability to deploy java transforms as an integral part of messaging

Transforms automatically generated using ETL Integrated Development Environment, Transform Manager

Open Interfaces allow transforms to and from virtually any format


Transformation Manager Rapid Development Process

Transformation Manager supports the whole development process Design Testing Deployment

The heart of the system is Transformation Builder, a state-of-the-art interactive transform designer

Close integration with Transformation Tester to quickly verify transforms

Flexible Deployment via mixed Java and XSLT run time environments

Deploy

Test

Design

Bu

siness

Mo

del

Dep

loy X

ML

Tran

sfom

s

InteractiveTest

MultipleTarget Models

SpecimenTarget Instances

Specimen

Target Instances

MultipleSource Models

SpecimenSource Instances

Specimen

Source Instances

TransformationBuilder

TransformationTest Tool

Client EnvironmentJava or XSLTcomponents

Dep

loy

Java

or X

SLT

Cod

eStatic Java or .Net

RuntimeDeployment

Tool

Deploy Java

Components


Step By Step Approach

Load data models into repository In-built support for RDBMS, XML, and Java Open Interface to access virtually any kind of data

Create Transforms Define transforms by drag and drop Add more complex mapping and complete business logic Viewing instance data to provide assistance

Test transforms Viewing instance data

Rework transforms Deploy

Simple Java deployment Update and maintain

Transforms generate HTML documentation Transforms are easily updated as models change


Transformation Manager Design Environment


Open Interfaces – Extends TM’s Ability to Any Scenario

Access to User Specified Models

Supports Associated Instance Data

Fits Client Architectures Supports Push and Pull

modes Pull Model supports

‘Propagation of Selection’ Example Uses

Express\Epicentre Part21 ASCII LIS, LAS, Binary RP66 Edifact Messages

Design

TransformationBuilder

Deploy

De

plo

y C

om

po

ne

nts

Ja

va

, XS

LT

, XM

L

TransformationDeployment

Options

RDBMS, Java, XMLModels

RDBMS, Java, XMLSpecimen Instances

RDBMS, XML, JavaSpecimen Instances

RDBMS, XML, JavaSpecimen Instances


Truly Open Interfaces

Access to all forms of models and instance data Allows user models to be loaded with full constraints, relationship

cardinality, etc. Easily create user-defined read and write adapters for accessing

instance data Transforms called by client architecture with parameter

passing and callbacks as required Java interface description published for users to implement Models and instance data can be accessed and viewed as

any other data in TM Numerous uses and examples available

Edifact, CSV files, EXPRESS Models and Part 21


ioTransform Interface

ioNode is designed to support a very simple and generic transformation interface

public ArrayList transform(String configFile,ArrayList dataIn)throws Exception;

Carry out a transform on a list of source data, dataIn, and return a list of target data entities.

The configuration of the transform is contained in the configuration file supplied by the caller.

The configuration file is read using methods contained by the transform provider so can be of any schema and format


ioDB Requirements

Storage of student learner records as native XML Generation of a consortium and LRDB unique identifier Update student learner records Roll back log of present number of transactions or ‘messages’ Generation of Asynchronous messages and responses to

business transactions such as new record. The ability to switch and configure the number of transactions

stored per consortium and LRDB unique identifier. The ability to switch the facility for extending and building

individual master records using xpath updates and a unique identifier.

An admin interface for record retrieval and reporting is provided. A standard API for third party interfaces such as the Learner

record portal is to be made available and documented.


Learner Record Database

Xindice XMLDatabase

IoAgent

RulesProcessor

QueryBuilder

XupdateBuilder

ioAgent passes an IMS-LIP record and a process action tothe Rules Processor

The Rules Processor decides between building an Xpathquery to request a result set or to build an Xupdate

command set to perform changes to a record.

The XML database stores records passed to it and returnsa unique identifier for new records. It processes requests

for data and returns sets of records in XML.

ResultsProcessor

Xindice XMLDatabase

IoAgent

XMLRecordsXupdate

confirmation

ioAgent receives back a data set result

The result of the XUpdate command or the XPath queryare sent back through the results processor

The XML database returns confirmation of an XUpdatecommand or a result set generated by an XPath query


ioDB

ioAgent

Messages received andand synchronous reply

Send Channel

Receive Channel

ioDB

Post Messages





Data Handling Rules

Messages sent andand asynchronous replies

ioHub



Data Handling Rules

LearnerRecord

Database

Add, update, delete andquery Student records

Create Asynchronous replycontaining original messageid, status, IMS-LIP content


ioDB Interface

Method Description

setDatabaseConnection Connect to the database that hosts the learner records repository

createCollection Create a new collection in the learner records

setLearnerRecordCollection Set the name of the collection that holds the learner records

setArchiveLearnerRecordCollection Set the name of the collection that holds the archived learner records

getCollectionList Get a list of collection names in the database

removeCollection Remove a collection from the learner records database

addRecord Add a new student to the learner record collection

updateRecord Update an existing student in the learner record collection

getRecord Get a particular learner record in the database

queryCollection Query the learner record database using the supplied XPath

deleteRecord Delete a student in the learner record collection

resetUniqueIDSequence Reset the unique ioDB sequence number

setNumberofArchivedRecords Set maximum number of archived entries for each learner record

rollbackRecord Reinstate archived learner record as the current learner record


ioDB Interface Sequence Diagram

ioDBInterface

ioDB

setDatabaseConnection(uri, driver)

createCollection(collectionName)

setLearnerRecordCollection(collectionName)

setArchiveLearnerRecordCollection (collectionName)

addRecord(urlXML)

updateRecord(urlXML)

InboundMessage

OutboundMessage

Process Incoming Message

Post Asynchronous Reply


withdrawRecord(urlXML)



Current Activities

Example CSV files provided by Phosphorix Create, Update, and Withdraw student records now

implemented

Transform CSV files to IMS-LIP LSC to HESA Lookup

Secure messaging between ioAgents and ioHub Stored in ioDB with unique Global ID Asynchronous replies generated Transform IMS-LIP files to CSV files on ‘round-trip’

completion HESA to LSC Lookup


Next Steps - Some Issues

Web Browser access to all software interfaces Provide easy to use access to data via existing interfaces

Resolve a few incompatibilities between CSV and IMS-LIP formats

Agree format for asynchronous replies Implement Publish / Subscribe messaging Implement rollback an archive logic in ioDB Implement full query logic in ioDB Testing of routing logic

Discuss logic for multi-hop messages Complete full JMS messaging (currently used only as a

persistent messaging mechanism) Obtain full set of messages and transform descriptions

Create and test full set use TM HTML documentation for review Maintain as regression test suite

Determine timescales for rollout


Q&A

?

?

?

?

??

? ?

?

??

???

?

?

?

?

??

?


Further Information

John Bigerstaff

+44 (0) 1740 645868

+44 (0) 773 6404079

[email protected]

or

www.etlsolutions.com

ionode a framework for interoperability shell project - etl activities october 15 th 2003 john...

Documents

etl role

ionode software slide

ioagent slide

transforms slide

solutions posc

message respond

data field

message use