<div><br></div><div><br></div>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. <div><br></div><div><br><div>-pedro<span></span></div><div><br>
<br>On Saturday, June 30, 2012, mehdi b wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div><div style="font-size:12pt;font-family:times new roman,new york,times,serif">
<div><span>I thought you were a <span style="text-decoration:underline">polite guy</span>, but you are not! My fault was to call you "Dear"! </span><span><span style="background-color:rgb(255,191,128)"><br></span></span></div>
<div><span><span style="background-color:rgb(255,191,128)">Y</span></span><span style="background-color:rgb(255,191,128)">ou
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, </span><span style="background-color:rgb(255,191,128)">really </span><span style="background-color:rgb(255,191,128)">try hard to learn to be polite!!!<br>
</span></div><div><br><span></span></div><div><span>I have enough pride and courage to use the framework for a project of 500,000$. </span><span>I have experience of using the framework for <span style="background-color:rgb(255,191,128)">more than one year</span> and I deployed <span style="background-color:rgb(255,191,128)">more than 20 Cubes</span> under it. I even change the source code and add some aggregator in <span style="background-color:rgb(255,191,128)">RolapAggregator</span> class to support stddev and some complex aggregation expressions!<br>
</span></div><div><span><br></span></div><div><span>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)</span><span style="background-color:rgb(255,191,128)"></span><span>!!!<br></span></div><div><span><br></span></div>
<div><span>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!!!</span></div>
<div><br><span></span></div><div><span><br></span></div><div><br></div> <div style="font-family:times new roman,new york,times,serif;font-size:12pt"> <div style="font-family:times new roman,new york,times,serif;font-size:12pt">
<div dir="ltr"> <font face="Arial"> <hr size="1"> <b><span style="font-weight:bold">From:</span></b> Julian Hyde <<a href="javascript:_e({}, 'cvml', 'jhyde@pentaho.com');" target="_blank">jhyde@pentaho.com</a>><br>
<b><span style="font-weight:bold">To:</span></b> mehdi b <<a href="javascript:_e({}, 'cvml', 'mehdi_xinu@yahoo.com');" target="_blank">mehdi_xinu@yahoo.com</a>> <br> <b><span style="font-weight:bold">Sent:</span></b> Thursday, June 28, 2012 4:03 PM<br>
<b><span style="font-weight:bold">Subject:</span></b> Re: [Mondrian] MDX for histogram over Large-Member level<br> </font> </div> <br>
<div><div>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?<div><br><div><br><div><div>On Jun 28, 2012, at 2:16 AM, mehdi b wrote:</div>
<br><blockquote type="cite"><div><div style="font-size:12pt;font-family:times new roman,new york,times,serif"><div><span>Dear </span><font face="Arial">Julian,</font></div><div><br></div><div><font face="Arial">Thanks for your answer. <span style="background-color:rgb(255,191,128)">The number of ranges and the start and end of each range is determined by the user, so they are not fixed</span>. Therefore, I can't define a dimension as [</font>Balance Range] with members like small, medium and large to apply slicing.</div>
<div><br></div><div>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):</div><div>
<br></div><div style="color:rgb(64,64,255)">with<br>member [Measures].[Range1] as
<br>'Count(Filter([CustomerID].[CustomerID].members, [Measures].[WholeBalance]<10000))'<br>member [Measures].[Range2] as <br>'Count(Filter([CustomerID].[CustomerID].members, [Measures].[WholeBalance]>=10000 and [Measures].[WholeBalance]<100000))'<br>
select<br>{[Measures].[Range1],[Measures].[Range2]} on axis(0)<br>from [CustomerBal]</div><div style="color:rgb(64,64,255)"><br></div><div style>The XML cube definition</div><div style="color:rgb(64,64,255)"><br></div><div style="color:rgb(64,64,255)">
<span style="color:rgb(127,63,0)"> <Cube name="CustomerBal" cache="false" enabled="true" defaultMeasure="WholeBalance"></span><br style="color:rgb(127,63,0)"><span style="color:rgb(127,63,0)"> <Table schema="BIDWH" name="COR_CB_CUSTOMERBAL"/></span><br style="color:rgb(127,63,0)">
<br style="color:rgb(127,63,0)"><span style="color:rgb(127,63,0)"> <DimensionUsage name="Currency" source="Currency" foreignKey="FD_CURRENCY"/></span><br style="color:rgb(127,63,0)">
<br style="color:rgb(127,63,0)"><span style="color:rgb(127,63,0)"><span style="background-color:rgb(255,255,191)"> </span><span style="background-color:rgb(255,255,191)"><Dimension
name="CustomerID"> </span></span><br style="color:rgb(127,63,0);background-color:rgb(255,255,191)">
<span style="color:rgb(127,63,0);background-color:rgb(255,255,191)"> <Hierarchy hasAll="true"
allMemberName="All"> </span><br style="color:rgb(127,63,0);background-color:rgb(255,255,191)"><span style="color:rgb(127,63,0);background-color:rgb(255,255,191)"> <Level name="CustomerID" column="MS_CUSTOMERID" uniqueMembers="true" type="Integer"/></span><br style="color:rgb(127,63,0);background-color:rgb(255,255,191)">
<span style="color:rgb(127,63,0);background-color:rgb(255,255,191)"> </Hierarchy> </span><br style="color:rgb(127,63,0);background-color:rgb(255,255,191)">
<span style="color:rgb(127,63,0);background-color:rgb(255,255,191)">
</Dimension> </span><br style="color:rgb(127,63,0)"><br style="color:rgb(127,63,0)">
<span style="color:rgb(127,63,0)"> <Measure name="WholeBalance" column="MR_WHOLEBALANCE" aggregator="sum"/></span><br style="color:rgb(127,63,0)"><span style="color:rgb(127,63,0)"> </Cube></span><br style="color:rgb(127,63,0)">
</div><div style="color:rgb(64,64,255)"><br></div><div style="color:rgb(64,64,255)"><span style="color:rgb(0,0,0)">I would appreciate any help on this issue :)</span><br></div><div><br></div> <div style="font-family:times new roman,new york,times,serif;font-size:12pt">
<div style="font-family:times new roman,new york,times,serif;font-size:12pt"> <div dir="ltr"> <font face="Arial"> <hr size="1"> <b><span style="font-weight:bold">From:</span></b> Julian Hyde <<a rel="nofollow" href="javascript:_e({}, 'cvml', 'jhyde@pentaho.com');" target="_blank">jhyde@pentaho.com</a>><br>
<b><span style="font-weight:bold">To:</span></b>
mehdi b <<a rel="nofollow" href="javascript:_e({}, 'cvml', 'mehdi_xinu@yahoo.com');" target="_blank">mehdi_xinu@yahoo.com</a>>; Mondrian developer mailing list
<<a rel="nofollow" href="javascript:_e({}, 'cvml', 'mondrian@pentaho.org');" target="_blank">mondrian@pentaho.org</a>> <br> <b><span style="font-weight:bold">Sent:</span></b> Wednesday, June 27, 2012 5:56 PM<br>
<b><span style="font-weight:bold">Subject:</span></b> Re: [Mondrian] MDX for histogram over Large-Member level<br> </font> </div> <br>
<div><div>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.<div>
<br></div><div>Can you create a measure [Measures].[Balance Range] that has values small, medium and large. Then you can slice on it, rather than filtering.</div><div><br></div><div>But then there's a question of when you are interested in the balance. Today? Aggregated over the last month? Aggregated over all time?</div>
<div><br></div><div>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.</div><div><br></div><div>Julian</div><div><br></div>
<div><div><div><div>On Jun 27, 2012, at 7:46 AM, mehdi b wrote:</div><br><blockquote type="cite"><div><div style="font-size:12pt;font-family:times new roman,new york,times,serif"><div>Hi,</div><div><br></div><div>I want to calculate histogram data for a level over a measure of a cube.I wrote the following MDX:</div>
<div><br></div><div style="color:rgb(0,0,191)">with<br>member [Measures].[Range1] as <br>'Count(Filter([CustomerID].[CustomerID].members, [Measures].[WholeBalance]<10000))'<br>member [Measures].[Range2] as <br>
'Count(Filter([CustomerID].[CustomerID].members, [Measures].[WholeBalance]>=10000 and [Measures].[WholeBalance]<100000))'<br>select<br>{[Measures].[Range1],[Measures].[Range2]} on axis(0)<br>from [CustomerBal]<br>
</div><div><br></div><div>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 <span style="background-color:rgb(255,191,128)">in memory</span> instead of using SQL count function. This results to long execution and bad performance for my reports.</div>
<div><br></div><div>The generated SQL for [Measures].[range1]:</div><div style="color:rgb(127,63,0)">SELECT <span style="background-color:rgb(255,191,128)">"COR_CB_CUSTOMERBAL"."MS_CUSTOMERID" </span>AS "c0"<br>
FROM "BIDWH"."COR_CB_CUSTOMERBAL" "COR_CB_CUSTOMERBAL"<br>GROUP BY "COR_CB_CUSTOMERBAL"."MS_CUSTOMERID"<br>HAVING (SUM("COR_CB_CUSTOMERBAL"."MR_WHOLEBALANCE") < 10000.0)<br>
ORDER BY "COR_CB_CUSTOMERBAL"."MS_CUSTOMERID" ASC</div><div><br></div><div>The generated SQL for [Measures].[range2]:</div><div style="color:rgb(127,63,0)">SELECT <span style="background-color:rgb(255,191,128)">"COR_CB_CUSTOMERBAL"."MS_CUSTOMERID"</span> AS "c0"<br>
FROM "BIDWH"."COR_CB_CUSTOMERBAL" "COR_CB_CUSTOMERBAL"<br>GROUP BY "COR_CB_CUSTOMERBAL"."MS_CUSTOMERID"<br>HAVING
((SUM("COR_CB_CUSTOMERBAL"."MR_WHOLEBALANCE") >= 10000.0)<br>AND (SUM("COR_CB_CUSTOMERBAL"."MR_WHOLEBALANCE") < 100000.0))<br>ORDER BY "COR_CB_CUSTOMERBAL"."MS_CUSTOMERID" ASC</div>
<div><br></div><div>But it would be better if generated SQL uses the SQL count function to directly count the members. <br></div><div><br></div><div>So what can I do?<br></div></div></div>_______________________________________________<br>
Mondrian mailing list<br><a rel="nofollow" href="javascript:_e({}, 'cvml', 'Mondrian@pentaho.org');" target="_blank">Mondrian@pentaho.org</a><br><a rel="nofollow" href="http://lists.pentaho.org/mailman/listinfo/mondrian" target="_blank">http://lists.pentaho.org/mailman/listinfo/mondrian</a><br>
</blockquote></div><br></div></div></div></div><br><br> </div> </div> </div></div></blockquote></div><br></div></div></div></div><br><br> </div> </div> </div></div></blockquote></div></div>