Logical Functions: IsGeneration(): Conditional Logic within Calculations - Page 3

May 7, 2007

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.

MDX Essentials Series
The LEVEL_NUMBER Member Property
The LEVEL_UNIQUE_NAME Intrinsic Member Property
Intrinsic Member Properties: The HIERARCHY_UNIQUE_NAME Property
Intrinsic Member Properties: The DIMENSION_UNIQUE_NAME Property
Further Combination of BottomCount() with Other MDX Functions
Combine BottomCount() with Other MDX Functions to Add Sophistication
Basic Set Functions: The BottomCount() Function, Part I
Intrinsic Member Properties: The MEMBER_VALUE Property
Intrinsic Member Properties: The MEMBER_UNIQUE_NAME Property
Intrinsic Member Properties: The MEMBER_NAME Property
Intrinsic Member Properties: The MEMBER_KEY Property
Intrinsic Member Properties: The MEMBER_CAPTION Property
Set Functions: The StripCalculatedMembers() Function
Set Functions: The AddCalculatedMembers() Function
MDX Numeric Functions: The Min() Function
MDX Numeric Functions: The Max() Function
Set Functions: The .AllMembers Function
MDX Essentials: Set Functions: The MeasureGroupMeasures() Function
String Functions: The .Properties Function, Part II
String Functions: The .Properties Function
Logical Functions: IsGeneration(): Conditional Logic within Filter Expressions
MDX Scripting Statements: Introducing the Simple CASE Statement
Logical Functions: IsGeneration(): Conditional Logic within Calculations
Logical Functions: IsAncestor(): Conditional Logic within Filter Expressions
MDX Clauses and Keywords: Use HAVING to Filter an Axis
Logical Functions: IsAncestor(): Conditional Logic within Calculations
Logical Functions: IsSibling(): Conditional Logic within Filter Expressions
Logical Functions: IsSibling(): Conditional Logic within Calculations
MDX Operators: The IsLeaf() Operator: Conditional Logic within Filter Expressions
MDX Operators: The IsLeaf() Operator: Conditional Logic within Calculations
MDX Numeric Functions: The .Ordinal Function
Other MDX Entities: Perspectives
MDX Operators: The IS Operator
MDX Set Functions: The Distinct() Function
MDX Set Functions: The ToggleDrillState() Function
Set Functions: The DrillUpLevel() Function
Set Functions: The DrillDownLevelTop() and DrillDownLevelBottom() Functions
MDX Set Functions: DrillDownLevel()
MDX Set Functions: The DRILLUPMEMBER() Function
MDX Essentials: Set Functions: The DRILLDOWNMEMBERTOP() and DRILLDOWNMEMBERBOTTOM() Functions
MDX Essentials : Set Functions: The DRILLDOWNMEMBER() Function
MDX Essentials: Drilling Through with MDX: The DRILLTHROUGH Statement
MDX Essentials: String Functions: The .UniqueName Function
MDX Essentials: String Functions: The .Name Function
MDX Essentials: String / Numeric Functions: The CoalesceEmpty() Function
MDX Essentials: Basic Set Functions: The TopCount() Function, Part II
MDX Essentials: Basic Set Functions: The TopCount() Function, Part I
MDX Essentials: Enhancing CROSSJOIN() with Calculated Members
MDX Essentials: Set and String Functions: The GENERATE() Function
MDX Essentials: The CROSSJOIN() Function: Breaking Bottlenecks
MDX Essentials: String / Numeric Functions: More on the IIF() Function
MDX Essentials: String / Numeric Functions: Introducing the IIF() Function
MDX Essentials: Logical Functions: The IsEmpty() Function
MDX Essentials: Basic Set Functions: The EXTRACT() Function
MDX Essentials: Numeric Functions: Introduction to the AVG() Function
MDX Essentials: Basic Member Functions: The .Item() Function
MDX Essentials: Basic Set Functions: Subset Functions: The Subset() Function
MDX Essentials: Basic Set Functions: Subset Functions: The Tail() Function
MDX Essentials: Basic Set Functions: Subset Functions: The Head() Function
MDX Essentials: Basic Set Functions: The CrossJoin() Function
MDX Essentials: Basic Numeric Functions: The Count() Function
MDX Essentials: Basic Set Functions: The Filter() Function
MDX Essentials: Basic Set Functions: The EXCEPT() Function
MDX Essentials: Basic Set Functions: The Intersect() Function
MDX Essentials: Basic Set Functions: The Union() Function
MDX Essentials: Basic Set Functions: The Order() Function
MDX Essentials - MDX Time Series Functions, Part III: The LastPeriods() and ParallelPeriod() Functions
MDX Time Series Functions, Part II: The OpeningPeriod () and ClosingPeriod() Functions
MDX Essentials - MDX Time Series Functions, Part I: PeriodsToDate() and Kindred Functions
MDX Essentials: MDX Member Functions: "Relative" Member Functions
MDX Member Functions: The Cousin () Function
MDX Essentials: Member Functions: More "Family" Functions
MDX Member Functions: The "Family" Functions
MDX Essentials: MDX Members: Introducing Members and Member
MDX Essentials : MDX Operators: The Basics
MDX Essentials: Structure of the MDX Data Model
MDX at First Glance: Introduction to SQL Server MDX Essentials








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers