Thomas.I Microsoft BI & Analytics

A Site Dedicated to General BI Issues and MS BI Issues: Architecture and Technology

Testing the MDX filter function

Posted by thomasivarssonmalmo on June 24, 2007

This is another short MDX blog entry in order to improve my MDX skills and to present end users problems of interaction with SSAS cubes.
 
The Filter function have sometimes returned resultsets that can be tricky to explain to a customer. It is used in several clients like ProClarity and Excel 2007. I do not have all answeres yet so feel free to add a comment.
 
Filter in MDX removes members from an axis that do not fullfill the terms i the function. The Where clause in MDX, also called a slicer, do not remove members only cell values in a resultset. Be aware that the Filter function condition is always about complete rows or columns, never about single cells.
 
In the first example I do not have an explicit time member in the filter clause so I am probably pointing to the all member of the TimeCalendar dimension. With this in mind I can remove members(product subcategory) on the row axis :
 
Select CrossJoin({[Date].[Calendar].[Calendar Year].members},
{[Measures].[Internet Sales Amount],[Measures].[Internet Order Quantity]}) On Columns,
Filter({[Product].[Product Categories].[Subcategory].Members},[Measures].[Internet Order Quantity]> 1100) On Rows
From [Adventure Works];
 
The second example breaks the rules of MDX and do not work. It says that the calandar hierarchy already exists on the Axis0 axis. I have no problem with that.
Select CrossJoin({[Date].[Calendar].[Calendar Year].members},
{[Measures].[Internet Sales Amount],[Measures].[Internet Order Quantity]}) On Columns,
Filter({([Product].[Product Categories].[Subcategory].Members,
[Date].[Calendar].[Calendar Year].&[2003])},[Measures].[Internet Order Quantity]> 1100) On Rows
From [Adventure Works];
 
In the third example , if I change the crossjoin to CustomerCountry and the measures, the filter clause will still work as expected, and add that year, in the filter, to the row axis, even if I have not explicitly asked for it. I was actually wrong here before but now I see that this example works as the first. It is the quantity for all countries, for a subcategory, that decide if the record will disappear or not.
Select CrossJoin({[Customer].[Customer Geography].[Country].members},
{[Measures].[Internet Sales Amount],[Measures].[Internet Order Quantity]}) On Columns,
Filter({([Product].[Product Categories].[Subcategory].Members,
[Date].[Calendar].[Calendar Year].&[2003])},[Measures].[Internet Order Quantity]> 1100) On Rows
From [Adventure Works]
 
The interesting thing with the last example is that I cannot filter like this in ProClarity Professional 6.3 , on the [Internet Order Quantity] for the total product subcategory group. I must filter on one of the column members as well, like USA. Still all the the other countries will be left in the grid and that is not strange because they are part of row that includes USA (Tires and Tubes if you set the limit to over 2000).
 
So the filter behaviour i Management Studio is not the same as in ProClarity Professional 6.3 . My opinion is that ProClarity’s way of implementing filters is more limited.
 
 
This is the first part of my test of the Filter function and I have not tried enough examples yet to be really sure of how it works in different scenarios. I will update this blog entry when I find more interesting examples. 
Advertisements

2 Responses to “Testing the MDX filter function”

  1. Darren said

    Just one point of clarification, you mentioned with the third scenario, that the year was included on the rows axis, even though you had not asked for it. Technically you had asked for it as you have included a year member in the set that is being filtered. If you wanted to include the year as part of the criteria, but not in the returned set, you would write the MDX like this:
     
    Select
    CrossJoin({[Customer].[Customer Geography].[Country].members},{[Measures].[Internet Sales Amount],[Measures].[Internet Order Quantity]}) On Columns,Filter({[Product].[Product Categories].[Subcategory].Members},([Measures].[Internet Order Quantity],[Date].[Calendar].[Calendar Year].&[2003])> 1100) On RowsFrom [Adventure Works]
     
    Which would return all of the subcategories that has more than 1000 in the [Internet Order Quantity] measure in 2003.
     

  2. Thomas said

    Good point Darren.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: