Pentaho Analysis - Mondrian
  1. Pentaho Analysis - Mondrian
  2. MONDRIAN-1105

AggLevel column attribute not used properly in all cases

    Details

    • QA Validation Status:
      Validated by QA

      Description

      There seems to be a bug with snowflake hierarchies that causes Aggregate tables to join using the wrong column name in some situations.

      (I will attach a sample testmart.xml to go along with this example)
      If for example, there is a Dimension based on Family => Genus => Species using tables DIM_FAMILY, DIM_GENUS, DIM_SPECIES

      dim_family columns: family_id, family_name
      dim_genus columns: family_id, genus_id, genus_name
      dim_species columns: genus_id, species_id, species_name

      Base Fact columns: species_id, population
      Aggregate table's columns: gen_id, population

      In this example, for whatever reason, the database designer gave genus_id a different name (gen_id) in the aggregate table. The column name is defined in the AggLevel tag but gets ignored


      So for a simple MDX like this:

          SELECT
              { [Measures].[Population] } ON COLUMNS,
              { [Animal].[Animals].[Family].Members } ON ROWS
          FROM [Test]



      It runs a query like this:

      select
          "DIM_FAMILY"."FAMILY_ID" as "c0",
          sum("AGG_SPECIES_MART"."POPULATION") as "m0"
      from
          "DIM_FAMILY" as "DIM_FAMILY",
          "DIM_GENUS" as "DIM_GENUS",
          "AGG_SPECIES_MART" as "AGG_SPECIES_MART"
      where
          "AGG_SPECIES_MART"."GENUS_ID" = "DIM_GENUS"."GENUS_ID"
      and
          "DIM_GENUS"."FAMILY_ID" = "DIM_FAMILY"."FAMILY_ID"
      group by
          "DIM_FAMILY"."FAMILY_ID"


      The problem being that agg_species_mart doesn't have a column called "genus_id", it is "gen_id".

      I've identified where in Mondrian's code the column name gets lost and will attach a patch that fixes it in this case.
      1. AggColumn.patch
        0.6 kB
        Dan Dosch
      2. testmart.xml
        2 kB
        Dan Dosch

        Activity

        Hide
        Julian Hyde added a comment -
        Working on this. I have created the test case. But I can't get it to use the agg table.
        Show
        Julian Hyde added a comment - Working on this. I have created the test case. But I can't get it to use the agg table.
        Show
        Julian Hyde added a comment - Fixed in http://github.com/pentaho/mondrian/commit/bb8cea9d0323790f2a2b276ee7daa6028a5d649b .

          People

          • Assignee:
            Curtis Boyden
            Reporter:
            Dan Dosch
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: