Design Advice
2 posters
Page 1 of 1
Design Advice
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.
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
Re: Design Advice
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
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 : 364
Join date : 2014-01-06
Location : London
Similar topics
» Fact design advice
» Advice on Fact Table Design
» Reasons to use a 3NF design over a Dimensional Model design for an EDW - Discussion
» FACT Design Question - How to design FACT Table when computation is required on the degenerate dimension?
» Advice on relationships between Type 2 SCDs ?
» Advice on Fact Table Design
» Reasons to use a 3NF design over a Dimensional Model design for an EDW - Discussion
» FACT Design Question - How to design FACT Table when computation is required on the degenerate dimension?
» Advice on relationships between Type 2 SCDs ?
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum