Preserving State During User-Interface Operations

OLE DB Programmer's Reference

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, a function such as DRILLDOWNMEMBER, DRILLDOWNLEVEL, or DRILLUPMEMBER 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 Overview. 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.

1998-2001 Microsoft Corporation. All rights reserved.