Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Severe Severe
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: None
    • Labels:
      None
    • 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

      For our customer we need to filter data by a date
      range. This is done with the Aggregate-function.

      If the Time-Dimension has a All-member,
      RolapAggregationManager.makeRequest(..) returns null,
      which leads a null-SQL-statement to be returned, which
      leads to a NPE.

      If the Time-Dimension does not contain a All-member,
      AggregationManager.getDrillThroughSQL(..) returns an
      invalid SQL-statement.

      How to reproduce with Foodmart:

      1) execute the following query:

      with set [Date Range] as
      '

      {[Time].[1997].[Q1],[Time].[1997].[Q2]}

      '
      member [Time].[Date Range] as 'Aggregate([Date
      Range])'

      select

      {[Store]}

      on rows,

      {[Measures].[Unit Sales]}

      on columns
      from [Sales]

      where [Time].[Date Range]

      This query returns a single cell. Then try to drill
      through => an SQLException occurs, because an invalid
      SQL-query has been returned by
      AggregationManager.getDrillThroughSQL(..):

      select `store`.`store_name` as `Store Name`,
      `store`.`store_city` as `Store City`,
      `store`.`store_state` as `Store State`,
      `store`.`store_country` as `Store Country`,
      `store`.`store_sqft` as `Store Sqft`,
      `store`.`store_type` as `Store Type`,
      `time_by_day`.`month_of_year` as `Month`,
      `time_by_day`.`quarter` as `Quarter`,
      `time_by_day`.`the_year` as `Year`,
      `product`.`product_name` as `Product Name`,
      `product`.`brand_name` as `Brand Name`,
      `product_class`.`product_subcategory` as
      `Product Subcategory`,
      `product_class`.`product_category` as `Product
      Category`,
      `product_class`.`product_department` as `Product
      Department`,
      `product_class`.`product_family` as `Product
      Family`,
      `promotion`.`media_type` as `Media Type`,
      `promotion`.`promotion_name` as `Promotion Name`,
      CONCAT(`customer`.`fname`, ' ',
      `customer`.`lname`) as `Name`,
      `customer`.`city` as `City`,
      `customer`.`state_province` as `State Province`,
      `customer`.`country` as `Country`,
      `customer`.`education` as `Education Level`,
      `customer`.`gender` as `Gender`,
      `customer`.`marital_status` as `Marital Status`,
      `customer`.`yearly_income` as `Yearly Income`,
      `sales_fact_1997`.`unit_sales` as `Unit Sales`

      from `store` as `store`,
      `sales_fact_1997` as `sales_fact_1997`,
      `time_by_day` as `time_by_day`,
      `product` as `product`,
      `product_class` as `product_class`,
      `promotion` as `promotion`,
      `customer` as `customer`

      where `sales_fact_1997`.`store_id` = `store`.`store_id`
      and
      `sales_fact_1997`.`time_id` =
      `time_by_day`.`time_id` and
      `time_by_day`.`the_year` = Date Range and
      `sales_fact_1997`.`product_id` =
      `product`.`product_id` and
      `product`.`product_class_id` =
      `product_class`.`product_class_id` and
      `sales_fact_1997`.`promotion_id` =
      `promotion`.`promotion_id` and
      `sales_fact_1997`.`customer_id` =
      `customer`.`customer_id`

      The problem is

      and `time_by_day`.`the_year` = Date Range and .....

      2) change the Time-Dimension in the FoodMart.xml:
      (set hasAll to true):

      <Dimension name="Time" type="TimeDimension">
      <Hierarchy hasAll="true" allMemberName="All"
      primaryKey="time_id">
      ....
      </Dimension>

      then execute the same query (as described above).
      Then try to drill through
      => The result is a NPE:

      java.lang.NullPointerException: DrillThroughSQL
      returned null
      ......

        Activity

        Hide
        Mondrian Importer User added a comment -
        {jhyde}, 05/10/2006: status_id, 1 | {jhyde}

        , 05/10/2006: resolution_id, 100 |

        {jhyde}, 05/10/2006: assigned_to, 100 | {jhyde}

        , 05/10/2006: close_date, 0

        Show
        Mondrian Importer User added a comment - {jhyde}, 05/10/2006: status_id, 1 | {jhyde} , 05/10/2006: resolution_id, 100 | {jhyde}, 05/10/2006: assigned_to, 100 | {jhyde} , 05/10/2006: close_date, 0
        Hide
        Mondrian Importer User added a comment -
        {jhyde}

        , 05/10/2006: Logged In: YES
        user_id=312935

        Fixed in change 6540/6541.

        Now if any of the members are calculated, it ignores their
        constraining effect. Not ideal, I know, but it's too
        difficult to translate general MDX expressions into SQL, but
        at least we always generate valid SQL.

        Show
        Mondrian Importer User added a comment - {jhyde} , 05/10/2006: Logged In: YES user_id=312935 Fixed in change 6540/6541. Now if any of the members are calculated, it ignores their constraining effect. Not ideal, I know, but it's too difficult to translate general MDX expressions into SQL, but at least we always generate valid SQL.

          People

          • Assignee:
            Julian Hyde
            Reporter:
            rehdie_easyplex
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: