Affects Version/s: None
Fix Version/s: 3.3.0 GA (4.1.0 GA Suite Release)
Tested on a custom build of mondrian 2.4, but looking at latest p4 source, seems to still be the case. Not sure how the SsasCompatibleNaming changes things though?
In any case, we have a Time dimension with two hierarchies:
<Dimension name="Time" foreignKey="time_id" type="TimeDimension">
<Hierarchy hasAll="true" allMemberName="All Times" primaryKey="time_id" uniqueKeyLevelName="Epoch">
<Level name="Year" column="the_year" uniqueMembers="true" levelType="TimeYears" type="Numeric"/>
<Level name="Month" column="month_of_year" uniqueMembers="false" levelType="TimeMonths" type="Numeric"/>
<Level name="Day" column="day_of_month" uniqueMembers="false" type="Numeric" levelType="TimeDays"/>
<Hierarchy name="Weekly" hasAll="true" allMemberName="All Times" primaryKey="time_id" uniqueKeyLevelName="Epoch">
<Level name="Year" column="weekly_the_year" type="Numeric" uniqueMembers="true" levelType="TimeYears"/>
<Level name="Week" column="week_of_year" uniqueMembers="false" type="Numeric" levelType="TimeWeeks"/>
to refer to the weekly hierarchy, we use names like [Time.Weekly]... ([Time].[Weekly] does not appear to work, and returns a null member in our 2.4 build – again, not sure how SsasCompatibleNaming changes things)
This works, but if my time dimension is defined at the bottom of the schema xml, results in mondrian searching through every other dimension for [Time.Weekly], using queries like:
select `dim`.`dim_value` as `c0` from `dim` as `dim` where UPPER(`dim`.`dim_value`) = UPPER('Time.Weekly') group by `dim`.`dim_value` order by ISNULL(`dim`.`dim_value`), `dim`.`dim_value` ASC;
The problem is that these query can be quite slow on certain very large dimensions, and searching many potentially large dimensions just compounds the issue.
Would suggest a breadth first search (try to find a dimension, then a hierarchy across all dimensions, then a level across all hierarchies, then a member name) in CubeBase#lookupChild() and related OlapElement#lookupChild() implementations rather than a depth first search, but there may be some tradeoffs there as well. We are currently working around this by placing the time dimension as the first one in the schema xml, which works for our usage because the time dimension may be the only one we use with multiple hierarchies.