Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Staging area design

5 posters

Go down

Staging area design Empty Staging area design

Post  kuldeepchitrakar 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 : 38
Location : India

http://www.bidwbooks.com

Back to top Go down

Staging area design Empty Re: Staging area design

Post  Jeff Smith 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

Back to top Go down

Staging area design Empty Staging and 3NF

Post  owvi4 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!
owvi4
owvi4

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

Back to top Go down

Staging area design Empty Re: Staging area design

Post  ngalemmo 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.
ngalemmo
ngalemmo

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

http://aginity.com

Back to top Go down

Staging area design Empty Re: Staging area design

Post  Jeff Smith 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

Back to top Go down

Staging area design Empty Re: Staging area design

Post  John Simon 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

http://jsimonbi.wordpress.com

Back to top Go down

Staging area design Empty Re: Staging area design

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top


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