[Mondrian] Hierarchy must have a foreign key error

Pedro Alves pmgalves at gmail.com
Fri May 16 09:48:48 EDT 2014 

I always get that message and always ignore it

On Friday, May 16, 2014, Hilario Fernandes <
hilario.fernandes at cortex-intelligence.com> wrote:

> 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<javascript:_e(%7B%7D,'cvml','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<javascript:_e(%7B%7D,'cvml','mondrian-bounces at pentaho.org');>[mailto:
>> mondrian-bounces at pentaho.org<javascript:_e(%7B%7D,'cvml','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<javascript:_e(%7B%7D,'cvml','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/a8e37e98/attachment.html

More information about the Mondrian mailing list