Help in DW Design

View previous topic View next topic Go down

Help in DW Design

Post  saravanan.r on Fri Jun 24, 2011 6:19 am

Gurus,
We are building a DW for one of our customer. They have some masters that are been used across various DMs. We have individual DB created for various DMs like Sales, Marketing etc that has corresponding fact tables..... WE have one more DM called DM Master that has EMployee, Geogrpahy, Product tables...
we reference these table in other DMs by creating views in the individual DMs. I have couple of questions
1. Is this approach fine
2. How can in enforce referential integrity while data load from fact tables to master tables

Pls advice.
Thanks in Advance

saravanan.r

Posts : 4
Join date : 2011-04-27

View user profile

Back to top Go down

Re: Help in DW Design

Post  ngalemmo on Fri Jun 24, 2011 5:17 pm

By 'idividual DB' I assume you mean multiple schema residing on the same physical database server. There is no problem with this. If they are physically separate systems, then you will experience performance issues trying to do joins.

As far as #2 goes, you should be using surrogate primary keys for dimensions and have a process that retrives those keys based on a natural key. If such is the case, you should not have any RI issues and need not implement FK contraints.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Help in DW Design

Post  saravanan.r on Sat Jun 25, 2011 2:27 am

Thanks for the reply... They are on the same physical server..We are enforcing RI in the staging area when we pull it from source and populate corresponding surrogate keys.. We have only indexes created in DM for performance and not RIs... So thot of confirming the approach

saravanan.r

Posts : 4
Join date : 2011-04-27

View user profile

Back to top Go down

Re: Help in DW 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