MDX Operators: The IS Operator

Wednesday Jul 5th 2006 by William Pearson
Share:

Compare objects with the MDX IS operator. Join Architect Bill Pearson in an examination and hands-on practice session.

About the Series ...

This article is a member of the series, MDX Essentials. The series is designed to provide hands-on application of the fundamentals of the Multidimensional Expressions (MDX) language, with each session progressively adding features and capabilities designed to meet specific real-world needs.

Virtually all of the MDX we have constructed in earlier articles can now be used in the SQL Server Management Studio, SQL Server Business Intelligence Studio, and various other areas within the Microsoft integrated Business Intelligence solution, and much of what we construct going forward can be executed in the Analysis Services 2000 MDX Sample Application (assuming connection to an appropriate Analysis Services data source). MDX as a language continues to evolve and expand: we will focus on many new features in articles to come, while still continuing to examine business uses of MDX in general. Exploiting MDX to meet the real-world needs of our business environments will continue to be my primary concentration within the MDX Essentials series.

For more information about the series in general, as well as the software and systems requirements for getting the most out of its member lessons, please see Set Functions: The DrillDownMember() Function, where important information is detailed regarding the applications, samples and other components required to complete our practice exercises.

Overview

While MDX functions comprise the lion's share of the MDX Essential series, numerous operators are supported within the language. (We examined a group of basic operators in my Database Journal article MDX Operators: The Basics). Of the logical, comparison, set, string, and unary operators provided by MDX, we will examine a logical operator in this session. The IS operator, like other logical operators, evaluates values and returns a Boolean value. The IS operator's utility becomes clear when we leverage it to perform comparisons between objects of any kind within Analysis Services.

In this article, we will extend our examination of MDX functions to concentrate upon the useful IS operator. We will discuss the straightforward purpose of the operator, to ascertain the equivalence of two object expressions, the manner in which IS manages to do this, and ways we can employ the operator to perform efficient comparisons, and to help us to meet various business needs in our own environments.

Along with an introduction to the IS operator, this lesson will include:

  • an examination of the syntax surrounding the operator;
  • illustrative examples of uses of the operator in practice exercises;
  • a brief discussion of the MDX results obtained within each of the practice examples.

The Is Operator

Introduction

According to the Books Online, the IS operator "performs a logical comparison on two object expressions." Moreover, the Books Online state that "the IS operator is often used to determine whether tuples and members are idempotent," or unchanged in value following multiplication by themselves. A Boolean value of "true" is returned if both object expressions refer to the same object, otherwise IS returns "false."

Another common use of the IS operator is to compare a given object expression with the NULL keyword. When we use NULL with IS, the operator allows us to ascertain existence, returning "true" if the expression to which it is compared is null, and returning "false" if not.

We will examine in detail the syntax for the IS operator after our customary overview in the Discussion section that follows. Following that, we will conduct practice examples within a couple of scenarios, constructed to support hypothetical business needs that illustrate uses for the operator. This will afford us an opportunity to explore some of the options that IS can offer the knowledgeable user. Hands-on practice with IS, where we will create queries that employ the function, will help us to activate what we have learned in the Discussion and Syntax sections.

Discussion

To restate our initial description of its operation, the IS operator performs a comparison between two object expressions, returning "true" if the object expressions are identical, and "false" if not. Using IS to compare an object expression to the NULL keyword is often used as an "existence" test for the object under consideration. As we have noted to be the case with most MDX functions and operators, pairing the IS operator with other MDX operators and functions can help us to leverage its power even further.

Let's look at syntax specifics to further clarify the operation of IS.

Syntax

Syntactically, we employ the IS operator between the two object expressions upon which we wish to perform a logical comparison. The general syntax is shown in the following string:

Object_Expression IS ( Object_Expression | NULL )

Both Object Expressions are valid MDX expressions that represent, or return, an MDX object reference. As we have noted, the operator returns a Boolean value of "true" if both Object Expressions refer to the same object; otherwise it returns "false." If the NULL keyword is substituted for the right Object Expression, then the IS operator returns "true" if the left Object Expression is null; otherwise it returns "false."

Let's take a look at an illustration. The following snippet, taken from the WITH section of a query, where it helps to define a calculated member that is subsequently specified in the SELECT section of the query, employs the IS operator:

WITH 
MEMBER
[Measures].[Annual Adjusted Sales]
 
