factless fact question

[topcat]

I have 2 dimensions, a customer dimension and a group dimension. customers can be assigned to 1 or more groups. so i created a factless fact associated to both of the dimensions. Both of the dimensions are slowly changing and have effective dates.

My question is how to handle a type 2 update on one of the dimensions after a factless fact record has been created. so for example:
customer A with key 100 and group B with key 200. A joins group B so a factless fact record gets created with cust key=100 and group key=200. Now the customer undergoes a change and now the current customer record is key 101. should i generate a new factless fact record with cust key=101 and group key=200? or do you just leave it as is and any analysis has to scan all the facts?

if i wanted to solve the business query of "provide a list of customer addresses for group x", i would want to access the current customer record to get the latest address information. if the address is taken from the customer record connected via the factless fact, then the address might not be current.

[BoxesAndLines]

You would not insert another row in the transaction fact table. The easiest solution is to create a snapshot fact table. This way you create a new set of "facts" for each day, week, month.

If you want to keep to a transaction fact you can get to the current dimension row by doing a self join on the dimension, or adding a new foreign key from the dimension that stays the same for all versions of the dimension instance (i.e. the dimension natural key), or simply copying the dimension natural key to the fact table.

[ngalemmo]

Actually, the best solution is to do nothing. It becomes a matter of how you query.

A fairly common misconception with type 2 is that when a type 2 dimension is updated the foreign keys referencing that member are somehow 'obsolete' and need to be made 'current'. That is not the case.

The relationship is still valid. The same customer still references the same group. What type 2 does is provide a history of attributes relating to those entities, the entities themselves have not changed.

What the problem at hand really is is you have a type 2 FK and you want to query current attributes. There are a variety of methods to do this. The basic method with a standard type 2 implementation is to perform a dimension self join on the natural key to locate the current row.

[topcat]

does anyone create a 2nd foreign key to the dimension which is the "current key". so what i am thinking is that when the type 2 dimension is created, there are 2 surrogate keys, one that changes for each new record and one where the value stays the same. then facts would join to both the "current" and "history" keys.

for example, a customer record for Joe is created with current key = 100 and history key = 200. then when Joe is updated, a new record is created: current key = 100 and history key = 201. in addition, the customer dimension would also include a current flag (which is common practice already). An order connected to a customer would have 2 FKs, a current key FK and a history key FK. if someone wants to see what the customer looked like when the order was placed, they would use the history key. if they want to see what the customer looks like now, they would use the current key + current flag = Y.

thoughts?

[BoxesAndLines]

Yes. That is what I meant when I said "or adding a new foreign key from the dimension that stays the same for all versions of the dimension instance". You can also use the dimension natural key and get the same result. I prefer natural key surrogate key since it's always a single numeric column.

[lovely coser]

The easiest solution is to create a snapshot fact table.

[Sponsored content]