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

Various issues preventing analyzer from moving to olap4j

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Unknown Unknown
    • Resolution: Fixed
    • Affects Version/s: None
    • Component/s: None
    • Labels:
      None
    • QA Validation Status:
      Not Yet Validated

      Description

      Attached is a spreadsheet of issues preventing Pentaho Analyzer from moving to olap4j. There are work items for Pentaho BIServer, Analyzer and olap4j. olap4j work items may have corresponding work in Mondrian (especially those that change the olap4j API).

        Activity

        Hide
        Julian Hyde added a comment -
        Spreadsheet of work items.
        Show
        Julian Hyde added a comment - Spreadsheet of work items.
        Hide
        Julian Hyde added a comment -
        Here are the olap4j items, and my commentary on them.

        I've mentioned various API changes we can make for olap4j-1.1. (We can't make any API changes in a patch release.) olap4j-1.1 is probably 6-9 months away, so I've proposed a short-term fix for each problem.

        Benny, All of the proposed fixes are just proposals. I need you to say "Yes, your proposal will solve my problem" before I do anything.

        > 5. Add support for annotations

        We could add add a method to org.olap4j.metadata.MetadataElement:

            Map<String, Annotation> getAnnotationMap();

        But then I think someone would soon need some other piece of metadata on, say, Member or Level, that is not an annotation. So I propose that we make MetadataElement extend java.sql.Wrapper. Then you can do this:

            org.olap4j.metadata.Member olap4jMember;
            if (olap4jMember.isWrapperFor(mondrian.olap.Annotated.class)) {
                mondrian.olap.Annotated annotated =
                    olap4jMember.unwrap(mondrian.olap.Annotated.class);
                System.out.println("annotation is " + annotated.get("foo"));
            }

        I have logged olap4j case 3473025, "Make MetadataElement extend java.sql.Wrapper". Targeted for olap4j-1.1.

        Short-term. Add method "Map<String, Object> XmlaHandler.XmlaExtra.getAnnotationMap(MetadataElement)".

        > 6. No format string on StandardMemberProperty
        >
        > 15. Need access to mondrian.olap.Property.FORMAT_EXP from within a Cell and
        > Member
        >
        > Passed through to Excel for formatting. Used to determine if a measure is
        > percentage based.

        Members don't have format strings. You can give an expression for the cell's FORMAT_STRING when you define a calc member (just as you give an expression for the cell's value), but it is only applied to cells.

        I see why you want to see the formula. Would it help if we added a new member property, FORMAT_STRING_FORMULA, that returns the unevaluated MDX formula?

        But note that if there is a calc member on another axis with a higher solve order and its own FORMAT_STRING, it might supersede the format string of your member. It probably wouldn't happen in queries generated by Analyzer, but it might happen in user-written queries.

        > 7. Need to differentiate between level member properties vs level system properties

        Can you be more specific? Do you have a Property object in-hand, or just its name?

        Note the method "Set<TypeFlag> org.olap4j.metadata.Property.getType()".

        > 8. Need to know if a level is a parent-child level

        Short-term, use "boolean XmlaHandler.XmlaExtra.isHierarchyParentChild(Hierarchy hierarchy)".

        We will add Hierarchy.isParentChild() in olap4j 1.1. (See olap4j bug #3473039, "Add method 'boolean Hierarchy.isParentChild'".)

        > 9. Need to know if a dimension is measure dimension

        org.olap4j.metadata.Dimension dimension;
        boolean isMeasures =
            dimension.getDimensionType() == Dimension.Type.MEASURE;

        > 10. Does level.getMembers include calculated members

        No, it does not.

        > The Java doc should state this clearly.

        Agreed. Will fix.

        By the way, to get calculated members, execute a query. For example,

        with member [Measures].[Zero] as 0
        select AddCalculatedMembers([Time].[Month].Members) on 0
        from [Sales]
        where [Measures].[Zero]

        The [Measures].[Zero] saves Mondrian the effort of retrieving cell values.

        > 11. Expose getParentLevel and getChildLevel on Level

        Write your own functions for this:

        static Level getParentLevel(Level level) {
          return level.getDepth() > 0
             ? level.getHierarchy().getLevels().get(level.getDepth() - 1)
             : null;
        }

        static Level getChildLevel(Level level) {
          return level.getDepth() < level.getHierarchy().getLevels().size() - 1
             ? level.getHierarchy().getLevels().get(level.getDepth() + 1)
             : null;
        }

        > 12. How to tell if a cell is drillable?

        > Need to know if a cell is drillable before calling cell.drillThrough.

        We will add cell property "ACTION_TYPE". You will have to ask for it in the CELL PROPERTIES clause of the query. The result is an integer bitmask. Bit MDACTION_TYPE_DRILLTHROUGH (0x100), will be set if you can drill through.

        > 13. Need to know total drill count

        This is difficult to support. I don't want an XMLA provider to have to calculate this for every cell before it sends the result back.

        > 14. Why does calculated measure member type not return Measure.Type.MEASURE?

        > XMLA spec behavior seems weird here. MDMEMBER_TYPE_FORMULA takes precedence
        > over MDMEMBER_TYPE_MEASURE. For example, if there is a formula (calculated)
        > member on the Measures dimension, it is listed as MDMEMBER_TYPE_FORMULA.
        > Analyzer can work around this by checking the measure hierarchy name
        > explicity. Maybe we should add an isMeasure method to Member.

        You can easily find out whether a member is a measure:

        org.olap4j.metadata.Member member;
        member.getDimension().getDimensionType() == Dimension.Type.MEASURES

        > 16. Member.getPropertyFormattedValue requires a Property instance to lookup a
        > value. To lookup a schema defined member property, the caller is forced to
        > create a dummy class with the name of the property.
        >
        > Add a helper util class in olap4j for this?

        I agree it's a bit tricky. It's not obvious where to get the necessary Property object to do the lookup, if the Property is not a standard one.

        The getPropertyFormattedValue and getPropertyValue methods should just take a String parameter. I got a bit confused when defining the API, because each position on a cellset axis can contain multiple members, therefore property names are not necessarily unique. But they are unique for a given member.

        Short-term, carry on using your workaround (creating your own class that implements Property, with the name you choose).

        For olap4j-1.1, I propose to change the type of these methods' parameters.

        > 17. OlapException needs to pass through the underlying
        > ResourceLimitExceededException, QueryTimeoutException and
        > MondrianEvaluationException.
        >
        > Extend OlapException to include the underlying exception name. Analyzer uses
        > this to give more user friendly error messages or to undo the last report
        > operation.

        How about if we make sure that OlapException.getSQLState() returns something useful in those cases?
        Show
        Julian Hyde added a comment - Here are the olap4j items, and my commentary on them. I've mentioned various API changes we can make for olap4j-1.1. (We can't make any API changes in a patch release.) olap4j-1.1 is probably 6-9 months away, so I've proposed a short-term fix for each problem. Benny, All of the proposed fixes are just proposals. I need you to say "Yes, your proposal will solve my problem" before I do anything. > 5. Add support for annotations We could add add a method to org.olap4j.metadata.MetadataElement:     Map<String, Annotation> getAnnotationMap(); But then I think someone would soon need some other piece of metadata on, say, Member or Level, that is not an annotation. So I propose that we make MetadataElement extend java.sql.Wrapper. Then you can do this:     org.olap4j.metadata.Member olap4jMember;     if (olap4jMember.isWrapperFor(mondrian.olap.Annotated.class)) {         mondrian.olap.Annotated annotated =             olap4jMember.unwrap(mondrian.olap.Annotated.class);         System.out.println("annotation is " + annotated.get("foo"));     } I have logged olap4j case 3473025, "Make MetadataElement extend java.sql.Wrapper". Targeted for olap4j-1.1. Short-term. Add method "Map<String, Object> XmlaHandler.XmlaExtra.getAnnotationMap(MetadataElement)". > 6. No format string on StandardMemberProperty > > 15. Need access to mondrian.olap.Property.FORMAT_EXP from within a Cell and > Member > > Passed through to Excel for formatting. Used to determine if a measure is > percentage based. Members don't have format strings. You can give an expression for the cell's FORMAT_STRING when you define a calc member (just as you give an expression for the cell's value), but it is only applied to cells. I see why you want to see the formula. Would it help if we added a new member property, FORMAT_STRING_FORMULA, that returns the unevaluated MDX formula? But note that if there is a calc member on another axis with a higher solve order and its own FORMAT_STRING, it might supersede the format string of your member. It probably wouldn't happen in queries generated by Analyzer, but it might happen in user-written queries. > 7. Need to differentiate between level member properties vs level system properties Can you be more specific? Do you have a Property object in-hand, or just its name? Note the method "Set<TypeFlag> org.olap4j.metadata.Property.getType()". > 8. Need to know if a level is a parent-child level Short-term, use "boolean XmlaHandler.XmlaExtra.isHierarchyParentChild(Hierarchy hierarchy)". We will add Hierarchy.isParentChild() in olap4j 1.1. (See olap4j bug #3473039, "Add method 'boolean Hierarchy.isParentChild'".) > 9. Need to know if a dimension is measure dimension org.olap4j.metadata.Dimension dimension; boolean isMeasures =     dimension.getDimensionType() == Dimension.Type.MEASURE; > 10. Does level.getMembers include calculated members No, it does not. > The Java doc should state this clearly. Agreed. Will fix. By the way, to get calculated members, execute a query. For example, with member [Measures].[Zero] as 0 select AddCalculatedMembers([Time].[Month].Members) on 0 from [Sales] where [Measures].[Zero] The [Measures].[Zero] saves Mondrian the effort of retrieving cell values. > 11. Expose getParentLevel and getChildLevel on Level Write your own functions for this: static Level getParentLevel(Level level) {   return level.getDepth() > 0      ? level.getHierarchy().getLevels().get(level.getDepth() - 1)      : null; } static Level getChildLevel(Level level) {   return level.getDepth() < level.getHierarchy().getLevels().size() - 1      ? level.getHierarchy().getLevels().get(level.getDepth() + 1)      : null; } > 12. How to tell if a cell is drillable? > Need to know if a cell is drillable before calling cell.drillThrough. We will add cell property "ACTION_TYPE". You will have to ask for it in the CELL PROPERTIES clause of the query. The result is an integer bitmask. Bit MDACTION_TYPE_DRILLTHROUGH (0x100), will be set if you can drill through. > 13. Need to know total drill count This is difficult to support. I don't want an XMLA provider to have to calculate this for every cell before it sends the result back. > 14. Why does calculated measure member type not return Measure.Type.MEASURE? > XMLA spec behavior seems weird here. MDMEMBER_TYPE_FORMULA takes precedence > over MDMEMBER_TYPE_MEASURE. For example, if there is a formula (calculated) > member on the Measures dimension, it is listed as MDMEMBER_TYPE_FORMULA. > Analyzer can work around this by checking the measure hierarchy name > explicity. Maybe we should add an isMeasure method to Member. You can easily find out whether a member is a measure: org.olap4j.metadata.Member member; member.getDimension().getDimensionType() == Dimension.Type.MEASURES > 16. Member.getPropertyFormattedValue requires a Property instance to lookup a > value. To lookup a schema defined member property, the caller is forced to > create a dummy class with the name of the property. > > Add a helper util class in olap4j for this? I agree it's a bit tricky. It's not obvious where to get the necessary Property object to do the lookup, if the Property is not a standard one. The getPropertyFormattedValue and getPropertyValue methods should just take a String parameter. I got a bit confused when defining the API, because each position on a cellset axis can contain multiple members, therefore property names are not necessarily unique. But they are unique for a given member. Short-term, carry on using your workaround (creating your own class that implements Property, with the name you choose). For olap4j-1.1, I propose to change the type of these methods' parameters. > 17. OlapException needs to pass through the underlying > ResourceLimitExceededException, QueryTimeoutException and > MondrianEvaluationException. > > Extend OlapException to include the underlying exception name. Analyzer uses > this to give more user friendly error messages or to undo the last report > operation. How about if we make sure that OlapException.getSQLState() returns something useful in those cases?
        Hide
        Julian Hyde added a comment -
        Benny's reply (pasted from a personal email, and quoted with '>'), and my replies interleaved.

        > 7. Level member properties - I have a mondrian.olap.Property object in-hand.
        > I think the issue was that I was getting back member properties that were internal
        > or had a $ sign in front. I'm not 100% sure anymore because this was long time
        > ago and I don't have my olap4j Analyzer environment anymore.

        I think you could use a property to get the formatting expression, as a string. I will add that.

        > 8. parent child - Today I am using RolapCubeLevel.isParentChild but I guess this
        > flag at the hierarchy would work as well since a parent child level only has one level
        > in the hierarchy.

        Yes.

        > 10. Are there performance implications of getting members via level.getMembers
        > vs. executing MDX? Do they share the same in-memory member cache? Any role
        > visibility issues?

        The only potential issue is that Mondrian would want to populate cell values -- and the calc member [Zero] solves that.

        Caching and access control are the same. And because you are using an MDX expression, you can easily add in sorting, filtering etc.

        You get a slightly different implementation of Member if you get members from a query versus from metadata API. E.g. members in a query have DISPLAY_INFO set. But the difference shouldn't matter.

        > 13. We do show the user today the total count but it's probably not end of the
        > world if we said we could only show them "10,000+ records" instead of the actual
        > count.

        Approximating the count doesn't help much. Suppose we are implementing the XMLA driver. If the result has 1,000 cells, Mondrian would have to calculate the drill-through count for each of these cells, before returning the result set. That is expensive, even if we use an approximation. The only thing we can do cheaply is say whether each cell is drillable.

        If you want the drill-through count, you would need a separate API call. In the XMLA driver, that would turn into a network round-trip.

        > All other items not mentioned above seem OK to me.

        Good. I will implement the "short term" solution then.
        Show
        Julian Hyde added a comment - Benny's reply (pasted from a personal email, and quoted with '>'), and my replies interleaved. > 7. Level member properties - I have a mondrian.olap.Property object in-hand. > I think the issue was that I was getting back member properties that were internal > or had a $ sign in front. I'm not 100% sure anymore because this was long time > ago and I don't have my olap4j Analyzer environment anymore. I think you could use a property to get the formatting expression, as a string. I will add that. > 8. parent child - Today I am using RolapCubeLevel.isParentChild but I guess this > flag at the hierarchy would work as well since a parent child level only has one level > in the hierarchy. Yes. > 10. Are there performance implications of getting members via level.getMembers > vs. executing MDX? Do they share the same in-memory member cache? Any role > visibility issues? The only potential issue is that Mondrian would want to populate cell values -- and the calc member [Zero] solves that. Caching and access control are the same. And because you are using an MDX expression, you can easily add in sorting, filtering etc. You get a slightly different implementation of Member if you get members from a query versus from metadata API. E.g. members in a query have DISPLAY_INFO set. But the difference shouldn't matter. > 13. We do show the user today the total count but it's probably not end of the > world if we said we could only show them "10,000+ records" instead of the actual > count. Approximating the count doesn't help much. Suppose we are implementing the XMLA driver. If the result has 1,000 cells, Mondrian would have to calculate the drill-through count for each of these cells, before returning the result set. That is expensive, even if we use an approximation. The only thing we can do cheaply is say whether each cell is drillable. If you want the drill-through count, you would need a separate API call. In the XMLA driver, that would turn into a network round-trip. > All other items not mentioned above seem OK to me. Good. I will implement the "short term" solution then.
        Hide
        Julian Hyde added a comment -
        Fixed in change 14886.

        > 5. Add support for annotations

        Added method "Map<String, Object> XmlaHandler.XmlaExtra.getAnnotationMap(MetadataElement)".

        I also made the implementations of org.olap4j.metadata.MetadataElement in
        MondrianOlap4jDriver (MondrianOlap4jCube, MondrianOlap4jDimension etc.)
        implement org.olap4j.OlapWrapper. Therefore you can call

          ((OlapWrapper) cube).unwrap(mondrian.olap.Annotated.class).get("foo")

        MetadataElement won't implement org.olap4j.OlapWrapper or java.sql.Wrapper until
        olap4j-1.1. That is a change that will break existing drivers.

        > 6. No format string on StandardMemberProperty
        >
        > 15. Need access to mondrian.olap.Property.FORMAT_EXP from within a Cell and
        > Member
        >
        > 7. Level member properties

        Added a new property "FORMAT_EXP", and made sure that it comes through the
        olap4j driver. You should specify 'DIMENSION PROPERTIES FORMAT_EXP' if you want it.

        > 10. Does level.getMembers include calculated members

        Fixed the javadoc.

        > 12. How to tell if a cell is drillable?

        Added cell property "ACTION_TYPE". You will have to ask for it in the
        CELL PROPERTIES clause of the query. The result is an integer bitmask. Bit
        MDACTION_TYPE_DRILLTHROUGH (0x100), will be set if you can drill through.

        > 13. Drill-through count.

        Added cell property "DRILLTHROUGH_COUNT". Value will be -1 if not drillable.
        You will have to ask for it in the CELL PROPERTIES clause of the query.
        Use with caution; may be expensive to evaluate.

        > 17. OlapException needs to pass through the underlying
        > ResourceLimitExceededException, QueryTimeoutException and
        > MondrianEvaluationException.

        The underlying exception is there, if you call getCause(). Furthermore,
        getSQLState() in these cases will return "ResourceLimitExceeded",
        "QueryTimeout", "EvaluationException".
        Show
        Julian Hyde added a comment - Fixed in change 14886. > 5. Add support for annotations Added method "Map<String, Object> XmlaHandler.XmlaExtra.getAnnotationMap(MetadataElement)". I also made the implementations of org.olap4j.metadata.MetadataElement in MondrianOlap4jDriver (MondrianOlap4jCube, MondrianOlap4jDimension etc.) implement org.olap4j.OlapWrapper. Therefore you can call   ((OlapWrapper) cube).unwrap(mondrian.olap.Annotated.class).get("foo") MetadataElement won't implement org.olap4j.OlapWrapper or java.sql.Wrapper until olap4j-1.1. That is a change that will break existing drivers. > 6. No format string on StandardMemberProperty > > 15. Need access to mondrian.olap.Property.FORMAT_EXP from within a Cell and > Member > > 7. Level member properties Added a new property "FORMAT_EXP", and made sure that it comes through the olap4j driver. You should specify 'DIMENSION PROPERTIES FORMAT_EXP' if you want it. > 10. Does level.getMembers include calculated members Fixed the javadoc. > 12. How to tell if a cell is drillable? Added cell property "ACTION_TYPE". You will have to ask for it in the CELL PROPERTIES clause of the query. The result is an integer bitmask. Bit MDACTION_TYPE_DRILLTHROUGH (0x100), will be set if you can drill through. > 13. Drill-through count. Added cell property "DRILLTHROUGH_COUNT". Value will be -1 if not drillable. You will have to ask for it in the CELL PROPERTIES clause of the query. Use with caution; may be expensive to evaluate. > 17. OlapException needs to pass through the underlying > ResourceLimitExceededException, QueryTimeoutException and > MondrianEvaluationException. The underlying exception is there, if you call getCause(). Furthermore, getSQLState() in these cases will return "ResourceLimitExceeded", "QueryTimeout", "EvaluationException".
        Hide
        Brandon Bruce added a comment -
        Luc, can you verify if this can be closed and what the fixversion should be?
        Show
        Brandon Bruce added a comment - Luc, can you verify if this can be closed and what the fixversion should be?

          People

          • Assignee:
            Unassigned User
            Reporter:
            Julian Hyde
          • Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: