[Mondrian] MDX for histogram over Large-Member level

Pedro Alves pmgalves at gmail.com
Sat Jun 30 05:15:00 EDT 2012


I think i speak for all the community - Please spare us from personal
disputes. This is not the place for it. We don't care.


-pedro


On Saturday, June 30, 2012, mehdi b wrote:

> I thought you were a polite guy, but you are not! My fault was to call
> you "Dear"!
> You are not obliged to answer me in the first place. In fact your first
> answer didn't help me. If it is hard for you, don't involve, you are not
> forced (I repeat it again for your attention!), and instead, really try
> hard to learn to be polite!!!
>
> I have enough pride and courage to use the framework for a project of
> 500,000$. I have experience of using the framework for more than one yearand I deployed more
> than 20 Cubes under it. I even change the source code and add some
> aggregator in RolapAggregator class to support stddev and some complex
> aggregation expressions!
>
> FYI, this is my third email to Pentaho group and the first one to be
> answered (the second one was a bug report, and I don't care if it has been
> read or not)!!!
>
> The reason that my email came to you directly is I thought when I click
> the reply, the Pentaho's forum email is set as receiver like other groups,
> but your email was set instead! So I didn't mean to send to you directly
> and "begging you", I'm so sorry for your viewpoints and thoughts!!!
>
>
>
>   ------------------------------
> *From:* Julian Hyde <jhyde at pentaho.com <javascript:_e({}, 'cvml',
> 'jhyde at pentaho.com');>>
> *To:* mehdi b <mehdi_xinu at yahoo.com <javascript:_e({}, 'cvml',
> 'mehdi_xinu at yahoo.com');>>
> *Sent:* Thursday, June 28, 2012 4:03 PM
> *Subject:* Re: [Mondrian] MDX for histogram over Large-Member level
>
> I said I don't have time to help you, then you send me a personal begging
> email. For heavens sake. Have you no pride? Or can't you read?
>
>
> On Jun 28, 2012, at 2:16 AM, mehdi b wrote:
>
> Dear Julian,
>
> Thanks for your answer. The number of ranges and the start and end of
> each range is determined by the user, so they are not fixed. Therefore, I
> can't define a dimension as [Balance Range] with members like small,
> medium and large to apply slicing.
>
> About my cube, I have a table of all customers with the last balance of
> each customer, and I defined it in Mondrian as a cube. To calculate the
> histogram values over last balance of each customer, I defined the
> CustomerId as a dimension to apply the aforementioned MDX (following one):
>
> 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 XML cube definition
>
>     <Cube name="CustomerBal" cache="false" enabled="true"
> defaultMeasure="WholeBalance">
>         <Table schema="BIDWH" name="COR_CB_CUSTOMERBAL"/>
>
>         <DimensionUsage name="Currency" source="Currency"
> foreignKey="FD_CURRENCY"/>
>
>         <Dimension
> name="CustomerID">
>
>             <Hierarchy hasAll="true"
> allMemberName="All">
>
>                 <Level name="CustomerID" column="MS_CUSTOMERID"
> uniqueMembers="true" type="Integer"/>
>
> </Hierarchy>
>
>
> </Dimension>
>
>
>         <Measure name="WholeBalance" column="MR_WHOLEBALANCE"
> aggregator="sum"/>
>     </Cube>
>
> I would appreciate any help on this issue :)
>
>   ------------------------------
> *From:* Julian Hyde <jhyde at pentaho.com <javascript:_e({}, 'cvml',
> 'jhyde at pentaho.com');>>
> *To:* mehdi b <mehdi_xinu at yahoo.com <javascript:_e({}, 'cvml',
> 'mehdi_xinu at yahoo.com');>>; Mondrian developer mailing list <
> mondrian at pentaho.org <javascript:_e({}, 'cvml', 'mondrian at pentaho.org');>>
>
> *Sent:* Wednesday, June 27, 2012 5:56 PM
> *Subject:* Re: [Mondrian] MDX for histogram over Large-Member level
>
> This is an instance of the "MDX is SQL" anti-pattern. Calling aggregate
> functions such as Count to large numbers of cells is rarely the right thing
> to do in MDX. It usually indicates that the writer is thinking in SQL.
>
> Can you create a measure [Measures].[Balance Range] that has values small,
> medium and large. Then you can slice on it, rather than filtering.
>
> But then there's a question of when you are interested in the balance.
> Today? Aggregated over the last month? Aggregated over all time?
>
> Hope this helps. I don't have time to fully solve your problem, but
> hopefully I gave you some pointers, and maybe someone else on the list can
> help.
>
> Julian
>
> On Jun 27, 2012, at 7:46 AM, mehdi b wrote:
>
> 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?
> _______________________________________________
> Mondrian mailing list
> Mondrian at pentaho.org <javascript:_e({}, 'cvml', 'Mondrian at pentaho.org');>
> http://lists.pentaho.org/mailman/listinfo/mondrian
>
>
>
>
>
>
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.pentaho.org/pipermail/mondrian/attachments/20120630/beab9b9a/attachment-0001.html 


More information about the Mondrian mailing list