Overlapping Dates in a dimension
Page 1 of 1
Overlapping Dates in a dimension
Hi,
I am wondering if someone could advise me on how to deal with this design problem. I have a promotion dimension keyed to a sales fact table. Initially, we thought that only one promotion could be active at one time for a sale, but it is actually the case that multiple promotions can be active in overlapping time frames. For example:
Promo 1 - Start DT 5/11/2009 End DT 5/22/2009
Promo 2 - Start DT 5/18/2009 End DT 5/28/2009
So, if I have a sale that occurs between 5/18 and 5/22, both promotions would apply. We thought about creating a bridge table to link the fact and dimension, but the complication is that there appears to be no good way to link the promotions to the bridge table. The record would be unique by the item, customer and date range. How would we know that the two promotions above should be linked to the same bridge record? Additionally, there would be a time when only promo 1 was in effect and only promo 2. A bridge table would have to account for these 3 instances. This would create a many to many relationship between the bridge table and dimension and we would be no better off then when we started. Can the bridge table appoach work or is there a wholely better approach.
Thanks for any thoughts,
Mark
I am wondering if someone could advise me on how to deal with this design problem. I have a promotion dimension keyed to a sales fact table. Initially, we thought that only one promotion could be active at one time for a sale, but it is actually the case that multiple promotions can be active in overlapping time frames. For example:
Promo 1 - Start DT 5/11/2009 End DT 5/22/2009
Promo 2 - Start DT 5/18/2009 End DT 5/28/2009
So, if I have a sale that occurs between 5/18 and 5/22, both promotions would apply. We thought about creating a bridge table to link the fact and dimension, but the complication is that there appears to be no good way to link the promotions to the bridge table. The record would be unique by the item, customer and date range. How would we know that the two promotions above should be linked to the same bridge record? Additionally, there would be a time when only promo 1 was in effect and only promo 2. A bridge table would have to account for these 3 instances. This would create a many to many relationship between the bridge table and dimension and we would be no better off then when we started. Can the bridge table appoach work or is there a wholely better approach.
Thanks for any thoughts,
Mark
MarkWojo128- Posts : 1
Join date : 2009-06-17
Similar topics
» Fact with eff / exp dates referencing dimension with eff / exp dates
» Date dimension multiple dates
» Fact dates before begin date of Dimension
» Loading dimension when source already has effective to and from dates
» To store dates or a reference to the date dimension?
» Date dimension multiple dates
» Fact dates before begin date of Dimension
» Loading dimension when source already has effective to and from dates
» To store dates or a reference to the date dimension?
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum