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

Mondrian: Parent-child hierarchies: <Join> used in dimension

    Details

    • Type: Bug Bug
    • Status: Open
    • Priority: Severe Severe
    • Resolution: Unresolved
    • Affects Version/s: None
    • Component/s: None
    • Labels:
      None

      Description

      I have a parent-child hierarchy that's also a multi-valued dimension. To deal with this I have a bridge table between the fact & dimension table. The dimension table has a key to the bridge table, the bridge has a foreign key to the dimension table.

        Within the schema I have:

      <Join leftAlias="bridge" leftKey="child_fk" rightAlias="dimension" rightKey="child_tk">
        <Table name="bridge" />
        <Table name="dimension" />
      </Join>

      ... for the hierarchy, and:

      <Level ... >
        <Closure parentColumn="parent_fk" childColumn="child_tk">
          <Table name="closure" />
        </Closure>
      </Level>

        When the dimension is used in the primary cube, I specify the key name within the fact table:

      <DimensionUsage source="Dimension" name="Dimension" foreignKey="au_fk" />

        ... and within the dimension itself I set the hierarchy's primary key:

      <Hierarchy name="Dimension" hasAll="true" allMemberName="All" allLevelName="All" primaryKey="au_fk" primaryKeyTable="bridge">
        ...
      </Hierarchy>

        The problem is that the closure table's childKey is joined against the fact table's foreign key. Properly speaking, it should be joined against the bridge table.

        Activity

        Hide
        Jared Cornelius added a comment -
        Hi György, we will be closing this case within a week if we do not hear from you. You can always re-open it once you are able to respond. Thanks.
        Show
        Jared Cornelius added a comment - Hi György, we will be closing this case within a week if we do not hear from you. You can always re-open it once you are able to respond. Thanks.
        Hide
        György Novák added a comment -
        Hi Jared,
        Sorry for not responding yet.
        We plan to fix the problems that Julian wrote and provide a patch for the latest Pentaho release, but we're not sure when we'll have time for this.
        Show
        György Novák added a comment - Hi Jared, Sorry for not responding yet. We plan to fix the problems that Julian wrote and provide a patch for the latest Pentaho release, but we're not sure when we'll have time for this.
        Hide
        Patrick Leckey added a comment -
        György, this is going in the right direction for us as well. We'll be working on fixing it as Julian suggests over the next few days based on your patch.
        Show
        Patrick Leckey added a comment - György, this is going in the right direction for us as well. We'll be working on fixing it as Julian suggests over the next few days based on your patch.
        Hide
        György Novák added a comment -
        That's really good news Patrick. Thanks! We couldn't still manage to spend more time on this.
        Show
        György Novák added a comment - That's really good news Patrick. Thanks! We couldn't still manage to spend more time on this.
        Hide
        Julian Hyde added a comment -
        For what it's worth (not much), here is the patch after I had tinkered with it for a couple of hours. I'm posting it here just so that I can revert it from my sandbox. If someone wants to try to move this patch forward, please start with my version.

        ==== //open/mondrian/src/main/mondrian/rolap/agg/AbstractQuerySpec.java#28 - /home/jhyde/open1/mondrian/src/main/mondrian/rolap/agg/AbstractQuerySpec.java ====
        @@ -14,6 +14,7 @@
         import mondrian.rolap.*;
         import mondrian.rolap.sql.SqlQuery;
         import mondrian.spi.Dialect;
        +import mondrian.util.ArrayStack;
         import mondrian.util.Pair;
         
         import java.util.*;
        @@ -100,6 +101,18 @@
                     }
                     table.addToFrom(sqlQuery, false, true);
         
        + // For bridge table to parent-child hierarchy.
        + if (true) {
        + final ArrayStack<RolapStar.Table> tableStack =
        + new ArrayStack<RolapStar.Table>();
        + tableStack.addAll(table.getChildren());
        + while (!tableStack.isEmpty()) {
        + RolapStar.Table currentTable = tableStack.pop();
        + currentTable.addToFrom(sqlQuery, false, true);
        + tableStack.addAll(currentTable.getChildren());
        + }
        + }
        +
                     String expr = column.generateExprString(sqlQuery);
         
                     StarColumnPredicate predicate = getColumnPredicate(i);
        ==== //open/mondrian/src/main/mondrian/rolap/RolapHierarchy.java#117 - /home/jhyde/open1/mondrian/src/main/mondrian/rolap/RolapHierarchy.java ====
        @@ -377,8 +377,10 @@
                         uniqueNameParts = Util.parseIdentifier(defaultMemberName);
                     } else {
                         uniqueNameParts =
        - Collections.singletonList(
        - new Id.Segment(defaultMemberName, Id.Quoting.UNQUOTED));
        + Collections.<Id.Segment>singletonList(
        + new Id.NameSegment(
        + defaultMemberName,
        + Id.Quoting.UNQUOTED));
                     }
         
                     // First look up from within this hierarchy. Works for unqualified
        @@ -1103,10 +1105,48 @@
                 peerHier.sharedHierarchyName = getSharedHierarchyName();
                 MondrianDef.Join join = new MondrianDef.Join();
                 peerHier.relation = join;
        - join.left = clos.table; // the closure table
        - join.leftKey = clos.parentColumn;
        - join.right = relation; // the unclosed base table
        - join.rightKey = clos.childColumn;
        +
        + // For bridge table to parent-child hierarchy.
        + if (peerHier.closureFor != null
        + && peerHier.closureFor.relation instanceof MondrianDef.Join)
        + {
        + join.left = clos.table; // the closure table
        + join.leftKey = clos.childColumn;
        +
        + // FIXME: Works only if the right relation is a simple table or a
        + // join with only 1 level. If it's a multi-level join, then joining
        + // to the closure table (using the right table for the join.rightKey
        + // column) won't be possible. The system uses the left-most (ie the
        + // highest) table from a join relation to join to other tables.
        + join.right = src.getHierarchy().getRelation(); // unclosed base tbl
        + join.rightKey = ((MondrianDef.Column)src.keyExp).name;
        + try {
        + MondrianDef.Hierarchy cloHier =
        + (mondrian.olap.MondrianDef.Hierarchy)
        + peerHier.closureFor.getXmlHierarchy().deepCopy();
        + MondrianDef.RelationOrJoin parentRel = cloHier.relation;
        + cloHier.relation =
        + new MondrianDef.Join(
        + null,
        + ((MondrianDef.Column)src.keyExp).table
        + + "."
        + + ((MondrianDef.Column)src.keyExp).name,
        + parentRel,
        + null,
        + clos.childColumn,
        + clos.table);
        + peerHier.xmlHierarchy = cloHier;
        + } catch (Exception e) {
        + throw Util.newInternal(
        + e,
        + "Exception while building closure hierarchy");
        + }
        + } else {
        + join.left = clos.table; // the closure table
        + join.leftKey = clos.parentColumn;
        + join.right = relation; // the unclosed base table
        + join.rightKey = clos.childColumn;
        + }
         
                 // Create the upper level.
                 // This represents all groups of descendants. For example, in the
        @@ -1456,11 +1496,20 @@
                     Id.Segment s,
                     MatchType matchType)
                 {
        - if (Util.equalName(s.name, dimension.getName())) {
        + if (!(s instanceof Id.NameSegment)) {
        + return null;
        + }
        + final Id.NameSegment nameSegment = (Id.NameSegment) s;
        +
        + if (Util.equalName(nameSegment.name, dimension.getName())) {
                         return dimension;
                     }
                     // Archaic form <dimension>.<hierarchy>, e.g. [Time.Weekly].[1997]
        - if (Util.equalName(s.name, dimension.getName() + "." + subName)) {
        + if (!MondrianProperties.instance().SsasCompatibleNaming.get()
        + && Util.equalName(
        + nameSegment.name,
        + dimension.getName() + "." + subName))
        + {
                         return RolapHierarchy.this;
                     }
                     return null;
        ==== //open/mondrian/src/main/mondrian/rolap/RolapStar.java#127 - /home/jhyde/open1/mondrian/src/main/mondrian/rolap/RolapStar.java ====
        @@ -352,16 +352,16 @@
                                 join.rightKey, join.getRightAlias());
                     } else if (join.getRightAlias().equals(joinKeyTable)) {
                         // right side must equal
        - right =
        - getUniqueRelation(
        - parent, join.right, foreignKey,
        - joinKey, joinKeyTable);
        - parent = nodeLookup.get(
        - ((MondrianDef.Relation) right).getAlias());
                         left =
                             getUniqueRelation(
                                 parent, join.left, join.rightKey,
                                 join.leftKey, join.getLeftAlias());
        + parent = nodeLookup.get(
        + ((MondrianDef.Relation) left).getAlias());
        + right =
        + getUniqueRelation(
        + parent, join.right, foreignKey,
        + joinKey, joinKeyTable);
                     } else {
                         throw new MondrianException(
                             "failed to match primary key table to join tables");
        Show
        Julian Hyde added a comment - For what it's worth (not much), here is the patch after I had tinkered with it for a couple of hours. I'm posting it here just so that I can revert it from my sandbox. If someone wants to try to move this patch forward, please start with my version. ==== //open/mondrian/src/main/mondrian/rolap/agg/AbstractQuerySpec.java#28 - /home/jhyde/open1/mondrian/src/main/mondrian/rolap/agg/AbstractQuerySpec.java ==== @@ -14,6 +14,7 @@  import mondrian.rolap.*;  import mondrian.rolap.sql.SqlQuery;  import mondrian.spi.Dialect; +import mondrian.util.ArrayStack;  import mondrian.util.Pair;    import java.util.*; @@ -100,6 +101,18 @@              }              table.addToFrom(sqlQuery, false, true);   + // For bridge table to parent-child hierarchy. + if (true) { + final ArrayStack<RolapStar.Table> tableStack = + new ArrayStack<RolapStar.Table>(); + tableStack.addAll(table.getChildren()); + while (!tableStack.isEmpty()) { + RolapStar.Table currentTable = tableStack.pop(); + currentTable.addToFrom(sqlQuery, false, true); + tableStack.addAll(currentTable.getChildren()); + } + } +              String expr = column.generateExprString(sqlQuery);                StarColumnPredicate predicate = getColumnPredicate(i); ==== //open/mondrian/src/main/mondrian/rolap/RolapHierarchy.java#117 - /home/jhyde/open1/mondrian/src/main/mondrian/rolap/RolapHierarchy.java ==== @@ -377,8 +377,10 @@                  uniqueNameParts = Util.parseIdentifier(defaultMemberName);              } else {                  uniqueNameParts = - Collections.singletonList( - new Id.Segment(defaultMemberName, Id.Quoting.UNQUOTED)); + Collections.<Id.Segment>singletonList( + new Id.NameSegment( + defaultMemberName, + Id.Quoting.UNQUOTED));              }                // First look up from within this hierarchy. Works for unqualified @@ -1103,10 +1105,48 @@          peerHier.sharedHierarchyName = getSharedHierarchyName();          MondrianDef.Join join = new MondrianDef.Join();          peerHier.relation = join; - join.left = clos.table; // the closure table - join.leftKey = clos.parentColumn; - join.right = relation; // the unclosed base table - join.rightKey = clos.childColumn; + + // For bridge table to parent-child hierarchy. + if (peerHier.closureFor != null + && peerHier.closureFor.relation instanceof MondrianDef.Join) + { + join.left = clos.table; // the closure table + join.leftKey = clos.childColumn; + + // FIXME: Works only if the right relation is a simple table or a + // join with only 1 level. If it's a multi-level join, then joining + // to the closure table (using the right table for the join.rightKey + // column) won't be possible. The system uses the left-most (ie the + // highest) table from a join relation to join to other tables. + join.right = src.getHierarchy().getRelation(); // unclosed base tbl + join.rightKey = ((MondrianDef.Column)src.keyExp).name; + try { + MondrianDef.Hierarchy cloHier = + (mondrian.olap.MondrianDef.Hierarchy) + peerHier.closureFor.getXmlHierarchy().deepCopy(); + MondrianDef.RelationOrJoin parentRel = cloHier.relation; + cloHier.relation = + new MondrianDef.Join( + null, + ((MondrianDef.Column)src.keyExp).table + + "." + + ((MondrianDef.Column)src.keyExp).name, + parentRel, + null, + clos.childColumn, + clos.table); + peerHier.xmlHierarchy = cloHier; + } catch (Exception e) { + throw Util.newInternal( + e, + "Exception while building closure hierarchy"); + } + } else { + join.left = clos.table; // the closure table + join.leftKey = clos.parentColumn; + join.right = relation; // the unclosed base table + join.rightKey = clos.childColumn; + }            // Create the upper level.          // This represents all groups of descendants. For example, in the @@ -1456,11 +1496,20 @@              Id.Segment s,              MatchType matchType)          { - if (Util.equalName(s.name, dimension.getName())) { + if (!(s instanceof Id.NameSegment)) { + return null; + } + final Id.NameSegment nameSegment = (Id.NameSegment) s; + + if (Util.equalName(nameSegment.name, dimension.getName())) {                  return dimension;              }              // Archaic form <dimension>.<hierarchy>, e.g. [Time.Weekly].[1997] - if (Util.equalName(s.name, dimension.getName() + "." + subName)) { + if (!MondrianProperties.instance().SsasCompatibleNaming.get() + && Util.equalName( + nameSegment.name, + dimension.getName() + "." + subName)) + {                  return RolapHierarchy.this;              }              return null; ==== //open/mondrian/src/main/mondrian/rolap/RolapStar.java#127 - /home/jhyde/open1/mondrian/src/main/mondrian/rolap/RolapStar.java ==== @@ -352,16 +352,16 @@                          join.rightKey, join.getRightAlias());              } else if (join.getRightAlias().equals(joinKeyTable)) {                  // right side must equal - right = - getUniqueRelation( - parent, join.right, foreignKey, - joinKey, joinKeyTable); - parent = nodeLookup.get( - ((MondrianDef.Relation) right).getAlias());                  left =                      getUniqueRelation(                          parent, join.left, join.rightKey,                          join.leftKey, join.getLeftAlias()); + parent = nodeLookup.get( + ((MondrianDef.Relation) left).getAlias()); + right = + getUniqueRelation( + parent, join.right, foreignKey, + joinKey, joinKeyTable);              } else {                  throw new MondrianException(                      "failed to match primary key table to join tables");

          People

          • Assignee:
            Unassigned User
            Reporter:
            Brian Vandenberg
          • Votes:
            2 Vote for this issue
            Watchers:
            5 Start watching this issue

            Dates

            • Created:
              Updated: