Calculated dimension attributes based on fact
3 posters
Page 1 of 1
Calculated dimension attributes based on fact
Hi,
I have a requirement to have an attribute "Date of Last Purchase" in the Customer Dimension in a simple retail schema where we have Customer dimension (Type-4) and Sales fact. Obviously the attribute "Date of Last Purchase" changes when the customer makes a purchase. I have curently designed it in this way -
1. I have current customer dimension and historical customer dimension (Type-4)
2. "Date of Last Purchase" is an attribute of the current customer dimension and is updated based on incremental sales fact records.
Not going into Type-2/Type-4 reasoning behind the design, I would like to get opinion about if this design is correct where we require calculated dimension attributes based on fact records? How else columns like "Date of Last ...." can be computed? If we do at the report level, then everytime it will scan on-the-fly the whole fact table to get the results.
Thanks
I have a requirement to have an attribute "Date of Last Purchase" in the Customer Dimension in a simple retail schema where we have Customer dimension (Type-4) and Sales fact. Obviously the attribute "Date of Last Purchase" changes when the customer makes a purchase. I have curently designed it in this way -
1. I have current customer dimension and historical customer dimension (Type-4)
2. "Date of Last Purchase" is an attribute of the current customer dimension and is updated based on incremental sales fact records.
Not going into Type-2/Type-4 reasoning behind the design, I would like to get opinion about if this design is correct where we require calculated dimension attributes based on fact records? How else columns like "Date of Last ...." can be computed? If we do at the report level, then everytime it will scan on-the-fly the whole fact table to get the results.
Thanks
suds.kumar- Posts : 1
Join date : 2012-12-11
Re: Calculated dimension attributes based on fact
So you have a type 2 and a type 1 customer dimensions and together that is called a type 4? I'm confused. Putting the date in a type 1 customer dimension is what I would strive to do. I would not want the massive history generated by a type 2. Alternatively, you can create a type 2 dimension and just not track history for the last purchase date. Either solution is acceptable.
BoxesAndLines- Posts : 1212
Join date : 2009-02-03
Location : USA
Similar topics
» dimension table design question for around 100 attributes and higher level calculated attributes
» Calculated attributes in Customer Dimension?
» Attributes (non-additive) based on specific combinations of dimension keys -- how best to model?
» Aggregate Dimension Based on Fact?
» Dimension Attributes and Fact attributes storing same data in multiple data marts??
» Calculated attributes in Customer Dimension?
» Attributes (non-additive) based on specific combinations of dimension keys -- how best to model?
» Aggregate Dimension Based on Fact?
» Dimension Attributes and Fact attributes storing same data in multiple data marts??
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum