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

Problem while building Parent Child hierarchy with hasAll=false

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed
    • Severity: Unknown
    • Resolution: Not a Bug
    • Affects Version/s: 3.4.1 GA (4.5.0 GA Suite Release)
    • Fix Version/s: None
    • Labels:
      None
    • Environment:
      I encounter the problem on Ubuntu 12.04.1 with postgresql and derby. But I didn't test any other environment.
    • 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

      here is the case with foodmart.xml

      I run those updates in pg_admin, in order to rename the employe id from
      1 to 99999 :

      update employee
      set employee_id='99999'
      where employee_id='1'

      update employee
      set supervisor_id='99999'
      where supervisor_id='1'

      update employee_closure set employee_id='99999'
      where employee_id='1'

      update employee_closure set supervisor_id='99999'
      where supervisor_id='1'

      With hasAll=true on the employee hierarchy, it seems to have no problems.
      But as our hierarchies already have an "all" member, we decided to set
      hasAll=false.
      (This also allow us to have a correct total, but I don't know if it is a
      side effect or a feature)

      If you set the hierarchy in FoodMart.xml that way :

      <Dimension name="Employees" foreignKey="employee_id">
      <Hierarchy hasAll="false" defaultMember="Sheri Nowmer"
      primaryKey="employee_id">
      <Table name="employee"/>
      <Level name="Employee Id" type="Numeric" uniqueMembers="true"
      column="employee_id" parentColumn="supervisor_id"
      nameColumn="full_name" nullParentValue="0">
      <Closure parentColumn="supervisor_id" childColumn="employee_id">
      <Table name="employee_closure"/>
      </Closure>
      <Property name="Marital Status" column="marital_status"/>
      <Property name="Position Title" column="position_title"/>
      <Property name="Gender" column="gender"/>
      <Property name="Salary" column="salary"/>
      <Property name="Education Level" column="education_level"/>
      <Property name="Management Role" column="management_role"/>
      </Level>
      </Hierarchy>
      </Dimension>

      and run in a short class that directly query mondrian the following MDX :

      select

      {[Measures].[Org Salary], [Measures].[Count]} ON COLUMNS,
      Hierarchize(Union(Crossjoin({[Position].[All Position]}, {[Employees].[Sheri Nowmer]}), Crossjoin({[Position].[All Position]},
      [Employees].[Sheri Nowmer].Children))) ON ROWS
      from [HR]
      where [Time].[1997]
      you get in console :
      ...
      DEBUG [main] - Util.lookupCompound: parent.name=Employees,
      category=member, names=[Sheri Nowmer]
      DEBUG [main] - 2: SqlTupleReader.readTuples [[Employees].[Employee Id]]:
      executing sql [select "employee"."supervisor_id" as "c0",
      "employee"."employee_id" as "c1", "employee"."full_name" as "c2",
      "employee"."marital_status" as "c3", "employee"."position_title" as
      "c4", "employee"."gender" as "c5", "employee"."salary" as "c6",
      "employee"."education_level" as "c7", "employee"."management_role" as
      "c8" from "employee" as "employee" group by "employee"."supervisor_id",
      "employee"."employee_id", "employee"."full_name",
      "employee"."marital_status", "employee"."position_title",
      "employee"."gender", "employee"."salary", "employee"."education_level",
      "employee"."management_role" order by "employee"."supervisor_id" ASC
      NULLS FIRST, "employee"."employee_id" ASC NULLS LAST]
      ...
      DEBUG [main] - 2: , exec 23 ms
      DEBUG [main] - SqlTupleReader.readTuples [[Employees].[Employee Id]]:
      executing sql [select "employee"."supervisor_id" as "c0",
      "employee"."employee_id" as "c1", "employee"."full_name" as "c2",
      "employee"."marital_status" as "c3", "employee"."position_title" as
      "c4", "employee"."gender" as "c5", "employee"."salary" as "c6",
      "employee"."education_level" as "c7", "employee"."management_role" as
      "c8" from "employee" as "employee" group by "employee"."supervisor_id",
      "employee"."employee_id", "employee"."full_name",
      "employee"."marital_status", "employee"."position_title",
      "employee"."gender", "employee"."salary", "employee"."education_level",
      "employee"."management_role" order by "employee"."supervisor_id" ASC
      NULLS FIRST, "employee"."employee_id" ASC NULLS LAST], exec 23 ms
      WARN [main] - The level [Employees].[Employee Id] 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.
      WARN [main] - The level [Employees].[Employee Id] 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.
      ...
      etc, lot of warn messages
      ...
      DEBUG [main] - 2: , exec+fetch 135 ms, 1155 rows
      DEBUG [main] - SqlTupleReader.readTuples [[Employees].[Employee Id]]:
      done executing sql [select "employee"."supervisor_id" as "c0",
      "employee"."employee_id" as "c1", "employee"."full_name" as "c2",
      "employee"."marital_status" as "c3", "employee"."position_title" as
      "c4", "employee"."gender" as "c5", "employee"."salary" as "c6",
      "employee"."education_level" as "c7", "employee"."management_role" as
      "c8" from "employee" as "employee" group by "employee"."supervisor_id",
      "employee"."employee_id", "employee"."full_name",
      "employee"."marital_status", "employee"."position_title",
      "employee"."gender", "employee"."salary", "employee"."education_level",
      "employee"."management_role" order by "employee"."supervisor_id" ASC
      NULLS FIRST, "employee"."employee_id" ASC NULLS LAST], exec+fetch 135
      ms, 1155 rows
      DEBUG [Mondrian Monitor] - SqlStatementEndEvent(2)
      DEBUG [main] - HierarchyBase.lookupChild: name=Employees,
      childname=[Sheri Nowmer] returning elementname=Sheri Nowmer
      DEBUG [main] - Util.lookupCompound: found child.name=Sheri Nowmer,
      child.class=mondrian.rolap.SqlMemberSource$RolapParentChildMember
      DEBUG [main] - Normal cardinality for [Employees]
      DEBUG [main] - HierarchyUsage: kind=PRIVATE,
      hierarchyName=Employees$Closure, fullName=Employees,
      foreignKey=employee_id, source=null, level=null, name=Employees,
      cubeDim=mondrian.olap.MondrianDef$Dimension
      DEBUG [main] - HierarchyUsage: kind=PRIVATE,
      hierarchyName=Employees$Closure, fullName=Employees,
      foreignKey=employee_id, source=null, level=null, name=Employees,
      cubeDim=mondrian.olap.MondrianDef$Dimension
      DEBUG [main] - RolapCube.createUsage: cube=HR,
      hierarchy=Employees$Closure, usage=HierarchyUsage: kind=PRIVATE,
      hierarchyName=Employees$Closure, fullName=Employees,
      foreignKey=employee_id, source=null, level=null, name=Employees
      DEBUG [main] - RolapCube.createUsage: register HierarchyUsage:
      kind=PRIVATE, hierarchyName=Employees$Closure, fullName=Employees,
      foreignKey=employee_id, source=null, level=null, name=Employees
      DEBUG [main] - Normal cardinality for [Employees$Closure]
      DEBUG [main] - Normal cardinality for [Employees$Closure]
      DEBUG [main] - RolapCube.getUsages: name=Employees$Closure
      ...
      etc
      ...
      Axis #0: {[Time].[1997]}
      Axis #1: {[Measures].[Org Salary]} {[Measures].[Count]}
      Axis #2: {[Position].[All Position], [Employees].[Sheri Nowmer].[Derrick Whelply]} {[Position].[All Position], [Employees].[Sheri Nowmer].[Michael Spence]} {[Position].[All Position], [Employees].[Sheri Nowmer].[Maya Gutierrez]} {[Position].[All Position], [Employees].[Sheri Nowmer].[Roberta Damstra]} {[Position].[All Position], [Employees].[Sheri Nowmer].[Rebecca Kanagaki]} {[Position].[All Position], [Employees].[Sheri Nowmer].[Darren Stanz]} {[Position].[All Position], [Employees].[Sheri Nowmer].[Donna Arnold]}
      Row #0: 36 494,07 €
      Row #0: 7 236
      Row #1:
      Row #1:
      Row #2:
      Row #2:
      Row #3: 428,76 €
      Row #3: 36
      Row #4: 234,36 €
      Row #4: 24
      Row #5: 832,68 €
      Row #5: 60
      Row #6: 577,80 €
      Row #6: 24


      So you get the WARN message that tells the hierarchy is not in the
      correct order, but the result seems ok.
      In JPivot, when you try to use the navigator component, you get all the
      record directly (like a sql resultset) instead of a well built
      hierarchy. (I haven't get how it queries mondrian yet)

      If we do not use Closure Table there's a lot of query on the employee table but that's ok because only closure table allow to load hierarchy in a few query. But it ends with that error :
      Exception in thread "main" mondrian.olap.MondrianException: Mondrian Error:Failed to parse query 'select {[Measures].[Org Salary], [Measures].[Count]}

      ON COLUMNS, Crossjoin(

      {[Position].[All Position]}

      , [Employees].[Sheri Nowmer].Children) ON ROWS from [HR] where [Time].[1997] '
      at mondrian.resource.MondrianResource$_Def0.ex(MondrianResource.java:942)
      at mondrian.olap.ConnectionBase.parseStatement(ConnectionBase.java:100)
      at mondrian.rolap.RolapConnection.parseStatement(RolapConnection.java:765)
      at mondrian.olap.ConnectionBase.parseQuery(ConnectionBase.java:55)
      at com.axege.test.analysis.TestMondrian.main(TestMondrian.java:33)
      Caused by: mondrian.olap.MondrianException: Mondrian Error:MDX object '[Employees].[Sheri Nowmer]' not found in cube 'HR'
      at mondrian.resource.MondrianResource$_Def1.ex(MondrianResource.java:959)
      at mondrian.olap.Util.lookup(Util.java:1079)
      at mondrian.olap.Id.accept(Id.java:103)
      at mondrian.olap.ValidatorImpl.validate(ValidatorImpl.java:79)
      at mondrian.olap.fun.FunUtil.resolveFunArgs(FunUtil.java:1981)
      at mondrian.mdx.UnresolvedFunCall.accept(UnresolvedFunCall.java:102)
      at mondrian.olap.ValidatorImpl.validate(ValidatorImpl.java:79)
      at mondrian.olap.fun.FunUtil.resolveFunArgs(FunUtil.java:1981)
      at mondrian.mdx.UnresolvedFunCall.accept(UnresolvedFunCall.java:102)
      at mondrian.olap.ValidatorImpl.validate(ValidatorImpl.java:79)
      at mondrian.olap.QueryAxis.resolve(QueryAxis.java:213)
      at mondrian.olap.ValidatorImpl.validate(ValidatorImpl.java:140)
      at mondrian.olap.Query.resolve(Query.java:561)
      at mondrian.olap.Query.resolve(Query.java:446)
      at mondrian.olap.Query.<init>(Query.java:200)
      at mondrian.olap.Query.<init>(Query.java:161)
      at mondrian.olap.Parser$FactoryImpl.makeQuery(Parser.java:927)
      at mondrian.parser.MdxParserImpl.selectStatement(MdxParserImpl.java:1184)
      at mondrian.parser.MdxParserImpl.statement(MdxParserImpl.java:1016)
      at mondrian.parser.MdxParserImpl.statementEof(MdxParserImpl.java:185)
      at mondrian.parser.JavaccParserValidatorImpl.parseInternal(JavaccParserValidatorImpl.java:57)
      at mondrian.olap.ConnectionBase.parseStatement(ConnectionBase.java:96)
      ... 3 more

      Damien

        Attachments

          Activity

            People

            • Assignee:
              project admin Triage
              Reporter:
              dhostin damien hostin
            • Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: