November 2002 MDX Puzzle Solution

For most dimensions, the business analyst determines like items by looking at their distance from the topmost (root) dimension member. Items at the same distance from the root member are at the same level. For example, in a customer dimension, all countries might be at one level, cities at another level, and customer names at still another level. But for some dimensions—such as the Employees dimension of the FoodMart 2000 HR cube—the analyst determines like items according to their distance from the bottom of the dimension. In FoodMart's HR cube, you can determine the CEO and senior management by counting levels from the root; but if you want to determine which employees are first-level employees or first-level managers, you have to count from the bottom.

Use the FoodMart 2000 HR cube to determine which 10 first-level employees have the lowest employee salary. Include only hourly employees who have a non-empty salary. Then, display the employee salary for each of these 10 employees.

The solution that Listing A shows uses the LEAVES flag in the Descendants() function. This flag tells the Descendants() function to return only the bottom 10 dimension members (the leaves). The NonEmptyEmployees set further restricts the leaves to those that have a non-empty Employee Salary measure. Then, the query uses the BottomCount() function to find the lowest employee salaries among the NonEmptyEmployees set. The inclusion of the Hourly dimension member in the WHERE clause limits the query to hourly employees. I also included the Number of Employees measure on the columns to verify that the employees the query returns don't have any employees under them.