title: Building aggregation/summary reporting database against Oracle, Sql Server and Mongodb
body: <p>This is a design, since i've not done anything similar in the past, and is a good challange. I have a server which supports Oracle, Sql Server and Mongodb. You can select which one to use at startup. Essentially each server stores xml packets, which are split down into their component elements.  </p>
  
  <p>I need to build a reporting database which provides aggration and summary data for reports for the dashboard, but the problem (opportunity) is Mongodb. I could easily use sql server reporting services to build the reportdb, same with Oracle, or I could something like Crystal which works against both, or even create a db, and set a bundle of triggers on each table, with some pl/sql logic with Oracle, or T-Sql with Sql to create the reporting db on the fly. And that would take care of report. But their is mongodb. Little or no reporting infrastructure, certainly not outside BIRT, or jaspersoft (Java). I'm using C#.</p>
  
  <p>I was thinking of having c# server component, which intercepts incoming xml packets, and extracts the appropriate element field data, and writes it into a reporting db, perhaps something like sqlite (which may be too small). If it was running on sql server, or Oracle then I would use that db instance to support the reporting db. </p>
  
  <p>On any database, i'm really only supporting upto 6 months data. The data will be classified as 24 hours, 1 week, 1 month, 3 months, 6 months, with a progressive archive onto on compression and backup db. </p>
  
  <p>But this is where it gets hazy. For instance, using sqlite as the reporting db, and mongodb as the xml databse. Taking an example. If a user wants to drill down, would I have to provide some kind of dynamic update that would pull the additional reporting info from Mongodb, or could all be done at the server component stage, when it's been writen in to sqlite. </p>
  
  <p>Or is all f bol.cks</p>
  
  <p>Any ideas or thoughts greatly appreciated.
  Bob.</p>