Hitachi Vantara Pentaho Community Forums
Results 1 to 2 of 2

Thread: “All” grouping includes filtered measurement values

  1. #1

    Default “All” grouping includes filtered measurement values

    I have an MDX statement that filters a dataset based on Measurement_1 (Measurement_1 >= 70) across three dimensions (via crossjoin). The results per value of each Dimension_0, Dimension_1 and Dimension_2 combination is correct and the filter is working as expected. The MDX statement is executed against Kylin using Mondrian.
    The problem arises in the "All" part of the resultset. If the values of Measurement_0 (not the filtered measurement) is summed together and compared to the "All" part of the result set, there is a difference in value.
    Below is the formatted result set:

    Code:
    {
      "report": [
        {
          "All Dimension_0": [
            {
              "XYZ": [
                {
                  "ABC": {
                    "[Measures].[Measurement_1]": 75.08282389023466,
                    "[Measures].[Measurement_0]": 191220044878
                  }
                }
              ]
            }
          ]
        },
        {
          "Dimension_0 value 0": [
            {
              "XYZ": [
                {
                  "ABC": {
                    "[Measures].[Measurement_1]": 74.039688653933,
                    "[Measures].[Measurement_0]": 9031912913
                  }
                }
              ]
            }
          ]
        },
        {
          "Dimension_0 value 1": [
            {
              "XYZ": [
                {
                  "ABC": {
                    "[Measures].[Measurement_1]": 75.15194214129325,
                    "[Measures].[Measurement_0]": 175519866354
                  }
                }
              ]
            }
          ]
        },
        {
          "Dimension_0 value 2": [
            {
              "XYZ": [
                {
                  "ABC": {
                    "[Measures].[Measurement_1]": 75.42622332399225,
                    "[Measures].[Measurement_0]": 6246658572
                  }
                }
              ]
            }
          ]
        }
      ]
    }



    The value of Measurement_0 on "All Dimension_0" grouping equals 191,220,044,878, but the sum of Measurement_0 on the subsequent groupings equals 190,798,437,839. Which is a difference of 421,607,039.
    On removing the filter from the MDX statement, an extra grouping appears in the result set (as expected), with a Measurement_1 value outside the filter (specifically equalling null). The value of Measurement_0 of this grouping is equal to the difference between the "All" grouping and the manual summing of the groupings shown above.
    Below is the additional grouping that appears on the removal of the filter:

    Code:
    {
      "Dimension_0 value 3": [
        {
          "XYZ": [
            {
              "ABC": {
                "[Measures].[Measurement_1]": null,
                "[Measures].[Measurement_0]": 421607039
              }
            }
          ]
        }
      ]
    }


    Below is the MDX statement with the filter:

    Code:
    SELECT {Measurement_0, Measurement_1} ON AXIS(0),
            {
                FILTER(
                        CROSSJOIN(
                            CROSSJOIN(
                                        {[Dimension_0].Members}, {[Dimension_1].XYZ}
                                    ), {[Dimension_2].ABC}
                            ), (((Measurement_1 >= 70))))} ON AXIS(1)
    FROM <table>


    Is there a way to exclude the value of Measurement_0 from the "All" calculation as the grouping is not returned as part of the filtered resultset?

    I have tried to use VisualTotals after the filter has been applied, but it seems VisualTotals works with members of the same hierarchy, where the FILTER returns tuples.

    Any advice or direction appreciated.

    Pieter



  2. #2

    Default

    Hi,

    The MDX with the VisualTotals is as follows:

    Code:
    SELECT {Measurement_0, Measurement_1} ON AXIS(0),
            VISUALTOTALS({FILTER(CROSSJOIN(CROSSJOIN({[Dimension_0].Members}, {[Dimension_1].XYZ}), {[Dimension_2].ABC}), (((Measurement_1 >= 70))))}) ON AXIS(1)
    FROM <table>

    When executing the MDX, the following error message is returned: Mondrian Error:Argument to 'VisualTotals' function must be a set of members; got set of tuples.

    To try and generate a set of members, I have added Hierarchize to try and create a set of members:

    Code:
    SELECT {Measurement_0, Measurement_1} ON AXIS(0),
            VISUALTOTALS(HIERARCHIZE({FILTER(CROSSJOIN(CROSSJOIN({[Dimension_0].Members}, {[Dimension_1].XYZ}), {[Dimension_2].ABC}), (((Measurement_1 >= 70))))})) ON AXIS(1)
    FROM <table>
    The same error message is returned as above.

    Thanks
    Pieter

    Quote Originally Posted by schutte.pieter View Post
    I have an MDX statement that filters a dataset based on Measurement_1 (Measurement_1 >= 70) across three dimensions (via crossjoin). The results per value of each Dimension_0, Dimension_1 and Dimension_2 combination is correct and the filter is working as expected. The MDX statement is executed against Kylin using Mondrian.
    The problem arises in the "All" part of the resultset. If the values of Measurement_0 (not the filtered measurement) is summed together and compared to the "All" part of the result set, there is a difference in value.

Tags for this Thread

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 - 2019 Hitachi Vantara Corporation. All Rights Reserved.