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

December 6, 2004

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.

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:

-- MDX26-1:  IIF() Margin Performance Indicator
WITH MEMBER
     [Measures].[Warehouse Margin]
AS
     '([Measures].[Warehouse Profit]/[Measures].[Warehouse Sales])',
          FORMAT_STRING = '#.00%'
MEMBER
     [Measures].[Margin Perf Indicator]
AS
    'IIF(([Measures].[Warehouse Margin] > .55), "High Margin", "Low Margin")'

SELECT
     {[Measures].[Warehouse Sales], [Measures].[Warehouse Cost], 
        [Measures].[Warehouse Profit], [Measures].[Warehouse Margin],    
        [Measures].[Margin Perf Indicator]} ON COLUMNS,
     CROSSJOIN( {[Warehouse].Children}, 
         {[Product].[Product Category].Members}) ON ROWS
FROM
    WAREHOUSE
WHERE 
     ([Time].[Year].[1998])

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.

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.

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

NOTE: For an introduction to the .CurrentMember and Ancestor() functions, see MDX Member Functions: "Relative" Member Functions and MDX Member Functions: The "Family" Functions, respectively, within my Database Journal MDX Essentials series.

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.

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

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