factless fact and scd2 dimensions

View previous topic View next topic Go down

factless fact and scd2 dimensions

Post  topcat on Thu Feb 19, 2015 10:19 am

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?

topcat

Posts : 19
Join date : 2012-08-09

View user profile

Back to top Go down

Re: factless fact and scd2 dimensions

Post  BoxesAndLines on Thu Feb 19, 2015 1:11 pm

4) Create a snapshot fact

I like the durable key or snapshot fact based on what I'm reading here.
avatar
BoxesAndLines

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

View user profile

Back to top Go down

Re: factless fact and scd2 dimensions

Post  topcat on Thu Feb 19, 2015 1:25 pm

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

topcat

Posts : 19
Join date : 2012-08-09

View user profile

Back to top Go down

Re: factless fact and scd2 dimensions

Post  nick_white on Fri Feb 20, 2015 4:03 am

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?

nick_white

Posts : 366
Join date : 2014-01-06
Location : London

View user profile

Back to top Go down

Re: factless fact and scd2 dimensions

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