[Mondrian] Hierarchy must have a foreign key error

Hilario Fernandes hilario.fernandes at cortex-intelligence.com
Fri May 16 09:35:52 EDT 2014


I believe i'm not violating that principle.

I've managed to isolate the issue a bit more. Some of the cubes are not
based on a single table but on a view, and the problem seems to appear
after a few "view cubes" are added/removed. If no "view cubes" are ever
added the problem apparently never occurs. Here is an example of a schema
using a view:


<Cube name='54e8009a64dd4e97bc5a15b07cacaff7' visible='true' cache='true'
enabled='true'>
  <View alias='VIEW_CUBE_daa60922f25442e99de732f80d6fdb8c_FACT'>
    <SQL dialect='generic'><![CDATA[select

"CUBE_daa60922f25442e99de732f80d6fdb8c_FACT"."MEASURE_4a70415b1aab44ae8ff2c65d1d4ff3cc",

"CUBE_daa60922f25442e99de732f80d6fdb8c_FACT"."ATTR_f71a0e19745e40c4bcbdce3d555412c5",
    "MULTI_ATTR_2f2916deca36481ab47bd55a7bf2a197"."UNIT_VALUE" as
"UNIT_VALUE_2f2916deca36481ab47bd55a7bf2a197",
    "CUBE_daa60922f25442e99de732f80d6fdb8c_FACT"."ID",

"CUBE_daa60922f25442e99de732f80d6fdb8c_FACT"."DIM_ID_4db645b51ea74582bc2295b11af6fe96",
    "MULTI_ATTR_f71a0e19745e40c4bcbdce3d555412c5"."UNIT_VALUE" as
"UNIT_VALUE_f71a0e19745e40c4bcbdce3d555412c5",

"CUBE_daa60922f25442e99de732f80d6fdb8c_FACT"."ATTR_fd0b6adf39c94ab4ba2fcc13966e49ba",

"CUBE_daa60922f25442e99de732f80d6fdb8c_FACT"."ATTR_2ca930c08ae440b0a2b313186c7196f4",
    "MULTI_ATTR_2ca930c08ae440b0a2b313186c7196f4"."UNIT_VALUE" as
"UNIT_VALUE_2ca930c08ae440b0a2b313186c7196f4",

"CUBE_daa60922f25442e99de732f80d6fdb8c_FACT"."ATTR_2f2916deca36481ab47bd55a7bf2a197"
from
    "CUBE_daa60922f25442e99de732f80d6fdb8c_FACT" as
"CUBE_daa60922f25442e99de732f80d6fdb8c_FACT"
    left join
        "MULTI_ATTR_2ca930c08ae440b0a2b313186c7196f4" as
"MULTI_ATTR_2ca930c08ae440b0a2b313186c7196f4"
    on

"CUBE_daa60922f25442e99de732f80d6fdb8c_FACT"."MV_ATTR_2ca930c08ae440b0a2b313186c7196f4"
= "MULTI_ATTR_2ca930c08ae440b0a2b313186c7196f4"."ID"
    left join
        "MULTI_ATTR_2f2916deca36481ab47bd55a7bf2a197" as
"MULTI_ATTR_2f2916deca36481ab47bd55a7bf2a197"
    on

"CUBE_daa60922f25442e99de732f80d6fdb8c_FACT"."MV_ATTR_2f2916deca36481ab47bd55a7bf2a197"
= "MULTI_ATTR_2f2916deca36481ab47bd55a7bf2a197"."ID"
    left join
        "MULTI_ATTR_f71a0e19745e40c4bcbdce3d555412c5" as
"MULTI_ATTR_f71a0e19745e40c4bcbdce3d555412c5"
    on

"CUBE_daa60922f25442e99de732f80d6fdb8c_FACT"."MV_ATTR_f71a0e19745e40c4bcbdce3d555412c5"
= "MULTI_ATTR_f71a0e19745e40c4bcbdce3d555412c5"."ID"]]></SQL>
  </View>

  <Dimension name='Topic' caption='2c37f4b9698d4b88a8378e7e82925cc5:Topic'
visible='true'>
    <Hierarchy name='Topic' caption='Topic' allMemberName='Total Topic'
visible='true' hasAll='true'>
      <Level name='Topic' caption='Topic' visible='true'
column='UNIT_VALUE_2c37f4b9698d4b88a8378e7e82925cc5' uniqueMembers='true'
levelType='Regular' hideMemberIf='Never' type='String' />
    </Hierarchy>
  </Dimension>
  <Dimension name='Picture'
caption='52a4ec09bd0a43b0803fe38372dd727b:Picture' visible='true'>
    <Hierarchy name='Picture' caption='Picture' allMemberName='Total
Picture' visible='true' hasAll='true'>
      <Level name='Picture' caption='Picture' visible='true'
column='ATTR_52a4ec09bd0a43b0803fe38372dd727b' uniqueMembers='true'
levelType='Regular' hideMemberIf='Never' type='String' />
    </Hierarchy>
  </Dimension>
  <Dimension name='Post' caption='62b8abdf73fd4736bc2df933c31d7324:Post'
visible='true'>
    <Hierarchy name='Post' caption='Post' allMemberName='Total Post'
visible='true' hasAll='true'>
      <Level name='Post' caption='Post' visible='true'
column='ATTR_62b8abdf73fd4736bc2df933c31d7324' uniqueMembers='true'
levelType='Regular' hideMemberIf='Never' type='String' />
    </Hierarchy>
  </Dimension>
  <Dimension name='Retweet'
caption='2f2916deca36481ab47bd55a7bf2a197:Retweet' visible='true'>
    <Hierarchy name='Retweet' caption='Retweet' allMemberName='Total
Retweet' visible='true' hasAll='true'>
      <Level name='Retweet' caption='Retweet' visible='true'
column='UNIT_VALUE_2f2916deca36481ab47bd55a7bf2a197' uniqueMembers='true'
levelType='Regular' hideMemberIf='Never' type='String' />
    </Hierarchy>
  </Dimension>
  <Dimension name='Minute'
caption='232ca4d9a0a047fa9e37402ad8959350:Minute' visible='true'>
    <Hierarchy name='Minute' caption='Minute' allMemberName='Total Minute'
visible='true' hasAll='true'>
      <Level name='Minute' caption='Minute' visible='true'
column='ATTR_232ca4d9a0a047fa9e37402ad8959350' uniqueMembers='true'
levelType='Regular' hideMemberIf='Never' type='String' />
    </Hierarchy>
  </Dimension>
  <Dimension name='CompaniesTwitter'
caption='2ca930c08ae440b0a2b313186c7196f4:Companies (Twitter)'
visible='true'>
    <Hierarchy name='CompaniesTwitter' caption='Companies (Twitter)'
allMemberName='Total Companies (Twitter)' visible='true' hasAll='true'>
      <Level name='CompaniesTwitter' caption='Companies (Twitter)'
visible='true' column='UNIT_VALUE_2ca930c08ae440b0a2b313186c7196f4'
uniqueMembers='true' levelType='Regular' hideMemberIf='Never' type='String'
/>
    </Hierarchy>
  </Dimension>
  <Dimension name='Url' caption='40bd4ef5fa1740c39b09031beabe923d:Url'
visible='true'>
    <Hierarchy name='Url' caption='Url' allMemberName='Total Url'
visible='true' hasAll='true'>
      <Level name='Url' caption='Url' visible='true'
column='ATTR_40bd4ef5fa1740c39b09031beabe923d' uniqueMembers='true'
levelType='Regular' hideMemberIf='Never' type='String' />
    </Hierarchy>
  </Dimension>
  <Dimension name='Sentiment'
caption='f71a0e19745e40c4bcbdce3d555412c5:Sentiment' visible='true'>
    <Hierarchy name='Sentiment' caption='Sentiment' allMemberName='Total
Sentiment' visible='true' hasAll='true'>
      <Level name='Sentiment' caption='Sentiment' visible='true'
