Logical Functions: IsSibling(): 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 IsSibling() 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 IsSibling() 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:

Procedure: Satisfy Business Requirements with MDX

We will assume a scenario upon which to base our practice example, 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 IsSibling() function, which we introduced to them in IsSibling(): Conditional Logic within Calculations.

Our client colleagues tell us that they need, once again, to understand a means, within MDX, of distinguishing whether or not members are siblings. This time, they need a general way to filter non-sibling members from a broader dimension membership that includes sibling members. As an example, they have an immediate need to determine a measure, Reseller Sales Amount, for Calendar Year 2004, for the Sales Territories member or members that exist, within the Sales Territories dimensional hierarchy, at the same level as the United States.

To review the structure, the Sales Territories 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 Territories dimensional structure is shown in Illustration 1.

The Adventure Works analysts tell us that they need to present the Reseller Sales Amount for the Sales Territories member or members that share the same parent within the Sales Territories dimensional hierarchy as the United States. They wish to do so with a single query, and ask us if, based upon what they have learned about the IsSibling() 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 IsSibling() function that we introduced in our last discussion with our client colleagues, and then we offer to illustrate the use of IsSibling() to meet the immediate needs. The client representatives acquiesce, and we set about the assembly of our first example to illustrate the use of IsSibling() in combination with the Filter() function.

Procedure: Use the IsSibling() 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 IsSibling() 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 sibling members within the Sales Territory dimension. This will address the request of the analysts; the results of this determination will form the basis for illustrating a means for meeting their business requirement to filter non-sibling members from the dimension for presentation purposes.

/*  MDX051-001 IsSibling() Operator: 
       Conditional Logic within Filter() Function  */
SELECT
   {[Measures].[Reseller Sales Amount]} ON AXIS(0),
   {FILTER(
     [Sales Territory].[Sales Territory].MEMBERS, 
        ISSIBLING([Sales Territory].[Sales Territory].CURRENTMEMBER, 
    [Sales Territory].[Sales Territory].[Sales Territory Country].[United States]))
 
  }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 siblings of the Sales Territory Country United States.” Our IsSibling() function forms the “search condition” of “Sales Territory Country members that are siblings of the United States.”

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 the United States and its single sibling within the Sales Territory dimension. We can verify that Canada is the only sibling by a quick review of the dimensional structure within the Metadata pane, which appears as partially shown in Illustration 5.

It is important to note that only Canada is selected, despite the fact that other Countries exist within the dimension. The key criterion that is met for selection as a sibling is that the two members have a common parent – in this case the North America Sales Territory Group.

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 we have seen in previous articles. (Properly implemented Named Sets can also boost performance, in addition to providing a means for enforcing consistent logic among the report authors that we support.)

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 attributable to individual sales people for Calendar Year 2004. We are given to understand that Reseller Sales Amounts can only be associated with Employees assigned to Sales, although the values associated with non-managers – the actual salespeople – are the values in which we are interested for our immediate purposes. (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 “employee siblings” within the sales department. (While many other Employees reside at the same dimensional level within the Employee dimension, we confirm our understanding that, since only sales Employees can have an associated Reseller Sales Amount value, it will be sufficient to retrieve employees members with the associated values; filtering for sibling members will then help in 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.