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

[Pedro Alves]

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