Data Aggregation via Email and Database

A sample demonstrating data received via email being transformed and aggregated into a central database

Use Case Description

Zenythz Corp. is a manufacturer of heavy machinery, runs several factories in different parts of mother country. As tooling for manufacturing heavy machinery is fairly expensive, Zenythz wishes to improve their tooling maintenance process by reporting maintenance details of each machine in a per-factory basis, daily, to the headquarters, which will maintain an aggregated record for each machine for further analysis.

Proposed Solution

After end of each day's work shift, each factory sends a CSV file to the headquarters via email (as an attachment), containing maintenance details of each machine in the following format:

                
                id,hours,restarts,minor_repairs,major_repairs,extra_cost
                1,10,0,0,0,0
                2,8,1,2,0,100
                

In order to distinguish the maintenance emails from other emails sent to headquarters, the email includes a special header X-Zenythz-Factory containing the ID of the respective factory. An ESB instance running at headquarters is assigned with the task of processing the sent attachments and aggregating daily maintenance details of each machine into a central MySQL database.