facts and dimension in staging area

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

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

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

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.

thank you for your time ... its really helpful