Logical Functions: IsGeneration(): Conditional Logic within Calculations

Monday May 7th 2007 by William Pearson
Share:

Use IsGeneration() to support conditional logic within calculations. BI Architect Bill Pearson introduces IsGeneration(), and then leads a hands-on practice session with this useful MDX function.

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: Current updates are assumed for MSSQL Server, MSSQL Server Analysis Services, and the related Books Online and Samples.

Overview

It should come as no surprise, to anyone who works with MDX on a regular basis, that, in working with multidimensional data, it is not uncommon to find ourselves confronted with business needs to have some calculated members give different results, depending upon “where in the cube” they are being evaluated. It is in these scenarios where we find great utility in leveraging the combination of conditional logic and the properties of sets, tuples, hierarchies, levels and / or members to determine the “evaluation location” of the member(s) of interest. Based upon the location that is ascertained, we can render the formula required to give the result we need for the given “position.”

We have seen this concept in action in several articles within my MDX Essentials series. We can test whether the current member is positioned at, above or below a given level by using the IS operator, or a function that integrates the IS operator, such as IsSibling() or IsAncestor(). We can also use other approaches, such as comparing the level ordinal of a member to the ordinal of another level of interest (via the .Ordinal function), or by comparing a given member’s ancestor to an ancestor of interest (through a combination of the Ancestor() and .UniqueName functions, among other possible approaches).

NOTE: For a detailed look at the .CurrentMember function, see MDX Member Functions: "Relative" Member Functions. For a hands-on introduction to the IS operator, see The IS Operator. For information about the IsSibling() function, see Logical Functions: IsSibling(): Conditional Logic within Calculations and Logical Functions: IsSibling(): Conditional Logic within Filter Expressions.

For more about the IsAncestor() function, see Logical Functions: IsAncestor(): Conditional Logic within Calculations and Logical Functions: IsAncestor(): Conditional Logic within Filter Expressions. Moreover, for a hands-on introduction to the Ancestor() function, see MDX Member Functions: The "Family" Functions. Finally, for details surrounding the .UniqueName function, see String Functions: The .UniqueName Function.

(All articles are members of my MDX Essentials series at Database Journal.)

In this lesson, we will expose another logical function which we can use for testing a member for which a cell is being calculated, the IsGeneration() function. IsGeneration(), like other logical functions and operators, performs an evaluation and returns a Boolean value. The utility of IsGeneration() becomes clear when we realize the capability that it gives us to determine the “position,” together with the relationship to progenitors, of a member within a dimensional hierarchy. IsGeneration() more specifically allows us to test whether or not a specified member exists within a specified generation. (The effective use of IsGeneration() as a testing mechanism is dependent upon a good understanding of the manner with which generation numbers are assigned within Analysis Services. For this reason, we will overview the generation concepts in our preliminary commentary surrounding the function.)

Similar to IsLeaf(), IsSibling(), IsAncestor() and other MDX functions, IsGeneration() can best be employed to apply conditional logic within a couple of ways: as a component within a calculation, and as a component within a filter expression. In this article, we will concentrate upon IsGeneration() from the perspective of its use within a calculation. We will discuss the straightforward purpose of the function, to ascertain (and indicate) whether a member is the ancestor of another specified member; the manner in which IsGeneration() accomplishes this; and ways we can leverage the function to support effective conditional logic to meet various business needs within our own environments.

Along with an introduction to the IsGeneration() function, and a brief discussion of generation number assignment, this lesson will include:

  • an examination of the syntax surrounding the function;
  • illustrative examples of uses of the function within practice exercises;
  • a brief discussion of the MDX results obtained within each of the practice examples.

The IsGeneration() Function

Introduction

According to the Books Online, the IsGeneration() function “returns true if the member indicated ... is in the generation specified....” Otherwise, the function returns false. Also, if the member we specify evaluates to an empty member, the IsGeneration() function returns false. A Boolean value of “True” is returned if the member to which the function is applied resides within the generation number specified by the Numeric Expression we provide, and “False” is returned if it does not (or if the member expression we supply identifies an empty member).

Part of understanding the operation of IsGeneration() centers upon an understanding of how generation numbers are assigned (since the number is used as the “comparison target” within the function, which determines the result of a comparison test to be “true” or “false”). The assignment of generation numbers actually begins with leaf-level members, who are assigned a zero (“0”) generation. The assignment of a generation number to a non-leaf level member is based upon a rule of “one" (“1”) plus the number of levels existing between the leaf level of a given hierarchy and the parent of the tree – in effect, it is the “number of steps away” from the leaf level members of the hierarchy which it inhabits. To illustrate the concept, a straightforward hierarchy (together with assigned generation numbers), Date.Calendar, from the sample Adventure Works cube, is partially depicted in Illustration 1.


Illustration 1: Partial View of the Date.Calendar
Hierarchy (Generation Numbers in Red ....)

Things can become somewhat more complicated, of course, in cases of ragged hierarchies, where parent visibility becomes important (only visible members factor into the generation number assignment, for example). We will stay within the bounds of balanced hierarchies for the purposes of our examples, but it is important to remember that this is often a consideration in the real world.

We will examine in detail the syntax for the IsGeneration() function after our customary overview in the Discussion section that follows. Following that, we will conduct practice examples within a couple of scenarios, constructed to support simple, hypothetical business needs that illustrate a use for the function. This will afford us an opportunity to explore some the basic options that IsGeneration() can offer the knowledgeable user. Hands-on practice with IsGeneration(), where we will create queries that employ the function, will help us to activate what we have learned in the Discussion and Syntax sections.

Discussion

To restate our initial description of its operation, IsGeneration() returnsTrue” if a specified Member Expression is the number of “steps” specified (by the Numeric Expression) away from the leaf level of the containing hierarchy. Alternatively, “False” is returned if the specified Member Expression is not the specified number of steps away, or if the Member Expression evaluates to an empty member. We can use IsGeneration() to apply conditional logic based upon the location and / or existence of members. As we have noted to be the case with most MDX functions, pairing IsGeneration() with other MDX functions can help us to leverage its power much further than we might in attempts to use it in standalone fashion.

Let’s look at syntax specifics to further clarify the operation of IsGeneration().

Syntax

Syntactically, we employ the IsGeneration() function by specifying the Member Expression (the member which we are testing as to “generation member status”) and the Numeric Expression (the generation number in relation to which we are testing the Member Expression) within parentheses to the immediate right of the function. The function takes the Member Expression and Numeric Expression thus appended as its arguments, and returns True if the member denoted by the Member Expression exists within the specified generation number (Numeric Expression) of the hierarchy within which it resides (or, in other words, if the Member Expression lies the number of steps specified by the Numeric Expression from the leaf level of the containing hierarchy.

If the member specified by the Member Expression is evaluated as an empty member, or if the Member Expression does not exist within the generation number specified by Numeric Expression, then a False is returned, as we have noted.

The general syntax is shown in the following string:

IsGeneration(Member_Expression, Numeric_Expression) 

Employing IsGeneration() is as straightforward, in the mechanical sense, as working with most of the MDX logical functions, assuming that we have an adequate grasp of the meaning of generation, as we have discussed in earlier sections . As we have noted, we simply place the Member Expression and Numeric Expression, respectively, in the parentheses to the right of the function. As an example, and as a confirmation of some of the details of the generation information presented in Illustration 1 above, within a query executed against the sample Adventure Works cube, for the dimension named Date (with a hierarchy of Calendar), the following pseudo-expression:

IsGeneration([Date].[Calendar].CurrentMember, 4)

returns True for the current member of the Date dimension / Calendar hierarchy for each of the following:

  • CY 2001
  • CY 2002
  • CY 2003
  • CY 2004

Each of the listed members is a “resident” of generation number 4 of the Date.Calendar hierarchy – which appears among the other levels as noted in Illustration 2.


Illustration 2: Generation Number 4 among the Other Generations of the Hierarchy ...

Depending upon the structure of the query (and specifically upon whether the syntax defining axes, etc., eliminates nulls), if members of other hierarchies, or members of generations other than number 4, within the hierarchy, were returned in, say, the row axis of the dataset, their associated values would be null.

We will practice some uses of the IsGeneration() function in the section that follows.

Practice

Preparation: Access SQL Server Management Studio

To reinforce our understanding of the basics we have covered, we will use the IsGeneration() 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 IsGeneration() 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:

Prepare MSSQL Server Management Studio to Query Analysis Services

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 analysis task that has been discussed at a recent meeting with the Controllers.

The analysts tell us that the values under immediate consideration involve Internet Order Counts, but, as is typically the case in our collaborative sessions, they want to develop an approach that will work equally well with other measures that have similar analysis potential. (As we have noted in other sessions of our series, our client colleagues often derive parameterized queries in Reporting Services from the basic MDX syntax we assemble together, and can thus create self-serve reports that allow information consumers to dictate what measure they wish to analyze, and myriad other options, at run time.) The desired end is to simply return the Internet Order Count recorded for each of the four operating calendar years contained within the Adventure Works cube.

As is often the case, this basic need might be easily met a number of ways with an MDX query. The analysts throw a further twist into the requirement, however: In addition to being likely to parameterize the calendar year, and perhaps other date specifics at runtime, they also want to be able to support parameterization of the level within the Date dimension (Calendar hierarchy) when executing the report (that is, to be able to change it from calendar year to a lower level, such as a quarter of a month, for example – and thus to “narrow” the member selection that appears within a given iteration of the report results, producing something akin to a selective “drilldown” effect.) Once 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 concepts 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 IsGeneration() function as one 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 lies within a given generation number (or numbers) of a specific dimensional hierarchy is something that they hope to be able to extrapolate to uses within other dimensions, as well. (As we note often within the MDX Essentials series, time / date dimensions are always good “starters” for introducing new functions. The relationships between the various levels are familiar to everyone, whereas the structures of other dimensions might not lend themselves to population accuracy and completeness “reasonability” testing undertaken by those not entirely knowledgeable of the corporate structure, geography, and so forth.)

We offer to illustrate the use of IsGeneration() 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 IsGeneration().

Procedure: Use the IsGeneration() 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 IsGeneration() function within a common context, the definition of a calculation based upon conditional logic. Our initial example will serve as an introduction to a means of ascertaining the presence of a group of members of the Date dimension / Calendar hierarchy (in the immediate case, the calendar years present within the cube), as requested by the analysts. This will serve as a basis for meeting the business requirement to present the simple Internet Order Counts at the desired levels.

1.  Type (or cut and paste) the following query into the Query pane:


-- MDX055-001 ISGENERATION() Function: Conditional Logic in 
--   the Definition of a Calculation
WITH 
MEMBER
   [Measures].[InternetSelectCount]
AS
   IIF(
      ISGENERATION([Date].[Calendar].CURRENTMEMBER,4),
         [Measures].[Internet Order Count],
      NULL)
SELECT
   {[Measures].[InternetSelectCount]}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 3.


Illustration 3: Our Initial Query in the Query Pane ...

The above query returns the Internet Order Count for each member of the Date dimension (Calendar hierarchy) in the cube; we use Non Empty to physically screen the results to show our “focus” Calendar Years. (Any row axis member existing outside generation 4 of the Date.Calendar hierarchy would evaluate to null with respect to the measure we specified in the column axis). Obviously, had we not inserted the Non Empty keyword, we would get all members of the Date dimension, Calendar hierarchy, with those non-generation-number-4 members simply indicating null as a measure value.

Recall that we have said that we might accomplish our ends through alternative methods. The approach we are taking here allows us to parameterize either or both of the Member Expression and Numerical Expression, to accomplish the extended ends of our client colleagues. In doing so, we could set up a hierarchical picklist within Reporting Services, whereby information consumers might select a given date, month, quarter, and so forth, to drive the level whose members’ values are returned, among other possibilities. The obvious advantage is that consumers can dictate the generation number (and, in effect, the dimensional level) upon whose members the “focus” is enacted. In some circumstances, “double leverage” could be provided by modifying the query to do more within a single parameter – which might be seen as a desirable efficiency within the realm of simulated dynamic drilldown effects and so forth.

In the Internet Select Count calculation, we put the IsGeneration() function to work in applying conditional logic to generate the Internet Order Count value: if the current member of the Date dimension / Calendar hierarchy (the Member Expression of our function) resides within the specified generation number (the Numeric Expression of “4,” which we have supplied within the function), then the corresponding Internet Order Count value is presented. Alternatively, we have directed (via the conditional logic of the IIF() function), that if the Member Expression (the current member of the Date.Calendar hierarchy) does not reside within the specified generation number (“4” ), then the Internet Order Count value is returned as null. (Moreover, as we have noted, 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.)

NOTE: For more detail surrounding the IIF() function, see String / Numeric Functions: Introducing the IIF() Function and String / Numeric Functions: More on the IIF() Function. For more about the .CurrentMember function, see MDX Member Functions: "Relative" Member Functions. All articles are members of the Database Journal MDX Essentials series.

For more information about, and hands-on practice with, the sort of parameterization of MDX queries to which I refer within this article, see various member articles of my MSSQL Server Reporting Services series.

2.  Execute the query by clicking the Execute button in the toolbar, as shown in Illustration 4.


Illustration 4: Click Execute to Run the Query...

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


Illustration 5: Results Dataset – IsGeneration() Function within a Calculation

In the partial view of the returned dataset, we see that the calculation accomplishes the intended purpose - generating the Internet Order Count for the individual Date.Calendar hierarchy members that belong to generation number 4 (the Calendar Year level). Again, the conditional test of generation membership is applied via a calculated member within which we have leveraged the IsGeneration() function.

3.  Select File -> Save MDXQuery1.mdx As ..., name the file MDX055-001, and place it in a meaningful location.

Our client colleagues express satisfaction with the contextual backdrop we have established for introducing the IsGeneration() function. We will next use a similar query within another such example, to confirm understanding of the concepts. This query will provide an illustration of the use of the IsGeneration() function within the context we have already seen, the definition of a calculated member based upon a comparison. As before, we will base our example upon a local, albeit slightly more sophisticated, scenario posed by the client representatives. Our client colleagues initially posed this scenario in a previous session: In working with the IsLeaf() function, we addressed a need to understand a means, within MDX, of distinguishing leaf-level members within the Date dimension of the Adventure Works cube, from the general membership of the dimension.

In recalling the requirement, the analysts tell us that, among other averages under immediate consideration, one need involves Sales Order Counts. The desired end is a simple average, to be calculated by averaging the number of Sales Orders recorded on the first day of each operating month (typically a dramatically larger number of Sales Orders are taken on the first day), and the number of Orders taken on the last day of the same month. The analysts emphasize that they are aware that more precise averages can be generated. This simple average is adequate for the limited purpose for which it is designed, they tell us, much as similar “quick and dirty” calculations have heretofore been performed manually within the realms of headcount and inventory.

The developers / authors within the group cite the foregoing example as useful, once again, in the exploration of a function with which they are becoming familiar. They would like to extend their understanding of the IsGeneration() function to include its use in identifying leaf members within a selected hierarchy, much as we accomplished together with the IsLeaf() function in my Database Journal articles MDX Operators: The IsLeaf() Operator: Conditional Logic within Calculations and MDX Operators: The IsLeaf() Operator: Conditional Logic within Filter Expressions. Per the request of our client colleagues, we will construct a simple query to provide an illustration of the use of the IsGeneration() operator, once again within the definition of a calculation based upon conditional logic, to meet this end.

4.  Press key combination CTRL + N, to open a tab for a new query within the current Analysis Server connection.

5.  Type (or cut and paste) the following query into the Query pane:


-- MDX055-002 IsGeneration() Operator:  Conditional Logic in Definition of a Calculation
WITH 
MEMBER
   [Measures].[BegCount]
AS
  
   IIF(ISGENERATION([Date].[Calendar].CURRENTMEMBER, 0),
       [Measures].[Order Count],
   ([Date].[Calendar].CURRENTMEMBER.FIRSTCHILD, [Measures].[BegCount]))
  SELECT
   {[Measures].[BegCount]}ON COLUMNS,
   {[Date].[Calendar].MEMBERS} ON ROWS
FROM 
   [Adventure Works]

The Query pane appears, with our input, as shown in Illustration 6.


Illustration 6: Our Second Practice Query in the Query Pane ...

The above query supplies the Order Count for each descendant of each Calendar Month in the cube, and, in effect, the basis for the simple average that our client colleagues have requested. Within the calculation involved, we put the IsGeneration() function to work – much as we did IsLeaf() in the first of the two articles we referenced earlier - to apply conditional logic to generate the Order Count value, if the Current Member of the Date dimension / Calendar hierarchy is a leaf-level member (meaning a day / date).

The expression IsGeneration([Date].[Calendar].CurrrentMember, 0), is equivalent to IsLeaf([Date].[Calendar].CurrrentMember). We have directed, via the conditional logic of the IIF() function, that if the Current Member is not a leaf-level member, then the Order Count for the First Child member of the Date dimension (Calendar hierarchy), at the level of the Current Member, applies.

NOTE: See Member Functions: More "Family" Functions, within the Database Journal MDX Essentials series, for more information about the .FirstChild function.

We have specified that the Calendar Date members are to populate the rows axis. This provides, to some extent, a quick means of reasonability testing of the logic within the calculation that we have defined, as we shall see.

6.  Execute the query by clicking the Execute button in the toolbar, as shown earlier.

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


Illustration 7: Results Dataset (Partial View) – IsGeneration() Function within a Calculation

In the partial view of the returned dataset, we see that the calculation accomplishes the intended purpose – generating the Order Count for the individual dates (the leaf level - or generation number0,” for the Date dimension / Calendar hierarchy), while returning the Order Count for the first child member of the Date dimension / Calendar hierarchy, at the level of each current member, for the members that are not at the leaf level.

7.  Select File -> Save MDXQuery2.mdx As ..., name the file MDX055-002, and place it in a meaningful location.

In the returned dataset, we see that the query appears to meet the business requirements outlined by the client analysts and developers. We have delivered a simple calculation, based upon conditional logic which does different things based upon the “generational membership” of the current member within the Date dimension – Calendar hierarchy. Specifically, for any Date.Calendar member that belongs to the leaf level (generation number 0”), the standard measure Order Count is returned, while for any non-leaf member, calculated measure Beg Count is returned.

Our calculation employs the IsGeneration() function, much in the same manner as we have employed and explained it in our first example: it supports conditional logic to determine the specified “focus” members of the Date dimension / Calendar hierarchy, and then selects the appropriate measure to retrieve, based upon the outcome of this test. We can see each of the Date.Calendar members in the rows axis, alongside the values returned within the measures column of the returned data set, making it easy to verify that our calculations are performing as expected.

The client representatives confirm that the immediate goal of the practice example has been met: the creation of a calculation which is dictated by the IsGeneration() function in a manner that lends itself to the parameterization opportunities that are expected to arise at the reporting layer. Moreover, they state that the illustration we have provided will be easily extrapolated to other scenarios where they need to perform an action, or to present a value, based upon the outcome of a test as to whether or not a given dimension / hierarchy member resides within a specified generation number.

8.  Select File -> Exit to leave the SQL Server Management Studio, when ready.

Summary ...

In this article, we exposed another logical function contained within the MDX toolset, the IsGeneration() function, whose general purpose, we learned, is to return a value indicating whether or not a member that we specify resides within a generation number that we also specify. We noted that, similar to other logical functions, IsGeneration() can best be employed to apply conditional logic in a couple of ways: as a component within a calculation, or as a component within a filter expression. In this article, we concentrated upon IsGeneration() from the perspective of its use within a calculation. We discussed the straightforward purpose of the function, the manner in which IsGeneration() manages to accomplish its purpose, and ways we can leverage the function to support effective conditional logic to meet various business needs within our own environments.

After introducing IsGeneration(), we examined the syntax with which we employ the function. We then undertook illustrative examples whereby we put the IsGeneration() function to work, within a couple of simple illustrations, to meet the business needs of a hypothetical client. Throughout our practice session, we briefly discussed the results datasets we obtained from each of the queries we constructed.

» 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