Filtering data with MDX

One of the key activities when analysing data is to filter.   This is especially true when the data source is a data mart.

This post is about filtering with MDX in preparation for reporting with SSRS.

The query editor within SSRS is okay for basic queries but for complex situations we have to write the query by hand.

In this post I’ll give an example of filtering a set of products based on part of the product name.

The 3 functions we need to do this are:

Except() : Except takes 2 sets and extracts 1 from the other, returning members that are left.

Filter() : Filter takes a set and filters out members based on an expression.

Instr() : Returns the position of the first occurrence of one string within another.

Before we get into the filter let’s have a look at the initial query.

SELECT { [Measures].[Reseller Sales Amount] } ON COLUMNS ,

NON EMPTY { [Product].[Product].CHILDREN } ON ROWS

FROM [Adventure Works]

s1

In this scenario we want to remove products with the words Front, Classic, or Full somewhere within the product name.

We can process the first filter to check we’re on the right track.

EXCEPT(
{[Product].[Product].CHILDREN},
FILTER([Product].[Product].MEMBERS,
Instr(1,[Product].[Product].CurrentMember.Name,"Front")>=1)
)

In the code above we’re taking the product children and removing members that are returned from the Filter function.

The Filter function is filtering the product members based on the occurrence of “Front”.

Once we’re satisfied that this works we can add the other filters.  I don’t know if there is a better way of doing this other than to nest the EXCEPT functions.

EXCEPT(
EXCEPT(
EXCEPT(
{[Product].[Product].CHILDREN},
FILTER([Product].[Product].members, Instr(1,[Product].[Product].CurrentMember.Name,"Front")>=1)),
FILTER([Product].[Product].members, Instr(1,[Product].[Product].CurrentMember.Name,"Classic")>=1)),
FILTER([Product].[Product].members, Instr(1,[Product].[Product].CurrentMember.Name,"Full")>=1))

The complete query is displayed below.

SELECT { [Measures].[Reseller Sales Amount] } ON COLUMNS ,

NON EMPTY

EXCEPT(
EXCEPT(
EXCEPT(
{[Product].[Product].CHILDREN},
FILTER([Product].[Product].members, Instr(1,[Product].[Product].CurrentMember.Name,"Front")>=1)),
FILTER([Product].[Product].members, Instr(1,[Product].[Product].CurrentMember.Name,"Classic")>=1)),
FILTER([Product].[Product].members, Instr(1,[Product].[Product].CurrentMember.Name,"Full")>=1))

ON ROWS

FROM [Adventure Works]

mdx1

I prefer this type of filter as any new occurrences of products matching the filter will be automatically filtered out.

When we explicitly reference products in MDX we often have to amend the query when new business entities are created. It’s best to avoid this.

Of course, use this filter with care and be sure you aren’t inadvertently filtering more members than required.

About Lee Hawthorn

Data Professional
This entry was posted in Analysis Services and tagged , . Bookmark the permalink.

Leave a Reply