Staging layer placement

[sahku]

We have been working on designing ETL process for our financial Data Mart.We have to make a decision on where to place the staging layer , that is , whether we should place it on the same data base as DM or on a different database.Please let us know the

1.Criteria we need to consider
2.Pros and cons of the different approaches.

It would be great if we can get hold of some material on the same.

Thanks

 

[KingCrab]

You mean the same box, right?
It all depends. If during the ETL process it eats up your memory and processor time then I would put it on another box. If your ETL runs during the middle of the night and doesn't bother anyone then you could probably get away with placing it there.

2 boxes add redundancy. If 1 fails the other will still be alive(hopefully).
They also allow each box to perform one function so the boxes may be able to be smaller.

There is no right or wrong here and is always something up for debate.

 

[sahku]

well , as of now we have just one Oracle box.I was wondering whether using a different database instance for staging and datamart would offer some advantage ?
Or should we just go ahead and create two schemas , one for staging and one for data mart.
Placing them in the same schema is not a very good idea or is it?

 

[johnherman]

I would use a separate instance for backup/restore/etc reasons.

-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright

 

[sahku]

Thanks a lot guys.
That was a great help.
Please let me know whether i can get any material anywhere to read on this topic.

 

[johnherman]

If you are on Oracle 10g, there are two books which are useful: Oracle Database 10g Data Warehousing and Oracle Data Warehouse Tuning for 10g.

If you are on 9i, I recommend, Oracle DBA Guide to Data Warehousing and Star Schemas, which is also useful for 10g.


-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright