[Mondrian] Except function performance

Hilario Fernandes hilario.fernandes at cortex-intelligence.com
Wed Mar 4 12:15:34 EST 2015


Hi!

I'm having somewhat of a performance problem when trying execute the
following query:

WITH
MEMBER [Measures].[TotalA] AS
IIF((NOT IsEmpty([Measures].[M1])), Aggregate({[A].CurrentMember} *
{[B].[Total B]} * {[C].[Total C]} * {[FILTERED_D]} * {[E].[Total E]},
[Measures].[M1]), NULL)

SET [FILTERED_D] AS
Except({[D].[D].Members},{[D].[D].[member1], [D].[D].[member2]})})

SELECT
NON EMPTY ({[Measures].[M1], [Measures].[TotalA]}) ON COLUMNS,
NON EMPTY [A].[A].Members * [B].[B].Members * [C].[C].Members
* [FILTERED_D] * [E].[E].Members ON ROWS
FROM [C]

The point of the TotalA measure is to return the total of the measure for
every A member, repeating it for the rest of the other dimension members on
the axis. The problem is this measure must respect the filters in that
axis, so the except is placed both on the rows and used on the aggregate.

Thing is, when the except is used in the TotalA calculation its makes the
evaluation a lot slower. This only happens with except, if something like
Filter(members, measure >100) is used then there is no major difference.

Can anyone shed some light on why this happens? And maybe some ideas to
work around it with some other way to achieve the same result.

Thanks


-- 
Hilario Fernandes
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.pentaho.org/pipermail/mondrian/attachments/20150304/06634292/attachment.html 


More information about the Mondrian mailing list