[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