Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Type 2 Dimension with accumulating snapshot with time stamp

3 posters

Go down

Type 2 Dimension with accumulating snapshot with time stamp Empty Type 2 Dimension with accumulating snapshot with time stamp

Post  mcpujabi 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

Back to top Go down

Type 2 Dimension with accumulating snapshot with time stamp Empty Re: Type 2 Dimension with accumulating snapshot with time stamp

Post  RafaelR 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

Back to top Go down

Type 2 Dimension with accumulating snapshot with time stamp Empty Re: Type 2 Dimension with accumulating snapshot with time stamp

Post  mcpujabi 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

Back to top Go down

Type 2 Dimension with accumulating snapshot with time stamp Empty Re: Type 2 Dimension with accumulating snapshot with time stamp

Post  BoxesAndLines 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.
BoxesAndLines
BoxesAndLines

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

Back to top Go down

Type 2 Dimension with accumulating snapshot with time stamp Empty Re: Type 2 Dimension with accumulating snapshot with time stamp

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top

- Similar topics

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