MDX Essentials: String / Numeric Functions: Introducing the IIF() Function

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 tutorial progressively adding features designed to meet specific real-world needs.

For more information about the series in general, as well as the software and systems requirements for getting the most out of the lessons included, please see the first article, MDX at First Glance: Introduction to MDX Essentials.

Note: Service Pack 3 updates are assumed for MSSQL Server 2000, MSSQL Server 2000 Analysis Services, and the related Books Online and Samples.

Overview

In this lesson, we will introduce a function that can technically be classified as either a string or a numeric function, depending upon which of two choices we make in its syntax. Whichever of the two options we select, the IIF() function returns one of two possible values, based upon whether a logical test it performs upon a specified expression is evaluated to be true or false.

The IIF() function provides far-reaching capabilities within MDX, both as a mechanism for influencing query results through the application of a logical test, and as a means for providing sophisticated solutions when used in conjunction with other MDX functions. I have found IIF() to be an excellent tool for implementing OLAP solutions with MSAS, as well as for implementing enterprise Business Intelligence with MSSQL Server Reporting Services, in a wide array of client environments and business requirements. Along with an introduction to the IIF() function, this lesson will include:

The IIF() Function

Introduction

The IIF() function, according to the Analysis Services Books Online, "returns one of two numeric or string values determined by a logical test." We will examine the function's manner of accomplishing its evaluations, and discuss factors that we should consider based in the selection of the options that IIF() offers, in the sections that follow.

We will examine the syntax for the IIF() function in general, building to its use in meeting an issue that arises in the real world, where we use it in a somewhat more sophisticated scenario to overcome an obstruction to analysis and reporting. In this way, we will be able to clearly see the rudiments of operation without distraction, then to see a second instance where we replicate a problem we might encounter in working with MDX and our OLAP data sources, and then how we can use the IIF() function to extend a cube's metadata, and to generate the results we need.

In our last article, Logical Functions: The IsEmpty() Function, we witnessed the use of the IIF() function, in combination with the ISEMPTY() function, in handling empty tuples, a common occurrence in the often sparse OLAP data sources that we encounter in the business environment. The use of IIF() in the latter part of the article served to expose the function to us prior to our getting to spend some "quality time" with the topic. (It might be advantageous to return to the practice example we undertook together in that article, after working through this one, in order to activate the concepts from the perspective of our discussions here.) Our objective, of course, is to gain a richer understanding of the capabilities found within the IIF() function, together with a feel for its many diverse applications in supporting the business needs of our clients and employers.

Discussion

IIF() affords us a means of testing a logical expression (or "search condition" ) for a true / false outcome, and then returning one of two specified values, based upon that outcome. IIF() is restricted to either a pair of potential numeric return values or a pair of potential string return values; we cannot mix the two in a single use of the function, as we shall see in later sections.

The test of the specified logical expression in the IIF() function cannot itself have null as an outcome, because the comparison operators that are inherent to a logical expression effectively convert any nulls to zeroes, for purposes of the comparison. Moreover, by similar reasoning, in cases where one of the two return values is a null, the function itself is limited to the numeric (versus string) option that we mentioned earlier. The consequence of this is that we cannot specify a null return value if we have chosen the string option, as we can when going the numeric route. Keeping these considerations in mind can help us to avoid errors in our uses of the IIF() function.

Let's look at a syntax illustration to further clarify the operation of IIF().

Syntax

Syntactically, the expression upon which the evaluation of "true or false" is to be applied by the IIF() function is placed within the parentheses to the right of IIF, and is followed by the true and false return values, respectively. As we have noted, we can select between the numeric and string options, but cannot mix the two. The syntax is shown in the following strings:

As is somewhat obvious, "Expression 1" is returned for the appropriate option if the Logical Expression is evaluated as "True." If the logical test is determined to be "False," then "Expression 2" is returned. Keep in mind that both return values in either option have to be of the same expression type, number or string. Attempting to mix numeric and string values here will result in failure, as will attempting to pair a null with a string, as we have mentioned earlier.

Examples

The following simple example illustrates conceptually the operation of the IIF() function. (We will be doing a practice exercise in subsequent sections, but if you want to "test drive" a sample, you can certainly cut and paste, or type, the below into the MDX Sample Application).

We will query the Warehouse sample cube to determine, for operating year 1998, and for Canadian stores, which of the Foodmart organization's Hamburger brands were profitable at the Warehouse level. If profitable, we would like to assign a "1" to the calculated member "Profitability" that we will create for presentation purposes. We will display the Profitability calculated member next to the Warehouse Profit for each brand, to allow us to easily verify that the calculated member is functioning correctly.

We note that the returned data indicates that two hamburger brands, Gerolli and Ship Shape, were not profitable within the scope of Canadian stores in 1998. We used the "numeric option" in this illustration, as we sought to simply return a numeric value, 1 or 2, based upon whether each brand was profitable or not, respectively.

We will activate the concepts involved in the foregoing discussions by practicing the use of the IIF() function in the section that follows.

Practice

To reinforce our understanding of the basics we have covered so far, we will use the IIF() function in a manner that illustrates its operation within a couple of practice examples. We will first create a query that attempts to meet a business need as stated by a hypothetical group of information consumers. We will then create a second query that illustrates the use of the function in supporting a different business need.

We will call upon the MDX Sample Application again, as our tool to construct and execute the MDX we examine, and to view the results datasets we obtain.

1. Start the MDX Sample Application.

2. Clear the top area (the Query pane) of any queries or remnants that might appear.

3. Ensure that FoodMart 2000 is selected as the database name in the DB box of the toolbar.

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

Let's assume for our practice example that we have received a request from a group of information consumers in the Finance Department of the FoodMart organization. Their request is for support in the presentation of some performance indicators surrounding Warehouse Profit Margins for the various Product Categories that comprise Foodmart operations. The consumers, who will be the intended audience for the requested data, inform us that the data from which we can derive the requested information is housed within the Warehouse sample cube.

The consumers wish to see the 1998 Warehouse Profit Margin for each of the Product Categories (computed by simply dividing the corresponding figures for Warehouse Profit by Warehouse Sales), and, alongside each margin, they wish to see a Margin Performance Indicator that flags the Profit Margin for each as High or Low Margin, with a fixed threshold of fifty-five percent (55.00%) acting as the basis for the classification. They will use the groupings that they can create from the Margin Performance Indicator for various analytical purposes after they receive the data.

We will begin by composing a simple query to meet this business requirement, which will illustrate the use of the IIF() function, through which we will leverage the string option to generate the Margin Performance Indicator flags. My objective is thus to illustrate use of the function to meet the hypothetical business requirement we have outlined, as a means of reinforcing the concepts in our minds. Once we have activated the concepts, they can be triggered upon meeting a similar situation in our respective business environments.

Let's set about meeting this relatively basic requirement with the following steps:

The purpose of the first WITH MEMBER section of the query is to create a calculated member to provide the Warehouse Profit Margin, or "Warehouse Margin," (that is, Warehouse Profit divided by Warehouse Sales) for each of the Product Category members. We use the cell property FORMAT_STRING to format the VALUE cell property for the calculated member, simply to make the information it presents more understandable, with clear indication that it is a percentage value.

The second WITH MEMBER section (calculated member Margin Perf Indicator) contains the focus of our lesson, the IIF() function, which we are using to apply the ">" operator to compare the Profit Margin for each Product Category to our fixed threshold of fifty-five percent (".55" in the function above). We are making this comparison as a means of assigning one of two string values, "High Margin" or "Low Margin."

The SELECT statement simply requests the calculated members, alongside the Warehouse Sales, Warehouse Cost, and Warehouse Profit measures, for each respective Product Category's activity for 1998.

NOTE: For a detailed introduction to calculated members, see the following Database Journal articles: Calculated Members: Introduction and Calculated Members: Further Considerations and Perspectives. In addition, for an introduction to the CrossJoin() function, see Basic Set Functions: The CrossJoin() Function.

7. Execute the query by clicking the Run Query button in the toolbar.

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

And so we see that the IIF() function is effective in creating the textual flags that the information consumers have requested. In like manner, IIF() can be used to generate many flags of this sort, either string or numeric in type, which can be useful in grouping, conditional formatting and other activities in numerous reporting and analysis contexts. (I have even used the IIF() function in certain client situations to support cell-level security, underlying permission rules, as well as other "less-than-intuitive" uses.) The next logical step within these pursuits is parameterization of the function to achieve ad hoc capabilities, and, although we do not explore that avenue in this article, my Database Journal article Black Belt Components: Ad Hoc Conditional Formatting for OLAP Reports delves into the mechanics of parameterized, conditional formatting, based upon IIF(), from the perspective of MSSQL Server Reporting Services.

Having accomplished our objectives within the example above, let's take a look at another example. We will perform a similar flagging operation, but will use a different approach, whereby we will make use of additional MDX functions within our IIF() statement. First, we will outline the requirement, once again, as put to us by our hypothetical group of information consumers.

Within the Product dimension of the Warehouse cube, once again, the Finance consumers wish to be able to perform another sort of flagging, as a means of supporting a grouping mechanism that is not directly available as part of the Warehouse cube metadata, but that can be generated using a logical rule that is supported. Because of a specialized analysis requirement, the consumers want to be able to generate a report of all Products, by Product Name, that FoodMart handles, but they wish to have in place a flag that classifies each as either "Dairy" or "Non-Dairy." While the "Dairy" classification does, indeed, exist in the metadata, there is no corresponding "Non-Dairy" classification (the "reciprocal" of the Dairy group, as it were).

Because the immediate need to analyze all products based on their "dairy" or "non-dairy" nature is a specialized requirement, which will not be frequent enough to justify the permanent alteration of our cube structure, we deduce that this is an ideal scenario for an IIF() function. The consumers corroborate our opinion further when they tell us that all dairy products contain a "Dairy" member at the Product Category level of the dimensional hierarchy. This is a "rule we can use," through leveraging another handy MDX function, .Name, together with the Ancestor() function, as we shall see in the steps that follow.

8. Select File ` Save As..., name the file MDX26-1, and place it in a meaningful location.

9. Select File ` New, to create a new query.

10. Type the following query into the Query pane:



-- MDX26-2:  IIF() Used for Flagging Groups

WITH MEMBER

     [Measures].[Dairy List]

AS

     'IIF(Ancestor([Product].CurrentMember, 

          [Product].[Product Category]).Name="Dairy", 

               "Dairy", "Non-Dairy")'

SELECT

     {[Measures].[Dairy List]} ON COLUMNS,

     {[Product].[Product Name].Members} ON ROWS

FROM

   WAREHOUSE

The WITH MEMBER section of the query again creates a calculated member, containing the IIF() function, with which we are applying the "equals" ("=") conditional operator, and stating that, if an ancestor of the Product current member, residing at the Product Category level, contains the Name "Dairy," then the calculated member will assume the value "Dairy;" if the current member of the Product dimension has a Product Category level ancestor whose Name does not evaluate to "Dairy," then assign the value "Non-Dairy."

The SELECT statement requests the calculated member, to be juxtaposed against each of the Product Names that appear on the rows.

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

Once again, IIF() function proves effective in creating the string values that the information consumers have requested. Its combination with the Ancestor() and .Name functions has, in this example, provided us the capability to support the external grouping mechanism that the intended audience will find useful in the specialized reporting and analysis tasks that it has identified, basing the new classification upon a rule that is metadata based. The IIF() function, in combination with other functions such as those we have seen, can often be used in this manner to extend the cube structure to meet such specialized needs.

Summary...

In this article, we explored the IIF() function, discussing its primary purpose, as well as touching upon its far-reaching capabilities, particularly when used in conjunction with other MDX functions. We discussed generalities about the use of the function, and then began practice examples that focused on two basic uses of IIF() to meet hypothetical business requirements that mirrored needs we might find in the "real world."

In addition to introducing the IIF() function in an exercise where we might see its power in returning values based upon a fixed numeric condition we imposed, we showed its use in delivering specific values based upon a rule we defined in the cube metadata. In both examples, we observed that the IIF() function assigned values based upon a conditional expression, as part of its operation. We examined the syntax surrounding the IIF() function before beginning our practice exercises, discussing the syntax for each of the two available return value options. Finally, we discussed the results datasets we obtained in each of the examples, upon executing the respective queries we had constructed.

MDX Essentials Series

About the Series ...

Overview

The IIF() Function

Introduction

Discussion

Syntax

Examples

Practice

Summary...