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

SQL using hierarchy attribute 'Column Name' instead of 'Column' in the filter

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Blocker Blocker
    • Resolution: Cannot Reproduce
    • Affects Version/s: None
    • Component/s: None
    • Labels:
      None
    • Environment:
      Windows
    • Browser:
      Internet Explorer 8.x
    • Customer Case:
    • QA Validation Status:
      Not Yet Validated

      Description

      No data is retrieved when filtering with values across multiple parent members.

      The behaviour only occurs when filter is defined using values from two different parent members. This behaviour appears to be caused by the use of different database fields for the Hierarchy Attribute 'Column' and 'Column Name'. For example, use 'Customer ID' for the 'Column' and 'Customer Name' for the 'Column Name'.

      The SQL generated will use the 'Column Name' for the 'Where' clause instead of 'Column' to do the filtering. This means the 'Where' clause will not match as you are attempting to match 'Customer ID' with 'Customer Name'. See the attached log from the client's environment pentaho.log.

        Issue Links

          Activity

          Hide
          Rob Johnson added a comment -
          This issue is very similar to MONDRIAN-485, detailed in http://forums.pentaho.org/showthread.php?p=203451, which also has a SQL where-clause that confuses the "nameColumn" and "column" Level attributes.
          Show
          Rob Johnson added a comment - This issue is very similar to MONDRIAN-485 , detailed in http://forums.pentaho.org/showthread.php?p=203451, which also has a SQL where-clause that confuses the "nameColumn" and "column" Level attributes.
          Hide
          Anthony Carter added a comment -
          Here you go Julian

          The problem lies after the OR command:

          or ("PRODUCTS"."PRODUCTCODE" = 'Unimax Art Galleries' and "PRODUCTS"."PRODUCTLINE" = 'Planes' and "PRODUCTS"."PRODUCTNAME" = '1900s Vintage Tri-Plane'))

          The first should not be PRODUCTCODE, but PRODUCTVENDOR because Unimax Art Galleries (which comes from the [S24_4278]) is the product vendor in [Product].[Planes].[S24_4278].[1900s Vintage Tri-Plane].

          Thanks,
          Anthony


          Log session start time Wed Aug 25 20:54:08 BST 2010

          Time Thread Level Category Message
          442412 Thread-46 DEBUG mondrian.rolap.RolapUtil // Request ID: 8f5a9b5f-2697-4f86-abca-5cc507061aa2 - RUN_REPORT
          With
          Set [*NATIVE_CJ_SET] as 'NonEmptyCrossJoin([*BASE_MEMBERS_Markets],[*BASE_MEMBERS_Product])'
          Set [*SORTED_ROW_AXIS] as 'Order([*CJ_ROW_AXIS],[Markets].CurrentMember.OrderKey,BASC,[Product].CurrentMember.OrderKey,BASC,Ancestor([Product].CurrentMember,[Product].[Vendor]).OrderKey,BASC)'
          Set [*BASE_MEMBERS_Markets] as '{[Markets].[APAC],[Markets].[EMEA]}'
          Set [*BASE_MEMBERS_Product] as '{[Product].[Planes].[S24_2841].[1900s Vintage Bi-Plane],[Product].[Planes].[S24_4278].[1900s Vintage Tri-Plane]}'
          Set [*BASE_MEMBERS_Measures] as '{[Measures].[*FORMATTED_MEASURE_0]}'
          Set [*CJ_ROW_AXIS] as 'Generate([*NATIVE_CJ_SET], {([Markets].currentMember,[Product].currentMember)})'
          Set [*CJ_COL_AXIS] as '[*NATIVE_CJ_SET]'
          Member [Measures].[*FORMATTED_MEASURE_0] as '[Measures].[Quantity]', FORMAT_STRING = '#,###', SOLVE_ORDER=400
          Select
          [*BASE_MEMBERS_Measures] on columns,
          Non Empty [*SORTED_ROW_AXIS] on rows
          From [SteelWheelsSales]


          and for info:

          442423 Thread-46 DEBUG mondrian.rolap.RolapUtil SqlMemberSource.getMemberChildren: executing sql [select "PRODUCTS"."PRODUCTLINE" as "c0" from "PRODUCTS" as "PRODUCTS" group by "PRODUCTS"."PRODUCTLINE" order by CASE WHEN "PRODUCTS"."PRODUCTLINE" IS NULL THEN 1 ELSE 0 END, "PRODUCTS"."PRODUCTLINE" ASC], exec 2 ms
          442425 Thread-46 DEBUG mondrian.rolap.RolapUtil SqlMemberSource.getMemberChildren: done executing sql [select "PRODUCTS"."PRODUCTLINE" as "c0" from "PRODUCTS" as "PRODUCTS" group by "PRODUCTS"."PRODUCTLINE" order by CASE WHEN "PRODUCTS"."PRODUCTLINE" IS NULL THEN 1 ELSE 0 END, "PRODUCTS"."PRODUCTLINE" ASC], exec+fetch 4 ms, 7 rows
          442572 Thread-46 DEBUG mondrian.rolap.RolapUtil SqlTupleReader.readTuples [[Markets].[Territory], [Product].[Product]]: executing sql [select "CUSTOMER_W_TER"."TERRITORY" as "c0", "PRODUCTS"."PRODUCTLINE" as "c1", "PRODUCTS"."PRODUCTVENDOR" as "c2", "PRODUCTS"."PRODUCTCODE" as "c3", "PRODUCTS"."PRODUCTNAME" as "c4", "PRODUCTS"."PRODUCTCODE" as "c5", "PRODUCTS"."PRODUCTVENDOR" as "c6", "PRODUCTS"."PRODUCTDESCRIPTION" as "c7" from "CUSTOMER_W_TER" as "CUSTOMER_W_TER", "ORDERFACT" as "ORDERFACT", "PRODUCTS" as "PRODUCTS" where "ORDERFACT"."CUSTOMERNUMBER" = "CUSTOMER_W_TER"."CUSTOMERNUMBER" and "ORDERFACT"."PRODUCTCODE" = "PRODUCTS"."PRODUCTCODE" and ("CUSTOMER_W_TER"."TERRITORY" in ('APAC', 'EMEA')) and (("PRODUCTS"."PRODUCTCODE" = 'Autoart Studio Design' and "PRODUCTS"."PRODUCTLINE" = 'Planes' and "PRODUCTS"."PRODUCTNAME" = '1900s Vintage Bi-Plane') or ("PRODUCTS"."PRODUCTCODE" = 'Unimax Art Galleries' and "PRODUCTS"."PRODUCTLINE" = 'Planes' and "PRODUCTS"."PRODUCTNAME" = '1900s Vintage Tri-Plane')) group by "CUSTOMER_W_TER"."TERRITORY", "PRODUCTS"."PRODUCTLINE", "PRODUCTS"."PRODUCTVENDOR", "PRODUCTS"."PRODUCTCODE", "PRODUCTS"."PRODUCTNAME", "PRODUCTS"."PRODUCTDESCRIPTION" order by CASE WHEN "CUSTOMER_W_TER"."TERRITORY" IS NULL THEN 1 ELSE 0 END, "CUSTOMER_W_TER"."TERRITORY" ASC, CASE WHEN "PRODUCTS"."PRODUCTLINE" IS NULL THEN 1 ELSE 0 END, "PRODUCTS"."PRODUCTLINE" ASC, CASE WHEN "PRODUCTS"."PRODUCTVENDOR" IS NULL THEN 1 ELSE 0 END, "PRODUCTS"."PRODUCTVENDOR" ASC, CASE WHEN "PRODUCTS"."PRODUCTNAME" IS NULL THEN 1 ELSE 0 END, "PRODUCTS"."PRODUCTNAME" ASC], exec 137 ms
          442573 Thread-46 DEBUG mondrian.rolap.RolapUtil SqlTupleReader.readTuples [[Markets].[Territory], [Product].[Product]]: done executing sql [select "CUSTOMER_W_TER"."TERRITORY" as "c0", "PRODUCTS"."PRODUCTLINE" as "c1", "PRODUCTS"."PRODUCTVENDOR" as "c2", "PRODUCTS"."PRODUCTCODE" as "c3", "PRODUCTS"."PRODUCTNAME" as "c4", "PRODUCTS"."PRODUCTCODE" as "c5", "PRODUCTS"."PRODUCTVENDOR" as "c6", "PRODUCTS"."PRODUCTDESCRIPTION" as "c7" from "CUSTOMER_W_TER" as "CUSTOMER_W_TER", "ORDERFACT" as "ORDERFACT", "PRODUCTS" as "PRODUCTS" where "ORDERFACT"."CUSTOMERNUMBER" = "CUSTOMER_W_TER"."CUSTOMERNUMBER" and "ORDERFACT"."PRODUCTCODE" = "PRODUCTS"."PRODUCTCODE" and ("CUSTOMER_W_TER"."TERRITORY" in ('APAC', 'EMEA')) and (("PRODUCTS"."PRODUCTCODE" = 'Autoart Studio Design' and "PRODUCTS"."PRODUCTLINE" = 'Planes' and "PRODUCTS"."PRODUCTNAME" = '1900s Vintage Bi-Plane') or ("PRODUCTS"."PRODUCTCODE" = 'Unimax Art Galleries' and "PRODUCTS"."PRODUCTLINE" = 'Planes' and "PRODUCTS"."PRODUCTNAME" = '1900s Vintage Tri-Plane')) group by "CUSTOMER_W_TER"."TERRITORY", "PRODUCTS"."PRODUCTLINE", "PRODUCTS"."PRODUCTVENDOR", "PRODUCTS"."PRODUCTCODE", "PRODUCTS"."PRODUCTNAME", "PRODUCTS"."PRODUCTDESCRIPTION" order by CASE WHEN "CUSTOMER_W_TER"."TERRITORY" IS NULL THEN 1 ELSE 0 END, "CUSTOMER_W_TER"."TERRITORY" ASC, CASE WHEN "PRODUCTS"."PRODUCTLINE" IS NULL THEN 1 ELSE 0 END, "PRODUCTS"."PRODUCTLINE" ASC, CASE WHEN "PRODUCTS"."PRODUCTVENDOR" IS NULL THEN 1 ELSE 0 END, "PRODUCTS"."PRODUCTVENDOR" ASC, CASE WHEN "PRODUCTS"."PRODUCTNAME" IS NULL THEN 1 ELSE 0 END, "PRODUCTS"."PRODUCTNAME" ASC], exec+fetch 138 ms, 0 rows
          442582 http-8080-Processor19 DEBUG mondrian.rolap.RolapUtil Request ID: 8f5a9b5f-2697-4f86-abca-5cc507061aa2 - RUN_REPORT ****** Started: 20:54:08 ****** SetupAxis: 0ms ****** GeneratedMDX: 1ms ****** ParsedMondrian: 18ms ****** ExecutedMondrian: 150ms ****** Sorted: 1ms ****** Rendered: 1ms ****** Total: 171ms
          Show
          Anthony Carter added a comment - Here you go Julian The problem lies after the OR command: or ("PRODUCTS"."PRODUCTCODE" = 'Unimax Art Galleries' and "PRODUCTS"."PRODUCTLINE" = 'Planes' and "PRODUCTS"."PRODUCTNAME" = '1900s Vintage Tri-Plane')) The first should not be PRODUCTCODE, but PRODUCTVENDOR because Unimax Art Galleries (which comes from the [S24_4278]) is the product vendor in [Product].[Planes].[S24_4278].[1900s Vintage Tri-Plane]. Thanks, Anthony Log session start time Wed Aug 25 20:54:08 BST 2010 Time Thread Level Category Message 442412 Thread-46 DEBUG mondrian.rolap.RolapUtil // Request ID: 8f5a9b5f-2697-4f86-abca-5cc507061aa2 - RUN_REPORT With Set [*NATIVE_CJ_SET] as 'NonEmptyCrossJoin([*BASE_MEMBERS_Markets],[*BASE_MEMBERS_Product])' Set [*SORTED_ROW_AXIS] as 'Order([*CJ_ROW_AXIS],[Markets].CurrentMember.OrderKey,BASC,[Product].CurrentMember.OrderKey,BASC,Ancestor([Product].CurrentMember,[Product].[Vendor]).OrderKey,BASC)' Set [*BASE_MEMBERS_Markets] as '{[Markets].[APAC],[Markets].[EMEA]}' Set [*BASE_MEMBERS_Product] as '{[Product].[Planes].[S24_2841].[1900s Vintage Bi-Plane],[Product].[Planes].[S24_4278].[1900s Vintage Tri-Plane]}' Set [*BASE_MEMBERS_Measures] as '{[Measures].[*FORMATTED_MEASURE_0]}' Set [*CJ_ROW_AXIS] as 'Generate([*NATIVE_CJ_SET], {([Markets].currentMember,[Product].currentMember)})' Set [*CJ_COL_AXIS] as '[*NATIVE_CJ_SET]' Member [Measures].[*FORMATTED_MEASURE_0] as '[Measures].[Quantity]', FORMAT_STRING = '#,###', SOLVE_ORDER=400 Select [*BASE_MEMBERS_Measures] on columns, Non Empty [*SORTED_ROW_AXIS] on rows From [SteelWheelsSales] and for info: 442423 Thread-46 DEBUG mondrian.rolap.RolapUtil SqlMemberSource.getMemberChildren: executing sql [select "PRODUCTS"."PRODUCTLINE" as "c0" from "PRODUCTS" as "PRODUCTS" group by "PRODUCTS"."PRODUCTLINE" order by CASE WHEN "PRODUCTS"."PRODUCTLINE" IS NULL THEN 1 ELSE 0 END, "PRODUCTS"."PRODUCTLINE" ASC], exec 2 ms 442425 Thread-46 DEBUG mondrian.rolap.RolapUtil SqlMemberSource.getMemberChildren: done executing sql [select "PRODUCTS"."PRODUCTLINE" as "c0" from "PRODUCTS" as "PRODUCTS" group by "PRODUCTS"."PRODUCTLINE" order by CASE WHEN "PRODUCTS"."PRODUCTLINE" IS NULL THEN 1 ELSE 0 END, "PRODUCTS"."PRODUCTLINE" ASC], exec+fetch 4 ms, 7 rows 442572 Thread-46 DEBUG mondrian.rolap.RolapUtil SqlTupleReader.readTuples [[Markets].[Territory], [Product].[Product]]: executing sql [select "CUSTOMER_W_TER"."TERRITORY" as "c0", "PRODUCTS"."PRODUCTLINE" as "c1", "PRODUCTS"."PRODUCTVENDOR" as "c2", "PRODUCTS"."PRODUCTCODE" as "c3", "PRODUCTS"."PRODUCTNAME" as "c4", "PRODUCTS"."PRODUCTCODE" as "c5", "PRODUCTS"."PRODUCTVENDOR" as "c6", "PRODUCTS"."PRODUCTDESCRIPTION" as "c7" from "CUSTOMER_W_TER" as "CUSTOMER_W_TER", "ORDERFACT" as "ORDERFACT", "PRODUCTS" as "PRODUCTS" where "ORDERFACT"."CUSTOMERNUMBER" = "CUSTOMER_W_TER"."CUSTOMERNUMBER" and "ORDERFACT"."PRODUCTCODE" = "PRODUCTS"."PRODUCTCODE" and ("CUSTOMER_W_TER"."TERRITORY" in ('APAC', 'EMEA')) and (("PRODUCTS"."PRODUCTCODE" = 'Autoart Studio Design' and "PRODUCTS"."PRODUCTLINE" = 'Planes' and "PRODUCTS"."PRODUCTNAME" = '1900s Vintage Bi-Plane') or ("PRODUCTS"."PRODUCTCODE" = 'Unimax Art Galleries' and "PRODUCTS"."PRODUCTLINE" = 'Planes' and "PRODUCTS"."PRODUCTNAME" = '1900s Vintage Tri-Plane')) group by "CUSTOMER_W_TER"."TERRITORY", "PRODUCTS"."PRODUCTLINE", "PRODUCTS"."PRODUCTVENDOR", "PRODUCTS"."PRODUCTCODE", "PRODUCTS"."PRODUCTNAME", "PRODUCTS"."PRODUCTDESCRIPTION" order by CASE WHEN "CUSTOMER_W_TER"."TERRITORY" IS NULL THEN 1 ELSE 0 END, "CUSTOMER_W_TER"."TERRITORY" ASC, CASE WHEN "PRODUCTS"."PRODUCTLINE" IS NULL THEN 1 ELSE 0 END, "PRODUCTS"."PRODUCTLINE" ASC, CASE WHEN "PRODUCTS"."PRODUCTVENDOR" IS NULL THEN 1 ELSE 0 END, "PRODUCTS"."PRODUCTVENDOR" ASC, CASE WHEN "PRODUCTS"."PRODUCTNAME" IS NULL THEN 1 ELSE 0 END, "PRODUCTS"."PRODUCTNAME" ASC], exec 137 ms 442573 Thread-46 DEBUG mondrian.rolap.RolapUtil SqlTupleReader.readTuples [[Markets].[Territory], [Product].[Product]]: done executing sql [select "CUSTOMER_W_TER"."TERRITORY" as "c0", "PRODUCTS"."PRODUCTLINE" as "c1", "PRODUCTS"."PRODUCTVENDOR" as "c2", "PRODUCTS"."PRODUCTCODE" as "c3", "PRODUCTS"."PRODUCTNAME" as "c4", "PRODUCTS"."PRODUCTCODE" as "c5", "PRODUCTS"."PRODUCTVENDOR" as "c6", "PRODUCTS"."PRODUCTDESCRIPTION" as "c7" from "CUSTOMER_W_TER" as "CUSTOMER_W_TER", "ORDERFACT" as "ORDERFACT", "PRODUCTS" as "PRODUCTS" where "ORDERFACT"."CUSTOMERNUMBER" = "CUSTOMER_W_TER"."CUSTOMERNUMBER" and "ORDERFACT"."PRODUCTCODE" = "PRODUCTS"."PRODUCTCODE" and ("CUSTOMER_W_TER"."TERRITORY" in ('APAC', 'EMEA')) and (("PRODUCTS"."PRODUCTCODE" = 'Autoart Studio Design' and "PRODUCTS"."PRODUCTLINE" = 'Planes' and "PRODUCTS"."PRODUCTNAME" = '1900s Vintage Bi-Plane') or ("PRODUCTS"."PRODUCTCODE" = 'Unimax Art Galleries' and "PRODUCTS"."PRODUCTLINE" = 'Planes' and "PRODUCTS"."PRODUCTNAME" = '1900s Vintage Tri-Plane')) group by "CUSTOMER_W_TER"."TERRITORY", "PRODUCTS"."PRODUCTLINE", "PRODUCTS"."PRODUCTVENDOR", "PRODUCTS"."PRODUCTCODE", "PRODUCTS"."PRODUCTNAME", "PRODUCTS"."PRODUCTDESCRIPTION" order by CASE WHEN "CUSTOMER_W_TER"."TERRITORY" IS NULL THEN 1 ELSE 0 END, "CUSTOMER_W_TER"."TERRITORY" ASC, CASE WHEN "PRODUCTS"."PRODUCTLINE" IS NULL THEN 1 ELSE 0 END, "PRODUCTS"."PRODUCTLINE" ASC, CASE WHEN "PRODUCTS"."PRODUCTVENDOR" IS NULL THEN 1 ELSE 0 END, "PRODUCTS"."PRODUCTVENDOR" ASC, CASE WHEN "PRODUCTS"."PRODUCTNAME" IS NULL THEN 1 ELSE 0 END, "PRODUCTS"."PRODUCTNAME" ASC], exec+fetch 138 ms, 0 rows 442582 http-8080-Processor19 DEBUG mondrian.rolap.RolapUtil Request ID: 8f5a9b5f-2697-4f86-abca-5cc507061aa2 - RUN_REPORT ****** Started: 20:54:08 ****** SetupAxis: 0ms ****** GeneratedMDX: 1ms ****** ParsedMondrian: 18ms ****** ExecutedMondrian: 150ms ****** Sorted: 1ms ****** Rendered: 1ms ****** Total: 171ms
          Hide
          Luc Boudreau added a comment -
          Hi, I'm the one picking on this issue from now on and I can't get the last provided MDX query to run on our SteelWheels test suite. Which SteelWheels database have you been able to run this query against? Can you reproduce it using FoodMart?
          Show
          Luc Boudreau added a comment - Hi, I'm the one picking on this issue from now on and I can't get the last provided MDX query to run on our SteelWheels test suite. Which SteelWheels database have you been able to run this query against? Can you reproduce it using FoodMart?
          Hide
          Rob Johnson added a comment -
          Luc, you need to modify the SteelWheels Mondrian schema file to make the example work. I attached the modified schema as steelwheels.mondrian.xml in support case 00011810, along with an Analyzer report and Analysis View that run with the modified schema and show the incorrect results.

          We ran the examples from the support case under Pentaho EE 3.5.2 with Analyzer plug-in 3.6.2.
          Show
          Rob Johnson added a comment - Luc, you need to modify the SteelWheels Mondrian schema file to make the example work. I attached the modified schema as steelwheels.mondrian.xml in support case 00011810, along with an Analyzer report and Analysis View that run with the modified schema and show the incorrect results. We ran the examples from the support case under Pentaho EE 3.5.2 with Analyzer plug-in 3.6.2.
          Hide
          Luc Boudreau added a comment -
          I extended the FilterTest.testBugMondrian706() function to validate the SQL which gets generated. I also set in the test all the environment properties that you had in the attachments. I can confirm that the current code base on the 3.2 branch generates the SQL correctly and uses the store_id column.

          The SQL generated is:

          select `store`.`store_country` as `c0`, `store`.`store_state` as `c1`, `store`.`store_city` as `c2`, `store`.`store_id` as `c3`, `store`.`store_name` as `c4` from `store` as `store` where (`store`.`store_state` in ('CA', 'OR')) and ((`store`.`store_id`, `store`.`store_city`, `store`.`store_state`) in ((11, 'Portland', 'OR'), (14, 'San Francisco', 'CA'))) group by `store`.`store_country`, `store`.`store_state`, `store`.`store_city`, `store`.`store_id`, `store`.`store_name` having NOT((sum(`store`.`store_sqft`) is null)) order by ISNULL(`store`.`store_country`), `store`.`store_country` ASC, ISNULL(`store`.`store_state`), `store`.`store_state` ASC, ISNULL(`store`.`store_city`), `store`.`store_city` ASC, ISNULL(`store`.`store_id`), `store`.`store_id` ASC
          Show
          Luc Boudreau added a comment - I extended the FilterTest.testBugMondrian706() function to validate the SQL which gets generated. I also set in the test all the environment properties that you had in the attachments. I can confirm that the current code base on the 3.2 branch generates the SQL correctly and uses the store_id column. The SQL generated is: select `store`.`store_country` as `c0`, `store`.`store_state` as `c1`, `store`.`store_city` as `c2`, `store`.`store_id` as `c3`, `store`.`store_name` as `c4` from `store` as `store` where (`store`.`store_state` in ('CA', 'OR')) and ((`store`.`store_id`, `store`.`store_city`, `store`.`store_state`) in ((11, 'Portland', 'OR'), (14, 'San Francisco', 'CA'))) group by `store`.`store_country`, `store`.`store_state`, `store`.`store_city`, `store`.`store_id`, `store`.`store_name` having NOT((sum(`store`.`store_sqft`) is null)) order by ISNULL(`store`.`store_country`), `store`.`store_country` ASC, ISNULL(`store`.`store_state`), `store`.`store_state` ASC, ISNULL(`store`.`store_city`), `store`.`store_city` ASC, ISNULL(`store`.`store_id`), `store`.`store_id` ASC

            People

            • Assignee:
              Luc Boudreau
              Reporter:
              Man Shing Yau
            • Votes:
              3 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: