mdxactivepivot

Performance when using a filter and Instr in a MDX query


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


Solution

  • 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.