Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Mar 22, 2004

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

By William Pearson

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.

1.  Select the Warehouse cube in the Cube drop-down list box.

2.  Select File --> New from the main menu to begin a new query.

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

3.  Create the following new query:

-- MXAS13-2  Dynamic Named Set
    SET [Low Performers]
    'BOTTOMCOUNT([Warehouse].[Warehouse Name].MEMBERS, 10, 
        [Measures].[Warehouse Profit])'
    {[Measures].[Warehouse Profit]} ON COLUMNS,
        {[Low Performers]} ON ROWS

4.  Execute the query using the Run Query button.

The results dataset appears as shown in Illustration 6.

Illustration 6: Results Dataset - Dynamic Named Set

5.  Save the query as MXAS13-2.

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

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM