July MDX Puzzle Solution Revealed - 17 Jul 2000

How would you write a query on the FoodMart 2000 Sales cube that returns the unit sales? Put the four quarters of 1997 on the columns; on the rows, put the stores that have more unit sales than the average warehouse sales of all warehouses. Remember that the sales cube has no warehouse sales measure; the warehouse sales figure comes from the Warehouse cube. (Don't cheat and use the warehouse and sales virtual cube.)

Hint: This feature is new to SQL Server 2000 Analysis Services.

Select [1997]. Children on Columns,Filter([Store Name].Members,  Store.CurrentMember >      LookupCube("Warehouse", "Avg([Warehouse Name].Members, (Warehouse.CurrentMember, [Warehouse Sales]))" )) on Rowsfrom Saleswhere ([Unit Sales])

The answer to this puzzle uses the new, useful LookupCube function, which is similar to the nested query capability of SQL Server 7.0. This MDX query uses the LookupCube function to retrieve the average Warehouse Sales of all warehouses in the Warehouse cube. The value then filters all the stores in the Sales cube. The results are displayed on rows, with the quarters of 1997 displayed on columns.