[prev in list] [next in list] [prev in thread] [next in thread]
List: mondrian
Subject: Re: [Mondrian] Problem with 93 SQL queries for each member of month level of each year
From: m.xinu <mehdi_xinu () yahoo ! com>
Date: 2012-12-03 15:03:48
Message-ID: 1354547028.83557.YahooMailNeo () web113905 ! mail ! gq1 ! yahoo ! com
[Download RAW message or body]
[Attachment #2 (multipart/alternative)]
I knew that, and before I send my second email, I did that either.
In
fact I have a simple swing application to test and check my MDX
queries, and after I changed my schema, I restarted it and I got the
same problem.
________________________________
From: Pedro Alves <pmgalves@gmail.com>
To: m.xinu <mehdi_xinu@yahoo.com>; Mondrian developer mailing list <mondrian@pentaho.org>
Sent: Monday, December 3, 2012 2:50 PM
Subject: Re: [Mondrian] Problem with 93 SQL queries for each member of month level of each year
Yes - flush the mondrian cache, you made a mistake :)
On Mon 03 Dec 2012 02:47:03 PM WET, m.xinu wrote:
> Thanks for your reply
>
> I changed it to hasAll="true" and the problem still exists. Any
> suggestion?
>
> ------------------------------------------------------------------------
> *From:* Pedro Alves <pmgalves@gmail.com>
> *To:* Mondrian developer mailing list <mondrian@pentaho.org>
> *Sent:* Monday, December 3, 2012 2:38 PM
> *Subject:* Re: [Mondrian] Problem with 93 SQL queries for each member
> of month level of each year
>
> Yeah - don't use hasAll=false unless you have a very short number of
> first level members
>
> On Mon 03 Dec 2012 02:33:57 PM WET, Paul Stoellberger wrote:
> > The problem is most probably that you have hasAll="false"
> >
> > From what I understand thats not advisable to do that.
> > You could either a) do hasAll=true or b) set a default member for the
> > hierarchy, that could prevent the large number of sql queries
> >
> > -Paul
> >
> >
> > On Dec 3, 2012, at 3:31 PM, m.xinu wrote:
> >
> >> Hi,
> >>
> >> I have a common date dimension as follows
> >>
> >> <Dimension name="Date">
> >> <Hierarchy primaryKey="JALALI_ID" hasAll="false">
> >> <Table schema="BIDWH" name="BAS_DM_DATE" />
> >> <Level name="Year" column="J_YEAR" uniqueMembers="true"
> >> type="Integer"/>
> >> <Level name="Month" column="J_MONTH" type="Integer"/>
> >> <Level name="Day" column="J_DAY" type="Integer"/>
> >> </Hierarchy>
> >> </Dimension>
> >>
> >> and I used it in all my cubes such as Deposit:
> >>
> >> <Cube name="Deposit" cache="false" enabled="true"
> >> defaultMeasure="DpCount">
> >> <Table schema="BIDWH" name="COR_CB_DEPOSIT" />
> >> <DimensionUsage name="OpenDate" source="Date"
> >> foreignKey="FD_OPENDATE"/>
> >> <Measure name="DpCount" column="MS_DEPOSIT_ID"
> >> aggregator="distinct-count"/>
> >> <Measure name="MinMainAcc" column="MR_MIN_MAINACCOUNT"
> >> aggregator="sum" />
> >> </Cube>
> >>
> >> when I execute MDX:
> >>
> >> select
> >> [OpenDate].[Month].members on axis(0),
> >> [Measures].[DpCount] on axis(1)
> >> from Deposit
> >>
> >> or
> >>
> >> select
> >> [OpenDate].[Month].members on axis(0),
> >> [Measures].[MinMainAcc] on axis(1)
> >> from Deposit
> >>
> >> Mondrian executes 93 SQL queries for each month like the following:
> >>
> >> select "BAS_DM_DATE"."J_DAY" as "c0" from "BIDWH"."BAS_DM_DATE"
> >> "BAS_DM_DATE" where ("BAS_DM_DATE"."J_MONTH" = 1 and
> >> "BAS_DM_DATE"."J_YEAR" = 1384) group by "BAS_DM_DATE"."J_DAY" order
> >> by "BAS_DM_DATE"."J_DAY" ASC NULLS LAST
> >>
> >> I don't understand the reason of such queries. I have the same
> >> problem with other cubes, and it takes so long for some cubes.
> >> I checked the MDX in versions 3.3.0.14703, 3.4.1, and 3.5.0 and the
> >> results are the same.
> >>
> >> Is my configuration wrong? Is there any solution to solve the problem?
> >>
> >>
> >>
> >>
> >> _______________________________________________
> >> Mondrian mailing list
> >> Mondrian@pentaho.org <mailto:Mondrian@pentaho.org>
> <mailto:Mondrian@pentaho.org <mailto:Mondrian@pentaho.org>>
> >> http://lists.pentaho.org/mailman/listinfo/mondrian
> >
> >
> >
> > _______________________________________________
> > Mondrian mailing list
> > Mondrian@pentaho.org <mailto:Mondrian@pentaho.org>
> > http://lists.pentaho.org/mailman/listinfo/mondrian
> _______________________________________________
> Mondrian mailing list
> Mondrian@pentaho.org <mailto:Mondrian@pentaho.org>
> http://lists.pentaho.org/mailman/listinfo/mondrian
>
>
>
>
> _______________________________________________
> Mondrian mailing list
> Mondrian@pentaho.org
> http://lists.pentaho.org/mailman/listinfo/mondrian
[Attachment #5 (text/html)]
<html><body><div style="color:#000; background-color:#fff; font-family:times new \
roman, new york, times, serif;font-size:12pt"><div>I knew that, and before I send my \
second email, I did that either.<br><br>In fact I have a simple swing application to \
test and check my MDX queries, and after I changed my schema, I restarted it and I \
got the same problem.</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" \
size="2"> <hr size="1"> <b><span style="font-weight:bold;">From:</span></b> Pedro \
Alves <pmgalves@gmail.com><br> <b><span style="font-weight: \
bold;">To:</span></b> m.xinu <mehdi_xinu@yahoo.com>; Mondrian developer mailing \
list <mondrian@pentaho.org> <br> <b><span style="font-weight: \
bold;">Sent:</span></b> Monday, December 3, 2012 2:50 PM<br> <b><span \
style="font-weight: bold;">Subject:</span></b> Re: [Mondrian] Problem with 93 SQL \
queries for each member of month level of each year<br> </font> </div> <br> Yes - \
flush the mondrian cache, you made a mistake :)<br><br>On Mon 03 Dec 2012 02:47:03 PM \
WET, m.xinu wrote:<br>> Thanks for your reply<br>><br>> I changed it to \
hasAll="true" and the problem still exists. Any<br>> suggestion?<br>><br>> \
------------------------------------------------------------------------<br>> \
*From:* Pedro Alves <<a ymailto="mailto:pmgalves@gmail.com" \
href="mailto:pmgalves@gmail.com">pmgalves@gmail.com</a>><br>> *To:* Mondrian \
developer mailing list <<a ymailto="mailto:mondrian@pentaho.org" \
href="mailto:mondrian@pentaho.org">mondrian@pentaho.org</a>><br>> *Sent:* \
Monday, December 3, 2012 2:38 PM<br>> *Subject:* Re: [Mondrian] Problem with 93 \
SQL queries for each member<br>> of month level of each year<br>><br>> Yeah \
- don't use hasAll=false unless you have a very short number of<br>> first level \
members<br>><br>> On Mon 03 Dec 2012 02:33:57 PM WET, Paul Stoellberger \
wrote:<br>> > The problem is most probably that you have hasAll="false"<br>> \
><br>> > From what I understand thats not advisable to do that.<br>> > \
You could either a) do hasAll=true or b) set a default member for the<br>> > \
hierarchy, that could prevent the large number of sql queries<br>> ><br>> \
> -Paul<br>> ><br>> ><br>> > On Dec 3, 2012, at 3:31 PM, m.xinu \
wrote:<br>> ><br>> >> Hi,<br>> >><br>> >> I have a \
common date dimension as follows<br>> >><br>> >> \
<Dimension name="Date"><br>> >> \
<Hierarchy primaryKey="JALALI_ID" hasAll="false"><br>> >> \
<Table schema="BIDWH" name="BAS_DM_DATE" /><br>> \
>> <Level name="Year" \
column="J_YEAR" uniqueMembers="true"<br>> >> type="Integer"/><br>> \
>> <Level name="Month" \
column="J_MONTH" type="Integer"/><br>> >> \
<Level name="Day" column="J_DAY" type="Integer"/><br>> \
>> </Hierarchy><br>> >> \
</Dimension><br>> >><br>> >> and I used it in all my cubes \
such as Deposit:<br>> >><br>> >> <Cube \
name="Deposit" cache="false" enabled="true"<br>> >> \
defaultMeasure="DpCount"><br>> >> <Table \
schema="BIDWH" name="COR_CB_DEPOSIT" /><br>> >> \
<DimensionUsage name="OpenDate" source="Date"<br>> >> \
foreignKey="FD_OPENDATE"/><br>> >> <Measure \
name="DpCount" column="MS_DEPOSIT_ID"<br>> >> \
aggregator="distinct-count"/><br>> >> \
<Measure name="MinMainAcc" column="MR_MIN_MAINACCOUNT"<br>> >> \
aggregator="sum" /><br>> >> </Cube><br>> \
>><br>> >> when I execute MDX:<br>> >><br>> >> \
select<br>> >> [OpenDate].[Month].members on axis(0),<br>> >> \
[Measures].[DpCount] on axis(1)<br>> >> from Deposit<br>> \
>><br>> >> or<br>> >><br>> >> select<br>> \
>> [OpenDate].[Month].members on axis(0),<br>> >> \
[Measures].[MinMainAcc] on axis(1)<br>> >> from Deposit<br>> \
>><br>> >> Mondrian executes 93 SQL queries for each month like the \
following:<br>> >><br>> >> select "BAS_DM_DATE"."J_DAY" as "c0" \
from "BIDWH"."BAS_DM_DATE"<br>> >> "BAS_DM_DATE" where \
("BAS_DM_DATE"."J_MONTH" = 1 and<br>> >> "BAS_DM_DATE"."J_YEAR" = 1384) \
group by "BAS_DM_DATE"."J_DAY" order<br>> >> by "BAS_DM_DATE"."J_DAY" ASC \
NULLS LAST<br>> >><br>> >> I don't understand the reason of such \
queries. I have the same<br>> >> problem with other cubes, and it takes so \
long for some cubes.<br>> >> I checked the MDX in versions 3.3.0.14703, \
3.4.1, and 3.5.0 and the<br>> >> results are the same.<br>> \
>><br>> >> Is my configuration wrong? Is there any solution to solve \
the problem?<br>> >><br>> >><br>> >><br>> \
>><br>> >> _______________________________________________<br>> \
>> Mondrian mailing list<br>> >> <a \
ymailto="mailto:Mondrian@pentaho.org" \
href="mailto:Mondrian@pentaho.org">Mondrian@pentaho.org</a> <mailto:<a \
ymailto="mailto:Mondrian@pentaho.org" \
href="mailto:Mondrian@pentaho.org">Mondrian@pentaho.org</a>><br>> <mailto:<a \
ymailto="mailto:Mondrian@pentaho.org" \
href="mailto:Mondrian@pentaho.org">Mondrian@pentaho.org</a> <mailto:<a \
ymailto="mailto:Mondrian@pentaho.org" \
href="mailto:Mondrian@pentaho.org">Mondrian@pentaho.org</a>>><br>> >> \
http://lists.pentaho.org/mailman/listinfo/mondrian<br>> ><br>> ><br>> \
><br>> > _______________________________________________<br>> > \
Mondrian mailing list<br>> > <a ymailto="mailto:Mondrian@pentaho.org" \
href="mailto:Mondrian@pentaho.org">Mondrian@pentaho.org</a> <mailto:<a \
ymailto="mailto:Mondrian@pentaho.org" \
href="mailto:Mondrian@pentaho.org">Mondrian@pentaho.org</a>><br>> > <a \
href="http://lists.pentaho.org/mailman/listinfo/mondrian" \
target="_blank">http://lists.pentaho.org/mailman/listinfo/mondrian</a><br>> \
_______________________________________________<br>> Mondrian mailing list<br>> \
<a ymailto="mailto:Mondrian@pentaho.org" \
href="mailto:Mondrian@pentaho.org">Mondrian@pentaho.org</a> <mailto:<a \
ymailto="mailto:Mondrian@pentaho.org" \
href="mailto:Mondrian@pentaho.org">Mondrian@pentaho.org</a>><br>> <a \
href="http://lists.pentaho.org/mailman/listinfo/mondrian" \
target="_blank">http://lists.pentaho.org/mailman/listinfo/mondrian</a><br>><br>><br>><br>><br>> \
_______________________________________________<br>> Mondrian mailing list<br>> \
<a ymailto="mailto:Mondrian@pentaho.org" \
href="mailto:Mondrian@pentaho.org">Mondrian@pentaho.org</a><br>> <a \
href="http://lists.pentaho.org/mailman/listinfo/mondrian" \
target="_blank">http://lists.pentaho.org/mailman/listinfo/mondrian</a><br><br><br> \
</div> </div> </div></body></html>
_______________________________________________
Mondrian mailing list
Mondrian@pentaho.org
http://lists.pentaho.org/mailman/listinfo/mondrian
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic