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

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