Architecture Guidance Needed?

Answer 1

Why don't you replace the various Excel templates with a proper reporting solution? Using a decent reporting product has the following benefits:

• everyone can have access to the same report
• you can use roles to prevent people seeing certain reports
• the users can change the parameters of the reports every time they run it, or they can use the defaults you set up for them
• the reporting can be highly automated, it can run automatically on certain schedules (like 9am every Monday morning, or every Sunday evening after end of week processing)
• reports can be delivered in a variety of formats
• reporting tools can use stored procs in the database, those stored procs can encapsulate business logic

Personally i would advocate the use of Sql Server Reporting Services (2008 version, try and avoid 2005), although there are other products out there as well. SSRS can talk to an Oracle database, it can be obtained and used for free.

You also made vague references to "processes". The way i read this, you have some code running somewhere that pulls data out of the database, massages it somewhat, and places it in to some staging area - maybe the Excel spreadsheet is the staging area, and further reports are derived from that staged data. If my interpretation is correct, then the above mentioned reporting product (and possibly many others) remove the need to do this.

Answer 2

We built a similar system many years ago. The system consisted of a main process and a series of Excel report generators that implemented a plugin architecture. Each report had its own report generator and could be generated on its own schedule, quite similar to how quartz and quartz.net handle scheduling (but this was many years before they were created). The plugin architecture was the key to the system, as new report generators could be created and dropped in dynamically.

The main process was in charge of launching/monitoring generators, listening for events that were triggered from generators, and distribution of all the reports. Distibution mostly consisted of FTP to a file server where reports could be viewed and saved from a web site. But we did also have some reports emailed to a distribution list.

The main database that stored all the report data also housed all the system meta data that informed the main process and the generators of the details of their work to be performed.

In our system all of the reports ran sequentially, mostly due to Excel limitations at the time. Assuming Excel is now capable of behaving itself, there's no reason that a new system couldn't be designed to run in paralel.