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.

EXPORTMI now available in M3 13.4 and M3 CE for SQL-like ad-hoc access to tables

During the IMUN conference last week, Ole Rasmussen mentioned Infor's new API EXPORTMI that allows us to perform SQL-like queries over M3 tables.  While we could achieve the same functionality by building custom APIs using MDBREADMI or CRS990MI, this gives power users and consultants a quick and dirty solution to pull data out of tables on an ad-hoc basis.

Using i3 Consulting's Excel REST API tool, talking to a M3 instance in Infor's cloud, we can see this in action:

This capability is a critical requirement for single and multi-tenant customers in Infor's cloud as SQL access is typically limited in single tenant and outright banned in multi-tenant.

The functionality is described in NCR 9965.

Configurable XML documents from M3

Infor has just released the XML output capability that was announced as part of the 13.4 deliverable and was originally expected late last year.  This is an alternative to the streamfiles that Movex / M3 has traditionally generated and provides another great opportunity for modification elimination.

The release notes are here.

The Infor press release is below.

"Infor M3 Configurable Output Management enables the use of extensible self-contained business documents in XML format combined with OpenText™ StreamServe StoryTeller, a marketing-leading document layout tool. 
For existing customers, Infor M3 Configurable Output Management and the use of XML can help simplify the creation and maintenance of business documents. 
For new Infor M3 customers, Infor M3 Configurable Output Management and XML can act as a project accelerator through reduction of the time required to define business documents. This phase one delivery enables you to switch invoice and credit note-related documents to the new XML format. The plan is to deliver more business document types for the Infor M3 13.4 platform. 

Why adopt Infor M3 Configurable Output Management

• Switching to XML business documents within Infor M3 can help reduce the cost of ownership of your solution by reducing the need for source code modifications and simplifying the tailoring of business document layouts.
• You can keep all your business documents when there is an upgrade to Infor M3 13.4 and you can move them to the new Infor M3 Configurable Output Management XML format whenever it suits your business, i.e. no immediate migration requirement.
• Provides greater flexibility in the generation of XML business documents, including the ability to extend with custom elements and user defined fields without the need for any source code modifications.
• All applicable XML business documents are self-contained, include relevant language translations and use standard data formats.
• The Infor M3 base StreamServe bundle includes new added value components, such as the StoryTeller layout tool, and customers that have a valid support agreement for the product can upgrade to the new bundle free of license charges.
• Use the newest capabilities of StreamServe, such as StoryTeller, which is designed to help reduce the number of document layouts that your customers need to maintain. In the new solution, layouts can be maintained in a global project template. 

Infor M3 version compatibility 
Infor M3 customers need to be on Infor M3 13.4 or later to use the new Infor M3 Configurable Output Management capabilities."

Smart Office 10.2 hotfix 17 resolves JScript not working on Windows 10

Infor have recently released hotfix 17 for Smart Office 10.2.  This resolves a number of bugs including the issue with JScripts not working on Windows 10 machines :).

The fix doesn't appear to have been back-ported yet to earlier versions of Smart Office - so M3 7.1 customers with Windows 10 are still unable to run JScripts.

Smart Office Hotfix 17 is available via CCSS.

Windows 10 patch MS16-072 / 3163622 / 3163018 can break Smart Office *WORKAROUND*

A couple of months ago I logged an issue with Xtreme regarding Smart Office client being unable to communicate with the Smart Office server.  This has become a more significant issue with the Windows 10 anniversary edition also breaking JScripts running within Smart Office.

Today I have received a suggested workaround from Infor now to resolve the issue of windows updates stopping Smart Office loading.  I've tried this on a couple of sites and it appears to address the issue.  The workaround is:

1. Download IIS Crypto 2.0 from Nartac.
2. Install this on the PC
3. In IIS Crypto 2.0 on the Cipher Suite tab (1), untick the TLS_DHE_RSA_WITH_AES_128_CBC_SHA Cipher (2) then click Apply (3):

4. Reboot the PC.

Smart Office should now load correctly.  I've tested this against multiple sites and multiple versions of Smart Office and all the Smart Office instances that were not working now do work.

Unfortunately this does not resolve the JScript issue :(