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

Incorrect tree structure in parent-child hierarchy

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed
    • Severity: Medium
    • Resolution: Not a Bug
    • Affects Version/s: 3.6.0 (5.0.0 GA Suite Release)
    • Fix 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.
    • Browser:
      Google Chrome 32.x
    • Operating System/s:
      Windows 7

      Description

      If parent id greater than child id then mondrian returns incorrect tree structure.

      Example

      Scheme description:

      <Schema name="test">
      <Cube name="org">
      <Table name="fact"/>
      <Dimension type="StandardDimension" foreignKey="org_id" name="org">
      <Hierarchy primaryKey="id" hasAll="false">
      <Table name="org"/>
      <Level name="org" column="id" nameColumn="name" parentColumn="parent_id" nullParentValue="0" type="Integer" uniqueMembers="true">
      <Closure parentColumn="parent_id" childColumn="id">
      <Table name="closure"/>
      </Closure>
      </Level>
      </Hierarchy>
      </Dimension>
      <Measure name="amount" column="amount" datatype="Numeric" aggregator="sum"/>
      </Cube>
      </Schema>

      SQL script 1:

      create table "org"("id" integer, "parent_id" integer, "name" varchar(25))
      create table "fact"("org_id" integer not null, "amount" numeric(18,2) not null)
      create table "closure"("id" integer not null, "parent_id" integer not null, "distance" integer)
      insert into "org" values(1,0,'org1')
      insert into "org" values(2,1,'org2')
      insert into "org" values(3,2,'org3')
      insert into "fact" values(1,1)
      insert into "fact" values(2,2)
      insert into "fact" values(3,3)
      insert into "closure" values(1,1,0)
      insert into "closure" values(2,1,1)
      insert into "closure" values(2,2,0)
      insert into "closure" values(3,1,2)
      insert into "closure" values(3,2,1)
      insert into "closure" values(3,3,0)

      SQL script 2:

      create table "org"("id" integer, "parent_id" integer, "name" varchar(25))
      create table "fact"("org_id" integer not null, "amount" numeric(18,2) not null)
      create table "closure"("id" integer not null, "parent_id" integer not null, "distance" integer)
      insert into "org" values(4,0,'org1')
      insert into "org" values(2,4,'org2')
      insert into "org" values(3,2,'org3')
      insert into "fact" values(4,1)
      insert into "fact" values(2,2)
      insert into "fact" values(3,3)
      insert into "closure" values(4,4,0)
      insert into "closure" values(2,4,1)
      insert into "closure" values(2,2,0)
      insert into "closure" values(3,4,2)
      insert into "closure" values(3,2,1)
      insert into "closure" values(3,3,0)

      If use script 1, then all right (test1.jpg).
      But if use script 2, Mondrian returns incorrect result (test2.jpg).

      In log will be:
      The level [org].[org] makes use of the 'parentColumn' attribute, but a parent member for key 2 is missing. This can be due to the usage of the NativizeSet MDX function with a list of members form a parent-child hierarchy that doesn't include all parent members in its definition. Using NativizeSet with a parent-child hierarchy requires the parent members to be included in the set, or the hierarchy cannot be properly built natively.

      Possible solution

      In SqlTupleReader replace:

      if (parentMember == null)

      { LOGGER.warn( MondrianResource.instance() .LevelTableParentNotFound.str( childLevel.getUniqueName(), String.valueOf(parentValue))); }

      to (copied from commit d5548ee42dde20d0d947d48d91114fb4e10b54d7):

      if (parentMember == null)

      { // Parent member has not been loaded yet. Skip // this member this time. As long as the data // forms a hierarchy, we will complete in a // number of passes not greater than the depth // of the hierarchy. ++missedMemberCount; return -1; }

      Probably this solution is not correct, because program may freezes, if in fact no parent record

        Attachments

        1. test1.jpg
          test1.jpg
          6 kB
        2. test2.jpg
          test2.jpg
          5 kB

          Activity

            People

            Assignee:
            Unassigned Unassigned
            Reporter:
            iisln-id Igor Demin
            Votes:
            1 Vote for this issue
            Watchers:
            3 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved: