Solved

what does staging area of data warehouse have there and what purpose of it?

Posted on 2011-03-06
3
914 Views
Last Modified: 2012-05-11
1. if we use staging area between ODS and EDW when perform ETL , what should be in staging area:

   i)  a copy of source tables  ,so the transformation is performing between staging area and EDW
   ii) a copy of target tables  , so the transformation is performing between ODS and staging area
   iii) a copy of source tables and a copy of target tables ,the transformation is performing at stage area

2.can the staging area be in the same source (ODS) database or in the EDW datawarehouse or it is in different database?
0
Comment
Question by:SayYou_SayMe
3 Comments
 
LVL 14

Accepted Solution

by:
ajexpert earned 250 total points
ID: 35046422
Well, it depends on requirement, but generally staging area table structure that is combination of source tables.

Once you have data collected in staging area from source tables, you then write a logic to load the data in target tables.

HTH
0
 

Author Comment

by:SayYou_SayMe
ID: 35046471
thanks ajexpert

the staging area has source tables , ETL quickly load the source data into staging area, and then load the data into target. so the transformation are performing between staging area and EDW

i) Is it possible that the staging area has target tables (dimension and fact table), transformation is performing at staging area, and then simple pass throught the staging target into EDW target?

ii) If perform incremental load, only load  the change source data,so the staging area :
   1. trucate load, there is only change source data there
   2. collect all the source data, so the staging area has the same amount data as source (ODS)
   which approach is better? and what is the pos/cons?

 
 
0
 
LVL 4

Assisted Solution

by:pinkuray
pinkuray earned 250 total points
ID: 35077972
i) Is it possible that the staging area has target tables (dimension and fact table), transformation is performing at staging area, and then simple pass throught the staging target into EDW target?

Yes it can ...

The staging db can have target tables but only if required a validation which can be done without disturbing the target table, for being to be in a safe said we can have this functionality.

So after all the validation in staging DB the loading can be done in target table in target DB.
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
As technology users and professionals, we’re always learning. Our universal interest in advancing our knowledge of the trade is unmatched by most industries. It’s a curiosity that makes sense, given the climate of change. Within that, there lies a…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question