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

Wrong table alias in SQL generated to populate member cache

    Details

    • 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.

      Description

      In case of multiple dimension usage of the same shared dimension in a cube,
      only for the second usage,
      if a level in the dimension hierarchy have a property or a ordinal column,
      and the MDX query contain "non empty",
      mondrian creates wrong alias table building member cache.
      This doasn't happen with a filter in the first dimension usage.

      To reproduce with FoodMart:
      1) clone the column store_id in the table "sales_fact_1997"; name it "buyer_id".
      2) in the FoodMart.xml schema add a dimension usage "Buyer" for the shared dimension "Store" with column "buyer_id" AFTER "Store" dimension usage

      <DimensionUsage source="Store" name="Buyer" visible="true" foreignKey="buyer_id" highCardinality="false">
      </DimensionUsage>

      3) query the schema:

      select [Measures].[Unit Sales] on columns,
      non empty [Buyer].[USA].[OR].[Portland].children on rows
      from [Sales]

      Error rise:
      **************************************
      Mondrian Error:java.util.concurrent.ExecutionException: mondrian.olap.MondrianException: Mondrian Error:Internal error: Error while executing query [select

      {[Measures].[Unit Sales]} ON COLUMNS,
      NON EMPTY [Buyer].[USA].[OR].[Portland].Children ON ROWS
      from [Sales]
      ]

      mondrian.olap.MondrianException: Mondrian Error:Internal error: Error while executing query [select {[Measures].[Unit Sales]}

      ON COLUMNS,
      NON EMPTY [Buyer].[USA].[OR].[Portland].Children ON ROWS
      from [Sales]
      ]

      Mondrian Error:Internal error: Error while executing query [select

      {[Measures].[Unit Sales]} ON COLUMNS,
      NON EMPTY [Buyer].[USA].[OR].[Portland].Children ON ROWS
      from [Sales]
      ]

      Mondrian Error:Internal error: while building member cache; sql=[select `store_1`.`store_name` as `c0`, `store`.`store_type` as `c1`, `store`.`store_manager` as `c2`, `store`.`store_sqft` as `c3`, `store`.`grocery_sqft` as `c4`, `store`.`frozen_sqft` as `c5`, `store`.`meat_sqft` as `c6`, `store`.`coffee_bar` as `c7`, `store`.`store_street_address` as `c8` from `store` as `store_1`, `sales_fact_1997` as `sales_fact_1997` where `sales_fact_1997`.`buyer_id` = `store_1`.`store_id` and `store_1`.`store_state` = 'OR' and `store_1`.`store_city` = 'Portland' group by `store_1`.`store_name`, `store`.`store_type`, `store`.`store_manager`, `store`.`store_sqft`, `store`.`grocery_sqft`, `store`.`frozen_sqft`, `store`.`meat_sqft`, `store`.`coffee_bar`, `store`.`store_street_address` order by ISNULL(`store_1`.`store_name`) ASC, `store_1`.`store_name` ASC]

      Unknown column 'store.store_type' in 'field list'
      *********************************
      *Note the alias "from `store` as `store_1`" not used for the field defined in the properties.

      *Note:

      **without "non empty" it runs:

      select [Measures].[Unit Sales] on columns,
      [Buyer].[USA].[OR].[Portland].children on rows
      from [Sales]
      *****************
      Axis #0:
      {}
      Axis #1:{[Measures].[Unit Sales]}

      Axis #2:

      {[Buyer].[USA].[OR].[Portland].[Store 11]}

      Row #0: 26.079
      ******************
      **with a filter in the first dimension usage it runs:

      select [Measures].[Unit Sales] on columns,
      non empty [Buyer].[USA].[OR].[Portland].children on rows
      from [Sales]
      where [Store].[USA]

      **querying the first dimension usage runs ([Store] vs. [Buyer]):

      select [Measures].[Unit Sales] on columns,
      non empty [Store].[USA].[OR].[Portland].children on rows
      from [Sales]

      Axis #0:
      {}
      Axis #1:

      {[Measures].[Unit Sales]}

      Axis #2:

      {[Store].[USA].[OR].[Portland].[Store 11]}

      Row #0: 26.079

        Attachments

          Activity

            People

            • Assignee:
              ldeng Li Deng
              Reporter:
              enricomariam42 Enrico Maria Carmona
            • Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: