Fact in Slow changing Dim

View previous topic View next topic Go down

Fact in Slow changing Dim

Post  dshams on Fri Sep 04, 2015 1:21 pm

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

dshams

Posts : 13
Join date : 2010-12-08

View user profile

Back to top Go down

Re: Fact in Slow changing Dim

Post  ngalemmo on Fri Sep 04, 2015 1:43 pm

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

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Fact in Slow changing Dim

Post  dshams on Fri Sep 04, 2015 1:49 pm

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

dshams

Posts : 13
Join date : 2010-12-08

View user profile

Back to top Go down

Re: Fact in Slow changing Dim

Post  ngalemmo on Fri Sep 04, 2015 1:59 pm

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Fact in Slow changing Dim

Post  dshams on Fri Sep 04, 2015 2:05 pm

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.

dshams

Posts : 13
Join date : 2010-12-08

View user profile

Back to top Go down

Re: Fact in Slow changing Dim

Post  ngalemmo on Fri Sep 04, 2015 3:53 pm

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Fact in Slow changing Dim

Post  saan99 on Wed Oct 07, 2015 7:14 am

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.

saan99

Posts : 1
Join date : 2015-10-07

View user profile

Back to top Go down

Re: Fact in Slow changing Dim

Post  ngalemmo on Wed Oct 07, 2015 11:37 am

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Fact in Slow changing Dim

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum