Post reply

help to design a Cube that shows me all purchases for those who bought a given product

  • April 25, 2013 at 3:26 am

    #394668

    Hi,

    I'm stuck, so stuck I'm not even sure what to google or put in the subject, so sorry for that.

    I've got a requirement which is simple in TSQL but can't figure out how to do it in SSAS, basically show me the purchase history for all contacts who have bought a specific product.

    Simplified I have a factTable of ContactID,ProductID,Quantity,Value and dimensions of dimContact, dimProduct

    My train of thought is I need to filter dimContact based on values in factTable but not sure how.

    I'm sure I just need to be pointed in the right direction to get me out of my thought rut.

    Thanks and apologies if this is a dumb post.

    Giles

  • April 25, 2013 at 7:48 am

    #1609944

    So, you have a cube right? Assuming you do, then using Excel (really just to save you the time of writing the MDX yourself), can you get the result you desire by putting Time (month, days whatver) in the columns, Customers on the rows, quantity and/or value in the data part of the crosstab, and then put the product name/id in the Filters, and then select a single (or multiple) products?

    Steve.

  • April 25, 2013 at 8:19 am

    #1609967

    Thanks Steve, really appreciate you taking the time to answer.

    Won't your suggestion only show the products selected in you filter though?

    Let's say I've got 26 products A through to Z.

    I want to know those customers that bought product B bought what else?

    So in Excel I'm going to have Rows of Customers, Columns of Products.

    I only want Customers who have bought B without losing the other products in the different columns.

    Or do I misunderstand how you're using Excel (I'm thinking Pivot Table)

    Regards

    Giles

  • April 25, 2013 at 12:53 pm

    #1610099

    Hi Giles,

    I misinterpreted your meaning of 'purchase history for all contacts who have purchased a specific product' - i thought you meant show me who had purchased this product.

    Examples of what you can google is "SSAS market basket analysis". You can do this (MBA) using the data mining functionality within SSAS and since around excel 2010 (i think) you can use the excel addins to make this easier. It's a classic requirement, where people typically use "who bought Diapers & Beer" in the grocery store as their example 🙂

    Steve.

  • April 26, 2013 at 1:53 am

    #1610228

    Thanks Steve, I'll do that right away.

    Cheers

    Giles

  • April 26, 2013 at 10:34 am

    #1610458

    Worked a treat, thanks very much for your guidance.

    Giles

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply