Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Critical Critical
    • Resolution: Incomplete
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: Aggregation Tables
    • Labels:
      None

      Description

      <Cube name="Cheques">
          <Table name="cheques">
        <AggName name="agg_lc_time_cheques">
              <AggFactCount column="FACT_COUNT"/>
              <AggMeasure name="[Measures].[Avg Amount]"
      column="amount_AVG" />
              <AggLevel name="[Worker].[Worker]"
      column="worker_worker_name"/>
              <AggLevel name="[Discount Card].[Discount
      Card Type]" column="discount_card_name"/>
              <AggLevel name="[Department].[Department]"
      column="department_department_name"/>
              <AggLevel name="[Department].[Store]"
      column="department_store_name"/>
          </AggName>
       </Table>
              <DimensionUsage name="Year" source="Year"
      foreignKey="year_id"/>
              <DimensionUsage name="Quarter"
      source="Quarter" foreignKey="quarter_id"/>
              <DimensionUsage name="Month" source="Month"
      foreignKey="month_id"/>
              <DimensionUsage name="Week" source="Week"
      foreignKey="week_id"/>
              <DimensionUsage name="WeekDay"
      source="WeekDay" foreignKey="weekday_id"/>
              <DimensionUsage name="Day" source="Day"
      foreignKey="day_id"/>
              <DimensionUsage name="Hour" source="Hour"
      foreignKey="hour_id"/>
              <DimensionUsage name="Worker" source="Worker"
      foreignKey="worker_id"/>
              <DimensionUsage name="Discount Card"
      source="Discount Card" foreignKey="discount_card_id"/>
              <DimensionUsage name="Department"
      source="Department" foreignKey="department_id"/>
              <Measure name="Avg Amount" caption="Товара по
      чеку" column="amount" aggregator="avg"
      formatString="0"/>
          </Cube>

      2006-08-16 08:52:57,453 DEBUG
      [mondrian.rolap.RolapUtil] Segment.load:
      executing sql [select sum
      (`agg_lc_time_cheques`.`amount_AVG`) / sum
      (`agg_lc_time_cheques`.`FACT_COUNT`) as `m0` from
      `agg_lc_time_cheques` as `agg_lc_time_cheques`], 0 ms

      Mondrian generates "select sum
      (`agg_lc_time_cheques`.`amount_AVG`) / sum
      (`agg_lc_time_cheques`.`FACT_COUNT`)"

      instead of

      select sum
      (`agg_lc_time_cheques`.`amount_AVG`*`agg_lc_time_chequ
      es`.`FACT_COUNT`) / sum
      (`agg_lc_time_cheques`.`FACT_COUNT`)
      so, I have incorrect result....
      (for example if you have a fact table fact_A with a
      columns A, B and aggregate table agg_l_B_fact_A with
      a column A_AVG
      fact_A agg_l_B_fact_A
      A B A_AVG fact_count
      1 6 1/1=1 1
      2 5 (2+3)/2=2.5 2
      3 5 (4+5+6)/3=5 3
      4 3
      5 3
      6 3
      Results:
      average value from the fact table -
      (1+2+3+4+5+6)/6=3.5
      average value from the aggregate table -
      (1 + 2.5 + 5)/3=2.8(3)
      correct evaluation for aggregate table -
      (1*1 + 2.5*2 + 5*3)/(1 + 2 + 3)=3.5

        Activity

        Hide
        Mondrian Importer User added a comment -
        {jhyde}, 08/24/2006: priority, 5 |
        {jhyde}, 08/24/2006: assigned_to, 100 |
        {sdyson}, 11/22/2007: IP, Comment Added: 195.171.160.226
        Show
        Mondrian Importer User added a comment - {jhyde}, 08/24/2006: priority, 5 | {jhyde}, 08/24/2006: assigned_to, 100 | {sdyson}, 11/22/2007: IP, Comment Added: 195.171.160.226
        Hide
        Mondrian Importer User added a comment -
        {jhyde}, 08/24/2006: Logged In: YES
        user_id=312935

        Priority 7. Must fix for mondrian-2.3. Assigning to Richard
        Emberson.
        Show
        Mondrian Importer User added a comment - {jhyde}, 08/24/2006: Logged In: YES user_id=312935 Priority 7. Must fix for mondrian-2.3. Assigning to Richard Emberson.
        Hide
        Mondrian Importer User added a comment -
        {emberson}, 10/09/2006: Logged In: YES
        user_id=543347

        It is beleived that checkin 7876 fixes this.
        Show
        Mondrian Importer User added a comment - {emberson}, 10/09/2006: Logged In: YES user_id=543347 It is beleived that checkin 7876 fixes this.
        Hide
        Mondrian Importer User added a comment -
        {weavelink}, 10/16/2006: Logged In: YES
        user_id=1254631

        Richard,

        I already have change 7876, and I don't see that the bug is
        solved.

        Do note I defined my aggregation tables in another way than
        Jana did.

        I defined my column name in the aggregate table just like
        the measure in my cube (required surface mth), so not
        containing any AVG keyword. The same thing happens as
        described here in the bug.

        Would change 7876 need to solve this or should my column
        contain _AVG at the end ?

        My column in the aggregate table contains the average, not
        the sum. I guess this is the correct way ? There is no
        other measure that needs that column, so mondrian my well
        assume that this column contains an avg ?.

        Thanks,
        Bart
        Show
        Mondrian Importer User added a comment - {weavelink}, 10/16/2006: Logged In: YES user_id=1254631 Richard, I already have change 7876, and I don't see that the bug is solved. Do note I defined my aggregation tables in another way than Jana did. I defined my column name in the aggregate table just like the measure in my cube (required surface mth), so not containing any AVG keyword. The same thing happens as described here in the bug. Would change 7876 need to solve this or should my column contain _AVG at the end ? My column in the aggregate table contains the average, not the sum. I guess this is the correct way ? There is no other measure that needs that column, so mondrian my well assume that this column contains an avg ?. Thanks, Bart
        Hide
        Mondrian Importer User added a comment -
        {weavelink}, 10/16/2006: Logged In: YES
        user_id=1254631

        Hi Richard,

        I studied the code in more detail and I see that
        Recognizer.convertAggregator() is in code in two ways, one
        variant is called in implicit recognizer the other one in
        the explicit recognizer.

        In the implicit case (my way) the AvgFromSum is always
        assumed.

        Is this correct ? If so, is it possible to add this to the
        documentation.

        Can you also explain in what case AvgFromAvg is taken ?

        Thanks Bart.
        Show
        Mondrian Importer User added a comment - {weavelink}, 10/16/2006: Logged In: YES user_id=1254631 Hi Richard, I studied the code in more detail and I see that Recognizer.convertAggregator() is in code in two ways, one variant is called in implicit recognizer the other one in the explicit recognizer. In the implicit case (my way) the AvgFromSum is always assumed. Is this correct ? If so, is it possible to add this to the documentation. Can you also explain in what case AvgFromAvg is taken ? Thanks Bart.
        Hide
        Mondrian Importer User added a comment -
        {sdyson}, 11/22/2007: Logged In: YES
        user_id=1753211
        Originator: NO

        There has been no activity on this bug for a while but as far as I can see it renders aggregate tables useless if you want to use averages.

        As Bart seems to have also noticed the problem is that the convertAggregator method is returning AvgFromSum rather than AvgFromAvg but then using the avg column in the query. There is a note in the method saying it assumes an average column does not exist. I have tried removing the avg columns from my aggregate tables thinking it would then correctly derive the averages from the sums but it then refused to use the aggregate tables at all.
        Show
        Mondrian Importer User added a comment - {sdyson}, 11/22/2007: Logged In: YES user_id=1753211 Originator: NO There has been no activity on this bug for a while but as far as I can see it renders aggregate tables useless if you want to use averages. As Bart seems to have also noticed the problem is that the convertAggregator method is returning AvgFromSum rather than AvgFromAvg but then using the avg column in the query. There is a note in the method saying it assumes an average column does not exist. I have tried removing the avg columns from my aggregate tables thinking it would then correctly derive the averages from the sums but it then refused to use the aggregate tables at all.

          People

          • Assignee:
            Unassigned User
            Reporter:
            juneone
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: