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

Allow Aggregate levels to use more attributes from hierarchy's levels so to avoid joins to the dimension tables

    XMLWordPrintable

    Details

    • Type: Improvement
    • Status: Closed
    • Severity: Medium
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 6.0.0 GA
    • 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.
    • Sprint Team:
      Maintenance
    • Sprint:
      August SP

      Description

      A typical example of this improvement is that when you use the ordinalColumn from a level such as Month from a time dimension; Mondrian will join to the time dimension to get the ordinalColumn and will degrade the performance of the query.

      ---> Aggregate level
      <AggLevel column="the_month" name="[Time].[Month]" collapsed="true">
      </AggLevel>

      ---> Level in time dimension
      <Level name="Month" visible="true" column="the_month" ordinalColumn="month_of_year" type="String" uniqueMembers="false" levelType="TimeMonths" hideMemberIf="Never">
      </Level>

      See Sample query:

      HighCardSqlTupleReader.readTuples [[Time].[Month]]: executing sql [
      select
      `agg_cc_14_sales_fact_1997`.`the_year` as `c0`,
      `agg_cc_14_sales_fact_1997`.`quarter` as `c1`,
      `time_by_day`.`the_month` as `c2`,
      `time_by_day`.`month_of_year` as `c3`
      from
      `agg_cc_14_sales_fact_1997` as `agg_cc_14_sales_fact_1997`,
      `time_by_day` as `time_by_day`
      where
      `time_by_day`.`the_month` = `agg_cc_14_sales_fact_1997`.`the_month`
      group by
      `agg_cc_14_sales_fact_1997`.`the_year`,
      `agg_cc_14_sales_fact_1997`.`quarter`,
      `time_by_day`.`the_month`,
      `time_by_day`.`month_of_year` having
      NOT((sum(`agg_cc_14_sales_fact_1997`.`store_sales`) is null))
      order by
      ISNULL(`agg_cc_14_sales_fact_1997`.`the_year`) ASC, `agg_cc_14_sales_fact_1997`.`the_year` ASC,
      ISNULL(`agg_cc_14_sales_fact_1997`.`quarter`) ASC, `agg_cc_14_sales_fact_1997`.`quarter` ASC,
      ISNULL(`time_by_day`.`month_of_year`) ASC, `time_by_day`.`month_of_year` ASC], exec 6912 ms

      If the ordinalColumn is not used in the level then the query used looks similar to this:
      HighCardSqlTupleReader.readTuples [[Time].[Month]]: executing sql [
      select
      `agg_cc_14_sales_fact_1997`.`the_year` as `c0`,
      `agg_cc_14_sales_fact_1997`.`quarter` as `c1`,
      `agg_cc_14_sales_fact_1997`.`the_month` as `c2`
      from
      `agg_cc_14_sales_fact_1997` as `agg_cc_14_sales_fact_1997`
      group by
      `agg_cc_14_sales_fact_1997`.`the_year`,
      `agg_cc_14_sales_fact_1997`.`quarter`,
      `agg_cc_14_sales_fact_1997`.`the_month` having
      NOT((sum(`agg_cc_14_sales_fact_1997`.`store_sales`) is null))
      order by
      ISNULL(`agg_cc_14_sales_fact_1997`.`the_year`) ASC, `agg_cc_14_sales_fact_1997`.`the_year` ASC,
      ISNULL(`agg_cc_14_sales_fact_1997`.`quarter`) ASC, `agg_cc_14_sales_fact_1997`.`quarter` ASC,
      ISNULL(`agg_cc_14_sales_fact_1997`.`the_month`) ASC, `agg_cc_14_sales_fact_1997`.`the_month` ASC], exec 159 ms

        Attachments

          Activity

            People

            Assignee:
            pavel_hrakovich Pavel Hrakovich (Inactive)
            Reporter:
            clopez Carlos Lopez
            Votes:
            4 Vote for this issue
            Watchers:
            9 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved: