[Mondrian] Strange SQLs to find member of child level when getting members of parent level in MDX

m.xinu mehdi_xinu at yahoo.com
Tue Dec 4 04:24:34 EST 2012


Hi,

After I sent a request yesterday with title "Problem with 93 SQL queries for each member of month level of each year", I checked some MDX against Mondrian and I found Mondrian executes some strange queries for all dimensions. The scenario is:

If you call .Members on any level in your MDX, Mondrian executes an SQL query for each member of that level to find members of the sublevel!


For example, I have a dimension called Branch with the following definition:

    <Dimension name="Branch">
        <Hierarchy primaryKey="BRANCH_ID" hasAll="true" allMemberName="All">
            <Table schema="BIDWH" name="COR_DM_BRANCH" />
            <Level name="State" column="STATE_ID" captionColumn="STATE_NAME" uniqueMembers="true" type="Integer" />
            <Level name="City" column="CITY_ID" captionColumn="CITY_NAME" uniqueMembers="true" type="Integer" />
            <Level name="Branch" column="BRANCH_ID" captionColumn="BRANCH_NAME" uniqueMembers="true" type="Integer"/>
        </Hierarchy>
    </Dimension>


and when I call [Branch].[State].Members the following SQL query is executed for each state:
select "COR_DM_BRANCH"."CITY_ID" as "c0", "COR_DM_BRANCH"."CITY_NAME" as "c1" from "BIDWH"."COR_DM_BRANCH" "COR_DM_BRANCH" where ("COR_DM_BRANCH"."STATE_ID" = 1699005429) group by "COR_DM_BRANCH"."CITY_ID", "COR_DM_BRANCH"."CITY_NAME" order by "COR_DM_BRANCH"."CITY_ID" ASC NULLS LAST

In fact in my MDX, I only need the members of State level, but Mondrian try to find all cities of each state! I also checked for City and Mondrian executes the similar query for each City to find the Branches. I checked it on most of my dimensions and the behavior is all the same.

As this behavior is the same for Date dimension and my Date dimension has many months, when I want to see members on Month level, It takes so long for results.

Is there any solution? Is it a bug? or can I change this behavior with a special config?
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.pentaho.org/pipermail/mondrian/attachments/20121204/bfc981c9/attachment.html 


More information about the Mondrian mailing list