<html><head><meta http-equiv="Content-Type" content="text/html charset=iso-8859-1"></head><body style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space; ">That isn't the behavior I'd expect. Can you please log a jira case.<div><br><div>
<div>Julian</div><br class="Apple-interchange-newline">
</div>
<br><div><div>On Dec 4, 2012, at 1:24 AM, m.xinu <<a href="mailto:mehdi_xinu@yahoo.com">mehdi_xinu@yahoo.com</a>> wrote:</div><br class="Apple-interchange-newline"><blockquote type="cite"><div><div style="background-color: rgb(255, 255, 255); font-family: 'times new roman', 'new york', times, serif; font-size: 12pt; "><div>Hi,</div><div><br></div><div style="font-size: 16px; font-family: 'times new roman', 'new york', times, serif; background-color: transparent; font-style: normal; ">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:</div><div style="font-size: 16px; font-family: times new roman,new york,times,serif; background-color: transparent; font-style: normal;"><span style="color: rgb(128, 0, 0);"><br></span></div><div style="font-size: 16px; font-family: 'times new roman', 'new york', times, serif; background-color: transparent; font-style: normal; "><span style="color: rgb(128, 0, 0);"><span><span style="font-weight:
bold;">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!</span></span></span><br></div><div style="font-size: 16px; font-family: 'times new roman', 'new york', times, serif; background-color: transparent; font-style: normal; "><br></div><div style="font-size: 16px; font-family: 'times new roman', 'new york', times, serif; background-color: transparent; font-style: normal; ">For example, I have a dimension called Branch with the following definition:</div><div style="font-size: 16px; font-family: 'times new roman', 'new york', times, serif; background-color: transparent; font-style: normal; "><br></div><div style="font-style: normal; "><span style="font-size: 13px;"><span><span style="font-family: Courier New,courier,monaco,monospace,sans-serif;"><span style="background-color: rgb(255, 223,
191);"> <Dimension name="Branch"><br> <Hierarchy primaryKey="BRANCH_ID" hasAll="true" allMemberName="All"><br> <Table schema="BIDWH" name="COR_DM_BRANCH" /><br> <Level name="State" column="STATE_ID" captionColumn="STATE_NAME" uniqueMembers="true" type="Integer" /><br> <Level name="City" column="CITY_ID" captionColumn="CITY_NAME" uniqueMembers="true" type="Integer" /><br> <Level name="Branch" column="BRANCH_ID" captionColumn="BRANCH_NAME" uniqueMembers="true" type="Integer"/><br> </Hierarchy><br> </Dimension><br></span></span></span></span></div><div style="font-size: 16px; font-family: 'times new roman', 'new york', times, serif; background-color: transparent; font-style: normal; "><br></div><div style="font-size: 16px; font-family: 'times new roman', 'new york', times, serif; background-color: transparent; font-style: normal; ">and when I call [Branch].[State].Members the following SQL query is executed for each state:</div><div style="font-style: normal; "><span style="font-size: 13px;"><span style="font-family: Courier New,courier,monaco,monospace,sans-serif;"><span style="background-color: rgb(255, 223, 191);">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</span></span></span></div><div style="font-size: 16px; font-family: 'times new roman', 'new york', times, serif; background-color: transparent; font-style: normal; "><br></div><div style="font-size: 16px; font-family: 'times new roman', 'new york', times, serif; background-color: transparent; font-style: normal; ">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.</div><div style="font-size: 16px; font-family: 'times new roman', 'new york', times, serif; background-color: transparent; font-style: normal; "><br></div><div style="font-size: 16px; font-family: 'times new roman', 'new york', times, serif; background-color: transparent; font-style: normal; ">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.</div><div style="font-size: 16px; font-family: 'times new roman', 'new york', times, serif; background-color: transparent; font-style: normal; "><br></div><div style="font-size: 16px; font-family: 'times new roman', 'new york', times, serif; background-color: transparent; font-style: normal; ">Is there any solution? Is it a bug? or can I change this behavior with a special config?<br></div></div></div>_______________________________________________<br>Mondrian mailing list<br><a href="mailto:Mondrian@pentaho.org">Mondrian@pentaho.org</a><br>http://lists.pentaho.org/mailman/listinfo/mondrian<br></blockquote></div><br></div></body></html>