Thursday, 8 May 2014

Can mail attachments be converted into meaningful reports ?

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


Srivatsan Aravamudan
Design and Communication

Google plus
LinkedIn
Related Posts Plugin for WordPress, Blogger...

Share Buttons