Marketing Model

View previous topic View next topic Go down

Marketing Model

Post  sourav.guha on Mon Dec 06, 2010 12:38 pm

I'm trying to build a Marketing DW from a transactional DB. We have a Personal Information table which holds the personal details and the classification attributes of his pattern or behaviour which changes every month for a new promotion. It is updated at the source side. We have a Promotion table which holds the holds some score values and the status. The scores changes every month and the status changes from 0 to 5 based on the disposition of an offer which are updated at the source side. The Offer table holds the offer details of the promotion. Now user works on the offer, i.e. disposition and the history is maintained in the Audit table. Now I've to keep track of the history of the behaviour changes attribute of the Personal_Info, the status changes and the score values of the Promotion and the Offer history as well. The relationship between Personal_Info and Promotion is 1:1 and the Promotion and the Offer is 1:*. The Offer, Promotion with the Audit is 1:*. Please suggest how can I deal with it to build a optimized model.



Posts : 5
Join date : 2010-11-26

View user profile

Back to top Go down

Re: Marketing Model

Post  hang on Thu Dec 30, 2010 7:16 pm

An effective approach is to use mini-dimension concept (similar to Junk dimension) and let the fact table reflect the correlations between those fast changing low cardinality attributes and other Personal details. As the attributes in mini-dimension change regularly, extracting them out of personal details will make the SCD2 dimension much smaller. You may have current profile FK of the mini-dimension in the personal details dimension, so that you can have current associations between dimensions, and historical correlations in the fact through FKs.


Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia

View user profile

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