Querying Infor's Datalake via Compass and ION APIs

When data has been published to Infor's Datalake from Infor or non-Infor applications we can access this data from the newly provided Compass API via ION APIs as follows:

Find the datalakeapi API & click Documentation:

The API methods exposed are:

We will use the POST / jobs API to submit our query, then GET /jobs/status to see if the query has completed and finally GET /jobs/result to get the result of the query.

This returns a query ID that we can use to obtain the status of our query and eventually get the result of the query:

Querying the status of the compass API call via GET /jobs/status:

As the status is FINISHED we can now request the result of the query by calling GET /jobs/result:

So the result of the query (select count(*) from MITBAL) against our data in Datalake is 22,624 records.

We can then leverage this capability through external tools like Powershell as described here.

Calling ION APIs from Powershell

Further to the post on how to call ION APIs from Postman, here are quick notes on how to call ION APIs from Powershell:

In the last post we had a json file from ION API with the credentials information:

{
"ti":"######_TRN",
"cn":"Example_TRN",
"dt":"12",
"ci":"######_TRN~********************",
"cs":"@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@",
"iu":"https://mingle-ionapi.se2.inforcloudsuite.com",
"pu":"https://mingle-sso.se2.inforcloudsuite.com:443/######_TRN/as/",
"oa":"authorization.oauth2",
"ot":"token.oauth2",
"or":"revoke_token.oauth2",
"ev":"^^^^^^^^^^^^",
"v":"1.0",
"saak":"######_TRN$$$$$$$$$$$$$$$$$$$",
"sask":":::::::::::::::::::::::::::::"

}

And a URI:

https://mingle-ionapi.se2.inforcloudsuite.com/#######_TRN/M3/m3api-rest/execute/EXPORTMI/Select/?SEPC=%7C&HDRS=1&QERY=count(*)%20from%20MITTRA
We can use this information to call the ION API from Powershell:

#Set the URI

$Uri = "https://mingle-sso.se2.inforcloudsuite.com:443/####_TRN/as/token.oauth2"

Set the token request:

$Body = @{
grant_type = 'password'
username = '######_TRN$$$$$$$$$$$$$$$$$$$'
password = ':::::::::::::::::::::::::::::'
client_id = '######_TRN~********************'
client_secret = '@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@'
scope = ''
redirect_uri = 'https://localhost/'
}

Request the token from ION API:

$AuthResult = Invoke-RestMethod -Method Post -Uri $Uri -Body $Body

We can show the token in Powershell:

$AuthResult.access_token

We then call the API, passing in the URI and the token:

$output = Invoke-RestMethod -Uri 'https://mingle-ionapi.se2.inforcloudsuite.com/#######_TRN/M3/m3api-rest/execute/EXPORTMI/Select/?SEPC=%7C&HDRS=1&QERY=count(*)%20from%20MITTRA' -Headers @{ 'Authorization' = 'Bearer TokenValue' }

Get the result of the API call - in this case the number of records in MITTRA

$output.miResult.MIRecord.NameValue.Value

In Powershell this is:

Giving us a row count for MITTRA of 4,152,575 rows.

Calling ION APIs from Postman

The first step in integrating to Infor's Multi-tenant CloudSuite via ION APIs is by simulating the call of these using a tool like Postman. This article will quickly run through the process for this. In this we:

1. Obtain the URL to call an ION API
2. Create an authorised transaction that can call the ION API
3. Call the ION API from Postman

Step 1: Calling the API from ION API in Ming.le

Select the suite to call the API from. I'll use M3:

Find the API to use, then click Documentation:

Select the API transaction to use, then click Try it out:

Enter the parameters to use for the API transaction and click Execute:

Save the request URL returned e.g.

https://mingle-ionapi.se2.inforcloudsuite.com/#######_TRN/M3/m3api-rest/execute/EXPORTMI/Select/?SEPC=%7C&HDRS=1&QERY=count(*)%20from%20MITTRA

Step 2: Creating an ION API Authorised application

From ION ION create a new Authorised App / Backend service:

Then Download Credentials:

Set the credentials to be a service account, then specify the user to use.

The json file returned will look like this:
{
"ti":"######_TRN",
"cn":"Example_TRN",
"dt":"12",
"ci":"######_TRN~********************",
"cs":"@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@",
"iu":"https://mingle-ionapi.se2.inforcloudsuite.com",
"pu":"https://mingle-sso.se2.inforcloudsuite.com:443/######_TRN/as/",
"oa":"authorization.oauth2",
"ot":"token.oauth2",
"or":"revoke_token.oauth2",
"ev":"^^^^^^^^^^^^",
"v":"1.0",
"saak":"######_TRN$$$$$$$$$$$$$$$$$$$",
"sask":":::::::::::::::::::::::::::::"

}

Step 3: Calling the ION API from Postman

In Postman, paste the request URL from step 1 into the URL field:

On the Authorisation tab, change the type to OAuth 2.0, and click the Get New Access Token button:

Change the Grant Type to Password Credentials.
Enter pu + ot from the json file into the Access Token URL e.g.
https://mingle-sso.se2.inforcloudsuite.com:443/######_TRN/as/token.oauth2
Enter saak from the json file into the Username.
Enter sask from the json file into the Password.
Enter ci from the json file into the Client ID.
Enter cs from the json file into the Client Secret.

Click Request Token. This will return a token. Click Use Token.

This will put the token into the Access Token field. Then click Send to call the ION API:

This returns the data requested.

For more details see the Infor presentation here.