Data Integration Architecture and Design Patterns

Here’s a snapshot of Adeptia’s ETL architecture that includes all the features needed to automate data flows. This architecture supports SOA framework. It provides the ability for developers to create a service bus that routes data and applies process rules on the data as it flows from one step of the process to another.

When designing a process flow you can include decision nodes that can route data to different activities at runtime based on routing conditions such as checking for exceptions, checking for specific data or context values, or any other business rules that can determine the routing behavior of a message in a process flow. Content based routing can be handled through Gateways or decision nodes where users can define expression conditions on how or when the data needs to be passed to the next activity or sub-process based on the evaluations performed by the conditional rules.

SOA approach allows for reuse of micro-services within your pipelines. And also with its meta-driven and not code-driven service approach your team can easily create or modify existing orchestrations with ease without going through a lengthy code build-outs and deployment process. Thus your solutions can support iterative process for modifying solutions that do not require a time consuming process of code re-writes and package deployments.

Let’s discuss some of the key components of this data integration architecture in more detail.

APIs are associated with backend processes that are triggered whenever a client sends a request to that API. For example, GetEnrollment is an API operation, when called, triggers the related orchestration that authenticates, processes the request and sends a response back to the client. Each operation can be associated with a specific process flow or you can group all the operations within a single dynamic flow whose runtime behavior is determined based on the type of incoming request or payload.

API implementation framework also depends upon whether your team can easily share these services with your customers so that they are discoverable and consumable in minutes. Moreover think about how would your low-tech customers or “citizen integrators” consume these services? Can they connect their Dropbox to your API, how about connecting their Google spreadsheet or Database to your API? Your API construct should be based on handling any eventual source system that your customers may like to use in order to share their information with your company. Having a framework that supports “any-endpoint-to-API” is an efficient design approach since using this approach will solve lot of challenges later on as your business grows and its success is dependent upon existing APIs to support these data variations and be able to scale accordingly.

For example, here’s an API implementation use case in Adeptia. Company implements multiple APIs for different business services and these APIs are then published outside the firewall in API Gateway running on DMZ for customers to discover and consume via a customer facing “Connect” portal.

APIs can also function as pass-through connections that take payloads from customers in variety of formats and simply pass the data into your company. An example could be receiving EDI files from suppliers or receiving HL7 data files from healthcare providers and routing them directly into a backend staging location.

To learn more about APIs, check out Adeptia API Deployment Architecture.

The type of data formats supported in B2B Integration includes EDI, ODETTE, EDIFACT, HIPAA, and HL7. Users can build base data mapping templates for the different message types and reuse these maps for new customers thus providing an accelerated ramp-up time for your business team to onboard new clients.

B2B Integration should also allow customizations to the EDI format. In most cases the customers tweak their EDI messages with custom segments and fields. Your data integration solution should be able to allow users to modify standards according to the file formats used by the clients. This can be done by accessing the standards through a UI or by editing the backend XSD definition of the EDI message. One of the key points here is that at runtime the compliance checks can be made more flexible so that the validation service does not reject a message outright. User can modify the standard compliance rules in order to accept custom segments.

Customers also send data in non-EDI formats such as CSV, Excel, Fixed Width, and XML. Users should be able to configure the integration solution to handle these data types along with the EDI message formats.

In this example, having connectors to Salesforce and BambooHR and also to internal databases are essential if we need to build a simple process that handles the data movement and conversion from one system to another.

Cloud integration is not simply about app-to-app connectivity, it also includes having the ability to map the data to a canonical format defined by the target application. Moving Salesforce data to BambooHR means that first we need to analyze which particular data object the record needs to be mapped to, and second, what are the mapping rules required to correctly map and send that data to BambooHR.

As part of implementing this example, some of the key steps are:

• Configure a webhook in Salesforce that triggers a workflow based on a new record entry in the Provision custom data object
• Webhook sends out the new data to the workflow when a new employee is added with status of “Start Provision”
• Workflow takes the source data, maps it to the NewEmployee method of BambooHR. Mapping rules are pre-defined at design time by the user in the data mapping service
• Workflow then updates the employee record in Salesforce and changes the status to “Completed”
• Workflow also notifies other departments about the completion of the employee provisioning process

The above steps outline the basic implementation of cloud application with your internal and other cloud systems. Some of the additional steps may also include things such as managing invalid data and handling of the data exceptions as part of another sub-process or through another error-handling activity. Exceptions may result from a system that is not responding or the data transferred to the target application being invalid. Since errors can occur both at the data level and also at the process level, how efficiently your data integration architecture allows you to handle these exceptions directly impacts how well you manage your integration solutions iteratively when onboarding new customers or partners.

I hope this brief overview of Data Integration Architecture has provided sufficient context to help you follow through some of the different design patterns explained in the next sections.

Data conversion activity takes the source data and converts it into the appropriate JSON or XML request format that is then passed during the subsequent API call. Depending upon the format of the JSON or XML request, user defines the mapping rules as part of the design time mapping service.

Suppose we have patient healthcare data from two different data sources, a subscriber and a claims database. We can design a flow that takes data from these two sources and merge them in a mapping activity. The output of the mapping activity is an aggregated data that is loaded into the target database.

