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

ValidMeasure not taken into account when Analyzer uses NonEmptyCrossjoin as it returns no data

    Details

    • Type: Bug
    • Status: Closed
    • Severity: High
    • Resolution: Fixed
    • Affects Version/s: 5.2.0 GA, 5.2.1.0
    • Fix Version/s: 7.0.0 GA
    • Component/s: Native SQL
    • Labels:
    • Story Points:
      0
    • 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.
    • Sprint Team:
      BB-8

      Description

      The following Cubes are defined within the schema:

      Cube Contracts
      [DIM] Contract
      [MSR] Contract Value

      Cube Events
      [DIM] Contract
      [DIM] Event
      [MSR] Qty Events

      And one virtual cube:

      Events vs Contracts
      [DIM] [from Contract] Contract
      [DIM] [from Events] Event
      [MSR] [from Contract] Contract Value
      [MSR] [from Events] Qty Events
      [CME] [Valid Contract Value] as ValidMeasure( [Measures].[Contract Value] )

      There's no dimension table. For this scenario they've built two fact tables only - with local dimensions.

      The issue happens when using the virtual cube and following these steps:
      1. Drag Dim Contract - shows contracts;
      2. Drag Msr [Valid Contract Value] - shows contract values;
      3. Drag Msr [Qty Events] - shows the quantity of events for each contract;
      4. Drag Dim Event - shows all the events for each contract and shows the Contract Value over the toplevel of the Dim Event;
      BUT
      5. take [Qty Events] out of the report canvas - it simply gives back an empty canvas stating there is not data for this report.

      The resulting MDX shows: the usage of a nonemptycrossjoin

      WITH
      SET [*NATIVE_CJ_SET] AS 'NONEMPTYCROSSJOIN([*BASE_MEMBERS__Contract_],[*BASE_MEMBERS__Event_])'
      SET [*BASE_MEMBERS__Event_] AS '[Event].[Event].MEMBERS'
      SET [*BASE_MEMBERS__Measures_] AS '

      {[Measures].[*FORMATTED_MEASURE_0]}

      '
      SET [*CJ_ROW_AXIS] AS 'GENERATE([*NATIVE_CJ_SET],

      {([Contract].CURRENTMEMBER,[Event].CURRENTMEMBER)}

      )'
      SET [*SORTED_ROW_AXIS] AS 'ORDER([*CJ_ROW_AXIS],[Contract].CURRENTMEMBER.ORDERKEY,BASC,[Event].CURRENTMEMBER.ORDERKEY,BASC)'
      SET [*BASE_MEMBERS__Contract_] AS '[Contract].[Contract].MEMBERS'
      MEMBER [Measures].[*FORMATTED_MEASURE_0] AS '[Measures].[Valid Contract Value]', FORMAT_STRING = '#,##0.00', SOLVE_ORDER=500
      SELECT
      [*BASE_MEMBERS__Measures_] ON COLUMNS
      ,NON EMPTY
      [*SORTED_ROW_AXIS] ON ROWS
      FROM [Events vs Contracts]

      The resulting SQL shows a bad constructed query:

      SqlTupleReader.readTuples [[Contract].[Contract], [Event].[Event]]: done executing sql [select 0 as "c0" from "case_contract" as "case_contract" where 1 = 0],

      No columns selected plus a conditional that will never be true.

      If we take the MDX and change the nonemptycrossjoin to crossjoin we get the expected results using Analyzer's MDX utility.

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                AKarneichyk Alena Karneichyk (Inactive)
                Reporter:
                clopez Carlos Lopez
              • Votes:
                14 Vote for this issue
                Watchers:
                15 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: