[Mondrian] Problem with 93 SQL queries for each member of month level of each year

m.xinu mehdi_xinu at yahoo.com
Mon Dec 3 10:03:48 EST 2012


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 at gmail.com>
To: m.xinu <mehdi_xinu at yahoo.com>; Mondrian developer mailing list <mondrian at 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 at gmail.com>
> *To:* Mondrian developer mailing list <mondrian at 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 at pentaho.org <mailto:Mondrian at pentaho.org>
> <mailto:Mondrian at pentaho.org <mailto:Mondrian at pentaho.org>>
> >> http://lists.pentaho.org/mailman/listinfo/mondrian
> >
> >
> >
> > _______________________________________________
> > Mondrian mailing list
> > Mondrian at pentaho.org <mailto:Mondrian at pentaho.org>
> > http://lists.pentaho.org/mailman/listinfo/mondrian
> _______________________________________________
> Mondrian mailing list
> Mondrian at pentaho.org <mailto:Mondrian at pentaho.org>
> http://lists.pentaho.org/mailman/listinfo/mondrian
>
>
>
>
> _______________________________________________
> Mondrian mailing list
> 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/20121203/ff768bf5/attachment.html 


More information about the Mondrian mailing list