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.

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:

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

6. Type the following query into a new, blank Query pane:


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

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.


Illustration 2: Initial Results Dataset (Partial View)

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.

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.

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

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


Illustration 3: Second Example Results Dataset (Partial View)

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.

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

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.

MDX Essentials Series
The LEVEL_NUMBER Member Property
The LEVEL_UNIQUE_NAME Intrinsic Member Property
Intrinsic Member Properties: The HIERARCHY_UNIQUE_NAME Property
Intrinsic Member Properties: The DIMENSION_UNIQUE_NAME Property
Further Combination of BottomCount() with Other MDX Functions
Combine BottomCount() with Other MDX Functions to Add Sophistication
Basic Set Functions: The BottomCount() Function, Part I
Intrinsic Member Properties: The MEMBER_VALUE Property
Intrinsic Member Properties: The MEMBER_UNIQUE_NAME Property
Intrinsic Member Properties: The MEMBER_NAME Property
Intrinsic Member Properties: The MEMBER_KEY Property
Intrinsic Member Properties: The MEMBER_CAPTION Property
Set Functions: The StripCalculatedMembers() Function
Set Functions: The AddCalculatedMembers() Function
MDX Numeric Functions: The Min() Function
MDX Numeric Functions: The Max() Function
Set Functions: The .AllMembers Function
MDX Essentials: Set Functions: The MeasureGroupMeasures() Function
String Functions: The .Properties Function, Part II
String Functions: The .Properties Function
Logical Functions: IsGeneration(): Conditional Logic within Filter Expressions
MDX Scripting Statements: Introducing the Simple CASE Statement
Logical Functions: IsGeneration(): Conditional Logic within Calculations
Logical Functions: IsAncestor(): Conditional Logic within Filter Expressions
MDX Clauses and Keywords: Use HAVING to Filter an Axis
Logical Functions: IsAncestor(): Conditional Logic within Calculations
Logical Functions: IsSibling(): Conditional Logic within Filter Expressions
Logical Functions: IsSibling(): Conditional Logic within Calculations
MDX Operators: The IsLeaf() Operator: Conditional Logic within Filter Expressions
MDX Operators: The IsLeaf() Operator: Conditional Logic within Calculations
MDX Numeric Functions: The .Ordinal Function
Other MDX Entities: Perspectives
MDX Operators: The IS Operator
MDX Set Functions: The Distinct() Function
MDX Set Functions: The ToggleDrillState() Function
Set Functions: The DrillUpLevel() Function
Set Functions: The DrillDownLevelTop() and DrillDownLevelBottom() Functions
MDX Set Functions: DrillDownLevel()
MDX Set Functions: The DRILLUPMEMBER() Function
MDX Essentials: Set Functions: The DRILLDOWNMEMBERTOP() and DRILLDOWNMEMBERBOTTOM() Functions
MDX Essentials : Set Functions: The DRILLDOWNMEMBER() Function
MDX Essentials: Drilling Through with MDX: The DRILLTHROUGH Statement
MDX Essentials: String Functions: The .UniqueName Function
MDX Essentials: String Functions: The .Name Function
MDX Essentials: String / Numeric Functions: The CoalesceEmpty() Function
MDX Essentials: Basic Set Functions: The TopCount() Function, Part II
MDX Essentials: Basic Set Functions: The TopCount() Function, Part I
MDX Essentials: Enhancing CROSSJOIN() with Calculated Members
MDX Essentials: Set and String Functions: The GENERATE() Function
MDX Essentials: The CROSSJOIN() Function: Breaking Bottlenecks
MDX Essentials: String / Numeric Functions: More on the IIF() Function
MDX Essentials: String / Numeric Functions: Introducing the IIF() Function
MDX Essentials: Logical Functions: The IsEmpty() Function
MDX Essentials: Basic Set Functions: The EXTRACT() Function
MDX Essentials: Numeric Functions: Introduction to the AVG() Function
MDX Essentials: Basic Member Functions: The .Item() Function
MDX Essentials: Basic Set Functions: Subset Functions: The Subset() Function
MDX Essentials: Basic Set Functions: Subset Functions: The Tail() Function
MDX Essentials: Basic Set Functions: Subset Functions: The Head() Function
MDX Essentials: Basic Set Functions: The CrossJoin() Function
MDX Essentials: Basic Numeric Functions: The Count() Function
MDX Essentials: Basic Set Functions: The Filter() Function
MDX Essentials: Basic Set Functions: The EXCEPT() Function
MDX Essentials: Basic Set Functions: The Intersect() Function
MDX Essentials: Basic Set Functions: The Union() Function
MDX Essentials: Basic Set Functions: The Order() Function
MDX Essentials - MDX Time Series Functions, Part III: The LastPeriods() and ParallelPeriod() Functions
MDX Time Series Functions, Part II: The OpeningPeriod () and ClosingPeriod() Functions
MDX Essentials - MDX Time Series Functions, Part I: PeriodsToDate() and Kindred Functions
MDX Essentials: MDX Member Functions: "Relative" Member Functions
MDX Member Functions: The Cousin () Function
MDX Essentials: Member Functions: More "Family" Functions
MDX Member Functions: The "Family" Functions
MDX Essentials: MDX Members: Introducing Members and Member
MDX Essentials : MDX Operators: The Basics
MDX Essentials: Structure of the MDX Data Model
MDX at First Glance: Introduction to SQL Server MDX Essentials








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers