Uploaded image for project: 'Pentaho Analysis - Mondrian'
  1. Pentaho Analysis - Mondrian
  2. MONDRIAN-811

Depth first search for hierarchy name performs poorly

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed
    • Severity: Medium
    • Resolution: Fixed
    • Affects Version/s: None
    • Component/s: None
    • Labels:
      None
    • Notice:
      When an issue is open, the "Fix Version/s" field conveys a target, not necessarily a commitment. When an issue is closed, the "Fix Version/s" field conveys the version that the issue was fixed in.

      Description

      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">
      <Table name="time_by_day"/>
      <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>
      <Hierarchy name="Weekly" hasAll="true" allMemberName="All Times" primaryKey="time_id" uniqueKeyLevelName="Epoch">
      <Table name="time_by_day"/>
      <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"/>
      </Hierarchy>
      </Dimension>

      to refer to the weekly hierarchy, we use names like [Time.Weekly].[2010].[37]. ([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.

        Attachments

          Activity

            People

            • Assignee:
              jhyde Julian Hyde (Inactive)
              Reporter:
              jbarnett Joe Barnett
            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: