Crosssell in cube? How?

Hi guys, I had some users ask me this the other day and I'm not even sure where to start...

We have a dimension (product) which has a one to many relationship with our customer fact table. In this configuration, we can easily see how many customers own each product.

What the user is requesting is to be able to see how many customers have different product combinations. For example, how many customers have product A and B? Or A, B and C?

Is there any way to do this in a cube?

thanks!

Data Mining is what you're going to want to use probably. Take a look at this and see if that answers your question:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=820872&SiteID=1

If you've got data mining question, try the Data Mining forum:

http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=81&SiteID=1

If you want to try doing this yourself with some MDX, something like the following might do the trick:

select {} on 0,

Intersect(

Exists(

[Customer].[Customer].[Customer].Members

,([Date].[Calendar].[June 2004],[Product].[Category].[Bikes])

,"Internet Sales"

)

,Exists(

[Customer].[Customer].[Customer].Members

,([Date].[Calendar].[June 2004],[Product].[Category].[Clothing])

,"Internet Sales"

)

)

on 1

from [Adventure Works]

There are a bunch of ways to write that, but this way gets the people who bought bikes this month, then the people who bought clothing this month, then finds where those customers are in both sets.

|||

Thanks for your response. I read up on data mining and produced a report for my useres.. they were very pleased but it appears that hit all around the issue but not quite right on. What they need in the cube is basically to use the product dimension twice, once as a filter and once as a slicer. So you can set the filter to 'Bikes' and then slice the result by products, showing the other products owned by people who own bikes. Is this possible via the cube browser?

|||What if you added the Product dimension to the cube a second time. It would be a role-playing dimension so wouldn't require duplicate storage. Then you could filter by one Product dimension and put the other Product dimension (maybe call it "Also Bought Products"?) on columns. That's my thought for letting users do it themselves in a cube browser.|||

Thanks for your response. I tried that. I created a 2nd product dimension as role-playnig, set the relationships the same as the original product dimension and processed the cube.

What happens is that when I set a filter with the original product dimension to "Product A" and slice by the "Other products" dimension, only "Product A" is shown. Maybe I have the relationships for the 2nd product dimension incorrect?

|||

You followed my instructions. I left out one important detail. Your fact table needs to relate to your Product dimensions via a many-to-many relationship, not a regular relationship. So you'll need to pull ProductKey out of your fact table and put it in another fact table. Look at the Sales Reasons in Adventure Works as I think it's m2m. Basically you do the following:

Fact --> IntermediateDimension --> BridgeTable --> M2M_Dimension

You'll do that twice, and the M2M_Dimension will be Products and Other Products. Your IntermediateDimension should probably be Customers or something like that. Then your BridgeTable needs to be built as a measure group in the cube with dimension relationships to Customer, Products, and Other Products. Then you should be able to select Products or Other Products as m2m dimension relationships from your original Fact table

.

Hope that helps. Sorry it's hard to describe. I think that will work.

|||

Thanks a lot for your help but I still can't seem to get this going... maybe if I'm more specific...

There's a GREAT paper out there on many to many relationships in SSAS 2005 here --> http://www.sqlbi.eu/Default.aspx?tabid=80

My setup is exactly like the first scenerio shown in this paper. Using this example, I want to use the Dim_Account dimension to filter the accounts and also slice by the same dimension, thus showing the other accounts owned by the type of account in the filter. Would you be so kind as to address this example?

Thanks SO much!

|||

Fact --> IntermediateDimension --> BridgeTable --> M2M_Dimension

So in Marco's first example, Fact_Balance is the Fact table, Dim_Account is the IntermediateDimension, Factless_AccountCustomer is what I called BridgeTable, and Dim_Customer is the M2M_Dimension. Your Dimension Usage tab in the cube designer will show a relationship from the Fact_Balance measure group to the Dim_Customer dimension as a many-to-many relationship.

You'll just need to repeat all of the above twice for both of your product dimensions. Hope this works out for you.

Was that what you meant to "address this example"?