history change requirement for every new iteration

View previous topic View next topic Go down

history change requirement for every new iteration

Post  elementary on Fri May 18, 2012 10:10 am

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.

elementary

Posts : 4
Join date : 2012-05-18

View user profile

Back to top Go down

Re: history change requirement for every new iteration

Post  LAndrews on Fri May 18, 2012 12:19 pm

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.

LAndrews

Posts : 132
Join date : 2010-05-13
Location : British Columbia, Canada

View user profile

Back to top Go down

Re: history change requirement for every new iteration

Post  BoxesAndLines on Fri May 18, 2012 1:54 pm

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

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

View user profile

Back to top Go down

Re: history change requirement for every new iteration

Post  hang on Fri May 18, 2012 5:43 pm

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

View user profile

Back to top Go down

Re: history change requirement for every new iteration

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