Help in DW Design
2 posters
Page 1 of 1
Help in DW Design
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
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
Re: Help in DW Design
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.
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.
Re: Help in DW Design
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
Similar topics
» Reasons to use a 3NF design over a Dimensional Model design for an EDW - Discussion
» FACT Design Question - How to design FACT Table when computation is required on the degenerate dimension?
» Before/After Design
» 1 to 1 Text Data in a Fact Table
» a strange design
» FACT Design Question - How to design FACT Table when computation is required on the degenerate dimension?
» Before/After Design
» 1 to 1 Text Data in a Fact Table
» a strange design
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum
|
|