Solved

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

Posted on 2011-03-06
3
886 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
Read about achieving the basic levels of HRIS security in the workplace.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to recover a database from a user managed backup

759 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now