MDX functions – Except & Filter – intermediate level

I’ve been getting to grips with MDX functions to aid analysis and reporting.  The recent functions I’ve come across are EXCEPT & FILTER.

In this post we’ll look at an example where these functions can come in handy.

If this is the first thing you’ve seen of MDX then this post is not for you.  I’m in the process of writing some introductory material for MDX that I will share on this site when it’s finished.

Before we get started it’s worth looking at what EXCEPT and FILTER do.

The descriptions below were taken from the TechNet site.

Except()

“Evaluates two sets and removes those tuples in the first set that also exist in the second set, optionally retaining duplicates.”

Let’s say we want run some analysis on most of the customers except for a few of them.   With the EXCEPT function the first set contains the complete list of customers and the second set provides the list of customers we want to remove from the analysis.

Filter()

“Returns the set that results from filtering a specified set based on a search condition.”

We can use this function to make dynamic selections rather than explicitly listing them in the MDX query.   For instance, we might want to get a set of customers with revenue less than £1,000.

Combining the functions

You start to see the power of MDX when combining functions.

In this example we have been asked by our marketing colleagues to produce some analysis on resellers with less than £1,000 revenue.   The output from this analysis will be used by the marketing team to stimulate the re-sellers with cross sell opportunities.  We want the subsequent report to be self updating so that as resellers rise above the revenue limit they will be removed from the report.

Ok, enough of the scenario let’s look at a basic MDX query to begin with that displays the re-seller revenue by month for the Bike product category.

SELECT NON EMPTY { NONEMPTY( { [Product].[Category].[Bikes] } *
{ [Measures].[Reseller Sales Amount] } ,
{ [Date].[Calendar].[All Periods] } *
{ [Reseller].[Reseller].[All Resellers].CHILDREN } *
{ [Date].[Month of Year].[All Periods].CHILDREN } ) } ON 0 ,

NON EMPTY { NONEMPTY( { [Reseller].[Reseller].[All Resellers].CHILDREN } *
{ [Date].[Month of Year].[All Periods].CHILDREN } ,
{ [Date].[Calendar].[All Periods] } *
{ [Product].[Category].&[1] } *
{ [Measures].[Reseller Sales Amount] } ) } ON 1

FROM [Adventure Works]
WHERE ( [Date].[Calendar].[Calendar Year].&[2006] )

This returns the set of data below.

Basic MDX query

Now this is okay to start with, however, our Marketing team want to create a campaign for the resellers with revenue less than £1,000.

The first step is to create a set using the WITH SET function.

//Create the column set for re-use in the EXCEPT and FILTER function
WITH SET COLS AS {NONEMPTY({[Reseller].[Reseller].[All Resellers].CHILDREN}
* {[Date].[Month of Year].[All Periods].CHILDREN},{[Date].[Calendar].[All Periods]}
* {[Product].[Category].[Bikes]} *{[Measures].[Reseller Sales Amount]})}

We can then start to build the query. This is easy enough to build, just like in the earlier query.

SELECT NON EMPTY {
NONEMPTY({[Product].[Category].[Bikes]} * {[Measures].[Reseller Sales Amount]},{[Date].[Calendar].[All Periods]}
* {[Reseller].[Reseller].[All Resellers].CHILDREN}
* {[Date].[Month of Year].[All Periods].CHILDREN})} ON 0,  // Build the rows

The tricky part is to ensure we see all resellers except for the ones with with revenue > £,1000.

When writing queries like this it is best to do it inside out. To identify the resellers we use the FILTER() function to identify Resellers with Revenue greater than 1000. Stay with me for a moment if your thinking we should be saying < 1000.

FILTER( COLS, ([Product].[Category].&[Bikes], [Measures].[Reseller Sales Amount]) > 1000)})

In this scenario we want to remove these resellers from the analysis.   We can use the EXCEPT() function. Doing it this way ensures as any new resellers are acquired they will be automatically built into the analysis.

EXCEPT(COLS,{FILTER( COLS, ([Product].[Category].[Bikes], [Measures].[Reseller Sales Amount]) > 1000)})}

The complete query is listed below.

//Create the column set for re-use in the EXCEPT and FILTER function
WITH SET COLS AS {NONEMPTY({[Reseller].[Reseller].[All Resellers].CHILDREN}
* {[Date].[Month of Year].[All Periods].CHILDREN},{[Date].[Calendar].[All Periods]}
* {[Product].[Category].[Bikes]} *{[Measures].[Reseller Sales Amount]})}

SELECT NON EMPTY {
NONEMPTY({[Product].[Category].[Bikes]} * {[Measures].[Reseller Sales Amount]},{[Date].[Calendar].[All Periods]}
* {[Reseller].[Reseller].[All Resellers].CHILDREN}
* {[Date].[Month of Year].[All Periods].CHILDREN})} ON 0,  // Build the rows

NON EMPTY {
 //Build the column but exclude any resellers with Sales above 1000
EXCEPT(COLS,{FILTER( COLS, ([Product].[Category].Bikes], [Measures].[Reseller Sales Amount]) > 1000)})} ON 1

FROM [Adventure Works]
WHERE ( [Date].[Calendar].[Calendar Year].&[2006])

Completed MDX

I hope you’ve found this post useful. As always, feel free to comment on the article. I’m particularly interested in learning if there is a better way to achieve this output with the IIF function.

Lee

About Lee Hawthorn

Business Intelligence Consultant
This entry was posted in Analysis Services and tagged , , . Bookmark the permalink.

Leave a Reply