[Mondrian] Apache Kylin and mondrian
Isaias Sanchez
isaias.sanchez.l at gmail.com
Mon May 30 08:16:08 EDT 2016
The dimension must have one key (except degenerate dimension that can
have none) and that key must be one of the attribute even if that
attribute use a composite key.
Your issue here can be solved by two ways (even I reckon that issues are
very unreal):
1. Assing the composite key to only one attribute inside dimension:
<Dimension name='time_dimension' table='KYLIN_CATEGORY_GROUPINGS'
key='month'>
<Attributes>
<Attribute name='year' hasHierarchy='true'>
<key>
<Column name='year'/>
</key>
</Attribute>
<Attribute name='month' hasHierarchy='true'>
<key>
<Column name="LEAF_CATEG_ID"/>
<Column name="SITE_ID"/>
</key>
<Name>
<Column name='month'/>
</Name>
</Attribute>
</Dimension>
2. Create two separate dimensions:
<Dimension name='time_dimension_01' table='KYLIN_CATEGORY_GROUPINGS'
key='year'>
<Attributes>
<Attribute name='year' hasHierarchy='true'>
<key>
<Column name="LEAF_CATEG_ID"/>
<Column name="SITE_ID"/>
</key>
<Name>
<Column name='year'/>
</Name>
</Attribute>
</Dimension>
<Dimension name='time_dimension_02' table='KYLIN_CATEGORY_GROUPINGS'
key='month'>
<Attribute name='month' hasHierarchy='true'>
<key>
<Column name="LEAF_CATEG_ID"/>
<Column name="SITE_ID"/>
</key>
<Name>
<Column name='month'/>
</Name>
</Attribute>
</Dimension>
Cheers,
Isaias S.
Jian Zhong wrote on 30/05/16 05:21:
> anyone any comments on this would be appreciated.
>
> thanks!
>
>
>
> On Fri, May 27, 2016 at 6:35 PM, Jian Zhong <zhongjian at apache.org
> <mailto:zhongjian at apache.org>> wrote:
>
> Thank you, Julian
>
> another question about composite key and hierarchy.
>
> Is it possible to define a <Dimension> with more than one
> <Attribute> with hierarchy and join relation between the fact
> table is composite key.
>
> Say in a star schema, I have lookup table
> KYLIN_CATEGORY_GROUPINGS, fact table KYLIN_SALES
>
> the join relation like this.
>
> * |KYLIN_SALES.LEAF_CATEG_ID =
> KYLIN_CATEGORY_GROUPINGS.LEAF_CATEG_ID|
> * |KYLIN_SALES.LSTG_SITE_ID = KYLIN_CATEGORY_GROUPINGS.SITE_ID|
>
>
>
> I have hierarchy column year,month on KYLIN_CATEGORY_GROUPINGS,
> the dimension define like
>
> <Dimension name='time_dimension'
> table='KYLIN_CATEGORY_GROUPINGS' >
> <Attributes>
> <Attribute name='year' hasHierarchy='true'>
> <key>
> <Column name="LEAF_CATEG_ID"/>
> <Column name="SITE_ID"/>
> </key>
> <Name>
> <Column name='year'/>
> </Name>
> </Attribute>
>
> <Attribute name='month' hasHierarchy='true'>
> <key>
> <Column name="LEAF_CATEG_ID"/>
> <Column name="SITE_ID"/>
> </key>
> <Name>
> <Column name='month'/>
> </Name>
> </Attribute>
> </Dimension>
>
> and the measure define like
>
> <ForeignKeyLink dimension='time_dimension'
> attribute='year'>
> <ForeignKey>
> <Column name="LEAF_CATEG_ID"/>
> <Column name="LSTG_SITE_ID"/>
> </ForeignKey>
> </ForeignKeyLink>
> <ForeignKeyLink dimension='time_dimension'
> attribute='month'>
> <ForeignKey>
> <Column name="LEAF_CATEG_ID"/>
> <Column name="LSTG_SITE_ID"/>
> </ForeignKey>
> </ForeignKeyLink>
>
>
> then the problem comes,
>
> if I do not defined [key] attribute on element <Dimension> , will
> throw log like
>
> " More than one link for dimension 'time_dimension' in measure
> group 'measures' (in ForeignKeyLink) "<
>
> if I try to add one [key] attribute to element <Dimension>, will
> throw log like
>
> " Mondrian Error:Dimension 'time_dimension' omits a defined key,
> which is only valid for degenerate dimensions with a single
> attribute. (in Dimension 'time_dimension') (at line 0, column 461) "
>
> So, how can I define hierarchy column on table with composite key
> join relation between fact table?
>
> Please correct me if you are available, I'm new to mondrian.
>
> Thanks !
>
> Best Regards !
>
>
> On Thu, May 26, 2016 at 12:34 AM, Julian Hyde
> <julianhyde at gmail.com <mailto:julianhyde at gmail.com>> wrote:
>
> You define the key once, at the table level. You define the
> foreign key in each link, i.e. each time the table is used as
> a dimension.
>
> I can’t imagine how any less information would suffice,
> because each reference is probably from a different fact
> table, and therefore the foreign keys are different.
>
> Julian
>
>> On May 25, 2016, at 6:46 AM, Isaias Sanchez
>> <isaias.sanchez.l at gmail.com
>> <mailto:isaias.sanchez.l at gmail.com>> wrote:
>>
>> I think that's not possible by now.
>>
>> Jian Zhong wrote on 25/05/16 12:30:
>>> one more question,
>>>
>>> since the join relation is between tables.
>>>
>>> in mondrian schema, i need to define composite keys in every
>>> dimension from the table.
>>>
>>> which cause many duplicate code.
>>>
>>> Can I define composite key at table level?
>>>
>>> thanks!
>>>
>>>
>>>
>>> On Wed, May 25, 2016 at 3:21 PM, Jian Zhong
>>> <hellowode110 at gmail.com <mailto:hellowode110 at gmail.com>> wrote:
>>>
>>> Thank you Isaias !
>>>
>>> you saved my day.
>>>
>>> On Tue, May 24, 2016 at 4:28 PM, Isaias Sanchez
>>> <isaias.sanchez.l at gmail.com
>>> <mailto:isaias.sanchez.l at gmail.com>> wrote:
>>>
>>> I guess:
>>>
>>> <Table name="KYLIN_CATEGORY_GROUPINGS">
>>> <key>
>>> <column name="LEAF_CATEG_ID"/>
>>> </key>
>>> </Table>
>>>
>>> or
>>>
>>> <Table name="KYLIN_CATEGORY_GROUPINGS">
>>> </Table>
>>>
>>> Will do the job.
>>>
>>> The composite key leave it only in dimension part.
>>>
>>> Cheers,
>>>
>>> Isaias S.
>>>
>>> Jian Zhong wrote on 24/05/16 10:12:
>>>> Julian,
>>>>
>>>> Thank you!
>>>>
>>>> I'm using Mondrian 4.4, I follow the guide. and I
>>>> get error log like
>>>>
>>>> 016-05-24 15:07:47,851 ERROR
>>>> [org.saiku.web.core.SecurityAwareConnectionManager]
>>>> Error connecting: kylin_sales_cal_compos_test
>>>>
>>>> mondrian.rolap.RolapSchema$MondrianSchemaException:
>>>> Key must have precisely one column; key
>>>> [KYLIN_CATEGORY_GROUPINGS.LEAF_CATEG_ID,
>>>> KYLIN_CATEGORY_GROUPINGS.SITE_ID] in table
>>>> 'KYLIN_CATEGORY_GROUPINGS'. (in Key) (at line 0,
>>>> column 275)
>>>>
>>>> We have table KYLIN_SALES and KYLIN_CATEGORY_GROUPINGS.
>>>>
>>>> the join relation like this.
>>>>
>>>>
>>>> * |DEFAULT.KYLIN_SALES.LEAF_CATEG_ID =
>>>> DEFAULT.KYLIN_CATEGORY_GROUPINGS.LEAF_CATEG_ID|
>>>> * |DEFAULT.KYLIN_SALES.LSTG_SITE_ID =
>>>> DEFAULT.KYLIN_CATEGORY_GROUPINGS.SITE_ID|
>>>>
>>>> Enclosure is the schema file.
>>>>
>>>> Coulld you coorect me?
>>>>
>>>> Thanks.
>>>>
>>>>
>>>> On Tue, May 24, 2016 at 11:57 AM, Julian Hyde
>>>> <julianhyde at gmail.com
>>>> <mailto:julianhyde at gmail.com>> wrote:
>>>>
>>>> Are you using Mondrian version 3 or 4? Mondrian
>>>> 4 supports composite keys. Define a composite
>>>> primary key for the dimension table, and
>>>> include multiple columns in the ForeignKeyLink.
>>>>
>>>> See
>>>> http://mondrian.pentaho.com/head/documentation/schema.php#Measures,
>>>> towards the end of the section.
>>>>
>>>> Neither version of Mondrian supports left join.
>>>> Can you describe what you hope would happen? If
>>>> a record in the “sales" fact table has a
>>>> “productId” value that doesn’t exist in the
>>>> “product" table, would you expect that that
>>>> fact to be counted against a “null” member of
>>>> the [Product] hierarchy? Each hierarchy has a
>>>> null member, but the system removes them from
>>>> axes. So you’d need a user-defined “other” member.
>>>>
>>>> Julian
>>>>
>>>>
>>>>> On May 23, 2016, at 7:21 PM, Jian Zhong
>>>>> <zhongjian at apache.org
>>>>> <mailto:zhongjian at apache.org>> wrote:
>>>>>
>>>>> Hi all,
>>>>>
>>>>> I'm a developer of apache kylin.
>>>>>
>>>>> I'm trying to write a tool generate mondrian
>>>>> schema for Apache Kylin cubes.
>>>>>
>>>>> As I know, mondrian does not support left
>>>>> join, and for composite key.
>>>>>
>>>>> If two tables join with more than one
>>>>> column,it seems not well supported.
>>>>>
>>>>> I'm blocked on how to define multiple columns
>>>>> join for tables.
>>>>>
>>>>> Could anyone give me some guide?
>>>>>
>>>>> Thanks
>>>>>
>>>>> Best Regards!
>>>>> _______________________________________________
>>>>> Mondrian mailing list
>>>>> Mondrian at pentaho.org <mailto:Mondrian at pentaho.org>
>>>>> http://lists.pentaho.org/mailman/listinfo/mondrian
>>>>
>>>>
>>>> _______________________________________________
>>>> Mondrian mailing list
>>>> Mondrian at pentaho.org <mailto:Mondrian at pentaho.org>
>>>> http://lists.pentaho.org/mailman/listinfo/mondrian
>>>>
>>>>
>>>>
>>>>
>>>> _______________________________________________
>>>> Mondrian mailing list
>>>> Mondrian at pentaho.org <mailto:Mondrian at pentaho.org>
>>>> http://lists.pentaho.org/mailman/listinfo/mondrian
>>>
>>>
>>> _______________________________________________
>>> Mondrian mailing list
>>> Mondrian at pentaho.org <mailto:Mondrian at pentaho.org>
>>> http://lists.pentaho.org/mailman/listinfo/mondrian
>>>
>>>
>>>
>>>
>>>
>>> _______________________________________________
>>> Mondrian mailing list
>>> Mondrian at pentaho.org <mailto:Mondrian at pentaho.org>
>>> http://lists.pentaho.org/mailman/listinfo/mondrian
>>
>> _______________________________________________
>> Mondrian mailing list
>> Mondrian at pentaho.org <mailto:Mondrian at pentaho.org>
>> http://lists.pentaho.org/mailman/listinfo/mondrian
>
>
> _______________________________________________
> Mondrian mailing list
> Mondrian at pentaho.org <mailto:Mondrian at pentaho.org>
> http://lists.pentaho.org/mailman/listinfo/mondrian
>
>
>
>
>
> _______________________________________________
> Mondrian mailing list
> Mondrian at pentaho.org
> http://lists.pentaho.org/mailman/listinfo/mondrian
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.pentaho.org/pipermail/mondrian/attachments/20160530/12c6a300/attachment-0001.html
More information about the Mondrian
mailing list