How to set up a Rest API connection

Center of Excellence

1. Abstract

This article explains how external systems can retrieve data from Board using Board API queries. The article also showcases a simple case on how Board can obtain data from external systems by calling their REST APIs, thus enabling smooth integration between the two solutions.

2. Context

APIs stand for Application Programming Interface - a software intermediary that allows two applications to talk to each other. APIs are an accessible way to extract and share data within and across applications, systems, and devices.

REST is the most popular API architecture for transferring data over the internet. In a RESTful context, resources are accessible via endpoints, and operations are performed on those resources with standard HTTP methods such as GET, POST, PUT, and DELETE.

Board REST APIs were created specifically for integrating data between Board and external systems, with a machine-to-machine approach in mind. In addition, Board has a “REST API Call” step in the Procedure editor. This step can make an API call to other systems (or other Board Platforms) to trigger automated processes, massively send or receive data, and much more.

3. Content

This section walks through two scenarios that most likely happen in every Board implementation.

• External client retrieving data from a Board API
• Board retrieving data from an external API

3.1 Scenario 1 – External client retrieving data from a Board API

3.1.1 Step 1 – Setting up Board API User

In this scenario, we are using Postman as the external client to retrieve data from Board using a Board API. You can download Postman for free using this link – Postman download.

To call a Board API from an external source, you need to set up a Board Client API in the Users section in Subscription Hub. It's important to note that the API user does not consume licenses.

To set up the API user, provide it with a Client ID and enter a client secret. The client secret acts as the password for the API user and can be of any length.

3.1.2 Step 2 – Setting up the Board API query

Navigate to the data model and click on "API Query”. Create a new query with any desired name. In my example, the query name is "Actuals". Next, choose the appropriate query type.

There are two options to consider:

1. Dataset: This option is recommended for large datasets with many entities by row. However, it does not support down totals or entities by column, sorting, filtering, and ranking functions. It offers significantly faster performance.

2. Layout: This option utilizes Board Layout Object capabilities to return a complete set of data. It supports a wide range of functionalities but may be slower with very large datasets and many entities in the axes.

Configure the layout based on the specific data and aggregation you want to retrieve. This configuration will generate an API URL (highlighted in red box), which you need to copy. Please ensure that you copy only the part within the double quotation marks.

Then save the changes.

3.1.3 Step 3 – Calling Board API Query from the external client

In this step, we will be calling the Board API query from our external client, Postman.

Paste the Board API URL from step 2 into the GET textbox. The GET method is used to retrieve data from a server at the specified resource.

• Within the Authorization tab, choose “Client Credentials” under the Grant Type drop-down list. The Access Token URL is https://boardna-idp.board.com/identity/connect/token. The URL for any Board environment with a subscription hub would be https://<boardcloudinstance>.board.com/connect/token. Enter your Board API ID and Client Secret (from step 1) as the Client ID and Client Secret.

• After setting the above parameters, your system will get an access token which will be used by the system to call the Board API query. In this instance, Postman calls the Board API query and retrieves the data (actuals by Month in this instance) and presents it under the “Body” tab at the bottom.

3.2 Scenario 2 – Board retrieving data from an external API

In this scenario, Board is going to retrieve the currency exchange rate data from an external site - https://fixer.io/ by calling their currency exchange rate API. You can sign up for free on this site after which you will be granted your own API Key (password).

3.2.1 Step 1 – Setting up the external API as a data source in Board

In this step, we are going to establish the external API as a data source in Board. Please follow the below steps.

1. Go to System Administration and select "Data sources."

2. Create a new data source connection by clicking on "+ Data Source Connection." Provide a name for the connection (e.g., "External API").

3. Choose the type as "API" since we are setting up an API connection.

4. The authentication type depends on the external source. In this instance, the site provides us with an API Key Value pair so we will choose “API Key” for authentication type.

5. Enter the Key and Value values provided by the site.

6. Choose “Query Params” from the Add to drop-down list

7. Paste the API Base URL that is provided from the external source and click on Save.

3.2.2 Step 2 – Setting up REST API CALL procedure

In this step, we are going to call the external API setup in the previous step from a database procedure.

1. Open a Board data model, create a new procedure and select the step "REST API Call."
2. Choose the connection that was created in step 3.2.1.
3. The Base URL will be automatically populated. Enter the Query name under the API path, as provided by the external source.
4. In the parameters section, you can define parameters that you want to pass to the endpoint to receive the expected response. In this instance, we pass a parameter to retrieve only the exchange rate for specific currencies (USD, CAD, AUD) as shown in the screenshot.
5. Choose the appropriate HTTP method based on your requirements:

• GET: Used to retrieve data from a specified resource.
• POST: Used to submit data to be processed and create new resources.
• PUT: Used to update a specified resource with new data.
• DELETE: Used to delete a specified resource.
• PATCH: Used to update a specified resource partially.
• In the example provided, GET is used, which is the most common method.

6. There is a field “On error go to group” which can be configured by the user to direct the procedure flow to another group where they can show a specific error message in case the API call returns an error.

7. Choose the output configuration i.e., the location where you want the file with the data retrieved from the external source to be saved. In this example, it is the local C: drive. However, in the client instance, it can be a path to Azure Z: drive or a Board Cloud server C: drive.

8. Also, before you execute the procedure, you can click on Edit Configuration to see if the request is successful (status 200) and how the data looks like after being automatically converted from json to tabular format.

The “ADVANCED” tab in the above screenshot can be used to parse the JSON response to tabular if it not automatically done.

For instance, using the JSON response in the BODY tab, we can define “rates” as the Root element path and map CAD, and USD to two different headers – Rates_CAD, and Rates_USD.

If we go back to the DATA tab, you will see that the output is in tabular format with two columns with headers – Rates_CAD, and Rates_USD.

9. Save the configuration according to the instructions.

3.2.3 Step 3 – Execute API call procedure

To complete the process, follow these steps:

1. Run the procedure as you would normally do. This will trigger the REST API call and retrieve the data from the specified resource. The extracted file will be generated under the path you specified in the output configuration.

2. Once you have the extracted file, you can use the Datareader functionality in Board to load this file into the target CUBE in the target DB. There are two options for accomplishing this:

a. Load from the same procedure: You can include the Datareader step within the same procedure that executed the REST API call. This allows for a seamless flow of retrieving and loading data within a single procedure.

b. Load from a separate procedure: Alternatively, you can create a separate procedure specifically for loading the extracted file into the target CUBE. This provides a modular approach and allows for better organization of the data extraction and loading processes.

By utilizing the Datareader functionality, you can easily import the extracted data into the desired CUBE within the target DB.

Center of Excellence

Thanks @Abhilash Sachidananda for the insights for this article!