[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 09:31:30 EST 2012
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 reasonof 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?
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.pentaho.org/pipermail/mondrian/attachments/20121203/d86af870/attachment.html
More information about the Mondrian
mailing list