DatawareHouse design - Suggestion req

Hi gurus,
Am coming up with DWH design for first time... I thot of validating my idea with people..

Following is the way we are planning to implement...

1. Client will load data from his oracle source table in to SQL tables (for example if there is a table "table1" exist in oracle, similar table do exist in staging in SQL Server "table 1"). On every incremental load he will truncate the data and load incremental data.
2. We will pull the data from Stage area and store it in similar table in our stage area (SQL server) -- to maintain complete data history, since he will truncate after every load.
3. From the above tables we pull it in to ADM similar structure in another SQL Server DB ( this will have Masters and Transaction in same DB with RI imposed). Once we load in to these tables, from there we pull in to actual data marts.
4. We have several DMs created... one of them will have all master (Dimension tables), the others DMs will have only Facts and Masters will be referenced by means of views in the same.( this is to avoid creating same Dimension tables in multiple DMs). The DMs will have only indexes to speed up data pull, RI cannot be created as we can't impose a relation ship on Views. DMs are business specific.. for example Marketing, Sales, Inventory (most of the masters are common across DMs)

Some points to note..
a. Tables in the point # 3, fact table will be truncated after incremantal load but Dimension data complete will be maintained, as future fact table will be enofrced RI against these and loaded in to actual ADM.


Some questions which i need help..

1. Is this design is valid.
2.we have data in 3 places ( Our stage area, ADM like structure and actual ADM), is that fine..
3. Any suggestions...

Looking forward.
Thanks

It seems the data is being handled a bit too much. Usually its just source to staging to dw. As far as keeping the old stuff, a more common technique is to send it out to a file, zip it and put it in an archive.

RI cannot be created as we can't impose a relation ship on Views.

I'm am not a fan of declaring and enforcing FK constraints, they are unnecessary. But, if you must, you declare constrains on the underlying tables. Constraints have no purpose in a view.

saravanan.r wrote:4. We have several DMs created... one of them will have all master (Dimension tables), the others DMs will have only Facts and Masters will be referenced by means of views in the same.( this is to avoid creating same Dimension tables in multiple DMs).

Be aware that if your views are in a different SQL Server database than the underlying tables, your users will need read permissions on both the view (in Database "A") and the table (in Database "B"). If the views are in the same database as the tables, you only need to grant your users permissions to the views.

You might find it beneficial to duplicate the dimension tables (and views) in your various data marts if your dimensions aren't too big.

(If your end-user access to the DM is through a single account rather than the individual users' identities, the permissions issue may be a moot point.)

Neil is right, way too much data handling.

Why the need for these different datamarts to be on separate databases within SQL Server? Everything can go in the one database, then you can use views for access if you need to, although I'm unsure what the goal is.

There is simply no need to separate these datamarts.