DW Design Help required

Question

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

Answer 1

hi

this is the normal way to create  D W  .

and then

you want how to populate dimension and fact tables  ???

 

 

Answer 2

Hi,

are you storing all your master tables and transaction tables (sales, marketting etc) in the same database? also wants to know what you are trying to achieve by "data load from fact tables to master tables"

Thanks,

Ramchandra

 

---

Thanks, Ramchandra

Answer 3

When we load fact tables we need to ensure RI (Referential Integrity) so that invalid master data doesn't go in to fact tables.

Answer 4

If you are using an ETL like SSIS or so on to load the data, you can add an extra validation step. In this you can look for all data issues on your requirement you can either send them to an error table or fix them.

Answer 5

Hi, Sarran

Microsoft SSIS has some validations in the ETL process. Did you try it?

Answer 6

Debu is correct. With your design, your data integrity must be enforced within the SSIS packages that load the fact tables. For example, if your source has a customer code used to identify the customer in the source system, you most likely have that customer code in your dim_customer  dimension. Your SSIS package needs to perform a Lookup of the customer_code in the dim_customer dimension and store the resulting customer_id (dim_customer primary key) into your fact table. If your lookup does not find a match, then you do not load that source record, or you default it to an "Unknown" customer if that is how you have designed the solution.

 

Answer 7

this is a good and classical approach.

to enforce referential integrity, when data are in several database, you can use trigger.

but the question is : are you sure you want to enforce referential integrity when you are in a datamart, datawarehouse ?

 

 

Olivier

Answer 8

Hi sarran

About your first question you can consider that there are two different views in Data Warehouse designing. Each of them has advantages and disadvantages. Go to the following link for more information:

http://opensourceanalytics.com/2006/03/14/data-mart-vs-data-warehouse-the-great-debate/