Integrate Systems and Data

Integrate Systems and Data
RESTful servers are a compelling architecture because they are network aware. They are a great way to connect mobile devices and Web applications. RESTful servers also provide web services, which are a great way to perform data integration functions.
These web services exchange data in real time. This is in contrast to previous batch-oriented approaches, which relied upon Extract, Transfer, and Load (ETL).
The following diagram illustrates real-time integration:
real-time data integration1
real-time data integration1
You can integrate data–ranging from integrating retrieval over multiple data sources to synchronizing updates–using
CA Live API Creator
's integration services.
In this article:
Common Patterns for Data Integration
The following integration services address common patterns for data integration, as illustrated in the following diagram:
The following process describes
CA Live API Creator
data integration:
  1. Define a resource to catch the webhook from partner/internal systems.
  2. Transform the request (alias and projection) with resources that you define explicitly in your API and request events (JSON reformatting, such as metadata tag insertion).
  3. Define reactive logic and JavaScript logic, part of which might be sending webhooks to related partner/internal systems.
Enable Systems Integration Using Resources
You can enable systems integration using resources. Use resources for incoming and outgoing requests. They provide declarative services for name mapping and nested objects.
You can:
  • Integrate the same data from different systems
    (for example, CustomerId vs AccountNumber) and receive or send data by providing name mapping, or transformations. You alias the names of tables and columns that are used to construct the resources that you explicitly define in your API. You can return GETs in this format and "de-alias" the resources so that they can share business logic by using update processing (PUT, POST, and DELETE).
    You can enable systems integration by providing webhooks. You can register resources as the handler for these posts, where the name mapping matches the webhook definition.
  • Integrate a set of related data between systems or business objects
    (for example, Order Header + Line Items + Shipment details), by explicitly defining resources in your API. You can also create sub-resources based on other data source types, such as Mongo, JavaScript APIs (for example, to other RESTful servers), and custom SQL.
For more information:
  • About using resources as transformation definitions (for example, in event handlers), including the
    method, see The logicContext Object.
Transform or Poll for the Data
In many cases, resources can directly process incoming requests (step 1 in the previous diagram). However, sometimes you must transform the data or poll for the data.
Custom Endpoints - Physical Transforms
If the payload data is in a format that is not easily matched by a resource, you can define a custom endpoint that can access the request data, reformat it, and then
it to a resource that you explicitly defined in your API using a command-line tool, such as cURL.
For more information about how to create a custom endpoint, see Manage Custom Endpoints.
Reactive logic fires only in response to requests
It requires a triggering event. If there is no initiating call, poll for the data and then
the result.
Polling can take many forms. Some systems can poll for the data ("call me every minute, and I'll tell you what has changed"), whereas others require that you to read the data periodically. For periodic polling, issue the reads by "waking up". One way to do this is by creating a cron job that inserts a row into a
and then providing logic (for example, an event rule) on
insert to read the data, and the
For more information about the Wake-up Request command pattern, see Command Pattern.
JSON Metadata Logic
You can designate that post operations perform merge operations, such as when receiving data from an external system that might or might not already exist locally. Or, you can de-reference natural keys into IDs by performing lookup operations.
For more information:
Business Logic
Business logic is generally regarded as the code you write that retrieves data and processes updates. The code involves not only the SQL, but also change propagation (for example, paying an order reduces the customer balance).
You can create row events that API Creator executes during GET operations. API Creator automatically invokes event logic as it processes updated requests. Row and update events are often used to invoke the APIs.
For more information:
Request Objects (the Request Pattern)
Received data often maps (typically by way of a resource that you explicitly define in your API) to database tables whose logic API Creator manages, but this is not always the case. A good practice is to create a request table that stores the posted data. Request tables have the following advantages:
  • You can place logic on the request table to process the request. For example, use REST utilities to integrate with other systems.
    For more information about these REST utilities, see the "REST Utilities to invoke APIs from other systems" section.
  • The request table inherently keeps a record of requests, for auditing.
The following is a typical pattern:
  1. Store the raw JSON using the request table.
  2. Move the request table into domain objects. This might fail if, for example, the data is garbled and the data cannot be converted (for example, alpha characters instead of numbers). You can trace back to the original submitted data using the request object. The data might convert properly but be invalid (for example, it is a number but it is too high). In that case:
    1. Store the data as received, but mark it as
      Not Ready
    2. Create a manual process to repair the data (perhaps send an email).
    3. When the data passes your logic, set the
      flag to ok, and use this to trigger the integration logic.
For more information about creating request tables and the request pattern, and the Ready Pattern, including marking data as Not Ready, see Logic Patterns.
Persistence-based Integration
Integration is more than message formatting and routing. It is often more complicated, requiring state. For example, generating an outbound message might require the consolidation of multiple inbound messages. Not only might this require the request pattern, but you might often find it necessary to consolidate multiple messages using additional logic. For example, you might accrue multiple requests by employing managed parent. In such cases, the managed parent object has logic that determines when to issue the outbound request.
For more information about managed parent, see Managed Parent Rule Type.
REST Utilities to Invoke APIs from other Systems
When mapping to integration systems that are managed by existing business logic, use RESTful APIs, if they exist. You can invoke RESTful services in other API servers (not necessarily API Creator servers) by enabling the REST utilities. You can issue these calls from reactive logic or row events.
CA Live API Creator
includes services, such as retrieving a resource or invoking another REST service, using the
JavaScript object.
For more information about the methods that are available with the
object, see The SysUtility Object.
Resources as Transformation Definitions
System integration often requires that you send POST requests to other systems. This posted data may require transformation logic, such as:
  • Choose only specific attributes (for example, not send Employee Salary information).
  • Alias their names (for example, use agreed-upon names rather than schema names).
  • Include related data (for example, an Order with its Order Details).
This kind of transformation logic is tedious to code and is not transparent to business users and API partners. You can integrate the same data from different systems (for example, CustomerId versus AccountNumber) and receive or send data by providing name mapping, or transformations. Using resources as transformation definitions satisfies all of the requirements. The
method is typically called in a table event to transform a table row to a resource instance. This provides transformation logic to select/rename attributes and related objects.
For more information:
Common System Integration Scenarios
The following are common system integration scenarios. The node in teal color denotes API Creator. The other nodes are other systems, which might be enterprise service bus products, other API Creator Servers, or NoSQL machines.
GET-triggered Integration
The simplest examples are triggered by GET requests.
RESTify a SQL Database to a Desired Format
In the following example, a partner's application server is needed to deal with many different databases, each with their own similar but unique schema. The application server coded wanted identical JSON for each, so adding a new database would not require any code changes.
The partner's solution was to declare an API Creator server for each database, where resources were defined on each Schema to match a canonical format. This required only a resource definition. No code changes were required on either the application server or each database.
Mongo Integration
You can integrate Mongo and SQL data into a common mashup API.
For more information about how to integrate with Mongo, see the "Enable Systems Integration using Resources" section.
Mashups with General RESTful Servers
A common requirement is for a client to send a GET request where the results return data from both a local database and another RESTful service. The following diagram illustrates this requirement:
You can specify JavaScript subresources that can materialize subresources by invoking a RESTful service. In this example, the called RESTful service is another API Creator instance (from Demo), illustrating you can use API Creator as both a client (per the previous code) and as a server (the called instance). With API Creator, there are no restrictions. The called service can be any RESTful server, such as Mongo and enterprise data sources.
For more information about creating JavaScript subresources, see Customize your API.
Time-triggered Integration
The following upsert external request example illustrates a "push" from a related system. At time, you must poll and "pull" the data you need, periodically. A common approach is to create a cron job(outside API Creator) and issue RESTful requests to
CA Live API Creator
Enable Systems Integration Using Webhooks
A common pattern for enabling systems integration is to provide webhooks, where the originating system provides a mechanism to register listeners to various update actions. These registrations often provide options to implement the listeners as a POST request (for example, specify URL for post when event occurs).
You can:
  1. Define resources in API Creator to match the webhook format.
  2. Register the resources as webhook listeners.
  3. Process POSTs.
    For more information about how to process POSTs, see the "Update-triggered Integration" section.
    You can view an example of processing POSTs in the B2B sample.
    For more information about this sample, see B2B API Sample.
Provide Custom Endpoints to Address Authorization and Formatting
Webhooks might require different authentication techniques than API Creator or might deliver JSON in expected formats. You can address both issues by providing a custom endpoint.
For more information about how to create custom endpoints, see Manage Custom Endpoints.
Update-triggered Integration
Updates (PUT, POST, DELETE) can trigger integration. You can use update events to address these updates.
Example: Upsert External Request
Your RESTful service might need to process data submitted by another service, perhaps a Master Data node. Your RESTful service might also require that the Master dictate the names of the submitted objects/attributes, as shown in the following diagram:
You can define resources whose aliased names match the partner system. This scenario is also common in a Master Data Management (MDM) configuration, where the master might send changes to subscribing systems. Often the submitted data might or might not already exist in your site. You can post data as either an insert or as a merge as an update using the
metadata action tag, or can upsert.
For more information about the
metadata action tag, see MERGE_INSERT Metadata Action Tag.
Post to External System
Your service might need to publish JSON changes to other systems. The publish might need to be in the form of multi-table business objects whose names match the target system, as shown in the following diagram:
You can approach this requirement by doing the following:
  1. Define a resource whose aliased table/attribute names match the target (
    in the following example).
  2. Provide an update event that invokes
    to create a JSON string for this resource and send it to the target (for example, as a post). The following image shows the commit event in API Creator:
Retrieve External Data for Current Transaction
Your business logic might need to do the following:
  1. Obtain data from an external system to process a transaction.
  2. Transform the external data into a local name.
  3. Store the external data, including transformations/derivations (for example, sums and counts).
  4. Use the resultant local data in the current transaction (for example, for validations).
The following diagram illustrates the requirement:
A reasonable approach is to:
  1. Define the
    resource whose alias names match the external system.
  2. Use
    to get the external data.
  3. Use
    to post this data into the
Integrate External Systems Without Required Persistence
You can integrate two systems that are not open to modification, available only by way of existing APIs. This type of integration might not necessarily require persistence. You can use the following alternative approaches:
  • Introduce persistence by using the request pattern.
  • Use JavaScript resources.
  • Use custom endpoints.
Introduce Persistence (for Example, for System Monitoring)
While you might not require persistence, you can introduce persistence so that you can log requests and their results. This can assist in system monitoring. You can introduce persistence by using the request pattern, as follows:
  1. Create a request table that receives in the incoming POST. This POST might be a WebHook from a remote system, or a cron job that polls for changes. The request table might have few attributes, for example, date and time, a completion status, and progress indicator fields.
  2. As required, declare rules on the request table that log date and time.
  3. Create an event on the request table that processes the request.
    This event might use
    to read other systems, and
    to write. You will commonly need to access data in the GET result to create the put/post request data using JSON parse and stringify.
    Best practice:
    Update values in the request object to indicate processing progress. This can help in problem diagnosis.
  4. Consider what happens when the request fails.
    You might, for example, want to set a field in the request table and use a request (response) event to control the response message.
For more information:
  • About the request pattern, see Logic Patterns.
  • About how to invoke other RESTful services from business logic or row events using the
    methods, see The SysUtility Object.
  • About JSON parse and stringify, see JavaScript (API Creator and JavaScript).
  • About Request (Response) events, see Event Handlers.
Use JavaScript Resources
You can define JavaScript resources and can specify similar code as you would in the event.
Use Custom Endpoints
Custom endpoints offer similar functionality, but are not subject to authentication and provide options to return response data in different formats.
Integrated Example
You can integrate multiple systems together using JavaScript events. JavaScript events can call local and remote REST services.
The Business-Oriented Introduction to
CA Live API Creator
video illustrates integration.
For more information about this video, see Videos.
The B2B sample provides a good example. You can process the entire transaction with several rules and the following JavaScript, which is about 20 lines of JavaScript using API Creator, operating with live logic. API Creator provides all the other REST/SQL handling. It is also be invoked as a consequence of an interactively entered order.
The logic is not coded into some button's controller in a mobile/web app. The logic is automatically partitioned for re-use. In many web apps, the logic is "buried in the buttons", and therefore mobile application cannot share the logic, called as a service by other systems/partners.
CA Live API Creator
automatically partitions logic to the API Server for data access and security/integrity enforcement and so that API Server can properly share the logic. Row-level security is delegated to the DBMS for optimization.
Partner Request
The partner request is a simple RESTful request, such as POSTing an order and two items. Here, we POST the following JSON in the LogicSample database to the
REST endpoint:
"customer": "Max Air",
"IsReady": true,
"items_o": [
"ProductName": "Dynamite",
"QuantityOrdered": 1
"ProductName": "Hammer",
"QuantityOrdered": 1
You can also POST this to the
"customer": "Max Air",
"isReady": true,
"LineItems": [
"productName": "Dynamite",
"quantityOrdered": 1
"productName": "Hammer",
"quantityOrdered": 1
For more information, see the Sample Database.
Compliance Message
Certain substances are controlled and require regulatory reporting. We can define an event on the products table using API Creator. The following image shows the
Controlled Substance Reporting
event rule for the
API in API Creator:
Screen Shot 2017-11-02 at 1.08.45 PM.png
For simplicity, this event rule logs the message instead of sending it; look for a log entry such as the following (ensure that your API keys debug settings are correct):
***sending message
CA Live API Creator
invokes the logic as a consequence of processing the order. Because
CA Live API Creator
sends the order as
in as a POST request, this invokes the make order ready logic; the event is triggered as a consequence of the adjustment to the
For more information:
Partner Response
The normal response JSON is the transaction summary. In this case, we want to reformat the response per our understanding of our partners' message formats, which are object and attribute names.
  1. Define a RESTful resource that maps our tables onto their names, using resource aliases to provide the proper names.
  2. Define a request event that overrides the normal response. The following image shows the
    Partner Response
    response event in the
    Screen Shot 2017-11-02 at 1.11.22 PM.png
For more information about how to define a request event, see Event Handlers.
In the REST Lab, test the
resource. The following image shows the result:
The response contains the resource result, without the transaction summary. The response JSON shows the
field, which was added in the last line.