The data sources in this case are databases that can be attached to a database trigger so that the process flow picks up only those records that are needed by the target system. Database trigger can poll for new changes or updates occurring in the source tables and based on our trigger rule we can pick only the delta that is identified by those updates. In addition to database trigger we can also use a scheduler to run batch jobs at a particular time and pull all the data from the two separate sources and push the combined output into the target system.

Here’s another view of the same process with modified activity labels and explicit data streams. One of the options in designing a flow is that you can also explicitly show the data streams being routed to another activity. Showing the streams in this type of “multiple source to target” design approach provides user with a better context of what the flow does and how the data is being passed from one activity to another. To learn more about how to design this process flow, check out the example related to two sources in one process flow.

A use case for this type of design pattern can be a scenario where user fills up a web form and requests particular patient’s healthcare report. User can specify the data that needs to be pulled for that patient such as recent visits, lab tests, and prescription refills and also selects the type of report format such as PDF, Excel, or a CSV. Once the form is submitted it triggers the above orchestration where based on the type of the report format selected by the user in the web form, the Gateway routes the data extracted from the database to the appropriate process path. For example if the report type is PDF, then the Gateway routes the data to the steps which would convert the raw data into a PDF and email the report to the end-user.

Let’s focus on the initial steps of this workflow where the data is received from Salesforce and how it is routed to the human workflow tasks. Here we have also used annotations to describe each activity’s function.

First activity is related to getting the data from Salesforce. Within Salesforce you can configure an outbound rule that would send out the changed data to this process endpoint. The data received would be in Salesforce XML format (based on the SFDC WSDL and the Provision custom object fields). Once the data arrives we convert the data through mapping into the format that can be rendered in a human workflow task form. These forms can be designed in Adeptia using the Rich Form utility and based on the mapping each field in the form would display the data that is collected from Salesforce. As part of this process design we can add additional source systems to get more information about the new hire. Refer to the previous example that explains how to design a flow with multiple sources.

Decision nodes can include multiple conditions each associated with different process routes the flow can take at runtime depending on the rules. Here we are sending the data back for correction, but you can also route data to a staging table where another system corrects the data and it is then routed back to the process flow. Conditions can vary based on the type of data as well. For example if most of the data is applicable for another department for review then the data can be routed to a sub-process that processes the particular data and returns the result back into the parent flow.

Another application of this design approach are in scenarios where a large file needs to be split into smaller chunks and then each chunk needs to be processed synchronously or asynchronously. You can also think about modifying the design where rather than sending the data to a target system, the data instead is sent to a sub-process that processes each chunk of records. This sub-process can run synchronously where after completing one batch it sends a signal back to the parent flow to get another batch of data. In cases of an asynchronous approach, the parent flow can spawn multiple threads of the sub-process corresponding to the number of splits or chunks. In this case the parent flow does not wait for the sub-process to complete one chunk at a time but it simply spawns hundreds of threads and the sub-process concurrently executes and completes the data load. Therefore using asynchronous approach in calling sub-processes is more efficient in handling large files since we take advantage of the multi-threaded, concurrent execution of the sub-processes.

To learn more about how to loop through data, check out Record by record processing in a loop.

The above data conversion flow is published as a REST API that receives customer data and in the second step it does a lookup into the rules table to determine how to parse the incoming payload in the request (schema), what data conversion service to use to transform the data (mapping) and, when the conversion is completed, the output data is sent back to the client. This is an example of a dynamic data mapping API that takes a request and based on certain rules it converts data to another format. Rules can also be passed inside the request that may contain the mapping service and parsing entity IDs that the flow can consume in order to convert the data. There can be additional use cases for dynamic process flows based on different business data scenarios. To learn more about a dynamic flow pattern, check out creating a dynamic service.

Here once the inbound EDI messages arrive they are validated and parsed (2nd activity in the flow) and are routed to a data mediation activity that converts the data into two separate target output streams. One of the streams is mapped to SAP connector that would take the IDoc and load it into SAP ERP system and the other stream is sent to an API as a web service payload. We can add additional steps after the application calls by placing activities that would process the response messages returned from the target systems. The main goal of this process approach is to give you several options on how to design flows that integrate data with multiple applications and how the data can be aggregated, routed and processed in an orchestration.

In this process flow, the data mapper plays an important role in data mediation between source and target systems. Data needs to be transformed into the particular IDoc and JSON formats and the developer can specify rules related to data quality and data conversion and generate the two outputs needed by the target systems. At runtime the data mapper applies all your data quality rules and only sends the correct data to SAP and to the API. The erroneous data can be routed to an error handling activity that fixes the data and resubmits back into the process flow as described in some of the previous examples. To learn more about application integration in Adeptia ESB, check out this example Integration of Salesforce with SAP.

In this process flow the source is a JMS connector that is listening to a new message in a MQ. When a new message arrives we route the data to a message validation service and as a result if this service throws out an error then that bad message is loaded back into the MQ with a status of error else the valid message is mapped to the SAP ERP system. We can also extend the process where we update the status of the particular valid message as “success” in the MQ. There are several other ways you can extend the process design by adding additional sources or targets and rewiring the paths that the data would take as it propagates from source to the target system. To explore this process design in more detail, check out real-time triggers with MQ.

To learn more; check out Data Integration Examples and Videos.