Pentaho Analysis - Mondrian
  1. Pentaho Analysis - Mondrian
  2. MONDRIAN-853

When using CASE WHEN in a CalculatedMember values are not returned the way expected. Return value looks like this : [Lmondrian.olap.Member;@6073d6f3

    Details

    • Type: Bug Bug
    • Status: Open
    • Priority: Severe Severe
    • Resolution: Unresolved
    • Affects Version/s: 3.2.1 GA (3.7.0 GA Suite Release)
    • Fix Version/s: Mondrian Backlog
    • Component/s: None
    • Labels:
      None
    • Environment:
      I am running Ubuntu 10.04, retrieving the data with PAT 0.8 under Chrome 8.0 and PRD 3.6.1.GA
    • 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.
    • Operating System/s:
      Ubuntu 10.04

      Description

      <?xml version="1.0"?>
      <Schema name="LogsSchema">
      <Cube name="Log">
      <Table name="livrable_fact_table"/>

      <Dimension name="Roadmap" foreignKey="livrable_id">
      <Hierarchy hasAll="true" primaryKey="livrableid">
      <Table name="dimlivrable"/>
      <Level name="RoadmapD" column="roadmapname" type="String" uniqueMembers="false"/>
      <Level name="Niveau" column="level" type="Numeric" uniqueMembers="false"/>
      <Level name="Exigence" column="exigencename" type="String" uniqueMembers="false"/>
      <Level name="Livrable" column="livrablename" type="String" uniqueMembers="false"/>
      </Hierarchy>
      </Dimension>

      <Measure name="Realized" column="done" aggregator="sum" formatString="#"/>
      <CalculatedMember name="PLTEST" dimension="Measures">
      <Formula>
      CASE [Roadmap].CurrentMember.Level.Name
      WHEN "Livrable"
      THEN ([Roadmap].CurrentMember.Value / COUNT([Roadmap].CurrentMember.Parent.Children))
      WHEN "Exigence"
      THEN SUM([Roadmap].CurrentMember.Children, [Measures].[PLTEST])
      WHEN "Niveau"
      THEN (SUM([Roadmap].CurrentMember.Children, [Measures].[PLTEST]) / COUNT([Roadmap].CurrentMember.Children))
      WHEN "RoadmapD"
      THEN ([Roadmap].[HSEGR- 01. 001-C03FUE1].[1], [Measures].[PLTEST])
      ELSE 999
      END</Formula>
      <CalculatedMemberProperty name="FORMAT_STRING" value="##,###"/>
      </CalculatedMember>

      </Cube>
      </Schema>

      You can find above the mondrian schema that I am using.

      The problem is that the WHEN "RoadmapD" THEN ([Roadmap].[HSEGR- 01. 001-C03FUE1].[1], [Measures].[PLTEST]) returns me something that has this shape : [Lmondrian.olap.Member;@6073d6f3

      Why Do I think that is a Bug :

      Using PAT I executed this MDX query

      WITH
      MEMBER [Measures].[Test]
      AS ' ( [Roadmap].[HSEGR- 01. 001-C03FUE1].[1], [Measures].[PLTEST]) ', FORMAT_STRING ='###########'

      MEMBER [Measures].[Test2]
      AS 'CASE [Roadmap].CurrentMember.Level.Name
      WHEN "Livrable"
      THEN ( [Roadmap].CurrentMember.Value / COUNT ( [Roadmap].CurrentMember.Parent.Children))
      WHEN "Exigence"
      THEN SUM ( [Roadmap].CurrentMember.Children, [Measures].[PLTEST])
      WHEN "Niveau"
      THEN ( SUM ( [Roadmap].CurrentMember.Children, [Measures].[PLTEST]) / COUNT ( [Roadmap].CurrentMember.Children))
      WHEN "RoadmapD"
      THEN ( [Roadmap].[HSEGR- 01. 001-C03FUE1].[1], [Measures].[PLTEST]) ELSE 5 END', FORMAT_STRING="###########"

      SELECT

      { [Measures].[Realized], [Measures].[PLTEST], [Measures].[Test], [Measures].[Test2] }

      ON COLUMNS,

      { [Roadmap].[HSEGR- 01. 001-C03FUE1], [Roadmap].[HSEGR- 01. 001-C03FUE1].Children }

      ON ROWS

      FROM
      [Log]

      You can see the result to this link :

      As you can see the first member [Measures].[Test] returns the [Measures].[PLTEST] that I wanted to retrieve.
      But the second member [Measures].[Test2] does not return the value expected.

      This is my first "bug" that I report in a open source project. I hope that I gave you enough information. If not please contact me.

        Activity

        Hide
        Yanis Guenane added a comment -

        The result of the query

        Show
        Yanis Guenane added a comment - The result of the query
        Hide
        Yanis Guenane added a comment -

        Sorry I did not put the link, I attached the result as an image

        Show
        Yanis Guenane added a comment - Sorry I did not put the link, I attached the result as an image
        Hide
        Julian Hyde added a comment -

        Very well logged bug! I was able to reproduce and create a simpler test case.

        The test case is FunctionTest.testCaseTuple. It is in change 13982.

        I think the cause is as follows. With the bug, CASE returns a member array "[Lmondrian.olap.Member;@151b0a5". Type deduction should realize that the result is a scalar, therefore a tuple (represented by a member array) needs to be evaluated to a scalar. I think that if we get the type deduction right, the MDX exp compiler will handle the rest.

        Show
        Julian Hyde added a comment - Very well logged bug! I was able to reproduce and create a simpler test case. The test case is FunctionTest.testCaseTuple. It is in change 13982. I think the cause is as follows. With the bug, CASE returns a member array "[Lmondrian.olap.Member;@151b0a5". Type deduction should realize that the result is a scalar, therefore a tuple (represented by a member array) needs to be evaluated to a scalar. I think that if we get the type deduction right, the MDX exp compiler will handle the rest.
        Hide
        Yanis Guenane added a comment -

        How can I know the advancement of the ticket ? Does someone have been assigned to this issue ? Basically all the newbie stuff.

        Show
        Yanis Guenane added a comment - How can I know the advancement of the ticket ? Does someone have been assigned to this issue ? Basically all the newbie stuff.
        Hide
        Tom Warfield added a comment -

        I got around this problem by converting the CASE statement to a bunch of IIFs.
        Any better workaround for this - any way to manually cast the " tuple (represented by a member array) " to a number? I tried:
        cast ( case .... end as numeric)
        and
        ( case .... end as numeric) . Item (0)
        but neither of those worked.

        Show
        Tom Warfield added a comment - I got around this problem by converting the CASE statement to a bunch of IIFs. Any better workaround for this - any way to manually cast the " tuple (represented by a member array) " to a number? I tried: cast ( case .... end as numeric) and ( case .... end as numeric) . Item (0) but neither of those worked.

          People

          • Assignee:
            Unassigned User
            Reporter:
            Yanis Guenane
          • Votes:
            1 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

            • Created:
              Updated: