Uploaded image for project: '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
    • Status: Closed
    • Severity: High
    • Resolution: Cannot Reproduce
    • Affects Version/s: None
    • Component/s: None
    • Labels:
      None
    • Environment:
      Windows
    • 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.
    • Browser:
      Internet Explorer 8.x

      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.

      1. analyzer_query_log_foodmart.html
        13 kB
        Benny Chow
      2. foodmart.mondrian.xml
        39 kB
        Benny Chow
      3. Last Analyzer Query Log.mht
        15 kB
        Man Shing Yau
      4. mondrian.properties
        5 kB
        Benny Chow

        Issue Links

          Activity

          Hide
          robj 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
          robj 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
          anthonycarter 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
          anthonycarter 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
          lboudreau 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
          lboudreau 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
          robj 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
          robj 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
          lboudreau 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
          lboudreau 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:
              lboudreau Luc Boudreau
              Reporter:
              myau Man Shing Yau
            • Votes:
              3 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: