Accumulating Snapshots: Can we update the non-milestone dimensional data associated with the shapshot?
3 posters
Page 1 of 1
Accumulating Snapshots: Can we update the non-milestone dimensional data associated with the shapshot?
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.
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
Re: Accumulating Snapshots: Can we update the non-milestone dimensional data associated with the shapshot?
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.
Re: Accumulating Snapshots: Can we update the non-milestone dimensional data associated with the shapshot?
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.
BoxesAndLines- Posts : 1212
Join date : 2009-02-03
Location : USA
Similar topics
» Reporting table data repository vs. Dimensional data store
» Dimensional update but no new facts
» clickstream fact data coming in with different levels of dimensional geography data
» Tracking of historical data using SCD2 in a non-dimensional data model
» Using the Dimensional Data Warehouse as source data for the OLTP process
» Dimensional update but no new facts
» clickstream fact data coming in with different levels of dimensional geography data
» Tracking of historical data using SCD2 in a non-dimensional data model
» Using the Dimensional Data Warehouse as source data for the OLTP process
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum