Decision Support DB and Transactional DB

What are people's feelings on creating a decision support db. Do you feel that the decision support db should be on the same box as the transaction db or a different box? And why?


Side note: I am new to this forum, so is it frowned upon if I posted a question about two databases and asked for people's opinion on each?

Different box -> PERFORMANCE!! No one wants to sit forever waiting for their report to come out.

In general, I agree with pabloj, but of course it all depends... There are always pros and cons to balance here, such as the extra work needed to replicate to the decision-support box, freshness of the data needed, current hardware, processor load, etc... You might find it's easier to just beef up the hardware, and run the decision-support queries in a low-priority mode.


Go ahead. No one here minds an honest discussion of the good and bad aspects of database systems, as long as we keep things friendly and professional .

rycamor is perfectly right, mine is the full blown solution, and it's neither cheap nor (usually) easy.

okay, can i ask for elaboration on extracting data from a transaction tables to a "reporting database" on another server

this will be sql server 7, and there will be a DBA working with me, what should i be looking into? DTS?

also, once the data has arrived in "landing" tables, there will have to be several intermediate tables for summarization, denormalization, joining with other tables, etc.

what's the best way to completely refresh an intermediate table -- drop and recreate, versus truncate? other options? pitfalls?

rudy

1. DTS are a good choice to extract, load and do basic transformation/cleaning
2. Summarization and so on should be performed through stored procedures
3. To refresh completely probably the best is truncating, but I would look at a way to make delta-updates, so keeping old good data, adding new and updating changed, this is expecially useful with huge tables.

An optional advice would be to use the most recent DB you can afford (ie Oracle 9 vs 8.1.7 or SQLServer 2000 vs 7) since the datawarehousing functionalities have been greatly improved.

thanks for the input

we're stuck with sql7

as for deltas, i don't think our tables will be huge enough to bother trying that, so we'll probably just extract everything

thanks again