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

Issues with aggregate table recognition when using <KeyExpression><SQL>...</SQL></KeyExpression> to define a level

    Details

    • 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.
    • CI Build:
      bi-server-ee-MON33.zip dated Sep 19, 2011 3:24:17 PM(#28).

      Description

      seen on custom build of mondrian 2.4, but seems to still exist in 3.2.

      We have a dimension with a single level defined as follows*:

      <Dimension name="Dim" foreignKey="$keyColumn$">
          <Hierarchy hasAll="true" primaryKey="$keyColumn$" allMemberName="All Dim">
                  <Table name="Dim_$CUSTOMER_ID$_$CLIENT_ID$" alias="Dim_$CUSTOMER_ID$_$CLIENT_ID$_$it$">
                  </Table>
              <Level name="Dim_Value" nameColumn="dim_value" type="String" levelType="Regular" uniqueMembers="true">
                  <KeyExpression><SQL>UPPER(dim_value)</SQL></KeyExpression>
              </Level>
          </Hierarchy>
      </Dimension>

      and an agg table with a rollup column of "dim_dim_value".

      loading up this schema results in:

      [24 Sep 2010 10:46:29] [http-8080-1] 269705 ERROR (mondrian.recorder.AbstractRecorder:46) - DefaultRecognizer.matchLevel:Recognizer.makeLevel: Rolap.Column not found (null) for tableAlias=Dim_1_1_23, factColumnName=UPPER(dim_value), levelColumnName=dim_dim_value, symbolicName=Dim_Value
      [24 Sep 2010 10:46:29] [http-8080-1] 269707 ERROR (com.marin.olap.server.OlapServer:194) - Mondrian Exception
      mondrian.olap.MondrianException: Mondrian Error:Too many errors, '1', while loading/reloadin aggregates.
              at mondrian.resource.MondrianResource$_Def8.ex(MondrianResource.java:898)
              at mondrian.rolap.aggmatcher.AggTableManager.loadRolapStarAggregates(AggTableManager.java:318)
              at mondrian.rolap.aggmatcher.AggTableManager.initialize(AggTableManager.java:94)
              at mondrian.rolap.RolapSchema.load(RolapSchema.java:332)
              at mondrian.rolap.RolapSchema.<init>(RolapSchema.java:205)
              at mondrian.rolap.RolapSchema.<init>(RolapSchema.java:79)
              at mondrian.rolap.RolapSchema$Pool.get(RolapSchema.java:831)
              at mondrian.rolap.RolapSchema$Pool.get(RolapSchema.java:703)
              at mondrian.rolap.RolapConnection.<init>(RolapConnection.java:156)
              at mondrian.rolap.RolapConnection.<init>(RolapConnection.java:80)
              at mondrian.olap.DriverManager.getConnection(DriverManager.java:190)



      Applying the following patch to RolapStar.java (Modifiying the body of RolapStar$Table#lookupColumn(String columnName)):
      Index: src/main/mondrian/rolap/RolapStar.java
      ===================================================================
      --- src/main/mondrian/rolap/RolapStar.java (revision 128)
      +++ src/main/mondrian/rolap/RolapStar.java (working copy)
      @@ -1346,6 +1346,12 @@
                           if (columnExpr.name.equals(columnName)) {
                               return column;
                           }
      + } else if (column.getExpression() instanceof MondrianDef.KeyExpression) {
      + MondrianDef.KeyExpression columnExpr =
      + (MondrianDef.KeyExpression) column.getExpression();
      + if (columnExpr.toString().equals(columnName)) {
      + return column;
      + }
                       } else if (column.getName().equals(columnName)) {
                           return column;
                       }

      fixes the issue.

      I think it makes sense, as Recognizer#getColumnName() is already special casing for both MondrianDef$Column and MondrianDef$KeyExpression, and the "columnName" argument to this lookupColumn() method originates from the Recognizer#getColumnName() call, so this is just extending that special case to the other side of the .equals().




      *Reason for the definition is that the 'dim_value' is not always capitalized the same, and we want the rollup to work case-insensitively, without actually transforming the case that the users are enterning into the system -- which is why nameColumn just ref's the column direclty

        Activity

        Hide
        Luc Boudreau added a comment -
        Could not reproduce on Mondrian 3.2.1. We created a test case to reproduce this and it passes. Looking at the SQL log, we can confirm that KeyExpressions are used correctly in the aggregatee table recognition and usage.

        http://ci.pentaho.com/job/mondrian-3.2/213/testReport/mondrian.rolap/TestAggregationManager/testLevelKeyAsSqlExpWithAgg/
        Show
        Luc Boudreau added a comment - Could not reproduce on Mondrian 3.2.1. We created a test case to reproduce this and it passes. Looking at the SQL log, we can confirm that KeyExpressions are used correctly in the aggregatee table recognition and usage. http://ci.pentaho.com/job/mondrian-3.2/213/testReport/mondrian.rolap/TestAggregationManager/testLevelKeyAsSqlExpWithAgg/
        Hide
        Joe Barnett added a comment -
        I can reproduce this on the 3.2.0 release, might be specific to the column in the agg table using the ${hierarchy_name}_${level_name} pattern. Again, the attached patch still fixes it.

        will try to get a foodmart test case when i have a chance, but won't be soon.
        Show
        Joe Barnett added a comment - I can reproduce this on the 3.2.0 release, might be specific to the column in the agg table using the ${hierarchy_name}_${level_name} pattern. Again, the attached patch still fixes it. will try to get a foodmart test case when i have a chance, but won't be soon.
        Hide
        Joe Barnett added a comment -
        this now looks to be fixed by cl 13911 ?
        Show
        Joe Barnett added a comment - this now looks to be fixed by cl 13911 ?
        Hide
        Suhas Gururaja added a comment -
        Tested this issue on CI build bi-server-ee-MON33.zip dated Sep 19, 2011 3:24:17 PM(#28).

        Repro:
        This issue is tested in foodmart database.
        Add the following piece of code into the foodmart.xml file for sales cube which has many aggregate tables.

        <Dimension foreignKey="customer_id" highCardinality="false" name="Customers">
              <Hierarchy hasAll="true" allMemberName="All Customers" primaryKey="customer_id">
                <Table name="customer">
                </Table>
                <Level name="Country" column="country" type="String" uniqueMembers="true" levelType="Regular" hideMemberIf="Never">
         <KeyExpression><SQL>LOWER(country)</SQL></KeyExpression>

        and changed the last line to UPPER case also by changing the last like of the code to :
        <KeyExpression><SQL>UPPER(country)</SQL></KeyExpression>
        We are not getting any error reported in the logs. This <KeyExpression><SQL>...</SQL></KeyExpression> tag is working fine.

        Please find the attached screen shots.
        Show
        Suhas Gururaja added a comment - Tested this issue on CI build bi-server-ee-MON33.zip dated Sep 19, 2011 3:24:17 PM(#28). Repro: This issue is tested in foodmart database. Add the following piece of code into the foodmart.xml file for sales cube which has many aggregate tables. <Dimension foreignKey="customer_id" highCardinality="false" name="Customers">       <Hierarchy hasAll="true" allMemberName="All Customers" primaryKey="customer_id">         <Table name="customer">         </Table>         <Level name="Country" column="country" type="String" uniqueMembers="true" levelType="Regular" hideMemberIf="Never">  <KeyExpression><SQL>LOWER(country)</SQL></KeyExpression> and changed the last line to UPPER case also by changing the last like of the code to : <KeyExpression><SQL>UPPER(country)</SQL></KeyExpression> We are not getting any error reported in the logs. This <KeyExpression><SQL>...</SQL></KeyExpression> tag is working fine. Please find the attached screen shots.

          People

          • Assignee:
            Suhas Gururaja
            Reporter:
            Joe Barnett
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: