[Mondrian] Apache Kylin and mondrian

Jian Zhong zhongjian at apache.org
Fri May 27 06:35:59 EDT 2016


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/20160527/a3b4c9b2/attachment-0001.html 


More information about the Mondrian mailing list