column='UNIT_VALUE_f71a0e19745e40c4bcbdce3d555412c5' uniqueMembers='true'
levelType='Regular' hideMemberIf='Never' type='String' />
    </Hierarchy>
  </Dimension>
  <Dimension name='Weekday'
caption='14796f51f80643f3ad48cc8172dd9ac9:Weekday' visible='true'>
    <Hierarchy name='Weekday' caption='Weekday' allMemberName='Total
Weekday' visible='true' hasAll='true'>
      <Level name='Weekday' caption='Weekday' visible='true'
column='ATTR_14796f51f80643f3ad48cc8172dd9ac9' uniqueMembers='true'
levelType='Regular' hideMemberIf='Never' type='String' />
    </Hierarchy>
  </Dimension>
  <Measure name='Fact Count' column='ID' formatString='Standard'
aggregator='count' />
  <Measure caption='SUM:4a70415b1aab44ae8ff2c65d1d4ff3cc:Contagem'
name='Contagem_SUM' column='MEASURE_4a70415b1aab44ae8ff2c65d1d4ff3cc'
formatString='Standard' aggregator='sum' />
  <Measure caption='AVG:4a70415b1aab44ae8ff2c65d1d4ff3cc:Contagem'
name='Contagem_AVG' column='MEASURE_4a70415b1aab44ae8ff2c65d1d4ff3cc'
formatString='Standard' aggregator='avg' />
</Cube>







On Fri, May 16, 2014 at 1:39 PM, Matt Campbell <mcampbell at pentaho.com>wrote:

> The rule is that a hierarchy must either specify a foreign key, or use the
> same table as the fact table (i.e. it must be degenerate).  Is your schema
> violating that rule?
>
>
>
> It is curous that you're seeing that error with newer versions of Mondrian
> but not pre-3.6.6, the logic around that check hasn't changed in quite
> awhile.  Can you post a snippet of your schema?
>
>
>
>
>
> *From:* mondrian-bounces at pentaho.org [mailto:mondrian-bounces at pentaho.org]
> *On Behalf Of *Hilario Fernandes
> *Sent:* Friday, May 16, 2014 7:57 AM
> *To:* Mondrian mailing list
> *Subject:* [Mondrian] Hierarchy must have a foreign key error
>
>
>
> Hi,
>
>
>
>
>
> Since mondrian 3.6.6 build and above i'm getting a strange error in my
> project:
>
>
>
> mondrian.olap.MondrianException: Mondrian Error:Hierarchy 'hier_name' in
> cube 'cube_name' must have a foreign key, since it is not based on the
> cube's fact table.
>
>             at
> mondrian.resource.MondrianResource$_Def1.ex(MondrianResource.java:989)
>
>             at
> mondrian.rolap.RolapCube.registerDimension(RolapCube.java:1770)
>
>             at mondrian.rolap.RolapCube.<init>(RolapCube.java:203)
>
>             at mondrian.rolap.RolapCube.<init>(RolapCube.java:227)
>
>             at mondrian.rolap.RolapSchema.createCube(RolapSchema.java:917)
>
>
>
> It seems like it's related to degenerate dimensions and the usage of alias
> in the fact table. In version 3.6.5 and bellow this error only occurred
> when the alias was specified, but now there seem to have been some changes
> on the class RolapSchema that enforces the usage of the alias.
>
>
>
> In my project i'm adding and removing cubes from the schema, and this
> seems to mask the problem, since it only occurs after some time so i'm
> unable to blame any specific query or cube.
>
>
>
> Anyone has ideas on this, or is getting the same error?
>
>
>
> Thank you
>
>
>
> --
>
> Hilario Fernandes
>
> _______________________________________________
> Mondrian mailing list
> Mondrian at pentaho.org
> http://lists.pentaho.org/mailman/listinfo/mondrian
>
>


-- 
Hilario Fernandes
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.pentaho.org/pipermail/mondrian/attachments/20140516/8231c8db/attachment-0001.html 


More information about the Mondrian mailing list