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 Jan 2, 2007

Logical Functions: IsSibling(): Conditional Logic within Filter Expressions - Page 3

By William Pearson

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 focus of sibling 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:


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

The Query pane appears, with our input, as shown in Illustration 7.


Illustration 7: 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 depicted in Illustration 8 appears.


Illustration 8: 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-sibling Employees. We can verify this by inspecting the dimensional structure in the Analysis Services Cube Browser, a view of which appears in Illustration 9.


Illustration 9: The Employee Dimension Hierarchy – Relevant Members

We can see that, while fourteen employees exist at the bottom level (Level 5), several more members exist when we count higher levels (including the “All” level) within the dimension. Our ultimate objective is to deliver the sibling Sales members – in this case, the fourteen individuals appearing within Level 5. We ask for the name of one of the salespersons within the level, so as to be able to specify it as the secondary member expression within the IsSibling() function (the one against which the Current Members will be compared to determine “siblinghood.”)

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

Our next step will be to filter the non-sibling 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. We are given the name Jae B. Pak, which we are able to locate within the hierarchy we examined earlier in a view from the Cube Browser. We can thus construct the qualified name of our “basis” member, at the appropriate point within the modifications we will next perform upon our initial query.

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

-- MDX051-002-2  Adding the Filter() / IsSibling() Combination

10.  Select File -> Save MDX051-002-1.mdx As ..., name the file MDX051-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].[Employee Department].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].[Employee Department].MEMBERS,”) line and the seventh (containing “})ON AXIS(1)”) line of the query, type in the following syntax:

ISSIBLING([Employee].[Employee Department].CURRENTMEMBER,

18.  Press the ENTER key two times, once again, to add space between the new line and the line immediately underneath it.

19.  Between what is now the seventh (containing “ISSIBLING([Employee].[Employee Department].CURRENTMEMBER,” line and the eighth (containing “})ON AXIS(1)”) line of the query, type in the following syntax:

[Employee].[Employee Department].[Sales Representative].[Jae B. Pak]))

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


-- MDX051-002-2  Adding the Filter() / IsSibling() Combination
        
SELECT
   {[Measures].[Reseller Sales Amount]} ON AXIS(0),
              
   NONEMPTY( {
      FILTER(
         [Employee].[Employee Department].MEMBERS,
            ISSIBLING([Employee].[Employee Department].CURRENTMEMBER,
         [Employee].[Employee Department].[Sales Representative].[Jae B. Pak]))
      })ON AXIS(1)
      
FROM
 
   [Adventure Works]
WHERE 
   [Date].[Calendar].[Calendar Year].[CY 2004]

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


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

20.  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 11 appears.


Illustration 11: Results Dataset – Sibling 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 represent members that are siblings of member Jae B. Pak (and include Jae B. Pak), as shown in Illustration 8 above, with an associated Reseller Sales Amount value.

21.  Select File -> Save MDX051-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