Logical Functions: IsGeneration(): Conditional Logic within Filter Expressions - Page 2

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:

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.

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.

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

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

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.

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.

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 number “0,” 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.