Type 2 Change Handling

View previous topic View next topic Go down

Type 2 Change Handling

Post  akjason on Mon Nov 12, 2012 8:45 pm

Hello, I have a design question for how to handle a type 2 change in the following situation:

I have a small dimension called ApplicationStatus with about 50 records. My fact Application table will have about a million records per year and it contains a FK to ApplicationStatus. The business wants to track the history when the status changes. The status will change on every fact record and it can change multiple times. I am hoping to avoid showing this tpye 2 change in the ApplicationStatus dimension, or it will get VERY large. Can anyone offer any design guidelines in this situation?

Thank,
Jason

akjason

Posts : 11
Join date : 2012-10-08

View user profile

Back to top Go down

Re: Type 2 Change Handling

Post  BoxesAndLines on Wed Nov 14, 2012 9:44 am

Yes, you can track status changes in a fact table.
avatar
BoxesAndLines

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

View user profile

Back to top Go down

Re: Type 2 Change Handling

Post  akjason on Wed Nov 14, 2012 1:35 pm

BoxesAndLines, I was not aware of that, thank you. Do you know of any Kimball articles or other good references on how to implement that?

I should point out, in case it matters, that the ApplicationStatus dimension has other attributes (about 10) besides just the status. Can I still efficiently track status changes in the fact table?

akjason

Posts : 11
Join date : 2012-10-08

View user profile

Back to top Go down

Re: Type 2 Change Handling

Post  BoxesAndLines on Wed Nov 14, 2012 1:41 pm

It would be a simple transaction fact. For each status change, insert a new row. The thing that is changing usually is represented as a degenerate dimension on the fact table (e.g. application number). If the status' are fixed in number and relatively small in cardinality (<10 or so) you can also look at an accumulating snapshot.
avatar
BoxesAndLines

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

View user profile

Back to top Go down

Re: Type 2 Change Handling

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