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

Monday Jan 3rd 2005 by William Pearson
Share:

Explore the further use of the IIF() function in meeting consumer business requirements. MSAS Architect Bill Pearson leads a practice example where we use IIF() in combination with a member property and conversion function, in producing a DataSet that we then filter, order and format.

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 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 my 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 continue our examination of the IIF() function, which we began in my article String / Numeric Functions: Introducing the IIF() Function. In that introductory session, we explored the IIF() function, discussing its primary purpose, and touching upon its many capabilities, particularly when used in conjunction with other MDX functions. We discussed generalities about the use of the function, and then undertook 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." If you are joining the series with this article, please consider reading String / Numeric Functions: Introducing the IIF() Function to gain a general overview of the function before you delve into the multi-step exercise in sections below.

In this article, we will continue our examination of the IIF() function, focusing on more evolved uses of the function, within a more involved practice scenario. We will not rehash the introduction, discussion or syntax sections that we provided in our last article, except in situations where we wish to emphasize points from those sections in the context of our current activities. Our focus here will be to provide another hands-on illustration, where we can experience firsthand the capabilities that the function can offer, and to expose a sequential set of steps aimed at getting to a specific outcome in the support of a set of hypothetical business requirements.

As has been the case with most of my articles, we will introduce a business requirement as requested by a group of information consumers. After we discuss their needs in detail, we will begin a multi-step exercise that builds to a successful end result. Along with the introduction of the expressed business needs, this lesson will include:

  • Creation of a core query, upon which we will build our end solution;
  • Creation of interdependent calculated members, to provide requested measures that do not exist "natively" in the cube structure;
  • Use of the IIF() function, in conjunction with a member property to achieve intended behavior within a calculated member;
  • Addition of the VBA VAL() function, to convert the member property to a usable data type;
  • Addition of the FILTER() and ORDER() functions, to render a presentation that meets the needs of the intended audience;
  • A brief discussion of the results DataSets we obtain in each of the steps of the practice example.

The IIF() Function

Introduction

As we noted in our last article, the IIF() function, according to the Analysis Services Books Online, "returns one of two numeric or string values determined by a logical test." We discussed the options we have in constructing the function, first undertaking an illustration to focus upon syntax, and then performing a couple of exercises where we put the IIF() function to some basic uses. Having already introduced IIF() in an earlier article, Logical Functions: The IsEmpty() Function, where we used it in combination with the ISEMPTY() function as a means of managing empty tuples, we focused, in our last article, upon using IIF() , in conjunction with other functions, to extend a cube's metadata.

We discussed various aspects of the IIF() function in the previous session, as well as the restrictions that exist with regard to the options we have in the values that IIF() can return. We also introduced, both in our discussions and within the practice exercises, the use of IIF() in combination with other MDX functions. We will continue along these lines with a more evolved query in the steps of the practice example that follows, expanding our introduction of additional functions to use, both in conjunction with and external to, the IIF() function, to deliver the data that our customers have requested in a presentation that effectively meets their needs.

Practice

To further activate our understanding of the basics we have covered so far, as well as to expand our grasp of its use in delivering further capability to support the analysis needs of our hypothetical group of information consumers, we will use the IIF() function in a manner that illustrates its operation in the multi-step example that follows. As is typically the case within the examples of the MDX Essentials series, we will call upon the MDX Sample Application, 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.

To set the scene for our practice exercise, we will assume that we have received a request from a group of information consumers in the Finance Department of the FoodMart organization. This time, their request is for support in the creation and analysis of some internal performance indicators, surrounding Stores from the Warehouse cube perspective. At the heart of the specification is the requirement to analyze Sales per Square Foot Store Area (which we will call Sales per Square Foot, and provide via a calculated measure), alongside the Store Invoice and Store Area (in square feet) for the respective stores in the cube for operating year 1998.

Sales per Square Foot, for purposes of our exercise, is computed as the measure Store Invoice divided by the square footage area value for each store. The consumers, who will be the intended audience for the requested data, inform us that they wish the data we provide to be largely "presentation ready," in that they wish it to be useful to consumers that do not necessarily understand Analysis Services (or, for that matter, OLAP), nor the significance of nulls and other concepts that might lead to ambiguity, unless we modify their presentation. To this end, the consumers ask that we ensure that only relevant Stores (that is, only Store dimension members that represent operating store locations, which had invoice activity / area values for year 1998) appear in the report. In other words, the consumers want to see only those Stores with actual values in the areas of focus it presents, without empty cells Finally, the intended audience wishes to see the Stores sorted by the new Sales per Square Foot measure, from highest to lowest value, for easy scanning of the top performing stores.

Because this requirement poses several important considerations, we will build the query to support it in multiple steps, concentrating upon the underlying concepts individually to emphasize their operation in reaching an integrated solution. This way, once we have activated each concept from the perspective of meeting an individual part of the requirement, we can establish the approach to both the respective requirement, and to the overall solution, in a way that the method can be triggered upon meeting a similar situation in our respective business environments.

We will begin our solution to meeting the business requirement by constructing a core query with the simplest measure first. Creating a "skeleton" in this manner often helps us to organize our thoughts and visually establish the layout and steps of our approach.

5.  Select File -> New, to create a new query.

6.  Type the following query into the blank Query pane:


-- MDX27-1:  Internal Sales / Store Area Analysis:  Step 1:  Core Query
SELECT
{[Measures].[Store Invoice]} ON COLUMNS,
    {[Store].[Store Name].Members} ON ROWS
FROM
    Warehouse
WHERE 
     ([Time].[Year].[1998]) 

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


Illustration 1: Initial Results DataSet (Partial View)

We begin with a core set of axes and the one "naturally" occurring measure, Store Invoice. Now let's move into the creation of the calculated members that will provide the other two requested measures, Store Area (we will call our calculated member "ApproxSqFootage") and Sales per Square Foot ("SalesPerSqFt"). We will present these alongside the existing measure, by taking the following steps:

8.  Select File -> Save As..., name the file MDX27-1, and place it in a meaningful location. Leave the file open for the next step.

9.  Replace the comment line with the following:

-- MDX27-2: Internal Sales / Store Area Analysis: Step 2:  Add Calc Members

10.  Save the file as MDX27-2, to protect MDX27-1.

11.  Insert the following syntax, beginning one line below the new comment line:


WITH MEMBER
    [Measures].[ApproxSqFootage]
AS
    'IIF([Store].CurrentMember.Level.Name = "Store Name", 
         Val([Store].CurrentMember.Properties("Store Sqft")), 0)'
MEMBER
     [Measures].[SalesPerSqFt]
AS
     '[Measures].[Store Invoice] / [Measures].[ApproxSqFootage]'

The purpose of the first WITH MEMBER section of the query is to create a calculated member to provide ApproxSqFootage for each Store location. We obtain this value from a member property, called Store Sqftb that exists in the Store Name level in the Store Dimension of the Warehouse cube, as depicted in Illustration 2.


Illustration 2: Cube Editor (Partial) View, Showing Store Name Member Property

The IIF() statement that comprises the definition of the ApproxSqFootage calculated member uses the numeric, versus string, option for returned values. (We discussed these options in String / Numeric Functions: Introducing the IIF() Function). Because member properties, by definition, are strings (even when storing numeric information), the string must be converted to a number for the function to allow us the "0" in the second return value expression in the function.

The purpose of the second WITH MEMBER section of the query is to create a calculated member to provide SalesPerSqFt for each Store location. This value is simply defined as Store Invoice divided by the new ApproxSqFootage value.

12.  Insert the following syntax, between [Measures].[Store Invoice] and the closing bracket of the ON COLUMNS line (first line under the SELECT keyword of the query:

,[Measures].[ApproxSqFootage],[Measures].[SalesPerSqFt]

NOTE: Be sure to insert the comma after [Measures].[Store Invoice], and before the added syntax, so that a comma separates the three measures. Placement of commas is important within later steps as well, where one appears in a similar placement to that in Step 12 above.

We are simply adding the two new calculated members to the SELECT clause to facilitate their retrieval in our query. Our modified query appears, with the modified sections in red rectangles, as shown in Illustration 3.


Illustration 3: Modified Query in Query Pane (with Modifications in Rectangles)

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

Analysis Services populates the Results pane, and the DataSet depicted in Illustration 4 appears.


Illustration 4: Results DataSet with New Calculated Members

While it appears that the columns we wish to present have materialized, our calculated members do not appear to be presenting the expected data in every case. First, we see the dreaded 1.#INF in several of the SalesPerSqFt fields, an indicator, in this case, of a "divide by zero" condition.

Next, we see that some of the ApproxSqFootage fields are populated by zeros - not that our function is not working: it certainly is, or these would be nulls. However, the zeros indicate "no square footage," for whatever reason, captured for the associated Stores, and our instructions from the information consumers clearly stated that they were interested in presenting "only operating store locations, which had invoice activity / area values for year 1998." (One of the locations in our current DataSet, HQ, is obviously an "overhead account" included in the Stores dimension for some other reason - a reason that, while perfectly appropriate within another context, does not apply from the perspective of our immediate criteria.)

Finally, it is obvious that the Stores are not ordered by SalesPerSqFt in descending order, another request of the intended audience. In cases like this, I usually consider sorting, as well as formatting, in a later step anyway, because I like to get the population of the DataSet correct first. We will remedy the other issues at this point, and then manage the ordering and formatting next.

Let's resolve the "holes" in the data first. We can do this quite easily with the highly useful FILTER() function, as we shall see in the next steps.

14.  Select File -> Save to resave file MDX27-2 in the location chosen earlier. Leave the file open for the next step.

15.  Replace the comment line with the following:

-- MDX27-3 Internal Sales / Store Area Analysis: Step 3: Filter Empties  
   and Zeros

NOTE: Be sure to comment out the second line if the above text "wraps" in the Sample Application. Mine did not, in this or later steps, as we can see in the associated illustrations; the "wrap" in the text above is a function of page margins for this article.

16.  Save the file as MDX27-3, to protect MDX27-2.

17.  Insert the following syntax, immediately within the left curly brace ("{") on the ON ROWS line of the SELECT clause,

FILTER(

between the brace and [Store].[Store Name].Members .

18.  Insert the following syntax, immediately to the right of [Store].[Store Name].Members, within the ON ROWS line of the SELECT clause,

, ([Measures].[ApproxSqFootage] > 0))

We are placing the FILTER() function within the curly braces, and placing [Store].[Store Name].Members into the FILTER() function, as the set, or first entry, which is followed by the search condition ([Measures].[ApproxSqFootage] > 0)).

NOTE: For a detailed introduction to the FILTER() function, see my Database Journal article MDX Essentials: Basic Set Functions: The Filter() Function.

Our modified query appears, with the modified sections in red rectangles, as shown in Illustration 5.


Illustration 5: Modified Query in Query Pane (with Modifications in Rectangles)

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

The Results pane is populated with the DataSet depicted in Illustration 6.


Illustration 6: Results DataSet with FILTER() in Place

As we can see in the illustration above, our filter seems to have done the trick. The population of the DataSet appears to fit the business requirements. All that remains is ordering, and a couple of cosmetic touches, to please the intended audience.

20.  Select File -> Save to resave file MDX27-3 in the location chosen earlier. Leave the file open for the next step.

21.  Replace the comment line with the following:


MDX27-4 Internal Sales / Store Area Analysis: Step 4: Order DataSet / 
     Formats

22.  Save the file as MDX27-4, to protect MDX27-3.

23.  Insert the following syntax, immediately within the left curly brace ("{") on the ON ROWS line of the SELECT clause,

ORDER(

between the brace and FILTER (inserted in the last set of modifications).

24.  Insert the following syntax, immediately to the right of ([Measures].[ApproxSqFootage] > 0)), which we added as part of the FILTER() function in the last set of modifications, within the ON ROWS line of the SELECT clause,

 , ([Measures].[SalesPerSqFt]), BDESC)

We are placing the ORDER() function within the curly braces, and placing the entire FILTER() function, put into place in our last set of steps, into the ORDER() function, as the set, or first entry, which is followed by the "order by" numeric expression and "direction." (We are breaking the hierarchy in our descending sort, to make the Stores sort in accordance with the requirements of the information consumers), ([Measures].[SalesPerSqFt]), BDESC).

NOTE: For a detailed introduction to the ORDER() function, see my Database Journal article MDX Essentials: Basic Set Functions: The Order() Function.

Drawing close to the final version of our query, we have only to add formatting for our new calculated members, to make the data easier to understand for its eventual audience. We will accomplish this using the FORMAT_STRING, which we have exposed in several other articles of the series, as follows:

25.  In the line under the AS keyword, (the line that is enclosed in single apostrophes), within the first WITH MEMBER clause, where we define the first calculated member, ApproxSqFootage, that is:


'IIF([Store].CurrentMember.Level.Name = "Store Name",   
     Val([Store].CurrentMember.Properties("Store Sqft")), 0)'

insert a comma (",") at the end of the line.

26.  Insert the following in a new line, just beneath that in which we inserted the comma in the last step:

FORMAT_STRING = '#,##0'

27.  In the line under the AS keyword, (the line that is enclosed in single apostrophes), under the second MEMBER keyword , where we define the second calculated member, SalesPerSqFt, that is:

     '[Measures].[Store Invoice] / [Measures].[ApproxSqFootage]'

insert a comma (",") at the end of the line.

28.  Insert the following in a new line, just beneath that in which we inserted the comma in the last step:

FORMAT_STRING = '$#,##0.00'

Within the definitions of both calculated members, we are simply inserting FORMAT_STRING to assign user-friendly formats (round numbers, with a comma thousands separator, for square footage Store Area, and currency presentation for the Sales per Square Feet value).

Our modified query appears, with the altered sections in red rectangles, as shown in Illustration 7.


Illustration 7: Modified Query in Query Pane (with Changes in Rectangles)

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

The Results pane is populated with the DataSet depicted in Illustration 8.


Illustration 8: Results DataSet with ORDER() and Formatting Additions

A review of the DataSet at this stage reveals that we have succeeded in meeting the requirements expressed by the information consumers. While there are multiple ways to approach the delivery and presentation of a DataSet such as this, we have seen that, with only a few sequential steps, we can evolve a large data population into precisely the presentation that the intended audience requires. At the heart of our success in this example is the IIF() function, which, in conjunction with the VBA VAL() function and member properties, has facilitated the core data upon which the rest of the presentation depends.

30.  Select File -> Save to resave the file MDX27-4 in its designated location.

Summary ...

In this article, we continued our examination of the IIF() function, which we began in String / Numeric Functions: Introducing the IIF() Function. Our goal in this article was to focus on additional uses of the function in a more involved practice scenario, whereby we could explore new considerations (such as the use of IIF() with member properties), while reinforcing concepts that we exposed in our introductory article. To achieve our objectives, we undertook a multi-step practice example aimed at providing a requested presentation of a specified DataSet, to support of the illustrative business requirements of a hypothetical group of information consumers.

After gaining an understanding of the business needs of the intended audience, we created a core query. We then sequentially evolved the specified presentation of the requested DataSet, discussing, along the way, the functions and methods we used and the results that we obtained from each. At the core of our solution lay the IIF() function, used in conjunction with a member property, whose data type we converted to make it useful in the context of the business needs of the consumers. We then filtered, ordered, and formatted our presentation to further comply with the requirements outlined.

» 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