For anybody who has been involved in conversations where one system has to communicate with another, there’s a good chance the term REST API’s will have come up. But what are REST API’s and why should we care?
This post will answer that question and provide some examples of how they can be useful for OneStream implementations.
What Are REST API’s
Let’s start by looking at each part separately.
API stands for Application Programming Interface. API’s are provided by vendors as a way of allowing third parties to interact with their application. For example, in an ERP system, adding a new HR record may involve updating many database tables. Instead of needing to know how these tables relate to each other, a third party can make a call to the API, passing parameters such as employee name or date of birth, and the API does the job of ensuring the correct tables get populated.
REST stands for REpresentational State Transfer. REST API’s allow HTTP commands to be used to make a request to a web service and receive a response in the form of a ‘payload’. Communication is kept to a minimum as each transaction is independent of any others, with only one request and one response.
The example above can be illustrated in the following diagram where the HTTP protocol POSTS a message to the REST API. Within the URL are details of an employee to be added to a system, and the REST API then make the appropriate updates to the underlying database tables.
OneStream REST API’s
Details of the REST API’s provided by OneStream can be found in the REST API Implementation Guide. There are three types:
Authentication - Mainly used to confirm that the OneStream components have been configured correctly against the authentication provider. The guide states that currently Okta, Azure AD and PingFederate are supported providers. Examples in this post use Okta as the authentication mechanism.
Data Management - Allows calls to be made to run either Data Management Sequences or Steps. An example of where this could be used would be a customer using an integration platform such as MuleSoft calling OneStream to run a Data Management export job. Once the export has been completed, the integration platform picks up the file and distributes it to its target systems.
Data provider – allows calls to be made to retrieve data using different methods, such as from Cube Views or running SQL commands. An example of how this could be used would be a report in a third party reporting system making a call to extract data from a Cube View at time.
Let’s take a look at some examples of each.
In the first instance a few simple Powershell commands can be used to make two REST API calls. The first transaction requests a token from Okta:
The returned token can then be passed into a second transaction that calls the OneStream DataManagement REST API to execute a Data Management Sequence:
Viewing the OneStream Task Activity log confirms that the Sequence did run:
For the second example we will use the Microsoft Power BI desktop to write a report that retrieves data from a Cube View. Calls to OneStream can be written in the Power Query Editors Advanced Editor dialog.
Once a call to Okta has been made to obtain a token, a call is made to the OneStream DataProvider REST API:
As you can see from the above query, additional parameters are passed in the call to the OneStream REST API to determine which rows and columns will be returned.
Once the query has been executed, OneStream returns a table which Power BI shows like this:
From here, Power BI features can be used to manipulate the table and create reports which include visualisations. The report can be refreshed meaning up-to-date data can be retrieved from OneStream on demand.
So, what does all this mean? The key takeaway is that integration shouldn’t be viewed only as a scheduled batch process moving data between environments on a schedule. REST API’s allow applications and cloud services to become extensions of each other, with data seamlessly moving between them.