Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

factless fact and scd2 dimensions

3 posters

Go down

factless fact and scd2 dimensions Empty factless fact and scd2 dimensions

Post  topcat 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

Back to top Go down

factless fact and scd2 dimensions Empty Re: factless fact and scd2 dimensions

Post  BoxesAndLines 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.
BoxesAndLines
BoxesAndLines

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

Back to top Go down

factless fact and scd2 dimensions Empty Re: factless fact and scd2 dimensions

Post  topcat 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

Back to top Go down

factless fact and scd2 dimensions Empty Re: factless fact and scd2 dimensions

Post  nick_white 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 : 364
Join date : 2014-01-06
Location : London

Back to top Go down

factless fact and scd2 dimensions Empty Re: factless fact and scd2 dimensions

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum