asked
Fact and Dimension Key Issues
In my data warehouse I have encountered an issue that I am not sure how best to resolve. First, I have fact table that has a surrogate key to the related dimension table (the primary key). The issue is that the surrogate key in the fact table now points to an expired record. Normally, I would join up to the natural key, but in my situation the natural key/business key is not unique. I have change-data-capture enabled on the fact table. The only solution that I've been able to come up with is to truncate and reload the fact table on a regular basis.
I'm not sure I follow you here. You have a fact table which has a relation to a dimension. One dimension record has expired, is not valid anymore. And you want to change it so the fact table points to another dimension record. But was the dimension record valid for the time when the values in the fact table were created? Please be a little more descriptive, and show an example of what you mean. Is the dimension a changing dimension, lika a customer address dimension could be. Or like a product dimension could be if it's hierarchical with categories.
The fact record has a FK relationship to the dimension; which in this case is a customer invoice. The dimension is being processed as a type 2 slowly changing dimension. In this particular instance, some aspect of the dimension changed and it resulted in the expiration of the original row and the creation of a new row. The fact record's foreign key points to the now expired dimension. From what I understand, I could simply do a one-to-many join based on the business key as opposed to the PK. In this case, however, the business key is composed of four different columns. IMHO, the source sytem does a lousy job of identifying primary keys. Using your example, the dimension is a changing dimension and the ETL processes it as such. I hope this helps.
I still don't really get it. If it's a type 2 dimension, and the fact points to a non-current dimension row, that's not a problem. The fact was collected when the now non-current row was in fact current. With a Type 2 dimension, the whole idea is to be able to aggregate values from the fact table and get them on the correct dimension for the given time. So if the dimension was correct for the fact at the time the fact was recorded, it's still valid in that timeline.
Perhaps I am not being sufficiently clear. The type 2 dimension underwent some kind of change and thus a new row was written. The criteria I'm using for the customer table includes RowEndDate = NULL in order to get the most current record for that customer. The fact record that points to the expired dimension record has a FK reference to the expired record. Now when I go to match up customer invoices and payments, using the criteria I referenced above, the payment doesn't appear because the FK in the fact table points to the expired record. I still need to capture all customer payments.
17 People are following this question.
