How to replace REST Connector with REST API Call

Center of Excellence · March 11

1. Abstract

This document illustrates how to adjust and optimize your REST API connection to leverage the more recent and advanced features and remove the technical limitations of previous solutions.

Board 12 introduced new ground-breaking integration capabilities. As a Board solution is periodically upgraded to newer versions, it is important to review the application to simplify, remove constraints and potentially expand the platform usage to new scenarios, especially when dealing with inbound or outbound data exchanges.

2. Context

2.1 Autonomous REST Connector

The Autonomous REST Connector is one of the Data Stores available on the Data Pipeline portal.

This connector, once the authentication is successfully configured, will sample the REST endpoint provided and build a relational schema map that represents the output data structure. For instance, if the json returned by the API has an indented structure where the fields are in a parent-child relationship, the connector creates a virtual relational database with tables linked to each other through foreign keys. By doing so, the remote REST API resource can be queried from Board with standard SQL syntax.

Theoretically, the advantage of this approach lies in the fact that the intermediate SQL layer can facilitate the filtering and manipulation of the data returned, especially in cases of complex json structures with multiple levels of indentation. However, in practice there are some limitations:

The endpoint sampling does not always “guess” the correct relationships and key fields and this configuration cannot always be manually adjusted to correct the initial guess.
The remote API is invoked through a SQL data reader and there is no easy way to apply dynamic filtering parameters coming from the selection context of a Board procedure.
POST requests with input data from Board are not supported.

2.2 REST API Call

The REST API Call, introduced with Board 12.5 (Spring 2023) and enhanced with 12.6 (Summer 2023), overcomes all the limitations of the REST Connector and provides further benefits. It relies on an http client component embedded into the Board server. The Data Pipeline is not involved in this flow, which makes the overall integration architecture leaner and potentially better performing.

Compared to the REST Connector, the configuration is more similar to the one required by the most popular API testing tools, like Postman. The json output returned is parsed and automatically converted in a csv-like format. For large datasets or cases where the records are not presented in a typical key-value format there is the option to implement custom rules. These rules leverage the json path syntax and provide the necessary flexibility to easily manipulate any data structure.

Furthermore, the API call supports the use of variables to perform dynamic http requests (GET, POST, PUT, PATCH, DELETE) with URL filtering strings, headers or body that are based on the selection set in the Board procedure. These variables are essentially substitution or data picker formulas. This makes this feature way more capable and integrated with the Board data structures than the REST Connector.

3. Content

3.1 Replacing the REST Connector

To replace the REST Connector with the REST API call, there are some considerations to be made. You first need to make sure that the Board instance where you will implement the API call is on version 12.5 or later. With this change, we will move from an implementation with

a connection on the Data Pipeline based on the REST Connector
an SQL Data Reader

to an implementation with

an API connection in Board, where we configure authentication and base URL
an API call in the loading procedure, where we configure the API path and static or dynamic parameters like body, headers, and filters (query strings in the url)
a Text Data Reader that ingests the file generated by the API call

The three steps above should ideally be tested on a Board sandbox, or at least on a copy of the data model dedicated to developments.

If when testing the API Call from the configuration interface, the call response has status 200 and the preview of the data is displayed correctly, you can save the output on a csv file. On the screenshots below, you can see on the API call configuration interface how a successful response looks like.

This file will then be fed into Board through a Text Data Reader. If there are no json parsing rules set and the fields to import are the same as before, this Data Reader should have the same mapping as the SQL Data Reader that was based on the REST Connector. If the json output has any of the following characteristics

more fields than needed
data is in value-value format rather than key-value
data is deeply nested within the json structure

then json rules can be applied. Here is a basic example of the json output returned by a public Currency Rates API.

As you can see, the data is represented in Currency value-Rate value pairs. The json path commands shown below will allow the REST API call to retrieve the output in a table-like format.

This syntax is a standard and is exhaustively documented online. The parsed output can then be saved on a text file or displayed from a layout on a screen in a “drill-through” fashion thanks to the Show Dialog option.

In the end, the original loading procedure with the SQL Data Reader, now includes a REST API Call and a Text Data Reader. This procedure changes on the data model will have to be promoted from development to the live environment after thorough testing and users validation.

3.2 Benefits

With this change, loading performances are not expected to worsen, since we are moving from an integration architecture with Board - Data Pipeline – Remote API Server to one without the Data Pipeline in the middle. Loading times may even improve in some cases.

However, the main benefit that comes with the adoption of REST API calls is the possibility to have substitution or data picker formulas in the value of headers and query strings. Compared to the REST Connector, this capability can bring the following advantages.

Loading performances. API call filters can be dynamic, for example based on selections on the time entity or on the user triggering the procedure. Each call can now retrieve the minimum dataset required rather than reading the whole dataset. When the REST Connector was used and a unique static filter could not be applied, the SQL Data Reader would have to read the entire unfiltered dataset and apply selections only at writing stage
Data segregation based on user security. For the same reason explained above, if an end-user triggers an API call from a procedure, the selections on the data requested or pushed to the remote API are respected. This means that this integration supports cross-application compatibility with the users security and ensures effective data segregation.
Board Data can be pushed to an API. API calls also supports REST methods allowing to write Board data to an external API or to update the API data.

The last two points can open up new implementation scenarios or simplify existing asynchronous integrations.