Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Blocker Blocker
    • Resolution: Fixed
    • Affects Version/s: 3.5.0 GA (4.8.0 GA Suite Release)
    • 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.
    • QA Validation Status:
      Not Yet Validated

      Description

      This issue relates to ESR-2182. When joining a dimension table to the aggregate table, if the level was non collapsed, Mondrian would join on the lowest key column, whereas it should join on the level's key column.

        Activity

        Hide
        Luc Boudreau added a comment -

        Fixed in https://github.com/pentaho/mondrian/commit/7523a0aa9e41371868f9d0476a940f00800d5a5f

        I've written a test in mondrian.rolap.TestAggregationManager.testMondrian1271()

        To reproduce, turn on aggregate tables and create the following schema.

        --------------------------------------
        <?xml version="1.0"?>
        <Schema name="custom">
        <Dimension name="Store">
        <Hierarchy hasAll="true" primaryKey="store_id">
        <Table name="store"/>
        <Level name="Store Country" column="store_country" uniqueMembers="true"/>
        <Level name="Store State" column="store_state" uniqueMembers="true"/>
        <Level name="Store City" column="store_city" uniqueMembers="false"/>
        <Level name="Store Name" column="store_name" uniqueMembers="true">
        </Level>
        </Hierarchy>
        </Dimension>
        <Dimension name="Time" type="TimeDimension">
        <Hierarchy hasAll="false" primaryKey="time_id">
        <Table name="time_by_day"/>
        <Level name="Year" column="the_year" type="Numeric" uniqueMembers="true"
        levelType="TimeYears"/>
        <Level name="Quarter" column="quarter" uniqueMembers="false"
        levelType="TimeQuarters"/>
        <Level name="Month" column="month_of_year" uniqueMembers="true" type="Numeric"
        levelType="TimeMonths"/>
        <Level name="Day" column="day_of_month" uniqueMembers="false" type="Numeric"
        levelType="TimeDays"/>
        </Hierarchy>
        </Dimension>
        <Cube name="Sales1" defaultMeasure="Unit Sales">
        <Table name="sales_fact_1997">
        <AggExclude name="agg_c_special_sales_fact_1997"/> <AggExclude name="agg_c_10_sales_fact_1997"/> <AggExclude name="agg_l_04_sales_fact_1997"/> <AggExclude name="agg_g_ms_pcat_sales_fact_1997"/> <AggExclude name="agg_lc_06_sales_fact_1997"/> <AggExclude name="agg_l_03_sales_fact_1997"/> <AggExclude name="agg_lc_100_sales_fact_1997"/> <AggExclude name="agg_pl_01_sales_fact_1997"/> <AggExclude name="agg_ll_01_sales_fact_1997"/> <AggExclude name="agg_l_05_sales_fact_1997"/> <AggName name="agg_c_14_sales_fact_1997">
        <AggFactCount column="FACT_COUNT"/>
        <AggIgnoreColumn column="PRODUCT_ID" />
        <AggIgnoreColumn column="CUSTOMER_ID" />
        <AggIgnoreColumn column="PROMOTION_ID" />
        <AggIgnoreColumn column="THE_YEAR" />
        <AggIgnoreColumn column="QUARTER" />
        <AggForeignKey factColumn="store_id" aggColumn="STORE_ID" />
        <AggMeasure name="[Measures].[Unit Sales]" column="UNIT_SALES" />
        <AggMeasure name="[Measures].[Store Cost]" column="STORE_COST" />
        <AggMeasure name="[Measures].[Store Sales]" column="STORE_SALES" />
        <AggLevel name="[Time].[Month]" column="month_of_year" collapsed="false" />
        </AggName>
        </Table>
        <DimensionUsage name="Store" source="Store" foreignKey="store_id"/>
        <DimensionUsage name="Time" source="Time" foreignKey="time_id"/>
        <Measure name="Unit Sales" column="unit_sales" aggregator="sum"
        formatString="Standard"/>
        <Measure name="Store Cost" column="store_cost" aggregator="sum"
        formatString="#,###.00"/>
        <Measure name="Store Sales" column="store_sales" aggregator="sum"
        formatString="#,###.00"/>
        </Cube>
        </Schema>
        --------------------------------------

        Then run the following MDX.

        --------------------------------------
        select

        {NonEmptyCrossJoin([Time].[Year].Members, [Store].[Store Country].Members)}

        on rows,

        {[Measures].[Unit Sales]}

        on columns from [Sales1]
        --------------------------------------

        Then check the SQL logs. If its all good, you will see these two sql queries.

        --------------------------------------
        select
        `time_by_day`.`the_year` as `c0`,
        `store`.`store_country` as `c1`
        from
        `time_by_day` as `time_by_day`,
        `agg_c_14_sales_fact_1997` as `agg_c_14_sales_fact_1997`,
        `store` as `store`
        where
        `agg_c_14_sales_fact_1997`.`month_of_year` = `time_by_day`.`month_of_year`
        and
        `agg_c_14_sales_fact_1997`.`STORE_ID` = `store`.`store_id`
        group by
        `time_by_day`.`the_year`,
        `store`.`store_country`
        order by
        ISNULL(`time_by_day`.`the_year`) ASC, `time_by_day`.`the_year` ASC,
        ISNULL(`store`.`store_country`) ASC, `store`.`store_country` ASC

        select
        `store`.`store_country` as `c0`,
        `time_by_day`.`the_year` as `c1`,
        sum(`agg_c_14_sales_fact_1997`.`unit_sales`) as `m0`
        from
        `store` as `store`,
        `agg_c_14_sales_fact_1997` as `agg_c_14_sales_fact_1997`,
        `time_by_day` as `time_by_day`
        where
        `agg_c_14_sales_fact_1997`.`STORE_ID` = `store`.`store_id`
        and
        `store`.`store_country` = 'USA'
        and
        `agg_c_14_sales_fact_1997`.`month_of_year` = `time_by_day`.`month_of_year`
        group by
        `store`.`store_country`,
        `time_by_day`.`the_year`
        --------------------------------------

        Notice that the agg table joins on the month level's column of the dimension.

        Show
        Luc Boudreau added a comment - Fixed in https://github.com/pentaho/mondrian/commit/7523a0aa9e41371868f9d0476a940f00800d5a5f I've written a test in mondrian.rolap.TestAggregationManager.testMondrian1271() To reproduce, turn on aggregate tables and create the following schema. -------------------------------------- <?xml version="1.0"?> <Schema name="custom"> <Dimension name="Store"> <Hierarchy hasAll="true" primaryKey="store_id"> <Table name="store"/> <Level name="Store Country" column="store_country" uniqueMembers="true"/> <Level name="Store State" column="store_state" uniqueMembers="true"/> <Level name="Store City" column="store_city" uniqueMembers="false"/> <Level name="Store Name" column="store_name" uniqueMembers="true"> </Level> </Hierarchy> </Dimension> <Dimension name="Time" type="TimeDimension"> <Hierarchy hasAll="false" primaryKey="time_id"> <Table name="time_by_day"/> <Level name="Year" column="the_year" type="Numeric" uniqueMembers="true" levelType="TimeYears"/> <Level name="Quarter" column="quarter" uniqueMembers="false" levelType="TimeQuarters"/> <Level name="Month" column="month_of_year" uniqueMembers="true" type="Numeric" levelType="TimeMonths"/> <Level name="Day" column="day_of_month" uniqueMembers="false" type="Numeric" levelType="TimeDays"/> </Hierarchy> </Dimension> <Cube name="Sales1" defaultMeasure="Unit Sales"> <Table name="sales_fact_1997"> <AggExclude name="agg_c_special_sales_fact_1997"/> <AggExclude name="agg_c_10_sales_fact_1997"/> <AggExclude name="agg_l_04_sales_fact_1997"/> <AggExclude name="agg_g_ms_pcat_sales_fact_1997"/> <AggExclude name="agg_lc_06_sales_fact_1997"/> <AggExclude name="agg_l_03_sales_fact_1997"/> <AggExclude name="agg_lc_100_sales_fact_1997"/> <AggExclude name="agg_pl_01_sales_fact_1997"/> <AggExclude name="agg_ll_01_sales_fact_1997"/> <AggExclude name="agg_l_05_sales_fact_1997"/> <AggName name="agg_c_14_sales_fact_1997"> <AggFactCount column="FACT_COUNT"/> <AggIgnoreColumn column="PRODUCT_ID" /> <AggIgnoreColumn column="CUSTOMER_ID" /> <AggIgnoreColumn column="PROMOTION_ID" /> <AggIgnoreColumn column="THE_YEAR" /> <AggIgnoreColumn column="QUARTER" /> <AggForeignKey factColumn="store_id" aggColumn="STORE_ID" /> <AggMeasure name=" [Measures] . [Unit Sales] " column="UNIT_SALES" /> <AggMeasure name=" [Measures] . [Store Cost] " column="STORE_COST" /> <AggMeasure name=" [Measures] . [Store Sales] " column="STORE_SALES" /> <AggLevel name=" [Time] . [Month] " column="month_of_year" collapsed="false" /> </AggName> </Table> <DimensionUsage name="Store" source="Store" foreignKey="store_id"/> <DimensionUsage name="Time" source="Time" foreignKey="time_id"/> <Measure name="Unit Sales" column="unit_sales" aggregator="sum" formatString="Standard"/> <Measure name="Store Cost" column="store_cost" aggregator="sum" formatString="#,###.00"/> <Measure name="Store Sales" column="store_sales" aggregator="sum" formatString="#,###.00"/> </Cube> </Schema> -------------------------------------- Then run the following MDX. -------------------------------------- select {NonEmptyCrossJoin([Time].[Year].Members, [Store].[Store Country].Members)} on rows, {[Measures].[Unit Sales]} on columns from [Sales1] -------------------------------------- Then check the SQL logs. If its all good, you will see these two sql queries. -------------------------------------- select `time_by_day`.`the_year` as `c0`, `store`.`store_country` as `c1` from `time_by_day` as `time_by_day`, `agg_c_14_sales_fact_1997` as `agg_c_14_sales_fact_1997`, `store` as `store` where `agg_c_14_sales_fact_1997`.`month_of_year` = `time_by_day`.`month_of_year` and `agg_c_14_sales_fact_1997`.`STORE_ID` = `store`.`store_id` group by `time_by_day`.`the_year`, `store`.`store_country` order by ISNULL(`time_by_day`.`the_year`) ASC, `time_by_day`.`the_year` ASC, ISNULL(`store`.`store_country`) ASC, `store`.`store_country` ASC select `store`.`store_country` as `c0`, `time_by_day`.`the_year` as `c1`, sum(`agg_c_14_sales_fact_1997`.`unit_sales`) as `m0` from `store` as `store`, `agg_c_14_sales_fact_1997` as `agg_c_14_sales_fact_1997`, `time_by_day` as `time_by_day` where `agg_c_14_sales_fact_1997`.`STORE_ID` = `store`.`store_id` and `store`.`store_country` = 'USA' and `agg_c_14_sales_fact_1997`.`month_of_year` = `time_by_day`.`month_of_year` group by `store`.`store_country`, `time_by_day`.`the_year` -------------------------------------- Notice that the agg table joins on the month level's column of the dimension.
        Hide
        Luc Boudreau added a comment -

        I have checked another fix for this case under:

        https://github.com/pentaho/mondrian/commit/f905a4b07ce73e8e9c7afe7a081ed7b4bcea4041

        My other fix had introduced a regression which could be triggered with the following MDX.

        select

        {NonEmptyCrossJoin([Time].[Day].Members, [Store].[Store Country].Members)}

        on rows,

        {[Measures].[Unit Sales]}

        on columns from [Sales1]

        To reproduce, execute the MDX and check that the cell values are not the same for every row.

        Show
        Luc Boudreau added a comment - I have checked another fix for this case under: https://github.com/pentaho/mondrian/commit/f905a4b07ce73e8e9c7afe7a081ed7b4bcea4041 My other fix had introduced a regression which could be triggered with the following MDX. select {NonEmptyCrossJoin([Time].[Day].Members, [Store].[Store Country].Members)} on rows, {[Measures].[Unit Sales]} on columns from [Sales1] To reproduce, execute the MDX and check that the cell values are not the same for every row.
        Hide
        Li Deng added a comment - - edited

        Followed the steps and verified the correct sql queries display in log after run the MDX.

        Show
        Li Deng added a comment - - edited Followed the steps and verified the correct sql queries display in log after run the MDX.

          People

          • Assignee:
            Li Deng
            Reporter:
            Luc Boudreau
          • Votes:
            1 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: