Should we put our Reporting data warehouse on our Transaction database Server.

Question

We are about to add a Reporting Data Warehouse to our system. Currently we are using Oracle 10g for our transaction database. Should we put the Reporting Warehouse on physically different hardware? or can we "go cheap" and share the Transaction Server?

Answer 1

It really depends on the timing and load of the warehouse operations that will affect on your OLTP.

For example, if you have major number crunching that takes a lot of resources, but you do it in the night / at a time when the OLTP is not used much, you should be fine.

If you use proper indexing and put aggregate tables in place so that you do not crunch much at report data retrieval time, then the resource utilization of the OLAP is fairly low.

The biggest thing you should do is denormalize, denormalize, denormalize. Make the reporting structures as flat as possible and then build good index structure to support your queries.

A lot of implementations already share resources between OLAP and OLTP.

Just don't let counting beans get in the way of making beans, and vice versa and you should be OK.

Answer 2

If you have a disaster recovery machine for your OLTP, you might be able to use that for your warehouse.

Another option is to go for two virtual machines on the same hardware.

• You can use the VM management to allocate min/max memory, CPUs etc.

• If one database needs an urgent patch, you don't have to worry about it affecting the other database.

• It also becomes simpler to move one off to its own hardware later, if it proves necessary.

If you use a different server, what is your storage ? If they are coming off the same shared disks, they can still impact each other, even with separate CPU/memory.