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.

      1. MONDRIAN-1599.wmv
        9.65 MB
        Alena Karneichyk

        Issue Links

          Activity

          There are no comments yet on this issue.

            People

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

              Dates

              • Created:
                Updated:
                Resolved:

                Agile