Performance when using a filter and Instr in a MDX query

Question

I have an MDX query of the following form, which I am using with ActivePivot. I need to filter the results (in my on rows), by the presence of a part of string in another dimension (columns):

SELECT
NON EMPTY Hierarchize({[CODE].[CODE].Members}) ON ROWS,
NON EMPTY Hierarchize({Filter([RELEVANCE].Members, InStr([RELEVANCE].CurrentMember.Name, "n/a") > 0)}) ON COLUMNS
FROM [CUBE]
WHERE ([Measures].[contributors.COUNT])

The performance of this query is very poor with the filter/instr. I think I can understand that in that it presumably 'scans' through all of the members.

Is there another way to acheive what I want, but with better performance.

Thanks

Answer 1

If your RELEVANCE dimension has 3 levels and "n/a" appears on the last one you can write something like this:

SELECT
NON EMPTY [CODE].[CODE].Members ON ROWS,
NON EMPTY Hierarchize({[RELEVANCE].Levels(0).Members,
                       [RELEVANCE].Levels(1).Members, 
                       Filter([RELEVANCE].Levels(2).Members, InStr([RELEVANCE].CurrentMember.Name, "n/a") > 0)}) ON COLUMNS
FROM [CUBE]
WHERE ([Measures].[contributors.COUNT])

It will reduce the number of useless filter checks.

You can also add to your cube another dimension with a level with 2 members : "n/a" and "not n/a".

In this case the query will become:

SELECT
NON EMPTY [CODE].[CODE].Members ON ROWS,
NON EMPTY [RELEVANCE].Members ON COLUMNS
FROM [CUBE]
WHERE ([Measures].[contributors.COUNT], [the new dimension].[...].[not n/a])

but this will change the value of your totals.