MDX Operators: The IS Operator(1)

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. 数据挖掘研究院

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. 数据挖掘研究院

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. 数据挖掘研究院

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

数据挖掘实验室

The SQL Server Management Studio opens. 数据挖掘研究院

The Databases folder opens, exposing the detected Analysis Services database(s), as depicted in Illustration 3. 数据挖掘研究院

The Database expands, exposing the folders for the various objects housed within the Analysis Services database, as shown in Illustration 4. 数据挖掘研究院

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.

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

数据挖掘研究院

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