Building Named Sets in MDX

A set in Multidimensional Expressions (MDX) can be a lengthy and complex declaration, and difficult to follow or understand. For example, the following MDX query examines the unit sales of the various Chardonnay and Chablis wines in FoodMart 2000:

SELECT
   {[Product].[All Products].[Drink].[Alcoholic Beverages].[Beer and Wine].[Wine].[Good].[Good Chardonnay],
   [Product].[All Products].[Drink].[Alcoholic Beverages].[Beer and Wine].[Wine].[Pearl].[Pearl Chardonnay],
   [Product].[All Products].[Drink].[Alcoholic Beverages].[Beer and Wine].[Wine].[Portsmouth].[Portsmouth Chardonnay],
   [Product].[All Products].[Drink].[Alcoholic Beverages].[Beer and Wine].[Wine].[Top Measure].[Top Measure Chardonnay],
   [Product].[All Products].[Drink].[Alcoholic Beverages].[Beer and Wine].[Wine].[Walrus].[Walrus Chardonnay],
   [Product].[All Products].[Drink].[Alcoholic Beverages].[Beer and Wine].[Wine].[Good].[Good Chablis Wine],
   [Product].[All Products].[Drink].[Alcoholic Beverages].[Beer and Wine].[Wine].[Pearl].[Pearl Chablis Wine],
   [Product].[All Products].[Drink].[Alcoholic Beverages].[Beer and Wine].[Wine].[Portsmouth].[Portsmouth Chablis Wine],
   [Product].[All Products].[Drink].[Alcoholic Beverages].[Beer and Wine].[Wine].[Top Measure].[Top Measure Chablis Wine],
   [Product].[All Products].[Drink].[Alcoholic Beverages].[Beer and Wine].[Wine].[Walrus].[Walrus Chablis Wine]} ON COLUMNS,
   {Measures.[Unit Sales]} ON ROWS
FROM Sales

The MDX query, although fairly simple in terms of the result set, is lengthy and unwieldy when it comes to maintenance.

One method of easing maintenance and increasing understandability of an MDX query such as the previous example is to create a named set. A named set is simply a set expression associated with an alias. A named set can incorporate member or function that can normally be incorporated into a set. The named set alias is treated as a set expression, and can be used anywhere a set expression is accepted.

To illustrate, the previous MDX query example is rewritten to employ a named set, as shown in the following example:

WITH SET [ChardonnayChablis] AS
   '{[Product].[All Products].[Drink].[Alcoholic Beverages].[Beer and Wine].[Wine].[Good].[Good Chardonnay],
   [Product].[All Products].[Drink].[Alcoholic Beverages].[Beer and Wine].[Wine].[Pearl].[Pearl Chardonnay],
   [Product].[All Products].[Drink].[Alcoholic Beverages].[Beer and Wine].[Wine].[Portsmouth].[Portsmouth Chardonnay],
   [Product].[All Products].[Drink].[Alcoholic Beverages].[Beer and Wine].[Wine].[Top Measure].[Top Measure Chardonnay],
   [Product].[All Products].[Drink].[Alcoholic Beverages].[Beer and Wine].[Wine].[Walrus].[Walrus Chardonnay],
   [Product].[All Products].[Drink].[Alcoholic Beverages].[Beer and Wine].[Wine].[Good].[Good Chablis Wine],
   [Product].[All Products].[Drink].[Alcoholic Beverages].[Beer and Wine].[Wine].[Pearl].[Pearl Chablis Wine],
   [Product].[All Products].[Drink].[Alcoholic Beverages].[Beer and Wine].[Wine].[Portsmouth].[Portsmouth Chablis Wine],
   [Product].[All Products].[Drink].[Alcoholic Beverages].[Beer and Wine].[Wine].[Top Measure].[Top Measure Chablis Wine],
   [Product].[All Products].[Drink].[Alcoholic Beverages].[Beer and Wine].[Wine].[Walrus].[Walrus Chablis Wine]}'

SELECT
   [ChardonnayChablis] ON COLUMNS,
   {Measures.[Unit Sales]} ON ROWS
FROM Sales

The WITH keyword is used to create the [ChardonnayChablis] named set, which is then reused in the MDX SELECT statement. In this fashion, the set created with the WITH keyword can be changed without disturbing the MDX SELECT statement. For more information about using the WITH keyword to create named sets, see Using WITH to Create Named Sets.

The named set makes the MDX query example a bit easier to follow, but still difficult to maintain because the named set is defined as part of the MDX query itself. The scope of the named set is limited to this MDX query alone, and is not reusable.

MDX and PivotTable® Service, however, offer the capability of creating a named set with a wider scope. The CREATE SET statement allows the client application to create a named set that exists for the lifetime of the MDX session, making the named set available to all MDX queries in that session. The CREATE SET statement makes sense, for example, in a client application that consistently reuses a set in a variety of queries. For more information about using the CREATE SET to create named sets in a session, see CREATE SET Statement.

Even this scope, however, may be limiting in terms of maintenance. Microsoft® SQL Server™ 2000 Analysis Services offers the capability of creating global named sets, stored as part of a cube. For more information about creating global named sets, see Creating Named Sets.

See Also

PivotTable Service