Type 2 Dimension with accumulating snapshot with time stamp

View previous topic View next topic Go down

Type 2 Dimension with accumulating snapshot with time stamp

Post  mcpujabi on Thu Dec 12, 2013 10:26 am

I have a question on how to add records into accumulating snapshot. I have a Type 2 Dimension and a Accumulating snapshot table with time stamp. When a new row is added to the Dimension should a row be added to the fact table, even if there is no change to any attribute in the fact table. I am assuming i don't revisit the fact table, but then the most current dimension record won't match to anything in the fact table. Thank you.

mcpujabi

Posts : 2
Join date : 2013-11-27

View user profile

Back to top Go down

Re: Type 2 Dimension with accumulating snapshot with time stamp

Post  RafaelR on Thu Dec 12, 2013 2:35 pm

Hi,
i use a fact snapshot too.

Take this dim_table like example:
dim(sk,atribute1, atribute2,...,flag_is_current,date_begining,date_end)

when a new row is added in dim_table a new sk is inserted and the last sk where the flag_is_current is "Y" ou "Yes" changes to "N" or "No" and the new SK had flag_is_current change to "Yes" or "Y". When fact look to this table will pick the SK where the flag is "Y" or "Yes".

Hope this helps

RafaelR

Posts : 10
Join date : 2013-11-20

View user profile

Back to top Go down

Re: Type 2 Dimension with accumulating snapshot with time stamp

Post  mcpujabi on Thu Dec 12, 2013 3:48 pm

An example might help.
Day 1: Source system

CustIDCustomerNameAmount
15416John Smith500
Datawarehouse:
DimCustomerKeyCustIDCustomerNameCurrentFlag
115416John SmithY
CustomerKeyAmount
1500
Day 2: Source system

CustIDCustomerNameAmount
15416John R Smith500
Datawarehouse:
DimCustomerKeyCustIDCustomerNameCurrentFlag
115416John SmithN
215416John R SmithY
CustomerKeyAmount
1500
If i now want to get the latest record in SQL: 
SELECT * FROM dimCustomer JOIN FactCustomer WHERE CurrentFlag = Y

This will give me no records as there is no Fact record. Should i put a fact record on Day 2 as well?

mcpujabi

Posts : 2
Join date : 2013-11-27

View user profile

Back to top Go down

Re: Type 2 Dimension with accumulating snapshot with time stamp

Post  BoxesAndLines on Thu Dec 12, 2013 5:22 pm

No. The fact record points to the dimension that was in effect when the event occurred. If you want the current row there are multiple strategies to do this including, adding an additional SK to the fact to join, performing a self join on the dimension to get the current row, or even adding another type 1 dimension. We need a sticky post to capture the Kimball Forum Tips and Tricks.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Type 2 Dimension with accumulating snapshot with time stamp

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