About the Series ...

This is the thirteenth tutorial article of the series, MDX in Analysis Services. The series is designed to provide hands-on application of the fundamentals of MDX from the perspective of MS SQL Server 2000 Analysis Services ("MSAS,"); our primary focus is the manipulation of multidimensional data sources, using MDX expressions, in a variety of scenarios designed to meet real-world business intelligence needs.

For more information on the series, as well as the hardware / software requirements to prepare for the tutorials we will undertake, please see the first lesson of this series, MDX Concepts and Navigation.

Note: At the time of writing, Service Pack 3 updates are assumed for MSSQL Server 2000, MSSQL Server 2000 Analysis Services, and the related Books Online and Samples. The screen shots that appear in this article were taken from a Windows 2003 Server, and may appear somewhat different from coinciding views in other operating systems.

Introduction

With this lesson, we will begin an examination of named sets, from the perspective the MDX query language. We obtained brief exposure to the concept of named sets earlier in the series (Using Sets in MDX Queries), touching upon them from the perspective of the MSAS Analysis Manager, the Cube Editor, and related interfaces in MSAS. We will be focusing in this lesson upon named sets that we create within an MDX query, through the use of the WITH statement.

Named sets are in most ways similar to calculated members (See Calculated Members: Introduction and Calculated Members: Further Considerations and Perspectives, both within the MDX in Analysis Services series), as we shall see. The syntax shared by the two is almost identical: we are required to name the set and to provide the specifications for its calculation inside the WITH clause, just as we must perform these steps for a calculated member.

In this article, we will introduce the concepts behind named sets, and then discuss the MDX syntax used in their creation. Next, we will undertake illustrative practice examples, based upon hypothetical business needs, and assembled step-by-step, to illustrate the value that named sets can offer us. Within the context of each step, we will discuss the results we obtain, to reinforce the subject matter in a way that activates the concepts involved, as well as to perhaps suggest expanded uses in our own business environments.

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.

Practice

Static Named Set

To reinforce our understanding of the basics we have covered so far, we will use the WITH clause to create a static named set to meet an illustrative business need. Let's say that information consumers from the Accounting Department wish to be provided with a simple report, reflecting the balances in their Income Statement line items, from the Budget cube, for the year 1997.

Let's use the WITH statement to create a named set to handle this requirement. First, we will open the MDX Sample Application, the usual platform from which we perform our practice exercises.

We are initially greeted by the Connect dialog, shown in Illustration 3.

The illustration above depicts the name of my server, MOTHER1, and properly indicates that we will be connecting via the MSOLAP provider (the default).

The MDX Sample Application window appears.

The MDX Sample Application window should resemble that depicted in Illustration 4, complete with the information from the Budget cube displaying in the Metadata tree (left section of the Metadata pane).

Let's add a query that includes the creation of a named set via the WITH clause.

-- MXAS13-1  Static Named Set

WITH

    Set [Income Statement L2]

AS



    '{[Account].[All Account].[Net Income].[Net Sales],

    [Account].[All Account].[Net Income].[Total Expense].Children,

    [Account].[All Account].[Net Income]}'

SELECT

    {[Measures].[Amount]} ON COLUMNS,

    {[Income Statement L2]} ON ROWS

FROM

[Budget]

WHERE [Time].[1997]

The results dataset appears as shown in Illustration 5.

(Keep in mind that the "life" of the named set is only as long as the query in which it resides.)

We see that the constituent line items for the Income Statement appear, generating, in effect, a mini-"report" unto itself. Indeed, full reports can be written in this manner, and, if created in Analysis Manager, together with the underlying objects to support drilldown, could mean powerful capabilities indeed - at a fraction of the time and effort costs of setting it up in MDX on an ad hoc query basis. (While our purpose here is to assimilate concepts, we can never consider too much any enterprise-level opportunities to add value!)

Let's take a look at a dynamic named set at this juncture, within another practice example.

Dynamic Named Set

As we stated earlier, named sets can be defined as either static or dynamic. A dynamic named set typically contains a relative function that gives it "context sensitivity;" We see this syntactical muscle flexed most often within the Time dimension, because MDX provides many relative functions that, while not limited to the Time dimension, are used more there than elsewhere. (For a hands-on review of these Time-friendly relative functions, see MDX Time Series Functions, Part I: PeriodsToDate() and Kindred Functions, Part II: The OpeningPeriod () and ClosingPeriod() Functions, and Part III: The LastPeriods() and ParallelPeriod() Functions.)

To practice with a dynamic named set, we will assume the following scenario: Our next business requirement arrives in the form of a request from information consumers in operational management, whose data is contained in the Warehouse cube. They want us to provide a list of the least performing Warehouses in Year 1998. It is late in the year, and most of the preliminary numbers have come in from the Warehouse locations. "Lowest performing" is defined in the context of Warehouse Profits, a measure that is stored in the Warehouse cube.

We decide to create the query with a dynamic named set, composed of the BottomCount() function, which meets our needs to a tee. The dynamic nature of the query, too, fits the business requirement not only in meeting the specified need, but also in the fact that it meets the situational reality of likely change: operational management will no doubt want final numbers that cannot be ascertained until after fiscal year end, coupled with the book-closing rituals so sacred to accountants. But we can be proactive and exceed expectations by providing a "flash" report as numbers roll in, or, at the very least, an early "peek," so that management can begin rehearsing those ominous phone calls that will go out after closing. Moreover, we can follow up with a "final" dataset after the fact, and hand management solidified numbers to support their bludgeoning efforts.

For purposes of our next example, we will shift the Sample Application to the Warehouse cube.

Let's add a query that includes the creation of a dynamic named set, again via the WITH clause.

-- MXAS13-2  Dynamic Named Set

WITH

    SET [Low Performers]

AS 

    'BOTTOMCOUNT([Warehouse].[Warehouse Name].MEMBERS, 10, 

        [Measures].[Warehouse Profit])'

SELECT 

    {[Measures].[Warehouse Profit]} ON COLUMNS,

        {[Low Performers]} ON ROWS

FROM 

    [Warehouse]

WHERE 

    [Time].[Year].[1998]

The results dataset appears as shown in Illustration 6.

And so we can see how named sets are enhanced through the use of the "relative" MDX functions; dynamic calculated members rely upon these powerful functions to deliver context-sensitive results that change to meet the conditions within the underlying data.

Summary and Conclusion ...

In this article, we have introduced named sets in MDX queries, focusing on their creation through use of the WITH clause, to allow us to gain an understanding of the general capabilities of static and dynamic named sets. We introduced the concepts behind named sets, then examined the MDX syntax required to create them, as well as to specify them for presentation in our results.

We discussed the nature of static and dynamic named sets, and then activated what we had learned through an illustrative practice example for each of the two types, based upon hypothetical business needs we might encounter in the real world. We constructed each query in a practical manner, then discussed the results we obtained in each, to illustrate the value that named sets can offer us.

We will return to named sets many times as our series advances, using them to illustrate more advanced concepts, as well as diverse and robust solutions to common needs that exist within the world of business analysis.

» See All Articles by Columnist William E. Pearson, III