Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Nov 6, 2006

MDX Operators: The IsLeaf() Operator: Conditional Logic within Filter Expressions - Page 2

By William Pearson

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.


Illustration 1: The Sales Territory Dimensional Hierarchy

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.

1.  Type (or cut and paste) the following query into the Query pane:


/*  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.


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

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."

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 – IsLeaf() Operator within Filter() Function

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.

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

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.

4.  Select File --> New from the main menu.

5.  Select Query with Current Connection from the cascading menu that appears next, as shown in Illustration 5.


Illustration 5: Create a New Query with the Current Connection ...

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."

6.  Type (or cut and paste) the following query into the Query pane:


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


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

7.  Execute the query by clicking the Execute button in the toolbar.

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


Illustration 7: Results Dataset – Unfiltered Employee Members

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.


Illustration 8: The Employee Dimension Hierarchy – Relevant Members

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.

8.  Select File --> Save MDXQuery2.mdx As ..., name the file MDX049-002-1, and place it in a meaningful location.

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.

9.  Replace the top line of the query (commented out) with the following:

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

10.  Select File --> Save MDX049-002-1.mdx As ..., name the file MDX049-002-2, and place it in a meaningful location.

11.  Place the cursor to the immediate right of the left curly brace - " { " – following the NONEMPTY keyword (currently on the fourth line of the query).

12.  Press the ENTER key four times to "push down" the rest of the line, and to add space between the remaining "NONEMPTY(" and the rest of the line.

13.  Between what is now the fourth (containing "NONEMPTY(" ) line and the fifth (containing "{[Employee].[Employees].MEMBERS})ON AXIS(1)") line of the query, type in the following syntax:

      FILTER(

14.  Place the cursor to the immediate right of the MEMBERS keyword (currently on the sixth line of the query), between "MEMBERS" and the right curly brace - " } " - that is at its right.

15.  Insert a comma ( "," ) to the immediate right of the MEMBERS keyword.

16.  Press the ENTER key four times, once again to "push down" the rest of the line, and to add space between the remaining "MEMBERS," and the rest of the line.

17.  Between what is now the sixth (containing "Employee].[Employees].MEMBERS,") line and the seventh (containing "})ON AXIS(1)") line of the query, type in the following syntax:

      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.


Illustration 9: Our Modified Query in the Query Pane ...

18.  Execute the query by clicking the Execute button in the toolbar.

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


Illustration 10: Results Dataset – Leaf-Level Employee Members

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.

19.  Select File --> Save MDX049-002-2 to save the file.



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM


















Thanks for your registration, follow us on our social networks to keep up-to-date