Friday, June 24, 2011 10:23 AM
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
Thanks in Advance
Monday, June 27, 2011 12:08 PM
this is the normal way to create D W .
you want how to populate dimension and fact tables ???
Tuesday, June 28, 2011 5:44 AM
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"
Friday, September 30, 2011 9:36 AMWhen we load fact tables we need to ensure RI (Referential Integrity) so that invalid master data doesn't go in to fact tables.
Friday, September 30, 2011 8:20 PMIf 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.
- Edited by Debu Basu Friday, September 30, 2011 8:20 PM
Tuesday, October 04, 2011 2:36 PM
Microsoft SSIS has some validations in the ETL process. Did you try it?
Tuesday, October 04, 2011 11:20 PM
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.
Wednesday, October 05, 2011 7:49 AM
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 ?
Wednesday, October 05, 2011 9:59 AM
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: