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

Does "n/a" appear on only one level? Could you provide a sample of the hierarchy tree? — Benoit, Oct 10 '12 at 13:18
Yes, it is one level, so I don't need the hierarchize function on the columns here, right? The selection on rows could need to be hierarchical though. — doc, Oct 10 '12 at 14:39
For ActivePivot, a colleague of mine came up with alternative solution that is much better performing than INSTR for discarding members based on their name. I created a Post Processsor in ActivePivot at the leaf level of RELEVANCE that returns null if the String does not match. I used context values to set the String. — doc, Oct 23 '12 at 15:28

score 2 · Accepted Answer · answered Oct 11 '12 at 12:03

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.

Performance when using a filter and Instr in a MDX query

1 Answers1