0

We have two database out of While Database A is transactional and database B is consolidated view of Database A and other System Compiled Data(Existing in Database B).

Database B use daily ETL to Load data from A(Flush and Load) and other other System Compiled Data(Existing in Database B).

Can we club these two db in to one i.e. A Only?

To rephrase my question, Can we should the reports directly from A rather than doing ETL every early morning in consolidated database B.

If yes What factor should i focus while merging these two w.r.t performance.

Harsimranjeet Singh
  • 514
  • 2
  • 6
  • 19
  • 1
    What is the reason to host both datasets in the same instance? Licence costs? server costs? I am asking because in general if you can keep the two separated you should get better performance and scalability on the Transactional side. Actually you probably get better performance on both sides, because they have different usage profiles. – p.marino Aug 24 '16 at 12:14
  • Actually both A & B are on same instance. As Reporting database is not maintaining history ,so to get previous year snap shot we need traverse Audit trails in transactional database i.e. Database A. So Rather than traversing history in seperate db, merging/or getting report from transactional database was what came to my mind. – Harsimranjeet Singh Aug 24 '16 at 12:41
  • Wouldn't make more sense to actually host the reporting on a different instance and keep previous years there? you are weighing down your transactional db both with historical data _and_ reporting activities without getting any benefit from having a dedicated reporting DB – p.marino Aug 24 '16 at 12:57
  • 1
    it just sounds as though the data mart is setup or designed poorly. Some of the purpose of DW include expedited reporting, reduced interference with OLTP, cleaner data, and etc. IF you are getting a better performance with the current DW, then why merge? The performance will most likely worsen. The better approach is to see if the DW design is good or not. Even if they are on the same instance, a properly setup DW will be a performance boost. – Kentaro Aug 25 '16 at 05:30
  • Yes @Kentaro data mart was constructed to get expedited reporting as that moment historical data was not a fator for the report, getting jist of data was our primary goal. – Harsimranjeet Singh Aug 26 '16 at 06:52
  • 1
    my suggesion is to do a thorough analysis on performance and the cost/time required for maintenance and management all databases, in addition to what will make sense for the business the most. At the moment, it seems you don't have any of that. so noone can help you. – Kentaro Aug 26 '16 at 13:27

0 Answers0