Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I am in process of designing a Data Warehouse Architecture. While exploring various options to Extract data from Production and putting into Data Warehouse, I came across many articles which mainly suggested following two approaches -

  1. Production DB ----> Data Warehouse (Star Schema) ----> OLAP Cube
  2. Production DB ----> Staging Database ----> Data Warehouse (Star Schema) ----> OLAP Cube

I am still not sure which one is the better approach in terms of Performance and reducing processing load on Production database.

Which approach you find better while designing Data Warehouse ?

share|improve this question

closed as off-topic by Joel Brown, Max Vernon, Mark Storey-Smith, ypercubeᵀᴹ, Paul White Jan 9 '14 at 11:36

If this question can be reworded to fit the rules in the help center, please edit the question.

This question is cross-posted at… – Joel Brown Jan 9 '14 at 3:32

There are a few potential advantages of using an intermediary staging database, which may or may not apply to your situation. There is no perfect, one-size fits all solution. Some of the potential advantages include:

  • If it is appropriate, you can take a snapshot of your production database (you may have a daily backup or hot-site snapshot already) and then do your ETL from the restored backup or snapshot. This could save load on your production database.
  • You may need complicated processing for your ETL which requires many intermediate tables which have no use except for the ETL process. You may not want to clutter your data warehouse with these intermediary tables.
  • Your raw data may not be available all at once and you need somewhere to accumulate it before starting your ETL process to build your data warehouse.
  • Your data warehouse may have production window requirements which can't be met by your ETL and so you need to stage your "output" (i.e. new records for the data warehouse) rather than or in addition to your production database.
  • It may be that your ETL process creates large intermediate tables. Sometimes space management is easier if you start with an empty model database for your ETL staging area and then "throw it away" each day rather than trying to recover the space in a more surgical way, as you might do with a production or reporting database.

There are possible disadvantages too, which may or may not matter to you. Chief among these is having to have another database server. A lot of the advantages could be meaningless if you are using the same server to host the production and/or data warehouse databases.

share|improve this answer

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