Design Advice

View previous topic View next topic Go down

Design Advice

Post  nsutton on Wed Apr 09, 2014 8:07 am

I am looking for a bit of design advice. One of the primary questions I need a cube to answer is product uptake, and by that I mean if I have a set of customers that have product A, how many other products have they purchased. If we have a campaign to sell product A, part of the measure of success is that although it is great that the customer took product A we would like to measure over time how many other products they purchase.
right now I have a customer dimension, and since this is for a financial institution I have an account fact table that holds daily measures of balance, accrued int, interest rate etc..


In addition I need the cube to track growth in balances, weighted rates on products, delinquency by branch etc which in the current design it does.

Just cant get my head around the product uptake issue.

I am not tied to this design, it is just my first pass any advice would be greatly appreciated.

nsutton

Posts : 1
Join date : 2014-04-02

View user profile

Back to top Go down

Re: Design Advice

Post  nick_white on Wed Apr 09, 2014 10:18 am

How about creating a Sales fact table with Dims of Customer, Product, Date and anything else you need? You then just run multiple/sub-queries against the table e.g.
Query 1: Select all customer Ids where the customer has bought Product A
Query 2: Count products sold where customer Id is in the resultset of Query 1 and Product sold is not Product A

If you are more (or also) interested in what products people buy together (i.e. the basket of products) then consider using a Product Group Bridge table - assuming the number of distinct combinations of products that can be bought is relatively low

nick_white

Posts : 366
Join date : 2014-01-06
Location : London

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