SAGE X3 ERP Reporting by Azure Power BI
The Client
The client imports, trades and distributes high quality ingredients to industrial manufacturers, bakery distributors, the foodservice sector and grocery chains across Canada and the Western United States.
Operating since 2010, the company has a strong network of global suppliers. Like most importers and distributors, they track a large number of inventory items, ingredients and transactions, so a sophisticated ERP system is paramount to their bottom line.
The Business Need
The client needed to modernize and automate their ERP reporting and the delivery of new reports. Substantial manual work was being spent on creating new data reports, which was a process they repeated on a regular, on-going basis. The client wanted to automate this process to save time and money. This would allow them to incur a one-time setup expense and then let the system run the reports without manual interaction.
The Optimus Solution
Optimus started by gaining an understanding of the business logic behind the reports and the client’s SAGE database architecture that was used to create the reports.
We installed a data gateway on the SAGE database and created reports using Power BI dashboards. We published them with Power BI services and ultimately connected the reports with the SAGE database.
We also modeled the data in Power BI. The data modeling consisted of tables relationships, new metrics definitions developed by DAX language, and data hierarchies definitions. This allowed us to use the same data model across different reports as well as improve overall data quality.
We developed the reports based on requirement templates that the client had shared. If they required any specific changes or new requests were made, we were able to provide them with new templates to deal with their evolving needs.
The Challenges
A major challenge from the project was database mapping. SAGE database tables and columns have their own naming conventions which are not intuitively easy to understand. The Optimus team engaged in reverse engineering the SAGE database to determine the usage of various columns and relationships within the tables and columns.
Optimus also advised the client in migrating it’s reporting from their previous on-premise solution to Power BI. As part of this, the Optimus team had to collaborate with the client’s IT vendor on a regular basis.
Over the course of the project, the client’s team did not have someone who could play the role of a business analyst on a regular basis. The solution was for the Optimus team to work directly with the business users to validate its understanding of the requirement and to get sign off.
Results
Optimus measured the average time taken to develop and deploy a report. We have managed to keep it under three person-days per report, which is a one-time investment. Once the report is created, it can automatically be replicated and populated when needed.
Since the reports were previously generated manually, data would become old by the time the report was complete. Now, the solution and implementation reports are automated and offer near real-time data.
The client now has a complete MIS process and can see the difference in expenditures of the previous manual report generation effort compared with the one time spend of working with Optimus.
Conclusion
The client is now in a much better position to make real, data-driven decisions. They have an increasingly bigger set of reports that is readily available to them on the cloud with data being refreshed several times in a day.
