[Mondrian] MDX for histogram over Large-Member level

[mehdi b]

Hi,

I want to calculate histogram data for a level over a measure of a cube.I wrote the following MDX:

with
member [Measures].[Range1] as 
'Count(Filter([CustomerID].[CustomerID].members, [Measures].[WholeBalance]<10000))'
member [Measures].[Range2] as 
'Count(Filter([CustomerID].[CustomerID].members, [Measures].[WholeBalance]>=10000 and [Measures].[WholeBalance]<100000))'
select
{[Measures].[Range1],[Measures].[Range2]} on axis(0)
from [CustomerBal]


The MDX calculates the result, but my CustomerID level has 2,150,354 members, and as I checked for every calculated member, Mondrian generates a SQL to find the members with the condition in Filter() and then count every found member in memory instead of using SQL count function. This results to long execution and bad performance for my reports.

The generated SQL for [Measures].[range1]:
SELECT "COR_CB_CUSTOMERBAL"."MS_CUSTOMERID" AS "c0"
FROM "BIDWH"."COR_CB_CUSTOMERBAL" "COR_CB_CUSTOMERBAL"
GROUP BY "COR_CB_CUSTOMERBAL"."MS_CUSTOMERID"
HAVING (SUM("COR_CB_CUSTOMERBAL"."MR_WHOLEBALANCE") < 10000.0)
ORDER BY "COR_CB_CUSTOMERBAL"."MS_CUSTOMERID" ASC

The generated SQL for [Measures].[range2]:
SELECT "COR_CB_CUSTOMERBAL"."MS_CUSTOMERID" AS "c0"
FROM "BIDWH"."COR_CB_CUSTOMERBAL" "COR_CB_CUSTOMERBAL"
GROUP BY "COR_CB_CUSTOMERBAL"."MS_CUSTOMERID"
HAVING ((SUM("COR_CB_CUSTOMERBAL"."MR_WHOLEBALANCE") >= 10000.0)
AND (SUM("COR_CB_CUSTOMERBAL"."MR_WHOLEBALANCE")      < 100000.0))
ORDER BY "COR_CB_CUSTOMERBAL"."MS_CUSTOMERID" ASC

But it would be better if generated SQL uses the SQL count function to directly count the members. 


So what can I do?
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.pentaho.org/pipermail/mondrian/attachments/20120627/09cf6666/attachment.html