Data Lake Component

The Data Lake component runs a SQL query against the Infor Data Lake, which holds a copy of your M3 data in the Infor cloud. Use it when you need a lot of data at once, for example for reports, exports or combining M3 data with data from other workflow steps. Fetching this much data through regular M3 API calls would be slow or impractical. Choose Data Lake over M3 API for large reads where slightly delayed data is fine; choose M3 API when you need live data or want to write to M3.

note

The Data Lake is not real-time. Changes made in M3 take some time to show up in the Data Lake. Use this component for reporting and analysis. If you need up-to-date data, for example to check current stock or an order you just created, use the M3 API component instead.

Before you start

You need a Connection that gives Vince access to your Infor Data Lake. Ask your administrator if you're not sure whether one exists.

Settings

Setting	Required	What to enter
Connection	Yes	The Connection that gives access to your Infor Data Lake.
Endpoint URL	Yes	The address of the Data Lake query service in your Infor environment. It usually looks like `https://mingle-ionapi.<region>.inforcloudsuite.com/<TENANT>/DATAFABRIC/compass/v2/jobs`. Your administrator or Infor contact can give you the exact address.
Query	Yes	The SQL query you want to run. See Writing a query.
Also save output as file	No	Also save the result as a file that later steps can use. Found under Advanced in the settings panel. See Also save output as file.

Writing a query

Queries run on Infor Compass, the query engine of the Infor Data Lake. Compass uses standard SQL with some additions. Only reading data (SELECT) is supported. You cannot change data in the Data Lake.

In the Data Lake, M3 tables have the same names as in M3 (for example OOHEAD for order headers and OCUSMA for customers). The column names do not include the two-letter prefix you see in M3. For example, the customer number is CUNO, not OACUNO.

Example

The 1,000 most recent customer orders in company 100.

SELECT CONO, ORNO, CUNO, ORDT, ORST
FROM OOHEAD
WHERE CONO = 100
ORDER BY ORDT DESC
LIMIT 1000

Compass functions

On top of standard SQL, you can use Compass functions. For example, infor.compasstimestamp() works with the timestamp that shows when a record was loaded into the Data Lake. You can use it to fetch only records that have changed recently.

Infor's documentation covers these topics in more detail:

Querying the Data Lake: how Data Lake queries work
Compass SQL syntax reference: all supported SQL commands and Compass functions

tip

Test and fine-tune your query in the Infor Compass Query Editor first. Once it returns what you expect, copy it into the Query field.

Time limit

The query must finish within about 4 minutes. If it takes longer, the step stops with a timeout error. If this happens, make the query smaller: select only the columns you need, add WHERE filters, or use LIMIT.

The result

The rows returned by your query become the step's output, as a list of records. Numbers, dates and true/false values are converted to the right type automatically. You can use the result in the next steps, for example with a Transform, Excel, Email or Table component.

Also save output as file

Turn on Also save output as file if you also want the result saved as a file. The rows are still returned as the step's output as usual; the file is created in addition.

The file is a JSON file named <step id>_output.json and contains the same records as the step output. It is attached to the step output, so later steps can use it, for example to send it as an email attachment.

To load the file in a later workflow step, use the Files API.

Running the workflow

Method	How to use
Manually	Run the workflow from the Workflows list. You can view and download the results in Execution Logs.
On a schedule	Set the trigger to Schedule and choose how often it should run.
From Excel	Sign in to the VXL Excel add-in, select the workflow, fill in any parameters, and run it.

Troubleshooting

Problem	What to do
The step fails with a timeout	The query took more than about 4 minutes. Make it smaller with filters, fewer columns or `LIMIT`.
The step fails with "Job execution failed"	The Data Lake could not run the query, often because of an SQL error. Test the query in the Compass Query Editor to find the problem.
The step fails with an authorization error (401 or 403)	Check that a Connection is selected and that it has access to the Data Lake.
Recent changes from M3 are missing	The Data Lake is not real-time. Wait and try again, or use the M3 API component for live data.

Before you start​

Settings​

Writing a query​

Example​

Compass functions​

Time limit​

The result​

Also save output as file​

Running the workflow​

Troubleshooting​

Further reading​