Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

help to design a Cube that shows me all purchases for those who bought a given product Expand / Collapse
Author
Message
Posted Thursday, April 25, 2013 3:26 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, August 28, 2014 2:19 AM
Points: 66, Visits: 340
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

Post #1446367
Posted Thursday, April 25, 2013 7:48 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: Moderators
Last Login: Wednesday, August 6, 2014 8:12 AM
Points: 1,815, Visits: 3,456
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.
Post #1446500
Posted Thursday, April 25, 2013 8:19 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, August 28, 2014 2:19 AM
Points: 66, Visits: 340
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
Post #1446527
Posted Thursday, April 25, 2013 12:53 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: Moderators
Last Login: Wednesday, August 6, 2014 8:12 AM
Points: 1,815, Visits: 3,456
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.
Post #1446680
Posted Friday, April 26, 2013 1:53 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, August 28, 2014 2:19 AM
Points: 66, Visits: 340
Thanks Steve, I'll do that right away.
Cheers
Giles
Post #1446833
Posted Friday, April 26, 2013 10:34 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, August 28, 2014 2:19 AM
Points: 66, Visits: 340
Worked a treat, thanks very much for your guidance.
Giles
Post #1447095
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse