Logical Functions: IsSibling(): Conditional Logic within Calculations - Page 2

Practice

Preparation: Access SQL Server Management Studio

To reinforce our understanding of the basics we have covered, we will use the IsSibling() function within a couple of queries that illustrate its operation, focusing, within this article, upon scenarios where we use the function to support conditional logic within a calculation. (We examine its use in combination with the MDX Filter() function in another article of this series). We will undertake our practice exercises within scenarios that place IsSibling() within the context of meeting basic requirements similar to those we might encounter in our respective daily environments. The intent is to demonstrate the use of the function in a straightforward, memorable manner.

We will turn to the SQL Server Management Studio as a platform from which to construct and execute the MDX we examine, and to view the results datasets we obtain. If you do not know how to access the SQL Server Management Studio in preparation for using it to query an Analysis Services cube (we will be using the sample Adventure Works cube in the Adventure Works DW Analysis Services database), please perform the steps of the following procedure, located in the References section of my articles index:

This procedure will take us through opening a new Query pane, upon which we will create our first query within the section that follows.

Procedure: Satisfy Business Requirements with MDX

Let’s assume, for purposes of our practice example, that we have received a request for assistance from representatives of our client, the Adventure Works organization. Analysts within the Controllers’ Group, with whom we have worked in the past to deliver solutions to meet various reporting and analysis needs, inform us that they have received a request to generate some simple values for a specific trending task that has been discussed at a recent meeting with the Controllers.

The analysts tell us that the values under immediate consideration involve Sales Order Counts, but, as always, that they want to develop an approach that will work equally well with other measures that have similar analysis potential. The desired end is to simply return the Order Count recorded on each day of a given operating month (typically, a dramatically larger number of Sales Orders are taken on the first day). The analysts further advise us that they will likely want to parameterize within the report layer of their business intelligence solution (they use Reporting Services for enterprise relational and OLAP reporting).

While this basic need might be easily met a number of ways with an MDX query, the analysts throw a further twist into the requirement: In addition to being likely to parameterize the month at runtime, they also want to be able to support parameterization of the level within the Date dimension (Calendar hierarchy) when executing the report. Again, the richness of MDX affords us a number of avenues to this objective. While parameterization is itself not a consideration in our current level of query design, we want to make it easy to accomplish within Reporting Services (the same concept would, of course, apply with other OLAP reporting tools that afford developer access to the MDX syntax that underlies them).

After we initially explain the use of the IsSibling() function as a candidate for meeting the requirement, our client colleagues state that they are interested in understanding how they might apply conditional logic via this function, within the context of a practical scenario such as the immediate requirement. A method of testing whether or not a specified member, or group of members, share the same parent as another member we specify is something that they hope to be able to extrapolate to uses with other dimensions, as well.

We offer to illustrate the use of IsSibling() to meet the immediate need, proposing to present a couple of examples, to solidify the analysts’ new understanding, as well as to assist in rounding their overall MDX “vocabularies.” We then set about the assembly of our examples to illustrate uses of IsSibling().

Procedure: Use the IsSibling() Function to Perform Conditional Logic within a Calculation

Per the request of our client colleagues, we will first construct a simple query to provide an illustration of the use of the IsSibling() function within a common context, the definition of a calculation based upon conditional logic. Our first example will serve as an introduction to a means of distinguishing the presence of a sibling relationship between members of the Date dimension (we will work with the month of January in Calendar Year 2004, within our initial example), as requested by the analysts, as a basis for meeting the business requirement to present the simple daily Order Counts.

-- MDX050-001 ISSIBLING()Function: Conditional Logic in 
--   the Definition of a Calculation

WITH 
MEMBER
   [Measures].[SelectCount]
AS
   IIF(
   
      ISSIBLING([Date].[Calendar].CURRENTMEMBER, 
         [Date].[Calendar].[Date].[January 1, 2004]),
      [Measures].[Order Count],
   
   NULL)
   
SELECT
   {[Measures].[SelectCount]}ON AXIS(0),
   NON EMPTY{[Date].[Calendar].MEMBERS} ON AXIS(1)
FROM 
   [Adventure Works]

The Query pane appears, with our input, as depicted in Illustration 1.

The above query returns the Order Count for each member of the Date dimension (Calendar hierarchy) in the cube; we use non-empty to screen the results to show our “focus” month, January, 2004, and the siblings of our specified, date-level primary member expression, January 1, 2004.

Recall that we have said that we might accomplish our ends through alternative methods. The approach we are taking here allows us to parameterize the secondary member expression. In doing so, we could set up a hierarchical picklist within Reporting Services whereby information consumers might select a given date, a month, quarter, and so forth, to drive the level for which values are returned. The obvious advantage is that consumers can dictate both the level of the date hierarchy and the specific “focus” member of the hierarchy itself, within the level (in our example, the month for which they wish to display the value for the corresponding children). In some circumstances such “double leverage” provided by a single parameter might be seen as a highly desirable efficiency.

In the SelectCount calculation, we put the IsSibling() function to work in applying conditional logic to generate the Order Count value: if the Current Member of the Date dimension / Calendar hierarchy (the primary member expression of our function) is a sibling of the secondary member expression (in the immediate example, the day / date of January 1, 2004), than the corresponding Order Count value is presented. Alternatively, we have directed, via the conditional logic of the IIF() function, that if the primary member expression (the Current Member of the Date dimension / Calendar hierarchy) is not a sibling of the secondary member expression (that is, the member’s parent is something other than the month of January, 2004), than the Order Count value is returned as null. (While we might have displayed all values, including nulls, we eliminated nulls in our present exercise by preceding the rows specification with the NON EMPTY keyword.)