MDX in Analysis Services: Named Sets in MDX: An Introduction - Page 2

Named Sets in MDX

A named set is created with reuse in mind. Named sets consist of one or more tuples. They can be made up of more than one dimension, because a tuple can consist of one or more dimension members; only one member from each dimension can be represented per tuple, however, as we have discussed in previous sessions.

Discussion

There are three ways to create named sets in MSAS. First, as some of us may recall from Using Sets in MDX Queries, we can create them within MSAS Analysis Manager, where they become permanent objects to which we can refer in calculated members, which can be accessed by client applications, and so forth. The other two ways of creating a named set lies within the domain of MDX. The difference between the types of sets created under the two methods lie primarily in the scope (or "life") of the set. We use the CREATE keyword to create a named set that persists for the life of the client session. The WITH keyword creates a named set whose scope extends for the life of the query in which it is created only.

Named sets are, in essence, customized lists of members. They can be either static or dynamic, depending upon whether we use "relative" syntax to construct them, with the difference between the two types largely lying in the ability of the latter to "flex" to underlying data conditions.

We will focus, within this article, on named sets created via the WITH keyword in MDX. We will work with an example of a static named set, then with a dynamic named set, creating and reviewing an example of each within the context of meeting an illustrative business need.

Syntax

As it was in the case of calculated members, construction of a named set is handled using the WITH keyword, as part of the MDX SELECT statement. In effect, we simply supply an alias name for a set, then follow the alias with a definition, immediately below it, and enclosed within single apostrophes.

The syntax appears as follows:

[WITH <formula_specification>

      [ <formula_specification>...]]

SELECT [<axis_specification>

       [, <axis_specification>...]]

  FROM [<cube_specification>]

[WHERE [<slicer_specification>]]

The <formula_specification> value for named sets is actually broken out as follows:

WITH
SET
      [ <alias>]
AS
     [<set expression>]

The following example illustrates a scenario whereby a named set might be created using the WITH keyword.

Let's say that a group of FoodMart information consumers, whose data is housed within the Warehouse cube, often request statistics surrounding Store Type, by Country. In the current scenario, they wish to see Units Ordered for Year 1998 for this combination. The WITH clause involved, in a query within which we propose to create a named set to answer this need, might appear as shown in Illustration 1.

We are simply asking that the Set, specified as a CrossJoin() between Store Country and Store Type, be aliased as Store Types by Country, in the creation of a named set by that name. We then ask that a result dataset be returned, with the Units Ordered measure on the column axis, and the new named set, Store Types by Country, appearing on the row axis. We set the slicer at Year 1998.

This query would return a dataset similar to that depicted in Illustration 2.

We will practice the creation of named sets in the section that follows.