AS
 
  'IIF([Date].[Calendar].CURRENTMEMBER IS 
 
     [Date].[Calendar].[Calendar Year].[CY 2004],
        
         [Measures].[Internet Sales Amount], 
 
            ([Measures].[Internet Sales Amount] * .8)

Let's say that the above-defined calculated member, together with the existing Internet Sales Amount measure, is crossjoined with the members of the Calendar Year level of the Date dimension (Calendar hierarchy) within the rows-axis specification. Moreover, we will assume that we specify All Products in the column-axis specification, and that the query that emerges is executed against the Adventure Works sample cube. Our query would produce a results dataset similar to that depicted in Illustration 1.


Illustration 1: Results Dataset – IS Operator Used in Definition of the Calculated Member ...

In the example dataset, we see that the IIF() function, using the IS operator, is effectively comparing the current year member (the Object Expression to the left of the IS operator), to Calendar Year 2004. The calculated member whose definition contains this comparison thus returns Annual Adjusted Sales equal to the Internet Sales Amount for 2004, while returning the adjusted Internet Sales Amount (80 percent of the measure) as Annual Adjusted Sales for the other years within the cube.

NOTE: For details surrounding the .CurrentMember function, see MDX Member Functions: "Relative" Member Functions, a member of my MDX Essentials series here at Database Journal.

We will examine the workings of the operator in more detail in the Practice section that follows.

Practice

Preparation: Access SQL Server Management Studio

To reinforce our understanding of the basics we have covered so far, we will use the IS operator in a couple of queries that illustrate its operation. We will do so in simple scenarios that place IS within the context of meeting basic requirements similar to those we might encounter within our respective daily environments. The intent is to demonstrate the use of the operator in a straightforward, memorable manner.

We will turn to the SQL Server Management Studio as a platform from which to construct and execute the MDX we examine, and to view the results datasets we obtain.

1.  Click the Start button.

2.  Select Microsoft SQL Server 2005 within the Program group of the menu.

3.  Click SQL Server Management Studio, as shown in Illustration 2.


Illustration 2: Opening SQL Server Management Studio

The Connect to Server dialog appears, after the brief Management Studio splash screen.

4.  Select Analysis Services in the Server type selector.

5.  Type / select the server name (server name / instance, if appropriate) in the Server name selector.

6.  Supply authentication information, as required in your own environment.

7.  Click the Connect button to connect with the specified Analysis Services server.

The SQL Server Management Studio opens.

8.  In the Object Explorer pane (it appears by default on the left side of the Studio), expand the Databases folder (click the "+" sign to its immediate left), appearing underneath the Analysis Server with which we are working.

The Databases folder opens, exposing the detected Analysis Services database(s), as depicted in Illustration 3.


Illustration 3: Exposing the Analysis Services Databases in the Object Browser ...

NOTE: The Analysis Services databases that appear will depend upon the activities that have taken place in your own environment, and will likely differ from those shown in Illustration 3 above. For purposes of this practice session, the Adventure Works DW database must be present. If this is not the case, consult the Books Online for the installation / connection procedures, and complete these procedures before continuing.

9.  Expand the Adventure Works DW database.

The Database expands, exposing the folders for the various objects housed within the Analysis Services database, as shown in Illustration 4.


Illustration 4: Exposing the Object Folders in the Database ...

10.  Expand the Cubes folder within the Adventure Works DW database.

The Cubes folder opens. We see two cubes, the first of which, Adventure Works, is the sample cube with which we will be conducting our practice exercises. The cubes appear as depicted in Illustration 5.


Illustration 5: The Cubes Appear ...

11.  Click the Adventure Works cube to select it.

12.  Click the New Query button just under the main menu, in the upper left corner of the Management Studio, as shown in Illustration 6.


Illustration 6: Click the New Query Button with the Adventure Works Cube Selected

The Metadata pane for the Adventure Works cube appears, along with the Query pane to its right, as depicted in Illustration 7.


Illustration 7: Adventure Works Cube Metadata Appears ...

We will be using the Query pane in the practice session that follows, to construct and execute our MDX queries.

As we discover in articles throughout my Introduction to MSSQL Server Analysis Services series, among my other series' at Database Journal, the SQL Server Management Studio serves us in providing a point of interface with all server types in the SQL Server family, including Analysis Services, Reporting Services and Integration Services servers, as well as supporting many additional functions. Among those functions, I find the capabilities to easily browse data, and to issue queries, highly convenient. We can accomplish querying in several other ways within the integrated Microsoft BI solution, but this is certainly one of the most direct. For more information on the use of the Query Editor within SQL Server Management Studio for issuing MDX queries within the practice exercises of the MDX Essentials series, see Set Functions: The DRILLDOWNMEMBER() Function. (Articles within my other series' explore other capabilities and features of the SQL Server Management Studio, as well as the SQL Server Business Intelligence Studio).

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
 
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.


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.

