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

Paul Stoellberger p.stoellberger at gmail.com
Mon Dec 3 09:50:14 EST 2012


Did you clear cache, restart the server etc?

On Dec 3, 2012, at 3:47 PM, 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>
> >> http://lists.pentaho.org/mailman/listinfo/mondrian
> >
> >
> >
> > _______________________________________________
> > Mondrian mailing list
> > 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
> 
> 
> _______________________________________________
> 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/6c576451/attachment-0001.html 


More information about the Mondrian mailing list