history change requirement for every new iteration
4 posters
Page 1 of 1
history change requirement for every new iteration
1) Given a potential roadmap for 3-6 iterations.
2) Given a customer dimension - but for which in the source system literally 100s of different attributes can be found / derived - both simple & very complex ones.
3) the facts in iteration 1 only require a couple of attributes (only type 1) for the customer dimension
4) at this moment in time it is not possible to identify all necessary customer attributes for the future - the business simplpy do not know today or might change their mind in the future. Mind you some potential attributes are promised to be very hard to come by.
5) in future iteration 2 this might change when new facts come along (and as such new attributes type 2 are added & the facts require history going back in time for 2 to 4 years)
6) in future iteration 3 this might change again when again new facts come along (same remark as above)
How to deal with such a situation.
The possibilities I see:
1) reload all facts everytime when the customer dimension is impacted?
2) keep a basic customer dimension with only type 1 attributes + a 'fact' capturing all changes over time related to the customer dimension - which can be redone again & again - but as such the relationship between the basic customer dimension & all existing + new facts is not destroyed?
3) make different customer dimensions: and attached each specific customer dimension only to a relevant fact
Thanks for helping me out on this one :-)
greetz,
W.
2) Given a customer dimension - but for which in the source system literally 100s of different attributes can be found / derived - both simple & very complex ones.
3) the facts in iteration 1 only require a couple of attributes (only type 1) for the customer dimension
4) at this moment in time it is not possible to identify all necessary customer attributes for the future - the business simplpy do not know today or might change their mind in the future. Mind you some potential attributes are promised to be very hard to come by.
5) in future iteration 2 this might change when new facts come along (and as such new attributes type 2 are added & the facts require history going back in time for 2 to 4 years)
6) in future iteration 3 this might change again when again new facts come along (same remark as above)
How to deal with such a situation.
The possibilities I see:
1) reload all facts everytime when the customer dimension is impacted?
2) keep a basic customer dimension with only type 1 attributes + a 'fact' capturing all changes over time related to the customer dimension - which can be redone again & again - but as such the relationship between the basic customer dimension & all existing + new facts is not destroyed?
3) make different customer dimensions: and attached each specific customer dimension only to a relevant fact
Thanks for helping me out on this one :-)
greetz,
W.
elementary- Posts : 4
Join date : 2012-05-18
Re: history change requirement for every new iteration
5. Build the customer dimension with the 100's attributes during the first iteration.
Unless your first iteration is a proof-of-concept/prototype, you probably need to tackle a key conformed dimension during the first iteration.
That's one of the key benefits of a dimensional data warehouse - each iteration gets easier to deliver because you utilize existing dimensions.
If new attributes are defined in the future, a common approach is to add them and start tracking changes from that time forward. Reloading historical facts is often not an option/difficult.
Unless your first iteration is a proof-of-concept/prototype, you probably need to tackle a key conformed dimension during the first iteration.
That's one of the key benefits of a dimensional data warehouse - each iteration gets easier to deliver because you utilize existing dimensions.
If new attributes are defined in the future, a common approach is to add them and start tracking changes from that time forward. Reloading historical facts is often not an option/difficult.
LAndrews- Posts : 132
Join date : 2010-05-13
Location : British Columbia, Canada
Re: history change requirement for every new iteration
I would go type 1 and keep the source files around for later. With 100's of customer dimension attributes, you're going to have make performance tuning changes (outrigger for type 2's for example) if the fact and dimension tables are of any significant size. In all but a few cases, the exact view of the customer 2 years ago is irrelevant. Worst case is you have all the source files to reload everything.
BoxesAndLines- Posts : 1212
Join date : 2009-02-03
Location : USA
Re: history change requirement for every new iteration
I like B&L's approach. Use outriggers to slim down a fat dimension that is potentially explosive. Treat the outrigger FK as type1 to minimize type2 impact. Leverage mini dimension and outrigger FK for history tracking in the fact tables. If necessary, you may create a transaction dimension to centralize type2 change tracking around customer.
hang- Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia
Similar topics
» How to model with a requirement for multiple grains?
» Relationship between a history tracking table and a non-history tracking table?
» DW Architecture for Special requirement
» Can we go for outtrigger for this business requirement?
» Snowflaking - BI tool requirement
» Relationship between a history tracking table and a non-history tracking table?
» DW Architecture for Special requirement
» Can we go for outtrigger for this business requirement?
» Snowflaking - BI tool requirement
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum
|
|