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 Jul 5, 2006

MDX Operators: The IS Operator - Page 3

By William Pearson

Procedure: Satisfy Business Requirements with MDX

Let's assume, for purposes of our practice example, that we have received a request from representatives of our client, the Adventure Works organization. As we have noted in other articles of the series, the Reporting department, a group of client-facing authors and developers, often requests assistance such as this: our relationship with Adventure Works is such that we provide on-site augmentation for business requirements gathering and training, as well as workshops, in many cases, that illustrate approaches to meeting specific needs.

To establish the context of the latest request for assistance, the authors and developers in the group inform us that they wish to leverage the IS operator, where possible, to improve performance within queries, both in reports and other applications, where comparisons take place. They state that they have recently become aware, from entries that they have encountered within blogs where general MDX considerations are addressed, that the IS operator often affords more efficient comparisons between objects than using the equals ("=") operator. For example, they relate an example recently noted, where the blog author states something like the following:

"... when comparing members, do this:

IIF([Date].[Fiscal]. CurrentMember IS [Date].[Calendar].[Calendar Year].[CY 2004], ...)

"... not this:

IIF([Date].[Fiscal]. CurrentMember.Name = "CY 2004", ...)

Our colleagues tell us that the author argued that performing the comparison with the IS operator results in more rapid query execution. His reasoning, they recall, was that the conversion of "members" to strings, implicit in the use of the equals operator, is more time consuming and simply "doesn't need to be done."

The Reporting department group informs us that they wish to test the assertions that the author seemed to be making. (They indicate that, after the manner of many "bloggers," the author attempted to "make his case" in unclear and imprecise terms, using mangled English within a "stream of consciousness" approach worthy of James Joyce.) The authors / developers predict that the use of the IS operator to perform comparisons will be more optimal, in most cases, within those of their own queries which rely upon the equals operator. They ask us to illustrate the use of the IS operator within a couple of working examples, so as to arm themselves with the familiarity needed to modify the respective queries to use IS.

After gaining an understanding of the need, we explain to the developers the basics surrounding the IS operator, as we have done in the introductory sections above, and then set about the assembly of examples to illustrate its use.

Procedure: Use the IS Operator to Perform Comparisons within a Calculated Member

Let's construct a simple query to provide an illustration of the use of the IS operator within a common context, the definition of a calculated member based upon a comparison. As an example, we will work within a local scenario described by the client representatives.

The developers / authors cite the following example as useful. They would like to create a basic query that returns the Customer Count for the respective month, quarter, half-year and annual levels for Calendar Year 2004. In addition, they are interested in seeing a simple 3-month Rolling Average Customer Count, but they wish for this calculated measure to appear only at the month level, and for a null to appear at the quarter, half-year and annual levels, of the Date hierarchy. They further specify that they wish to see the calculated measure rounded to two decimal places. They prefer to present the Date hierarchy levels in the columns and the measures in the rows of the returned dataset.

1.  Type (or cut and paste) the following query into the Query pane:

-- MDX045-001-1 IS Operator in Definition of a Calculated Member
   [Measures].[3-Mo Rolling Avg Customer Count]
      [Date].[Calendar].CURRENTMEMBER.LEVEL IS [Date].[Calendar].[Month],
         AVG(LASTPERIODS(3, [Date].[Calendar].CURRENTMEMBER), 
            [Measures].[Customer Count]),
     )', FORMAT_STRING = "#,###.00" 
      [Date].[Calendar].[Calendar Year].[CY 2004],[Date].[Calendar].[Month],
      {[Product].[Product Categories].[Category].[Bikes].CHILDREN },
         {[Measures].[Customer Count],
             [Measures].[3-Mo Rolling Avg Customer Count]}
                ) ON AXIS(1)
   [Adventure Works]

The Query pane appears, with our input, as shown in Illustration 8.

Illustration 8: Our Initial Query in the Query Pane ...

The above query supplies the simple rolling average that the developer group has requested alongside the monthly counts, thus providing, to some extent, a quick means of "reasonability testing" of the accuracy of the calculated member.

2.  Execute the query by clicking the Execute button in the toolbar, as depicted in Illustration 9.

Illustration 9: Click Execute to Run the Query...

The Results pane is populated by Analysis Services, and the dataset, partially shown in Illustration 10, appears.

Illustration 10: Results Dataset (Partial View) – IS Operator within a Calculated Member Definition

In the partial view of the returned dataset, we see that the calculated member accomplishes the intended purpose – generating the simple moving average at the month level, while returning null at the quarterly, bi-annual and annual levels of the Date hierarchy.

3.  Select File -> Save MDXQuery1.mdx As ..., name the file MDX045-001-1, and place it in a meaningful location.

Our developer / author colleagues express satisfaction with the contextual backdrop we have established for introducing the IS operator. We will undertake using the operator outside of a calculated member in our next steps, within a "fresh" query we will construct to meet another illustrative business need.

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