Is there anyone who has been able to connect Power BI to M3? If so what is the process you used to do this?
Assuming you are in M3 Cloud, possible approach is to utilize a replication database (hosted) or your own Datalake and work your way around those 2 options.
we use the infor etl tool and Azure Sql Server
We use the Infor ETL tool to replicate to a local SQL server and have connected Power BI to it.
We have been using Compass API's called from Python scripts being run from a SQL Server server agent job to extract M3 data from the data lake into an ODS database running on SQL Server. Then we use SSIS to extract the M3 ODS data to an analytics (warehouse) database to integrate our other source data from other systems with the M3 data. That becomes our reporting and analytics truth for all other external systems analytics / reporting including Power BI.
However, using the Compass API's put a huge demand on the allocated INFOR fabric resources. We're allocated 20 minutes of CPU / day and we are well over 200 minutes / day. So we are implementing M3 Streaming Pipelines in the INFOR fabric to have a real-time pipeline of M3 updates to our ODS on a PostgreSQL server on Azure, bypassing the data lake altogether. We're just getting started on this but the streaming seems to work well in our proof-of-concept.
We have developed a Infor Data Lake Sync tool that replicates the data to a sql server database. If you want more information drop me a message
Is it different than the Infor ETL tool?
Yes it is different. It creates tables automatically in your local database and updates them if new columns are available. Files are send by ION through the enterprise connector and imported by the tool, which does not give a high load on the Data Lake or API's. You can choose to keep history of every record or just update latest.
Hi Roy, I would like to have more information on the sync tool.
