October 2003 MDX Puzzle Solution

Problem: One of the most common problems in data analysis is narrowing down a list of items to show the items that are most relevant for a given situation. One way to narrow down a list is to filter the top 10 or top 100 items, but although using TOPCOUNT() guarantees that you'll have fewer items to view, it doesn't guarantee that the items left are the ones you want.

October's challenge was to write a query that returns all the customers in the FoodMart 2000 Sales cube whose total Store Sales equal at least 5 percent of the sales to the customer who has the highest total Store Sales. Note that this isn't exactly what the MDX TOPPERCENT() function does. TOPPERCENT() finds the items whose Store Sales totals sum together to make a given percentage of the total Store Sales for all the items you specify. Write a query that displays all the qualifying customers on the rows and their total Store Sales values on the columns.

Solution: As Listing A shows, to solve this problem, I first created a set called LargestCustomer that contains the one customer who has the most Store Sales. Then, I used that customer to create the MaxSales measure that returns the value of Store Sales for the LargestCustomer. I used the MaxSales measure in the query's FILTER() function to identify which of all the Customers had Store Sales greater than or equal to 5 percent of the highest Store Sales value. Note that you need to separate the determination of the largest customer from the MaxSales member definition. The separation is necessary because the query evaluates sets only once, whereas it evaluates member definitions every time a result cell in the query references them. If I had embedded the TOPCOUNT() function inside the MaxSales definition, the query would be significantly slower because the query would be evaluating that definition repeatedly even though it didn't need to.