[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:33:57 EST 2012


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
> http://lists.pentaho.org/mailman/listinfo/mondrian

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.pentaho.org/pipermail/mondrian/attachments/20121203/664df0fe/attachment-0001.html 


More information about the Mondrian mailing list