Can mail attachments be converted into meaningful reports ?
08 May, 2014
-
Srivatsan Aravamudan Nemili
How would you collate files of different types sent as attachment to the system into central repository and produce meaningful reports?
Let me introduce the MailCube, our state of art web apps that was developed for a client in service industry. The challenge is to gather, save, sort and process the data sent via email as attachment and produce meaningful reports instantaneously to the client who has sent the data.
To achieve this complex task, we have developed the MailCube which collaborated our two expertise – Data warehouse and Business Intelligence. MailCube uses ETL to extract and process data, while Business Intelligence is used to produce reports and dashboards to aid the management in making business decisions at ease. Below Image shows the overall process.
MailCube contains the following stages:
Email Listener
- Runs 24 hours 7 days a week and checks the email system for new attachments.
- When a new email arrives with attachment, the email listener grabs the file and sends it to file process
File Process
- Segregates the files according to type and system
- Extracts Data and prepares it to be uploaded to central repository
- Data sent to SQL Database
SQL Database
- Data is read and stored into the database
- Data is processed for next stage
- Data is sent to Data writer
Data Writer
- Prepares reports based on pre-defined report schema definition
- Sends the reports to the client via Email
Helpdesk
- Monitors the errors and alerts
- Recurring errors are identified and customers are notified for subsequent actions
- MailCube has reduced the processing time for the customer substantially, sending the processed data and reports within few minutes from the time the raw data is sent via email. Data warehouse and Business Intelligence could be best used in unison and this project is live proof of that. Are you facing similar situation? What kind of solutions you have used to overcome this challenge? Please feel free to write your thoughts in the comment section.
