locked
Designing a Report Database Separate From an Application Database? Pros, Cons. RRS feed

  • Question

  • There is currently a production database which has a table called OnlineReports.  Each client has there own production database, so if we have 200 clients, they all need to have OnlineReports table, and everytime there is an change, all 200 need to be changed, so my thought was that it looks like it is a better Idea to design a separate reporting database for all clients instead of each client having their own separate table(s).  What do you think of this?

     

    -Saied

    Saturday, March 22, 2008 8:35 PM

Answers

  • Yes, because creating 200 tables does not sound like an option for the saiedh...

     

    Authorization for the data across each client can be managed better by creating queries, so one customer is not snooping into another customer's information by manipulating where clause (dont know if that is possible in saiedh's app)

     

    In current application, assuming the report is generated from a table called "Report", then the query has to change to either a filter criteria [not recommended] or to the view depending on how the change can be handled by the current application...

     

    In our enterprise, we replicate these information to business objects universe and the report is generated out of there, hence my first reponse was to look for pureplay OLAP solution...  Current response could be a stand-in dirty approach, in absence of OLAP.

     

    I would like to know if there are other options as well...

    Tuesday, March 25, 2008 3:52 PM

All replies

  • It is not a good practice to have transaction application (OLTP) and reporting application (OLAP) reside in the same database instance...

     

    Usually you would replicate the information to a separate OLAP database instance and build your reporting application off of your OLAP instance...

     

    Sunday, March 23, 2008 3:07 PM
  • I've been burnt on this before, what do you mean by report? What problem are you trying to solve? If you're worried that your customers can't do complex reporting for fear of tying up the production server then sure look at the "offline" analysis cube route. However, if you're trying to create one database that all your customers share in order to keep maintance costs down that's something very different.

    Sunday, March 23, 2008 11:56 PM
  • Ok, Assume you have 200 clients and each client has a database.  In each database there is a table called reports which

    holds reports for the client.  So if you make an update to a report, you need to update 200 tables, so wouldn't it make sense to create a separate db to hold report tables, so you would just need to make 1 update for example.

     

    Thanks,

    Saied

    Tuesday, March 25, 2008 2:22 PM
  • Can you create one database that holds all the reports for 200 clients?  Basically you will create a database called Reports and probably create one table partitioned for each client or one table with a column identifier to identify each client information and create view for each client...  Do you think that would help?

     

     

    Tuesday, March 25, 2008 3:40 PM
  • So you're suggesting that the 200 clients all share the same report table on the same database? Also before you answer that, how in your application is changing a report different from say changing a form? I'm not suggesting you're wrong, just trying to get a clearer picture.

     

    Tuesday, March 25, 2008 3:41 PM
  • Yes, because creating 200 tables does not sound like an option for the saiedh...

     

    Authorization for the data across each client can be managed better by creating queries, so one customer is not snooping into another customer's information by manipulating where clause (dont know if that is possible in saiedh's app)

     

    In current application, assuming the report is generated from a table called "Report", then the query has to change to either a filter criteria [not recommended] or to the view depending on how the change can be handled by the current application...

     

    In our enterprise, we replicate these information to business objects universe and the report is generated out of there, hence my first reponse was to look for pureplay OLAP solution...  Current response could be a stand-in dirty approach, in absence of OLAP.

     

    I would like to know if there are other options as well...

    Tuesday, March 25, 2008 3:52 PM
  • GajaKannan, we posted at the same time so that wasn't directed at your post. However, I would guess that the application hasn't been architected/designed for SaaS style running and all the interesting security topics that raises (such as you mention snooping). Hence my question about why are reports any different to any other part of the system. IIF they're all configurable rather than custom to the clients then I'd would consider a SaaS approach. However, if it's just reports and if the reports are custom to the clients then I don't really see the benefit of such an approach. Simply a better deployment strategy would be all that is needed.

     

     

    Tuesday, March 25, 2008 3:59 PM
  • pkr2000...lol...  I thought you were responding to my post, hence I posted more details...  Not a problem, made me put my thinking hat little longer...  Good exercise...

     

    Tuesday, March 25, 2008 4:43 PM