Fact in Slow changing Dim

Customer always buy these 3 items with each Purchase, on 1/1/2010 he does a purchase
Custmer Dim
C_DWID Customer_ID Name From_Date To_Date Current
1 1 Jack 1/1/2010 9999 Y
Item Dimension
Item_DWID ItemID Desc From_Date To_Date Current
-1
1 1 Item1 1/20/2010 Yes
2 2 Item2 1/20/2010 Yes
3 3 Item3 1/20/2010 Yes

Fact
TransactionDate C_DWID Item_DWID CC_closed outDate Open Count Close Count
1/1/2010 1 1 1 0
1/1/2010 1 2 1 0
1/1/2010 1 3 1 0

On 4/2/2010 Customer 1 name changes to Mary
Customer Dim
C_DWID CustomerID Name From Date To Date Row Current
-1
1 1 Jack 1/1/2010 4/1/2010 No
2 1 Mary 4/2/2010 9999 Yes

What is the fact table would look like?

Thanks




Fact

The fact table is the same. It does not change.

Thx, I thought the transactional facts table has to register every transaction that accrued that day.

Facts record transactions for the transactions they were designed to capture. In this case purchases. A dimensional update is not a purchase transaction.

When you use a type 2 dimension is a fact table, the key references the dimension at the time of the transaction. This doesn't change.

Thx, but the SID in the Fact will point to the old Sid not the new changed SID, if we use the same fact. If we register it in the new fact then it will point to the current customer SID.
The fact is for tracing the customer not tracking the purchases.

Well, your fact had the item as a dimension, so I assume it had something to do with items.

I don't know what the fact table is supposed to represent. If you are trying to track changes to customer, beyond what a type 2 dimension already does, you would typically have something like consumer, date, timestamp, change code or column name, old value and new value.

Hi ngalemmo,

I am new to this data warehousing and looking for more insight into the subject being discussed.

In this scenario, when we look at the fact, we would look at the data that it has been bought by Jack and not Mary, though the data has changed in the dimension to reflect the current name.

My query is that if the name changes don't the data in the fact point to the current name.
Could you pls explain in detail.

In a type 2 dimension there is a row for every version of the entity (such as customer). It has a unique primary key. When rows are added to a fact table, it references the current row at the time the fact is loaded. This reference never changes.

In addition to the primary key and its attributes, a type 2 also contains a static alternate key that is the same for all versions of that entity. It is either the natural key or a surrogate of the natural key. In addition there is a 'current' flag that is set for the most current version of the row. This flag is maintained every time the a new version is added.

When querying the fact, a join to the dimension will return the version of the entity that was current at the time of the fact. If you want to see the most current version, you perform a self-join on the dimension based on the static alternate key and the current flag.