Solved

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

Posted on 2011-03-06
3
949 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Do you have a plan for Continuity?

It's inevitable. People leave organizations creating a gap in your service. That's where Percona comes in.

See how Pepper.com relies on Percona to:
-Manage their database
-Guarantee data safety and protection
-Provide database expertise that is available for any situation

Question has a verified solution.

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

Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
A company’s centralized system that manages user data, security, and distributed resources is often a focus of criminal attention. Active Directory (AD) is no exception. In truth, it’s even more likely to be targeted due to the number of companies …
Via a live example, show how to take different types of Oracle backups using RMAN.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

688 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