Staging area design

View previous topic View next topic Go down

Staging area design

Post  kuldeepchitrakar on Tue Apr 26, 2011 11:39 pm

Hi,

I need to design a staging area (doing it first time). SO I have few questions related to staging area design.

My data source is OLTP model which highly normalized. So as a part of ETL design process its better to have staging area so that we trouble the performance of OLTP to get data in DW.

Now questions is what should be the design of staging are.

1. Should we create same table structure in staging are as like in OLTP model along with few more date columns for extracting and storing the data in staging.

e.g. If I have customer data is in 3 tables in OLTP source, should i create same 3 table in staging area so that we can just extract the data from source and keep it in staging so that we dont need to hit source again and again.

Or is it good to have one table per dimension in staging area and extract data from source, de-normalize it in memory using ETL tool and store in staging are

What's your thought on this would like to know if any other approach with advantage on other approach.




kuldeepchitrakar

Posts : 17
Join date : 2010-04-21
Age : 34
Location : India

View user profile http://www.bidwbooks.com

Back to top Go down

Re: Staging area design

Post  Jeff Smith on Thu Apr 28, 2011 12:25 pm

I think a staging area in 3NF is a good thing. It might have additional items than the source system in case you need hierarchies that don't exist in transacation system.

But, I wouldn't go overboard with the 3NF in the staging area.

Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

Staging and 3NF

Post  owvi4 on Thu Apr 28, 2011 12:56 pm

I assume this is a staging area that will be used to source a data warehouse. For a DW project I was recently on, we built our staging area to look almost exactly like the source system's database. But we dropped the foreign key constraints. This allowed us to load tables in parallel, truncate individual tables and re-load, etc.

Why keep staging like the 3NF source system design? Why not? If you have a dimensional design planned for the DW, then why have the staging area be some OTHER model of the data from source or DW designs? The more different models of the data, the harder it will be to communicate within the team. Also, if stage is different from the source, then it will make communication with the source system owners difficult.

Keep it simple!
avatar
owvi4

Posts : 3
Join date : 2009-04-16
Location : Indianapolis, IN

View user profile

Back to top Go down

Re: Staging area design

Post  ngalemmo on Thu Apr 28, 2011 1:14 pm

Personally, I do the opposite. I tend to design staging tables to better suit the target rather than the source.

Reasons:

1. ETL is usually a two-step process. Stage then load. if the staging does mild transformations to better suit the target, I need only create one set of load processes. If the DW gets similar data from multiple sources. all I need to do is create new source specific staging processes and let the existing load processes handle the new source.

2. Sources change. I don't want to rewrite ETL processes from end-to-end because of a change in the source.

3. Most of the heavy transformation logic occurs on the load side. With the staging tables closer in structure to the target, the load process code tends to be simpler.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Staging area design

Post  Jeff Smith on Fri Apr 29, 2011 2:39 pm

I think it's easier to create dimensions from 3nf. With fact tables, it's whatever gets it done.

Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

Re: Staging area design

Post  John Simon on Sun May 01, 2011 6:55 pm

I agree with Neil on this one.

John Simon

Posts : 186
Join date : 2009-05-29
Location : Melbourne, Australia

View user profile http://jsimonbi.wordpress.com

Back to top Go down

Re: Staging area design

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum