Preserving State During UI Operations

Many graphical user interface (GUI) OLAP tools do the following:

Create a dataset based on some initial criteria.
Enable the user to “operate” on the dataset by using UI operations such as drill down and drill up.

The brute-force way to accommodate such UI operations is to recreate the MDX query by going into the axis expressions. This is not only cumbersome, but can also be inefficient. MDX provides a powerful and simple alternative.

The idea is to represent each axis expression as a named set. When the user does roll upand drill downs, functions such as DRILLDOWNMEMBER, DRILLDOWNLEVEL, or DRILLDOWNMEMBER can be used on the named set. That way, the application does not have to peek into the axis expression and modify it.

For example, consider the dataset described in the section “Cube and Dataset Example” of chapter 2. By using named sets, the MDX statement for this dataset looks like this:

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 that 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 by

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.