facts and dimension in staging area

View previous topic View next topic Go down

facts and dimension in staging area

Post  huny on Wed Apr 03, 2013 1:44 pm

is it right to make facts and dimensions in staging area and then load facts and dimension in data ware house.........
like there is source area that contains data from source then u build dimensions and facts in staging area and do all transformations and then u load these dimension and facts into data ware house dimensions and facts........

huny

Posts : 5
Join date : 2013-03-24

View user profile

Back to top Go down

Re: facts and dimension in staging area

Post  Mike Honey on Wed Apr 03, 2013 5:51 pm

Hi huny,

I think the answer depends on the scale. I've implemented a number of successful small/medium sized DW/BI solutions without Staging areas. With modern ETL tools you can transform data in memory and deliver it directly to the data warehouse.

This design speeds the design / build / test /maintain process (less moving parts) and also speeds the runtime execution (less IO).

An example of small/medium would be around 300GB, with the largest Fact Table at 10-20 million rows.

When this approach runs out of puff, I add Staging tables just for that particular object.

Good luck!
Mike
avatar
Mike Honey

Posts : 185
Join date : 2010-08-04
Location : Melbourne, Australia

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

Back to top Go down

Re: facts and dimension in staging area

Post  huny on Wed Apr 03, 2013 11:08 pm

i have build my staging area with facts and dimensions.right now, the thing bothering me is that my staging will be same as my DWH. it is like i have two same areas in ware house....
some questions in my mind came like,

why i m having same dimensions and facts in both areas ?am i creating overhead of maintaining 2 same areas?

I AM CONFUSED about having 2 similar areas in my ware house

huny

Posts : 5
Join date : 2013-03-24

View user profile

Back to top Go down

Re: facts and dimension in staging area

Post  Mike Honey on Thu Apr 04, 2013 12:47 am

If I use a Staging area, the list of tables and their schema would never be the same as the DW area. I would only use Staging tables where data extracts from source systems are slow or fragile, or data volumes are huge. Therefore my staging schema design suits those purposes. A separate ETL component takes care of transforming the Staging data into the DW, so there is no need for the Staging and DW schema to be the same or even similar.

avatar
Mike Honey

Posts : 185
Join date : 2010-08-04
Location : Melbourne, Australia

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

Back to top Go down

Re: facts and dimension in staging area

Post  huny on Thu Apr 04, 2013 5:03 am

thank you for your time ... its really helpful

huny

Posts : 5
Join date : 2013-03-24

View user profile

Back to top Go down

Re: facts and dimension in staging area

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