MDX Tip

Sometimes you need to aggregate a group of dimension members for a single query. For example, suppose you want to return Unit Sales for the quarters of 1997 for each product family. The solution is easy. But what if you want to run the same query for only the customers in California and Oregon, leaving out Washington? This is a common problem with a simple solution. All you have to do is create a calculated member that aggregates California and Oregon, and select that calculated member in the WHERE clause, as Listing A shows.

The Aggregate function aggregates the set of members passed to it and uses the Aggregation method defined for the member's dimension. In this case, the Customers dimension is aggregated with a Sum function as defined in the OLAP Manager when the cube was built, so the new dimension member \[CA-OR\] is the sum of \[CA\] and \[OR\].

This tip is useful in many analysis situations. Be careful, though: Performance can suffer if you use aggregation heavily in the WHERE clause. If you have a common alternative aggregation, you might be better off creating a second hierarchy for your dimension. See Mastering OLAP, "Multiple Hierarchies per Dimension" (September), for more information.