factless fact and scd2 dimensions
3 posters
Page 1 of 1
factless fact and scd2 dimensions
i have two type 2 dimensions, customer and group. customers can belong to 1 or more groups, a factless fact has been created to make the association.
now the customer dimension and group dimension are both type 2. what is the best way to manage the relationship between the dimensions when one (or both) of the dimensions is updated? i think there are 3 options, and none are very good:
1) each time a dimension is modified (new pk created), copy all factless fact records to maintain the association.
2) leave as is, and let reporting logic find associations - this is how other facts are managed, always pointing to the dimension record at the time the fact was created
3) create a durable dimension key in addition to the standard surrogate key. use the durable key in the factless fact
from a data perspective, i think option 1 is the best, but our model has multiple factless facts associated to the customer dim. so each time an update is made, each of the factless facts would require new records to be created.
thoughts?
now the customer dimension and group dimension are both type 2. what is the best way to manage the relationship between the dimensions when one (or both) of the dimensions is updated? i think there are 3 options, and none are very good:
1) each time a dimension is modified (new pk created), copy all factless fact records to maintain the association.
2) leave as is, and let reporting logic find associations - this is how other facts are managed, always pointing to the dimension record at the time the fact was created
3) create a durable dimension key in addition to the standard surrogate key. use the durable key in the factless fact
from a data perspective, i think option 1 is the best, but our model has multiple factless facts associated to the customer dim. so each time an update is made, each of the factless facts would require new records to be created.
thoughts?
topcat- Posts : 19
Join date : 2012-08-09
Re: factless fact and scd2 dimensions
4) Create a snapshot fact
I like the durable key or snapshot fact based on what I'm reading here.
I like the durable key or snapshot fact based on what I'm reading here.
BoxesAndLines- Posts : 1212
Join date : 2009-02-03
Location : USA
Re: factless fact and scd2 dimensions
i like the snapshot solution. and i would assume to update (delete/insert) the snapshot rather than refreshing in total. only a small percentage would change daily, less than 1%.
thanks
thanks
topcat- Posts : 19
Join date : 2012-08-09
Re: factless fact and scd2 dimensions
Hi - presumably you have implemented a factless fact table because you have a requirement to report on these relationships e.g. how many customers belong to Group X? If this is the case then your reporting requirements should drive how you design this: do you just want to see the current position, do you want to see the position at any point in history both in terms of the relationships and the attributes of the entities, etc.?
If you just want to relate the dimensions when you are reporting against other fact tables (e.g. Sales Fact > Customer > Group) then why not implement a bridge table?
If you just want to relate the dimensions when you are reporting against other fact tables (e.g. Sales Fact > Customer > Group) then why not implement a bridge table?
nick_white- Posts : 364
Join date : 2014-01-06
Location : London
Similar topics
» Factless fact tables and SCD2
» Fact, factless fact, and current view dimensions
» Slowly changing fact with SCD2 Dimensions
» Impact of type 2 dimensions on factless tables
» Loading a Fact Table with SCD2
» Fact, factless fact, and current view dimensions
» Slowly changing fact with SCD2 Dimensions
» Impact of type 2 dimensions on factless tables
» Loading a Fact Table with SCD2
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum