[Mondrian] Apache Kylin and mondrian

Jian Zhong zhongjian at apache.org
Sun May 29 23:21:05 EDT 2016


anyone any comments on this would be appreciated.

thanks!



On Fri, May 27, 2016 at 6:35 PM, Jian Zhong <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>
> 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>
>> 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>
>> 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>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>
>>>> 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>
>>>>> 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>Mondrian at pentaho.org
>>>>> <http://lists.pentaho.org/mailman/listinfo/mondrian>
>>>>> http://lists.pentaho.org/mailman/listinfo/mondrian
>>>>>
>>>>>
>>>>>
>>>>> _______________________________________________
>>>>> Mondrian mailing list
>>>>> Mondrian at pentaho.org
>>>>> http://lists.pentaho.org/mailman/listinfo/mondrian
>>>>>
>>>>>
>>>>
>>>>
>>>> _______________________________________________
>>>> Mondrian mailing listMondrian at pentaho.orghttp://lists.pentaho.org/mailman/listinfo/mondrian
>>>>
>>>>
>>>>
>>>> _______________________________________________
>>>> Mondrian mailing list
>>>> Mondrian at pentaho.org
>>>> http://lists.pentaho.org/mailman/listinfo/mondrian
>>>>
>>>>
>>>
>>
>>
>> _______________________________________________
>> Mondrian mailing listMondrian at pentaho.orghttp://lists.pentaho.org/mailman/listinfo/mondrian
>>
>>
>> _______________________________________________
>> Mondrian mailing list
>> 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/a4ec4dac/attachment-0001.html 


More information about the Mondrian mailing list