Procedure: Use the IS Operator to Perform Comparisons within the FILTER() Function

The report authors / developers have an additional request which we can help them to meet with the IS operator. This time, they wish to use the Filter() function within a query which will allow them to focus upon Internet Sales at a specific geographical level. While our colleagues have asked that we craft the query to return the Internet Sales measure for customers within a single U. S. state, Georgia, for Fiscal Year 2004, we confirm their understanding that, given the core query, the capability to perform ad hoc queries, where the information consumer is prompted at runtime for a state or states (or members at other geographic levels, for that matter) becomes a simple matter of parameterizing the unique name for the state, etc., within the Filter() function we will place within the rows-axis specification of the query. Because we have demonstrated to the developers, that parameterization of this sort becomes easily attainable within Reporting Services, assuming that sufficiently sophisticated queries are put into place to support it (within either the Reporting Services or Analysis Services layers), the core query is deemed valuable to the team.

NOTE: For detailed information about the Filter() function, including practical examples that demonstrate its use, see Basic Set Functions: The Filter() Function, within my MDX Essentials series.

Having obtained consensus on the proposed target dataset, we are ready to begin constructing the query.

4.  Select File --> New from the main menu.

5.  Select Query with Current Connection from the cascading menu that appears next, as depicted in Illustration 11.


Illustration 11: Create a New Query with the Current Connection ...

A new tab, with a connection to the Adventure Works cube (we can see it listed in the selector of the Metadata pane, once again) appears in the Query pane.

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

-- MDX045-002-1 IS Operator within a Filter() Function
 
SELECT 
 
{[Measures].[Internet Sales Amount]} ON AXIS(0),
 
{FILTER (
 
   [Customer].[Customer Geography].MEMBERS,
    
      [Customer].[Customer Geography].CURRENTMEMBER.PARENT 
    
         IS [Customer].[Customer Geography].[State-Province].[Georgia]
   
      ) 
 
   } ON AXIS(1)
   
FROM 
 
   [Adventure Works]
 
WHERE
 
   ([Date].[Fiscal].[Fiscal Year].[FY 2004])

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


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

7.  Execute the query by clicking the Execute button in the toolbar, as before.

The Results pane is, once again, populated by Analysis Services. This time, the dataset depicted in Illustration 12 appears.


Illustration 13: Results Dataset –The Filtered Dataset is Returned ...

In the returned dataset, we see that the query appears to deliver the intended results: only those members of Customer Geography whose parent is the State-Province of Georgia (the cities of Georgia) are displayed.

8.  Select File -> Save MDXQuery2.mdx As ..., name the file MDX045-002-1.mdx, and place it in the same location used to store the earlier queries.

The effect, as we see, has been to filter the results dataset to return only those members that "pass" the conditional test enacted by the IS operator within the Filter() function. That is, only those members who return a "true" are included within the final results.

The client representatives inform us that their immediate goals have been met, and that the examples we have shared have illustrated the principles of operation behind IS operator. We discuss related matters, such as parameterization within Reporting Services, and make an appointment to return to the client site at a future date to demonstrate the concepts.

9.  Select File -> Exit to leave the SQL Server Management Studio, when ready.

Summary ...

In this article, we shifted our examination from the MDX functions, as we have upon occasion in the past, to concentrate upon an operator. We introduced the IS operator, noting that, like other logical operators, it evaluates values and returns a Boolean value. We explained that the IS operator's utility lies in its capability to perform comparisons between objects within Analysis Services.

After discussing the straightforward purpose of the IS operator, to ascertain the equivalence of two object expressions, we examined the manner in which IS manages to do this, and ways we can leverage the operator to perform efficient comparisons to meet various business needs in our own environments. Next, we examined the syntax with which we employ IS. Finally, we undertook illustrative examples whereby we put the IS operator to work, both within the definition of a calculated member and, in conjunction with the MDX Filter() function, within an axis specification of a SELECT statement, to gain some hands-on practice in its use. Throughout our practice session, we briefly discussed the results datasets we obtained from each of the queries we constructed.

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

Discuss this article in the MSSQL Server 2000 Analysis Services and MDX Topics Forum.

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved