MDX Operators: The IsLeaf() Operator: 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 IsLeaf() operator 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 IsLeaf() 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 operator 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

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 ad hoc reporting and analysis needs, inform us that they have determined a further need for our assistance in their use of the IsLeaf() function, which we introduced to them in MDX Operators: The IsLeaf() Operator: Conditional Logic within Calculations.

Our client colleagues tell us that they need, once again, to understand a means, within MDX, of distinguishing leaf-level members. This time, they need a general way to filter non-leaf-level members from a broader dimension membership that includes many leaf-level members. As an example, they have an immediate need to determine a measure, Reseller Sales Amount, for Calendar Year 2004, for the lowest Sales Territory members within the Sales Territory dimensional hierarchy.

The Sales Territory dimension within the Adventure Works cube contains members at different levels. Reseller Sales Amount is aggregated no lower than the Country level for some territories, while the "lowest level value" exists for one, the United States, at a Regional level (Central, Northeast and Southwest United States, for example). The Sales Territory dimensional structure is shown in Illustration 1.

The Adventure Works analysts tell us that they need to present the Reseller Sales Amount for each territory's lowest level. They wish to do so with a single query, and ask us if, based upon what they have learned about the IsLeaf() function, the same sort of logic might be used in a filter of the Sales Territories within a query crafted to return the Sales information.

We review the concepts behind the IsLeaf() operator that we introduced in our last discussion with our client colleagues, and then we offer to illustrate the use of IsLeaf() to meet the immediate needs. The client representatives acquiesce, and we set about the assembly of our first example to illustrate the use of IsLeaf() in combination with the Filter() function.

Procedure: Use the IsLeaf() Operator 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 IsLeaf() operator within a common context, the definition of a filter based upon conditional logic. Our first example will serve as an introduction to a means of distinguishing leaf-level members within the Sales Territory dimension. This will address the request of the analysts; the results of this determination will form the basis for meeting their business requirement to filter non-leaf members from the dimension for presentation purposes.

/*  MDX049-001-1 IsLeaf() Operator: 
       Conditional Logic within Filter() Function  */
SELECT
   {[Measures].[Reseller Sales Amount]} ON AXIS(0),

   {FILTER(
      [Sales Territory].[Sales Territory].MEMBERS, 
         ISLEAF([Sales Territory].[Sales Territory].CURRENTMEMBER))
 
      }ON AXIS(1)
FROM 
   [Adventure Works]
WHERE 
    [Date].[Calendar].[Calendar Year].[CY 2004]

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

The above query selects the Reseller Sales Amount for all Sales Territory members, filtered by the condition " ... that are leaf-level," as our IsLeaf() function forms the "search condition" of "members at leaf-level."

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

In the returned dataset, we see that the query delivers the intended result: the Reseller Sales Amount is returned for each of the individual Sales Territory members that exist at leaf level. This happens in spite of the fact that "leaf level" means different things for different countries, as we see; the results dataset presents the measure at country level for all countries except the United States, for which it presents the value at the region level. Because the non-US countries do not subanalyze below country level within the Sales Territories dimension, their respective leaf-level values appear at the country level.

Our client colleagues express satisfaction with the example we have provided, and agree with our suggestion that another example will further reinforce their understanding. This time, we suggest, we will derive the MDX to meet a requirement, and then add a Named Set to contain the logic, a practice that can mean flexible reuse of the code in a reporting scenario.

As an illustration, we formulate a business requirement that relates to Sales Associates, one of several Employee groups at Adventure Works. Let's say that we wish to present the Reseller Sales Amount value for individual sales people for Calendar Year 2004. We are given to understand that only Employees involved in Sales have a Reseller Sales Amount associated with them, although the values associated with non-managers – the actual salespeople – are the values with which we are interested. (The values associated with management personnel typically contain "rolled up" values for those sales people within their management spheres as at least part of their totals, so we wish in this case to exclude them).

To paraphrase the requirement, then, we are interested in retrieving the Reseller Sales Amount for employees in the sales department who also reside at the leaf level within the Employee dimension. (While many other Employees reside at the leaf level in this dimension, we confirm our understanding that, since only sales Employees can have an associated Reseller Sales Amount value, it will be sufficient to retrieve leaf-level employees with the associated values; filtering for leaf-level members will also serve the tandem function of eliminating sales managers from consideration.)

We will begin a new query, and build a proposed approach in multiple steps.

A new tab, with a connection to the Adventure Works cube (we can see it listed in the selector of the Metadata pane, once again) appears in the Query pane.

Let's begin with an "intuitive" approach – as a means of crafting a core query, as well as generating a result that will form a basis for contrast between a listing of "all Sales employees with an associated Reseller Sales Amount value" (including the sales managers I mentioned earlier) and our ultimate objective of "leaf-level members of the Sales organization with an associated Reseller Sales Amount value."

-- MDX049-002-1  Initial Attempt at a Solution
        
SELECT
   {[Measures].[Reseller Sales Amount]} ON AXIS(0),
              
   NONEMPTY( {[Employee].[Employees].MEMBERS})ON AXIS(1)
      
FROM
 
   [Adventure Works]
WHERE 
   [Date].[Calendar].[Calendar Year].[CY 2004]

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

The Results pane is, once again, populated by Analysis Services. This time, the dataset partially shown in Illustration 7 appears.

In the returned dataset, we see the unfiltered list of Employees with an associated Reseller Sales Amount value. As we have discussed, these members happen to be sales personnel, but the presented list contains non-leaf level Employees. We can verify this by inspecting the dimensional structure in the Analysis Services Cube Browser, a view of which appears in Illustration 8.

We can see that, while fourteen employees exist at the bottom level (Level 5), a total of twenty members exist when we count higher levels (including the "All" level) within the hierarchy. Our ultimate objective is to deliver the leaf-level members – in this case, the fourteen individuals appearing within Level 5.

Our next step will be to filter the non-leaf members from the Employees listed in the returned dataset. We will do this within the query first, before finalizing the solution by placing the working logic into a Named Set we create for that purpose in the last step.

-- MDX049-002-2  Adding the Filter() / IsLeaf() Combination

      FILTER(

      ISLEAF([Employee].[Employees]))

The complete query is as follows, if cutting and pasting is the preference:

-- MDX049-002-2  Adding the Filter() / IsLeaf() Combination
        
SELECT
   {[Measures].[Reseller Sales Amount]} ON AXIS(0),
              
   NONEMPTY({
   
      FILTER(
   
         [Employee].[Employees].MEMBERS,
   
      ISLEAF([Employee].[Employees]))
      
   })ON AXIS(1)
FROM
 
    [Adventure Works]
WHERE 
   [Date].[Calendar].[Calendar Year].[CY 2004]

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

The Results pane is, once again, populated by Analysis Services. This time, the dataset shown in Illustration 10 appears.

In the returned dataset, we see the now-filtered list of Employees. We can see that the Employees that appear in the returned dataset comprise leaf-level (Level 5, as shown in Illustration 8 above) members with an associated Reseller Sales Amount value.