MDAC 2.5 SDK - OLE DB Programmer's Reference
Chapter 24: Multidimensional Expressions

Preserving State During User-Interface Operations

Many graphical user interface (GUI) OLAP tools can be used to do the following:

• Create a dataset based on some initial criteria.
  
  
• Enable the user to manipulate the dataset by implementing functions that perform drill-down and drill-up operations.

Instead of rewriting the entire query to perform drill operations, which could be cumbersome and inefficient, MDX provides a powerful and simple alternative using the DRILLDOWN and DRILLUP functions.

The idea is to represent each axis expression as a named set. When the user does rollups (aggregations) and drill-downs, functions such as DRILLDOWNMEMBER, DRILLDOWNLEVEL, or DRILLDOWNMEMBER can be used on the named set. That way, the application does not have to access the axis expression and modify it.

For example, consider the dataset described in the SalesData example in Chapter 21, "OLE DB for OLAP Concepts." By using named sets, the MDX statement for this dataset looks like the following:

CREATE SalesCube.ColumnsAxisSet AS 
   CROSSJOIN({Venkatrao, Netz},
   {USA_North.CHILDREN, USA_South, Japan})
CREATE SalesCube.RowsAxisSet AS 
   {Qtr1.CHILDREN, Qtr2, Qtr3, Qtr4.CHILDREN}
SELECT ColumnsAxisSet ON COLUMNS, RowsAxisSet ON ROWS
FROM SalesCube
WHERE (Sales, [1991], Products.[All])

Now, suppose the user drills down on Qtr2. This operation can be expressed easily by using the following MDX statement (which occurs in the same session, so the named sets ColumnsAxisSet and RowsAxisSet are still visible and alive):

SELECT 
   DRILLDOWNMEMBER(ColumnsAxisSet, {Qtr2}) ON COLUMNS, 
   RowsAxisSet ON ROWS
FROM SalesCube
WHERE (Sales, [1991], Products.[All])

Now the user drills up on Qtr1, which can be expressed as follows:

SELECT 
   DRILLUPMEMBER(DRILLDOWNMEMBER(ColumnsAxisSet, 
      {Qtr2}), {Qtr1}) ON COLUMNS, 
      RowsAxisSet ON ROWS
FROM SalesCube
WHERE (Sales, [1991], Products.[All])

Essentially, using named sets provides a way to preserve the state of the dataset when it was created. Further operations on the dataset can be modeled as set function calls on these named sets.