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: