Daily Business Reporting Automation

October 3, 2024

Sector/s: Retail, Construction

The client, a company that mixes and delivers cement to customers across South Australia, was pulling together daily reporting on the operational efficiency of each of their locations by combining data from their labor management software, Wageloch, and their sales management software, Dorner. Data was manually compiled in Excel using exports from each system, creating attached costs around timeliness of updating the data, labor overhead in report production and potential for entry error when populating the report. To address these issues, a Power BI reporting solution was constructed with automated connections to both softwares to provide timely data updates, improved data accuracy and removal of overheads in report production.

Problem

A triggering event for this engagement was the amount of labor involved in keeping the report up to date, with timeliness of data becoming a hurdle to quickly actioning insights. Extraction and manual transformation of data from both reporting systems consumed considerable time each week for accounting staff.

Limited Insights: Level of detail provided by system exports limited the opportunity for drill-down into plant performance

Lack of Automation: Minimal automation of processes, contributing to inefficiencies and raising potential for data quality issues.

Manual Distribution: Suboptimal visibility of plant performance due to dependencies on email circulation of the report once updated. Commentary added to the report by its recipients creates risk of multiple versions.

Solution

The solution implemented included:

Power Automate Flow: Created a Power Automate flow to handle collection of .xml data files from Dorner, inserting them into SharePoint for collation and management.

Real-Time Data Access: Developed an access point for centrally managed SharePoint data updated hourly via Power BI. This involved a dataflow capturing changes to commentary and efficiency target information and rolling it into the data model.

Data Integration: Modeled a dataset to map SharePoint elements, Wageloch labor data and Dorner sales/production records to each other. Created timestamp visuals to indicate timeliness of data from each system to give users visibility of information reflected.

Interactive Reporting: Provided comprehensive Power BI reporting on the accumulated data, evolving upon the original report to deliver faster insights, and further operational detail.

Benefits

The implemented solution completely automated the reporting process for the client, seamlessly tying together data from their labour and sales management softwares, as well as enabling easily configurable manual inputs for commentary and operational efficiency targets.

Functional Automation: Implemented a functional automation system that significantly reduced manual effort and improved data quality.

Enhanced Reporting: Developed reporting with automatically updated data from both systems plus manual inputs, enabling the business to track operational efficiencies across their plants and employees more effectively.

Improved Visibility: The new system allowed for faster comparisons between realized and expected labor per cubic meter of concrete to identify outlier periods and locations, with greater data granularity to enable drill-down and investigation.

Conclusion

Hence, the Power BI solution implemented for the client not only addressed their immediate operational challenges but also provided a scalable framework for long-term improvements in efficiency and decision-making. By automating the integration of data from their labor and sales management systems, the solution eliminated manual report generation and significantly reduced the potential for errors. The introduction of real-time data access and interactive reporting enabled more granular insights into plant performance, allowing the business to take swift, informed actions. Ultimately, this solution streamlined operations, improved data visibility, and empowered the client to track and enhance their operational efficiencies with greater precision.

Related Case Studies