Reporting Database Considerations

Question

We have a very large database that we want to write almost real time. We want to take data and move it to another instance of the database on top of which will be a GUI and MI tool which will be able to report on the data.

I've got the following so far - does anybody else have any considerations I should bear in mind when spec'ing this:

• Data Migration / Retention Period / Legal Requirements of Storage
• Tables / Fields that need to be available in reporting database
• Permissioning / User Access
• Key fields that need to be queried
• Disaster Recovery Requirements
• Replication Timing

Answer 1

I think what you're looking for is, "What are all the things I need to keep in mind when designing a data warehouse?"

There's a lot of good resources out there on it:

• https://www.cu.edu/irm/stds/ddg/dzproces.html
• http://www.slideshare.net/markginnebaugh/architecting-a-data-warehouse-a-case-study
• http://msdn.microsoft.com/en-us/library/aa902672%28SQL.80%29.aspx

Answer 2

From your question, the key is how you intend to replecate the data, and where you will store that. How you do that would depend on how you update your database engine, hardware and performance requirments.

You could use your database engine to create backups (differencial or full) that you restore into another database instance, possibly on different hardware (periodical update).

You could create trigger functions (real time update) or a regular batch process (periodical update) that port the data across to your reporting instance - either a different set of tables, or in a difference table space or a different database (engine dependant).

You state that the data will be logically isolated from the active data, so you wont get any logical contention (table locks) between the main and reporting parts of the application but you could have issues with disk and CPU between the main and reporting parts of the application that would affect the main applications performance if the reporting data is on the same hardware as the main application database - but that would depend on load and hardware spec.