Mdx (Analysis Services (sql server)) mdx

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

{[Prod].[All Prods].[Drink].[AlcBev].[B&W].[Wine].[Good].[Good Chardonnay],

[Prod].[All Prods].[Drink].[AlcBev].[B&W].[Wine].[Pearl].[Pearl Chardonnay],

[Prod].[All Prods].[Drink].[AlcBev].[B&W].[Wine].[Portsmouth].[PortsmouthChardonnay],

[Prod].[All Prods].[Drink].[AlcBev].[B&W].[Wine].[Top Measure].[TopMeasureChardonnay],

[Prod].[All Prods].[Drink].[AlcBev].[B&W].[Wine].[Walrus].[Walrus Chardonnay],

[Prod].[All Prods].[Drink].[AlcBev].[B&W].[Wine].[Good].[Good Chablis Wine],

[Prod].[All Prods].[Drink].[AlcBev].[B&W].[Wine].[Pearl].[Pearl Chablis Wine],

[Prod].[All Prods].[Drink].[AlcBev].[B&W].[Wine].[Portmouth].[Portmouth Chablis Wine],

[Prod].[All Prods].[Drink].[AlcBev].[B&W].[Wine].[TopMeasure].[TopMeasureChablisWine],

[Prod].[All Prods].[Drink].[AlcBev].[B&W].[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

'{[Prod].[All Prods].[Drink].[AlcBev].[B&W].[Wine].[Good].[Good Chardonnay],

[Prod].[All Prods].[Drink].[AlcBev].[B&W].[Wine].[Pearl].[Pearl Chardonnay],

[Prod].[All Prods].[Drink].[AlcBev].[B&W].[Wine].[Portsmouth].[PortsmouthChardonnay],

[Prod].[All Prods].[Drink].[AlcBev].[B&W].[Wine].[Top Measure].[TopMeasureChardonnay],

[Prod].[All Prods].[Drink].[AlcBev].[B&W].[Wine].[Walrus].[Walrus Chardonnay],

[Prod].[All Prods].[Drink].[AlcBev].[B&W].[Wine].[Good].[Good Chablis Wine],

[Prod].[All Prods].[Drink].[AlcBev].[B&W].[Wine].[Pearl].[Pearl Chablis Wine],

[Prod].[All Prods].[Drink].[AlcBev].[B&W].[Wine].[Portmouth].[Portmouth Chablis Wine],

[Prod].[All Prods].[Drink].[AlcBev].[B&W].[Wine].[TopMeasure].[TopMeasureChablisWine],

[Prod].[All Prods].[Drink].[AlcBev].[B&W].[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.

Using WITH to Create Named Sets

The WITH keyword is included as part of the MDX SELECT statement, to allow construction of named sets as part of an MDX query.

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

[WITH

[ ...]]

SELECT [

[, ...]]

FROM []

[WHERE []]

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

::= SET AS ''

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

For example, the [ChardonnayChablis] named set is used to refer specifically to all of the Chardonnay and Chablis wine members in the Product dimension of the FoodMart 2000 database. The syntax for the named set is depicted in the following example:

WITH SET [ChardonnayChablis] AS

'{[Prod].[All Prods].[Drink].[AlcBev].[B&W].[Wine].[Good].[Good Chardonnay],

[Prod].[All Prods].[Drink].[AlcBev].[B&W].[Wine].[Pearl].[Pearl Chardonnay],

[Prod].[All Prods].[Drink].[AlcBev].[B&W].[Wine].[Portsmouth].[PortsmouthChardonnay],

[Prod].[All Prods].[Drink].[AlcBev].[B&W].[Wine].[Top Measure].[TopMeasureChardonnay],

[Prod].[All Prods].[Drink].[AlcBev].[B&W].[Wine].[Walrus].[Walrus Chardonnay],

[Prod].[All Prods].[Drink].[AlcBev].[B&W].[Wine].[Good].[Good Chablis Wine],

[Prod].[All Prods].[Drink].[AlcBev].[B&W].[Wine].[Pearl].[Pearl Chablis Wine],

[Prod].[All Prods].[Drink].[AlcBev].[B&W].[Wine].[Portmouth].[Portmouth Chablis Wine],

[Prod].[All Prods].[Drink].[AlcBev].[B&W].[Wine].[TopMeasure].[TopMeasureChablisWine],

[Prod].[All Prods].[Drink].[AlcBev].[B&W].[Wine].[Walrus].[Walrus Chablis Wine]}'

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([Prod].Members, (InStr(1, [Prod].CurrentMember.Name, "chardonnay") <> 0)

OR

(InStr(1, [Prod].CurrentMember.Name, "chablis") <> 0))'
SELECT

[ChardonnayChablis] ON COLUMNS,

{Measures.[Unit Sales]} ON ROWS

FROM Sales