Fact in Slow changing Dim
3 posters
Page 1 of 1
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
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
dshams- Posts : 13
Join date : 2010-12-08
Fact in Slow changing Dim
Thx, I thought the transactional facts table has to register every transaction that accrued that day.
dshams- Posts : 13
Join date : 2010-12-08
Re: Fact in Slow changing Dim
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.
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.
Fact in Slow changing Dim
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.
The fact is for tracing the customer not tracking the purchases.
dshams- Posts : 13
Join date : 2010-12-08
Re: Fact in Slow changing Dim
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.
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.
Re: Fact in Slow changing Dim
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.
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.
saan99- Posts : 1
Join date : 2015-10-07
Re: Fact in Slow changing Dim
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.
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.
![-](https://2img.net/i/empty.gif)
» Slow changing fact data with an effective date
» Changing Fact for Order Modifications
» Fact table's changing measures
» Designing Sales Promotion for Packed Products
» Changing Fact table quetion
» Changing Fact for Order Modifications
» Fact table's changing measures
» Designing Sales Promotion for Packed Products
» Changing Fact table quetion
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum