What are the options to retrieve 50K records from M3 on a Daily basis

Hi,

We have a requirement where we need to retrieve all the ItemMaster (M3 tables: MITMAS and MITFAC using JOIN) data and need to deliver the same to the FTP server in a TAB Delimiter file format. We need to do the same on the daily basis(after business hours) and Unfortunately we can't follow Event Driven approach and this is the requirement we need to stick to.

The total number of records that we expect is around 50k. Unfortunately the M3 CMS100MI API can't return more than 10K.

Can anyone please help me to list the possible options to achieve this requirement? I could see two possible options but not sure which one is preferable in terms of performance.

1) Noticed in one of the discussions that Infor provide fix to increase the CMS100MI API limit to 10K+. I am not sure how feasible this option. I need to check with Infor about it. If it is possible we can use the same API in the DataFlow.

2) Using Compass Query API in the Data flow. I am not sure how good is this approach to handle bigger data.

Any Alternative approaches are also welcome.

Thanks.

Find more posts tagged with

General Discussions

Accepted answers

All comments

unknown

Hi GS,

Your best bet is to use the Compass API or JDBC driver for Datalake. You won't have the 10k limitation there.

Mark

antonio-caria

correct.

Read 50K records is very fast.

Rene Bottern

Have you considered the 'ION ETL for DataLake' to get the Data to a local SQL instance, this is REALLY fast and querying Local Database is always Fast.

unknown

Hey GS,

The ExportMI APIs for M3 were actually restricted to 10,000 records in one of the 2020 updates, I believe. With all of the M3 table data being easily replicable to Data Lake, the best practice recommendation is to use ExportMI for operational data use cases and Data Lake for large scale processing.

The use case you presented is interesting. The Compass SQL APIs don't support TSV as a standard content response type but I would definitely recommend submitting an enhancement request through the portal (https://ers.infor.com/ers) and ask others to vote on it. Otherwise, you can take the CSV or x-ndjson response type and convert that yourself.

There have been some delays in getting AnySQL on top of Data Lake available but in the future (2022 ETA), this would allow you to extract content from the Data Lake and publish that as part of an integration flow to something like a file connector in ION. AnySQL only provides JSON & NDJSON today but you may want to submit an ERS for this as well to help encourage product teams to take a look.

unknown

I am using Ad-hoc report for similar thing , no limit 10K lines

unknown

Rene, Where coould we find more information about the 'ION ETL for DataLake"?

Thanks

unknown

Hey Jim,

There is a paid ETL tool customers can purchase that connects to the Data Lake and ETLs copies of data over into your data warehouse or database's tables. You can reach out to your CSM for more information.

However, a lot of organizations already have ETL tooling or processes in place and so you may want to look at how to use the Compass SQL platform (APIs or JDBC) to query & move data over to a data warehouse. You can find some helpful details on the Data Fabric playlist on the Infor OS YouTube channel @ www.youtube.com/playlist

antonio-caria

EXPORTMI is NEVER a solution for this case.

antonio-caria

Ad-hoc report is not what @GS wants.

Copyright © 2025 Infor. All rights reserved.