Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Design Advice

2 posters

Go down

Design Advice Empty Design Advice

Post  nsutton 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.


Posts : 1
Join date : 2014-04-02

Back to top Go down

Design Advice Empty Re: Design Advice

Post  nick_white 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


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

Back to top Go down

Back to top

- Similar topics

Permissions in this forum:
You cannot reply to topics in this forum