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
  • I'm not sure I actually understand your first sentence. What applies to a regular db and doesn't apply to a reporting db?
    – Marian
    Nov 11 '11 at 9:53
  • What specifically is your question? It's not clear. Nov 11 '11 at 16:12
  • Sorry maybe I wasn't very clear. We have a main database which holds all of our production data for about 1000 users globally . We're looking to create a separate reporting database with a Web GUI so that users can run queries / extract reports without impacting performance in production and we're also looking at putting an MI tool on top to perform trending analysis. Basically what are the key things I need to think about when writing the requirements other than the areas that I've listed above? Thanks - it's my first q here so hopefully I'll be a bit mroe concise next time.
    – Rob
    Nov 14 '11 at 3:23
  • 4
    It would be wise to verify just what your realtime requirements are. Building a near-realtime reporting system is substantially more fiddly than building a data warehouse with a batched load. Jan 2 '12 at 0:32
  • 1
    If you really need to "write almost real time", have you considered simply moving your OLTP to better hardware, so that it can handle the additional workload of reporting? That could be a simpler and cheaper alternative to what you are thinking of.
    – A-K
    Apr 1 '12 at 1:46

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:

  • Brent, can you also recommend resources on choosing whether someone needs a datawarehouse or not?
    – A-K
    Apr 1 '12 at 22:19
  • @AlexKuznetsov Here's my thought on that: if you want to run reports across multiple source systems on different platforms (like SQL Server, Oracle, DB2, mainframes) then you need a data warehouse.
    – Brent Ozar
    Apr 1 '12 at 23:52

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.