Named Sets in MDX
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
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.
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:
<formula_specification> value for named sets is actually broken out as
following example illustrates a scenario whereby a named set might be created
using the WITH keyword.
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.
Syntax Example, with Annotated Sections
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.
Results Dataset, Named Set as Row Axis
practice the creation of named sets in the section that follows.