Create offline cube files (.cub) with OlapCube. Download Now!


table of contents

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.

The following syntax is used to add the WITH keyword to the MDX SELECT statement:

[WITH <formula_specification>
      [ <formula_specification>...]]
SELECT [<axis_specification>
       [, <axis_specification>...]]
  FROM [<cube_specification>]
[WHERE [<slicer_specification>]]

The <formula_specification> value for named sets is further broken out in the following syntax definition:

<formula_specification> ::= SET <set_name> AS '<set>'

The <set_name> parameter contains the alias for the named set. The <set> parameter contains the set expression to which the named set alias will refer.

You can also use MDX functions in the set expression used to create a named set. The following MDX query example uses the Filter, CurrentMember, Name, and InStr functions to create the [ChardonnayChablis] named set, as used in earlier MDX query examples in this topic.

WITH SET [ChardonnayChablis] AS
   'Filter([Product].Members, (InStr(1, [Product].CurrentMember.Name, "chardonnay") <> 0) OR (InStr(1, [Product].CurrentMember.Name, "chablis") <> 0))'

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