Logical Functions: IsGeneration(): Conditional Logic within Filter Expressions

Tuesday Aug 7th 2007 by William Pearson
Share:

Use IsGeneration() to support conditional logic within filter expressions. BI Architect Bill Pearson looks beyond employing IsGeneration() in calculations, and provides hand-on practice in its use within the MDX Filter() 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

In Logical Functions: IsGeneration(): Conditional Logic within Calculations, another article within my MDX Essentials series, we introduced the IsGeneration() function from the perspective of its use within a calculation. We discussed the straightforward purpose of this logical function: IsGeneration() provides us a means of testing whether or not a specified member exists within a specified generation. As a part of introducing the function, we noted that 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 overviewed the generation concepts, within our preliminary commentary surrounding the function, in Logical Functions: IsGeneration(): Conditional Logic within Calculations.

In this article, we will examine IsGeneration(), once again as a conditional logic modifier, but within the context of a filter. Combining IsGeneration() with the MDX Filter() function is another way we commonly see it in action in the business environment, and our exposure to the practical aspects of its employment in this way will serve to enhance our overall awareness of the potential of IsGeneration(). From the perspective of the use of the IsGeneration() function in combination with Filter(), this article will include:

  • A review of the general syntax surrounding the function;
  • Illustrative examples of uses of the function in practice exercises;
  • A brief discussion of the MDX results obtained within each of the practice examples.

The IsGeneration() Function

Introduction

As we related in Logical Functions: IsGeneration(): Conditional Logic within Calculations, the Books Online tell us that 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). In its capacity, as a logical function, to “test” the nature / status of a member, IsGeneration() can be employed in conjunction with the IIF() function to conditionally drive the return of data, such as a member or members, or values, based upon the relationship between members as ancestor / descendant. Moreover, as we shall see in this article, IsGeneration() can be employed more directly within a filter expression we place into the specification of an axis within an MDX query.

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 hypothetical business needs that illustrate uses for the function. This will afford us an opportunity to explore some of the basic options that IsGeneration() can offer the knowledgeable user. Our current examination will focus upon the use of IsGeneration() within the context of a filter. 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.

NOTE: For more detail surrounding the Filter() function, see Basic Set Functions: The Filter() Function, a member of my Database Journal MDX Essentials series.

Discussion

To restate our initial description of its operation, IsGeneration() returns “True” 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 beyond what we might otherwise achieve in attempts to use it in standalone fashion.

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

Syntax

Let’s review the syntax involved with employing the IsGeneration() function. We employ IsGeneration() 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)

As we noted earlier, 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, (a topic we discussed in depth in Logical Functions: IsGeneration(): Conditional Logic within Calculations). As we learned, we simply place the Member Expression and Numeric Expression, respectively, in the parentheses to the right of the function. As an example, 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 annotated in Illustration 1.


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

NOTE: For information on several of the “relative” functions, of which .CurrentMember (used in the pseudo-expression above) is an example, see my article MDX Member Functions: "Relative" Member Functions, within the Database Journal MDX Essentials series.

We will practice some uses of the IsGeneration() function, within the context of filtering, 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 in a couple of queries that illustrate its operation, this time focusing on combinations with the MDX Filter() function. We will do so in simple scenarios that place IsGeneration () within the context of meeting basic requirements similar to those we might encounter within 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

Procedure: Satisfy Business Requirements with MDX

We will assume a business scenario within which to base our practice examples, as we consistently do within articles of this series: we have received a request for assistance from representatives of our client, a team of analysts within the Controllers’ Group of the Adventure Works organization. The group informs us that they have determined a further need for our assistance in their use of the IsGeneration() function, which we introduced to them within the scenario described in Logical Functions: IsGeneration(): Conditional Logic within Calculations.

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 request is identical to the requirement we worked to meet in Logical Functions: IsGeneration(): Conditional Logic within Calculations. We will simply employ IsGeneration() in conjunction with a Filter() expression, instead of performing conditional logic within a calculated member, as we did in the referenced article, within this session.)

To repeat the requirement, 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 once again 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. Moreover, instead of using IsGeneration() within a calculated member, the team states that they would like to see IsGeneration() employed more directly, within a filter expression added to the rows axis of a query. They again tell us that IsGeneration(), in its capacity as 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() within a filter expression.

Procedure: Use the IsGeneration() Function to Perform Conditional Logic within a Filter Expression

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

/* MDX057-001 ISGENERATION() Function: In Conjunction 
   
   with FILTER() function */
   

SELECT

   {[Measures].[Internet Order Count]} ON AXIS(0),
         
      {FILTER(
   
        [Date].[Calendar].MEMBERS,
       
           ISGENERATION([Date].[Calendar].CURRENTMEMBER,4))}
        
        ON AXIS(1)

FROM 

   [Adventure Works]

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


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

The above query returns the Internet Order Count for each “generation number 4” member of the Date dimension (Calendar hierarchy) in the cube – which, of course, translates to years.

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 (“[Date].[Calendar]”, and thus the Date hierarchy, and perhaps the dimension itself) and Numerical Expression (“4” in our example – but we could parameterize this to allow, say, selection of different generation numbers, and therefore different levels of the Date hierarchy, in a report, etc.), to accomplish the extended ends of our client colleagues. To further detail this illustration, 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 our query, we put the IsGeneration() function to work, within a filter expression, 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.

NOTE: 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 3.


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

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


Illustration 4: Results Dataset – IsGeneration() Function in Conjunction with Filter()

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 test of generation membership is applied via an MDX Filter() function, within which we have leveraged the IsGeneration() function.

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

Our client colleagues express satisfaction with the “direct filter” context with which we have demonstrated use of the IsGeneration() function. We will next assemble 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 filter in an axis specification. And, as before, we will base our example upon a local scenario posed by the client representatives (again, a requirement similar to a scenario we managed via a calculated member in Logical Functions: IsGeneration(): Conditional Logic within Calculations.)

To recall the requirement posed by our client colleagues in the earlier session, the analysts told us that, among other averages under immediate consideration, one need involves Sales Order Counts. The current desired end is to simply display the number of Sales Orders recorded on each operating day captured in the cube. For purposes of this exercise, we will simply pull the daily totals (from which averages and other values could be easily calculated, of course), as our colleagues have requested.

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 filter within an axis specification, 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:

/* MDX057-002 ISGENERATION() Function: In Conjunction 
   
   with FILTER() function */  

SELECT

   {[Measures].[Order Count]}ON AXIS(0),
      
      {FILTER(
   
        [Date].[Calendar].MEMBERS,
       
           ISGENERATION([Date].[Calendar].CURRENTMEMBER,0))}
        
        ON AXIS(1)

FROM 

   [Adventure Works]

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


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

The above query supplies the Order Count for each leaf-level descendant of the Date dimension, Calendar hierarchy, and, in effect, the basis for the simple average that our client colleagues have requested. In conjunction with the Filter() function in the row axis specification, we put the IsGeneration() function to work – much as we did IsLeaf() in the first of the two articles we referenced earlier, among others - to generate the Order Count value, when 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 combination of the IsGeneration() and Filter() functions, that for any Current Member that is not a leaf-level member, the Order Count is filtered out. We have thus 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 6 appears.


Illustration 6: 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 filtering out any values for the members that are not at the leaf level.

7.  Select File -> Save MDXQuery2.mdx As ..., name the file MDX057-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 query, based upon a testing mechanism, which filters row axis members 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 Filter() expression allows the Order Count value to be returned; Order Count values for any non-leaf member are filtered out by the same Filter() and IsGeneration() combination in the row specification of the query.

Our calculation employs the IsGeneration() function, much in the same manner as we have employed and explained it in our first example: it supports the Filter() expression in delivering the specified “focus” members of the Date dimension / Calendar hierarchy. 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 filter is performing as expected.

The client representatives confirm that the immediate goal of the practice example has been met: the creation of a filter mechanism which is driven 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 extended our exploration of 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 filter. 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 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