SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
giles.clapham
giles.clapham
Old Hand
Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)

Group: General Forum Members
Points: 382 Visits: 451
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
stevefromOZ
stevefromOZ
SSCrazy Eights
SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)

Group: Moderators
Points: 9490 Visits: 3757
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.
giles.clapham
giles.clapham
Old Hand
Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)

Group: General Forum Members
Points: 382 Visits: 451
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
stevefromOZ
stevefromOZ
SSCrazy Eights
SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)

Group: Moderators
Points: 9490 Visits: 3757
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 Smile

Steve.
giles.clapham
giles.clapham
Old Hand
Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)

Group: General Forum Members
Points: 382 Visits: 451
Thanks Steve, I'll do that right away.
Cheers
Giles
giles.clapham
giles.clapham
Old Hand
Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)

Group: General Forum Members
Points: 382 Visits: 451
Worked a treat, thanks very much for your guidance.
Giles
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search