MDX Operators: The IS Operator - Page 3

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:

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.

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

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

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.

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

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.

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.