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

DimensionUsage joined at non-leaf level does not work

    Details

    • Customer Case:

      Description

      The following forum posts imply that the level attribute of DimensionUsage - to join a shared dimension into a cube at a level other than its leaf level - does not work.

      http://forums.pentaho.org/showthread.php?p=198616

      http://forums.pentaho.org/showthread.php?t=48055

      I have not personally tried it.

      If someone can provide a test case in terms of the foodmart schema, that will make it easier for us to reproduce and therefore more likely that we will fix this.

        Issue Links

          Activity

          Hide
          Mondrian Importer User added a comment -
          {jhyde}, 10/18/2008: IP, Artifact Created: 195.23.115.32 |
          {calinm}, 10/19/2008: IP, Comment Added: 24.81.62.233 |
          {calinm}, 10/19/2008: IP, Comment Added: 24.81.62.233 |
          {jhyde}, 02/05/2009: priority, 5
          Show
          Mondrian Importer User added a comment - {jhyde}, 10/18/2008: IP, Artifact Created: 195.23.115.32 | {calinm}, 10/19/2008: IP, Comment Added: 24.81.62.233 | {calinm}, 10/19/2008: IP, Comment Added: 24.81.62.233 | {jhyde}, 02/05/2009: priority, 5
          Hide
          Mondrian Importer User added a comment -
          {calinm}, 10/19/2008: There are two bugs in the two posts
          Problem 1) In http://forums.pentaho.org/showthread.php?t=48055 the problem is that measures are wrong when the bind is not at the leaf level.
          Here's how to reproduce using the Foodmart schema:
          - add a column called "store_state" to sales_fact_1997 and populate it with the two letter state code for the store, from store.store_state
          - in the "Sales" Cube, change the store binding for Store to:
          <DimensionUsage name="Store" source="Store" foreignKey="store_state" level="Store State" />
          that is, pretend the grain of the fact is at the State level in the Store dimension.

          Run:

          select NON EMPTY {[Measures].[Unit Sales], [Measures].[Store Cost], [Measures].[Store Sales]} ON COLUMNS,
            NON EMPTY Hierarchize(Union(Union({[Store].[All Stores]}, [Store].[All Stores].Children), [Store].[All Stores].[USA].Children)) ON ROWS
          from [Sales]
          where [Time].[1997]

          The numbers for "All Stores" are still right, but the numbers at the Country and State level are too big (same as in http://forums.pentaho.org/showthread.php?t=48055)

          All Stores 266,773 225,627.23 565,238.13
          USA 1,379,620 1,168,467.28 2,926,945.88
          CA
           373,740 317,652.13 795,839.20
          OR
           135,318 113,545.00 284,554.14
          WA
           870,562 737,270.16 1,846,552.54


          Problem 2) Described in http://forums.pentaho.org/showthread.php?p=198616
          is that if a dimension has NESTED <Join> elements, schema loading will fail with a ClassCastException as described in the post.

          So, this http://mondrian.pentaho.org/documentation/developer_notes.php#Snowflakes would not work in the current version of mondrian.

          Thank you.
          Show
          Mondrian Importer User added a comment - {calinm}, 10/19/2008: There are two bugs in the two posts Problem 1) In http://forums.pentaho.org/showthread.php?t=48055 the problem is that measures are wrong when the bind is not at the leaf level. Here's how to reproduce using the Foodmart schema: - add a column called "store_state" to sales_fact_1997 and populate it with the two letter state code for the store, from store.store_state - in the "Sales" Cube, change the store binding for Store to: <DimensionUsage name="Store" source="Store" foreignKey="store_state" level="Store State" /> that is, pretend the grain of the fact is at the State level in the Store dimension. Run: select NON EMPTY {[Measures].[Unit Sales], [Measures].[Store Cost], [Measures].[Store Sales]} ON COLUMNS,   NON EMPTY Hierarchize(Union(Union({[Store].[All Stores]}, [Store].[All Stores].Children), [Store].[All Stores].[USA].Children)) ON ROWS from [Sales] where [Time].[1997] The numbers for "All Stores" are still right, but the numbers at the Country and State level are too big (same as in http://forums.pentaho.org/showthread.php?t=48055) All Stores 266,773 225,627.23 565,238.13 USA 1,379,620 1,168,467.28 2,926,945.88 CA  373,740 317,652.13 795,839.20 OR  135,318 113,545.00 284,554.14 WA  870,562 737,270.16 1,846,552.54 Problem 2) Described in http://forums.pentaho.org/showthread.php?p=198616 is that if a dimension has NESTED <Join> elements, schema loading will fail with a ClassCastException as described in the post. So, this http://mondrian.pentaho.org/documentation/developer_notes.php#Snowflakes would not work in the current version of mondrian. Thank you.
          Hide
          Mondrian Importer User added a comment -
          {calinm}, 10/19/2008: Below are my workaround for the classcastexception problem as well as my dimension definition. I should probably get the alias for the Join from the parent not from the child but I haven't figured out how. The problem seems to be that the "Join" element does not have an "alias" attribute.

          ### Eclipse Workspace Patch 1.0
          #P mondrian
          Index: origsrc/mondrian/rolap/RolapStar.java
          ===================================================================
          --- origsrc/mondrian/rolap/RolapStar.java (revision 3815)
          +++ origsrc/mondrian/rolap/RolapStar.java (working copy)
          @@ -306,6 +306,10 @@
                               getUniqueRelation(
                                   parent, join.left, foreignKey,
                                   joinKey, joinKeyTable);
          + if (left instanceof MondrianDef.Join) {
          + LOGGER.error("On the left you should always have a table that has the same alias as the parent join");
          + throw new RuntimeException("On the left you should always have a table that has the same alias as the parent join");
          + }
                           parent = nodeLookup.get(
                               ((MondrianDef.Relation) left).getAlias());
                           right =
          @@ -318,8 +322,16 @@
                               getUniqueRelation(
                                   parent, join.right, foreignKey,
                                   joinKey, joinKeyTable);
          + if (right instanceof MondrianDef.Join) {
          + // assume left is always a table
          + String alias = ((MondrianDef.Join) right).getLeftAlias();
          + LOGGER.error("Activating nested Joins hack, using left alias on the inside left as the alias...");
          + parent = nodeLookup.get(
          + (alias));
          + } else {
                           parent = nodeLookup.get(
                               ((MondrianDef.Relation) right).getAlias());
          + }
                           left =
                               getUniqueRelation(
                                   parent, join.left, join.rightKey,

          This is my dimension:

           <Dimension name="Age">
                  <Hierarchy hasAll="true" allMemberName="ALL"
                      allMemberCaption="All Ages">
                      <Join leftAlias="AGE_YR_TBL" rightAlias="FVYR_TBL" leftKey="FVYR_UK"
                          rightKey="FVYR_UK">
                          <Table name="XCM_DM_AGE_AGE_YR" alias="AGE_YR_TBL" />
                          <Join leftAlias="FVYR_TBL" leftKey="OVUN65_UK" rightAlias="OVUN65_TBL"
                              rightKey="OVUN65_UK">
                              <Table name="XCM_DM_AGE_FVYR" alias="FVYR_TBL" />
                              <Table name="XCM_DM_AGE_OVUN65" alias="OVUN65_TBL" />
                          </Join>
                      </Join>
                      <Level name="Over Under 65" table="OVUN65_TBL" column="OVUN65_UK"
                          captionColumn="OVUN65_CAPTION" uniqueMembers="true" ordinalColumn="OVUN65_ORDER" />
                      <Level name="Five Year Age Groups" table="FVYR_TBL" column="FVYR_UK"
                          captionColumn="FVYR_CAPTION" uniqueMembers="true" ordinalColumn="FVYR_ORDER" />
                      <Level name="Age in Years" table="AGE_YR_TBL" column="AGE_YR_UK"
                          captionColumn="AGE_YR_CAPTION" uniqueMembers="true" ordinalColumn="AGE_YR_ORDER" >
                          <Property column="AGE_YR_VALUE" type="Numeric" name="Age Value"/>
                      </Level>
                  </Hierarchy>
              </Dimension>
          Show
          Mondrian Importer User added a comment - {calinm}, 10/19/2008: Below are my workaround for the classcastexception problem as well as my dimension definition. I should probably get the alias for the Join from the parent not from the child but I haven't figured out how. The problem seems to be that the "Join" element does not have an "alias" attribute. ### Eclipse Workspace Patch 1.0 #P mondrian Index: origsrc/mondrian/rolap/RolapStar.java =================================================================== --- origsrc/mondrian/rolap/RolapStar.java (revision 3815) +++ origsrc/mondrian/rolap/RolapStar.java (working copy) @@ -306,6 +306,10 @@                      getUniqueRelation(                          parent, join.left, foreignKey,                          joinKey, joinKeyTable); + if (left instanceof MondrianDef.Join) { + LOGGER.error("On the left you should always have a table that has the same alias as the parent join"); + throw new RuntimeException("On the left you should always have a table that has the same alias as the parent join"); + }                  parent = nodeLookup.get(                      ((MondrianDef.Relation) left).getAlias());                  right = @@ -318,8 +322,16 @@                      getUniqueRelation(                          parent, join.right, foreignKey,                          joinKey, joinKeyTable); + if (right instanceof MondrianDef.Join) { + // assume left is always a table + String alias = ((MondrianDef.Join) right).getLeftAlias(); + LOGGER.error("Activating nested Joins hack, using left alias on the inside left as the alias..."); + parent = nodeLookup.get( + (alias)); + } else {                  parent = nodeLookup.get(                      ((MondrianDef.Relation) right).getAlias()); + }                  left =                      getUniqueRelation(                          parent, join.left, join.rightKey, This is my dimension:  <Dimension name="Age">         <Hierarchy hasAll="true" allMemberName="ALL"             allMemberCaption="All Ages">             <Join leftAlias="AGE_YR_TBL" rightAlias="FVYR_TBL" leftKey="FVYR_UK"                 rightKey="FVYR_UK">                 <Table name="XCM_DM_AGE_AGE_YR" alias="AGE_YR_TBL" />                 <Join leftAlias="FVYR_TBL" leftKey="OVUN65_UK" rightAlias="OVUN65_TBL"                     rightKey="OVUN65_UK">                     <Table name="XCM_DM_AGE_FVYR" alias="FVYR_TBL" />                     <Table name="XCM_DM_AGE_OVUN65" alias="OVUN65_TBL" />                 </Join>             </Join>             <Level name="Over Under 65" table="OVUN65_TBL" column="OVUN65_UK"                 captionColumn="OVUN65_CAPTION" uniqueMembers="true" ordinalColumn="OVUN65_ORDER" />             <Level name="Five Year Age Groups" table="FVYR_TBL" column="FVYR_UK"                 captionColumn="FVYR_CAPTION" uniqueMembers="true" ordinalColumn="FVYR_ORDER" />             <Level name="Age in Years" table="AGE_YR_TBL" column="AGE_YR_UK"                 captionColumn="AGE_YR_CAPTION" uniqueMembers="true" ordinalColumn="AGE_YR_ORDER" >                 <Property column="AGE_YR_VALUE" type="Numeric" name="Age Value"/>             </Level>         </Hierarchy>     </Dimension>
          Hide
          Anthony Carter added a comment -
          See http://jira.pentaho.com/browse/MONDRIAN-747 for a testcase with foodmart.
          Show
          Anthony Carter added a comment - See http://jira.pentaho.com/browse/MONDRIAN-747 for a testcase with foodmart.
          Hide
          Julian Hyde added a comment -
          There are two different problems in this bug. I declare that problem #1 (measure values too large) is covered by MONDRIAN-747.

          Cannot reproduce problem #2. I have added test cases in change 13724.

          * One test case creates a 3-table snowflake dimension. It works because it is right-deep. (Join A (Join B C))

          * Another test case is the same, but the dimension is shared.

          * Another test case is a left-deep join. This now gives a clear error message that left-deep joins are not supported.
          Show
          Julian Hyde added a comment - There are two different problems in this bug. I declare that problem #1 (measure values too large) is covered by MONDRIAN-747 . Cannot reproduce problem #2. I have added test cases in change 13724. * One test case creates a 3-table snowflake dimension. It works because it is right-deep. (Join A (Join B C)) * Another test case is the same, but the dimension is shared. * Another test case is a left-deep join. This now gives a clear error message that left-deep joins are not supported.

            People

            • Assignee:
              Julian Hyde
              Reporter:
              Julian Hyde
            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: