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

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.

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.

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

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

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:

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

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.