Q1: Is my dimension and measures designed properly for scd 2 changes in the dimension?
Q2: If using a parent-child hierarchy is an acceptable approach, why would my query perform so poorly against my customer dimension?
I have developed a customer dimension with scd 2. Within in the dimension I have two surrogate keys: corporate customer key and customer key and the source system ID: customer ID. I use the corporate customer key to group or relate fuzzy
matched customer records (based on other customer attributes) and to group or relate the scd 2 changes.
Now my fact table has the customer corp key, customer id, and customer key.
If you notice, the customer dimension has undergone a change. The new customer key is not in the fact table, but I want to keep the historical changes of the customer dimension, but I can no longer link the fact to the customer dimension
unless I use the customer corp key and customer key combination. I thought I could use a parent-child dimension on the customer corp key and customer key attributes
View Complete Post