Accumulating Snapshots: Can we update the non-milestone dimensional data associated with the shapshot?

View previous topic View next topic Go down

Accumulating Snapshots: Can we update the non-milestone dimensional data associated with the shapshot?

Post  Bumblebee on Wed Mar 04, 2015 9:02 pm

Hi
I have a question regarding the modeling and updating of accumulating snapshots.  There is some ambiguity in the material I have read thus far about the updating of accumulating snapshots.  It is obvious from the descriptions I have found that these fact tables will be updated whenever a key milestone is reached, and that is quite clear.
In design tip number 27 it says that ‘The other unusual aspect of the accumulating snapshot fact table is that we revisit the same records over and over, physically changing both foreign keys and measured facts, as the (usually short) life of the entity unfolds).  My question is – does this mean that dimensional attributes related to the accumulating pipeline can be updated throughout the life of the process?  I am referring to those dimensional attributes that do not specifically related to any milestone status being reached,  or contribute to the business key of the table.  Or do these dimensional attributes have to stay fixed as they first populated at the time the record was created?

The example I have is we are looking at a pipeline for managing an offender through a process, and one of the dimensional attributes linked to this pipeline is the offenders address.  If the address is updated can we update the FK in the accumulating snapshot to reflect the new address, or does it have to stay fixed as it was at the time the record was created?

Thanks in advance.

Bumblebee

Posts : 3
Join date : 2015-03-04

View user profile

Back to top Go down

Re: Accumulating Snapshots: Can we update the non-milestone dimensional data associated with the shapshot?

Post  ngalemmo on Wed Mar 04, 2015 11:15 pm

It depends on what you want. The quote is just saying you can, not that you have to. If you want both current and at the time addresses, you can have two foreign keys in the fact or use a type 2 dimension and leave the key alone.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Accumulating Snapshots: Can we update the non-milestone dimensional data associated with the shapshot?

Post  BoxesAndLines on Thu Mar 05, 2015 12:29 am

Unless you only have one fact table, more than likely your dimensions will be shared with other fact tables. If the dimensions are type 1, you will get the current version of the dimension, so yes, the dimension values will change even though you're not updating the FK. If they are type 2, and they are shared, the dimensions will again be updated, but I typically do not update the type 2 FK's. If you do, you're basically saying the dimension should be type 1 and you're going through a lot of work for nothing. I typically only update the milestone dates and the count of day metrics.
avatar
BoxesAndLines

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

View user profile

Back to top Go down

Re: Accumulating Snapshots: Can we update the non-milestone dimensional data associated with the shapshot?

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