September 2003 MDX Puzzle Solution

Downloads
39998.zip

Problem:

This market-basket analysis problem isn't for the faint of heart. Use the FoodMart 2000 Sales cube to determine which 10 non-beer products customers purchased most frequently in the same transaction with beer products. For this problem, I define a transaction as a unique combination of a customer, a store, and a month. Be careful with this one: If you write your query inefficiently, you might have Analysis Services looking for the answer overnight.

Format the results of your query so that the top 10 products are on the rows and both store sales and the number of transactions are on the columns. Here's a hint: Try to use the concepts in the main article to make your query more efficient. The answer I came up with ran for 3 minutes on my 1GHz laptop.

Solution:

Listing A shows the answer I came up with for this puzzle. The BeerTrans set uses the NONEMPTYCROSSJOIN() function to return the full list of transactions that included a beer product. Then, the NumTrans measure counts the number of beer transactions that the currently selected product member was part of. It does this by cross-joining the currently selected product with all beer transactions (identified by store, time, and number) and eliminating the empty combinations. Finally, the TOPCOUNT() function in the SELECT statement finds the 10 non-beer products that have the most transactions in common with beer. Who would have guessed that Hermanos mandarin oranges would be the most popular companion to beer, with corned beef, small eggs, and apple fruit rolls coming in close behind?