Hitachi Vantara Pentaho Community Forums
Results 1 to 3 of 3

Thread: Problems with Cross-Join

  1. #1
    Join Date
    Nov 2016
    Posts
    28

    Default Problems with Cross-Join

    Hi All,

    I´m migrating from Pentaho Server 5.1 / Mondrian-3.7.0.0-752.jar To Pentaho Server 7.1 / Mondrian mondrian-3.14.0.0-12.jar.

    It works fine except MDX queries with "NON EMPTY CrossJoin(CrossJoin(.........."

    Query is this:

    WITH
    MEMBER Measures.[Accepted Amount] AS '([Accumulated.Invoice accumulated].[YTD], [Measures].[Importe aceptado Final], [Date].[${SP_Year}].[${SP_Month}])'


    SELECT
    NON EMPTY {Hierarchize({[Measures].[Accepted Amount]})} ON COLUMNS,
    NON EMPTY CrossJoin(CrossJoin([Offer Code].[Offer Description].Members, [Customer Group 2].[Customer Group 2].Members), [Date 2].[YM].Members) ON ROWS


    FROM [Aggr - Offers]


    WHERE ([Comparative Scenario.Comparative scenario].[Actual], [Previous year].[Current Year], [Currency].[EUR])

    I was checking that everything is the same in pPentaho 5 and 7.

    I have tried with mondrian Jars, from 3.11 to 3.14 and it works except MDX with double crossjoin

    With 3.10 Jars files or before nothing works fine.

    I have tried to import everything to Pentaho Server 6.01 and 5.4 and It does not work.

    The Query never report an error or result.............

    Some Help?

    Best regards.

  2. #2
    Join Date
    Nov 2016
    Posts
    28

    Default

    This is the mondrian.properties from Pentaho 5

    # Allow the use of aggregates
    mondrian.rolap.aggregates.Use=false
    mondrian.rolap.aggregates.Read=false


    # mondrian.properties
    mondrian.result.limit=5000000


    ######################################################################
    # Integer property indicating timeout value, in seconds, for queries.
    # Default of 0 indicates no timeout
    mondrian.rolap.queryTimeout=3600


    # Property which controls the amount of tracing displayed. Seperate from log4j.
    mondrian.trace.level=0


    # Maximum number of simultaneous queries the system will allow.
    mondrian.query.limit=40


    # Property which determines when a dimension is considered "large".
    mondrian.rolap.LargeDimensionThreshold=100


    # Boolean property that controls whether a RolapStar's aggregate data cache is cleared after each query.
    mondrian.rolap.star.disableCaching=false


    # Boolean property which controls SQL pretty-print mode.
    mondrian.rolap.generate.formatted.sql=false


    # Boolean property which controls whether the MDX parser resolves uses case-sensitive matching when looking up identifiers.
    mondrian.olap.case.sensitive=true


    # Boolean property which controls whether to use a cache for frequently evaluated expressions.
    mondrian.expCache.enable=true


    # if enabled some NON EMPTY CrossJoin will be computed in SQL
    mondrian.native.crossjoin.enable=true


    # if enabled some TopCount will be computed in SQL
    mondrian.native.topcount.enable=true


    # if enabled some Filter() will be computed in SQL
    mondrian.native.filter.enable=false


    # some NON EMPTY set operations like member.children, level.members and member descendants will be computed in SQL
    mondrian.native.nonempty.enable=true


    # enable grouping sets, if the dialect supports it.
    mondrian.rolap.groupingsets.enable=true


    # Max number of constraints in a single `IN' SQL clause.
    mondrian.rolap.maxConstraints=1000


    # Property which defines the maximum number of passes allowable while evaluating an MDX expression.
    mondrian.rolap.evaluate.MaxEvalDepth=10


    ###############################################################################
    # Boolean property indicating whether errors related to non-existent members
    # should be ignored during schema load. If so, the non-existent member is
    # treated as a null member.
    mondrian.rolap.ignoreInvalidMembers=true


    ###############################################################################
    # Boolean property indicating whether errors related to non-existent members
    # should be ignored during query validation. If so, the non-existent member is
    # treated as a null member.
    mondrian.rolap.ignoreInvalidMembersDuringQuery=true


    ###############################################################################
    # Integer property indicating the maximum number of iterations allowed when
    # iterating over members to compute aggregates. Default of 0 indicates no
    # limit.
    mondrian.rolap.iterationLimit=5000000


    ###############################################################################
    # String property which controls alerting behavior in case native
    # evaluation of a function is enabled but not supported for that
    # function's usage in a particular query. (No alert is ever raised in
    # cases where native evaluation would definitely have been wasted
    # effort.) Values recognized are { OFF, WARN, ERROR }.
    mondrian.native.unsupported.alert=WARN


    ###############################################################################
    # Boolean property which controls whether sibling members are
    # compared according to order key value fetched from their ordinal
    # expression. The default is false (only database ORDER BY is used).
    mondrian.rolap.compareSiblingsByOrderKey=true


    ###############################################################################
    # If true, a division having a non-null numerator and a null denominator
    # evaluates to Infinity. If false, the same division results in Null.
    mondrian.olap.NullDenominatorProducesNull=true


    ###############################################################################
    # Boolean property which controls whether to expand the non native sub-
    # expressions of a native expression into MemberLists
    mondrian.native.ExpandNonNative=true


    ###############################################################################
    # Property determines if elements of dimension (levels, hierarchies, members)
    # need to be prefixed with dimension name in MDX query.
    #
    # For example when the property is true, the following queries
    # will error out. The same queries will work when this property
    # is set to false.
    # * select {[M]} on 0 from sales
    # * select {[USA]} on 0 from sales
    # * select {[USA].[CA].[Santa Monica]} on 0 from sales
    #
    # When the property is set to true, any query where elements are
    # prefixed with dimension name as below will work
    # * select {[Gender].[F]} on 0 from sales
    # * select {[Customers].[Santa Monica]} on 0 from sales
    #
    # Please note that this property does not govern the behaviour where in
    # * [Gender].[M]
    # is resolved into a fully qualified
    # * [Gender].[All Gender].[M]
    #
    # In a scenario where the schema is very large and dimensions have large
    # number of members a MDX query that has a invalid member in it will cause
    # mondrian to to go through all the dimensions, levels, hierarchies, members
    # and properties trying to resolve the element name. This behaviour consumes
    # considerable time and resources on the server. Setting this property to
    # true will make it fail fast in a scenario where it is desirable
    #
    mondrian.olap.elements.NeedDimensionPrefix=true


    ###############################################################################
    # Property to define which data source resolver class Mondrian should use within
    # the BI platform.
    #
    # This SPI implementation makes it possible for Mondrian to access the data sources
    # defined by the EE data sources plugin.
    #
    # Comment this property to have Mondrian resolve datasources
    # using straight JNDI APIs.
    mondrian.spi.dataSourceResolverClass=org.pentaho.platform.web.servlet.PentahoDataSourceResolver


    # Allows the MATCHES MDX function to be natively evaluated
    mondrian.native.EnableNativeRegexpFilter=true


    ###############################################################################
    # Maximum number of MDX query threads per Mondrian server instance.
    # Defaults to 20.
    mondrian.rolap.maxQueryThreads=20

  3. #3
    Join Date
    Nov 2016
    Posts
    28

    Default

    This is the mondrian.properties from Pentaho 7.1:



    # Allow the use of aggregates
    mondrian.rolap.aggregates.Use=false
    mondrian.rolap.aggregates.Read=false


    # mondrian.properties
    mondrian.result.limit=5000000


    ######################################################################
    # Integer property indicating timeout value, in seconds, for queries.
    # Default of 0 indicates no timeout
    mondrian.rolap.queryTimeout=3600


    # Property which controls the amount of tracing displayed. Seperate from log4j.
    mondrian.trace.level=0


    # Maximum number of simultaneous queries the system will allow.
    mondrian.query.limit=40


    # Property which determines when a dimension is considered "large".
    mondrian.rolap.LargeDimensionThreshold=100


    # Boolean property that controls whether a RolapStar's aggregate data cache is cleared after each query.
    mondrian.rolap.star.disableCaching=false


    # Boolean property which controls SQL pretty-print mode.
    mondrian.rolap.generate.formatted.sql=false


    # Boolean property which controls whether the MDX parser resolves uses case-sensitive matching when looking up identifiers.
    mondrian.olap.case.sensitive=true


    # Boolean property which controls whether to use a cache for frequently evaluated expressions.
    mondrian.expCache.enable=true


    # if enabled some NON EMPTY CrossJoin will be computed in SQL
    mondrian.native.crossjoin.enable=true


    # if enabled some TopCount will be computed in SQL
    mondrian.native.topcount.enable=true


    # if enabled some Filter() will be computed in SQL
    mondrian.native.filter.enable=true


    # some NON EMPTY set operations like member.children, level.members and member descendants will be computed in SQL
    mondrian.native.nonempty.enable=true


    # enable grouping sets, if the dialect supports it.
    mondrian.rolap.groupingsets.enable=true


    # Max number of constraints in a single `IN' SQL clause.
    mondrian.rolap.maxConstraints=1000


    # Property which defines the maximum number of passes allowable while evaluating an MDX expression.
    mondrian.rolap.evaluate.MaxEvalDepth=10


    ###############################################################################
    # Boolean property indicating whether errors related to non-existent members
    # should be ignored during schema load. If so, the non-existent member is
    # treated as a null member.
    mondrian.rolap.ignoreInvalidMembers=true


    ###############################################################################
    # Boolean property indicating whether errors related to non-existent members
    # should be ignored during query validation. If so, the non-existent member is
    # treated as a null member.
    mondrian.rolap.ignoreInvalidMembersDuringQuery=true


    ###############################################################################
    # Integer property indicating the maximum number of iterations allowed when
    # iterating over members to compute aggregates. Default of 0 indicates no
    # limit.
    mondrian.rolap.iterationLimit=5000000


    ###############################################################################
    # String property which controls alerting behavior in case native
    # evaluation of a function is enabled but not supported for that
    # function's usage in a particular query. (No alert is ever raised in
    # cases where native evaluation would definitely have been wasted
    # effort.) Values recognized are { OFF, WARN, ERROR }.
    mondrian.native.unsupported.alert=WARN


    ###############################################################################
    # Boolean property which controls whether sibling members are
    # compared according to order key value fetched from their ordinal
    # expression. The default is false (only database ORDER BY is used).
    mondrian.rolap.compareSiblingsByOrderKey=true


    ###############################################################################
    # If true, a division having a non-null numerator and a null denominator
    # evaluates to Infinity. If false, the same division results in Null.
    mondrian.olap.NullDenominatorProducesNull=true


    ###############################################################################
    # Boolean property which controls whether to expand the non native sub-
    # expressions of a native expression into MemberLists
    mondrian.native.ExpandNonNative=true


    ###############################################################################
    # Property determines if elements of dimension (levels, hierarchies, members)
    # need to be prefixed with dimension name in MDX query.
    #
    # For example when the property is true, the following queries
    # will error out. The same queries will work when this property
    # is set to false.
    # * select {[M]} on 0 from sales
    # * select {[USA]} on 0 from sales
    # * select {[USA].[CA].[Santa Monica]} on 0 from sales
    #
    # When the property is set to true, any query where elements are
    # prefixed with dimension name as below will work
    # * select {[Gender].[F]} on 0 from sales
    # * select {[Customers].[Santa Monica]} on 0 from sales
    #
    # Please note that this property does not govern the behaviour where in
    # * [Gender].[M]
    # is resolved into a fully qualified
    # * [Gender].[All Gender].[M]
    #
    # In a scenario where the schema is very large and dimensions have large
    # number of members a MDX query that has a invalid member in it will cause
    # mondrian to to go through all the dimensions, levels, hierarchies, members
    # and properties trying to resolve the element name. This behaviour consumes
    # considerable time and resources on the server. Setting this property to
    # true will make it fail fast in a scenario where it is desirable
    #
    mondrian.olap.elements.NeedDimensionPrefix=true


    ###############################################################################
    # Property to define which data source resolver class Mondrian should use within
    # the BI platform.
    #
    # This SPI implementation makes it possible for Mondrian to access the data sources
    # defined by the EE data sources plugin.
    #
    # Comment this property to have Mondrian resolve datasources
    # using straight JNDI APIs.
    mondrian.spi.dataSourceResolverClass=org.pentaho.platform.web.servlet.PentahoDataSourceResolver


    ###############################################################################
    # Maximum number of MDX query threads per Mondrian server instance.
    # Defaults to 20.
    mondrian.rolap.maxQueryThreads=20

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2017 Pentaho Corporation. All Rights Reserved.