You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@kylin.apache.org by 杨海乐 <ya...@letv.com> on 2016/11/18 07:56:18 UTC
mondrian and kylin's multiple fields association
Hello all,
when we use saiku to connect kylin, We found that mondrian not seem to
support multiple fields association which kylin use. do someone know how to
solve this problem ? thanks very mush.
--
View this message in context: http://apache-kylin.74782.x6.nabble.com/mondrian-and-kylin-s-multiple-fields-association-tp6343.html
Sent from the Apache Kylin mailing list archive at Nabble.com.
Re: mondrian and kylin's multiple fields association
Posted by Julian Hyde <jh...@apache.org>.
Mondrian 4 supports composite keys. Mondrian 3 does not.
The below schema is a Mondrian 4 schema (note the “PhysicalSchema” element). I don’t know whether the original questioner is running 3 or 4.
Julian
> On Nov 18, 2016, at 12:04 AM, Jian Zhong <zh...@apache.org> wrote:
>
> Actually, you can do it.
>
> This is mondrian schema for kylin_sales_cube
>
> I mark composite key as red.
>
> FYI.
>
>
> <?xml version="1.0" encoding="UTF-8"?>
> <Schema name="kylin_sales_cube_desc" metamodelVersion="4.0">
> <PhysicalSchema>
> <Table name="KYLIN_SALES" schema="DEFAULT" ></Table>
> <Table name="KYLIN_CAL_DT" schema="DEFAULT" ></Table>
> <Table name="KYLIN_CATEGORY_GROUPINGS" schema="DEFAULT" ></Table>
> </PhysicalSchema>
> <Cube name="kylin_sales_cube_desc" defaultMeasure="TRANS_CNT">
> <Dimensions>
> <Dimension name="DEFAULT.KYLIN_SALES" table="KYLIN_SALES"
> key="LSTG_FORMAT_NAME">
> <Attributes>
> <Attribute name="LSTG_FORMAT_NAME"
> keyColumn="LSTG_FORMAT_NAME" />
> <Attribute name="LEAF_CATEG_ID"
> keyColumn="LEAF_CATEG_ID" />
> <Attribute name="LSTG_SITE_ID" keyColumn="LSTG_SITE_ID"
> />
> <Attribute name="PART_DT" keyColumn="PART_DT" />
> <Attribute name="TOP_SELLER_SELLER_ID"
> keyColumn="SELLER_ID" />
> </Attributes>
> </Dimension>
> <Dimension name="DEFAULT.KYLIN_CATEGORY_GROUPINGS"
> table="KYLIN_CATEGORY_GROUPINGS" key="LEAF_CATEG_ID1">
> <Attributes>
> <Attribute name="LEAF_CATEG_ID1" >
> <key>
> <Column name="LEAF_CATEG_ID" />
> <Column name="SITE_ID" />
> </key>
> <Name>
> <Column name="LEAF_CATEG_ID" />
> </Name>
> </Attribute>
> <Attribute name="SITE_ID" >
> <key>
> <Column name="SITE_ID" />
> </key>
> </Attribute>
> <Attribute name="CATEGORY_USER_DEFINED_FIELD1" >
> <key>
> <Column name="USER_DEFINED_FIELD1" />
> </key>
> </Attribute>
> <Attribute name="CATEGORY_USER_DEFINED_FIELD3" >
> <key>
> <Column name="USER_DEFINED_FIELD3" />
> </key>
> </Attribute>
> <Attribute name="CATEGORY_UPD_DATE" >
> <key>
> <Column name="UPD_DATE" />
> </key>
> </Attribute>
> <Attribute name="CATEGORY_UPD_USER" >
> <key>
> <Column name="UPD_USER" />
> </key>
> </Attribute>
> <Attribute name="CATEGORY_HIERARCHY"
> hasHierarchy='false' >
> <key>
> <Column name="META_CATEG_NAME" />
> </key>
> </Attribute>
> <Attribute name="CATEGORY_HIERARCHY1"
> hasHierarchy='false' >
> <key>
> <Column name="META_CATEG_NAME" />
> <Column name="CATEG_LVL2_NAME" />
> </key>
> <Name>
> <Column name="CATEG_LVL2_NAME"/>
> </Name>
> </Attribute>
> <Attribute name="CATEGORY_HIERARCHY2"
> hasHierarchy='false' >
> <key>
> <Column name="META_CATEG_NAME" />
> <Column name="CATEG_LVL2_NAME" />
> <Column name="CATEG_LVL3_NAME" />
> </key>
> <Name>
> <Column name="CATEG_LVL3_NAME"/>
> </Name>
> </Attribute>
> </Attributes>
> <Hierarchies>
> <Hierarchy name='CATEGORY_HIERARCHY_Hierarchy'
> hasAll='true'>
> <Level attribute="CATEGORY_HIERARCHY"/>
> <Level attribute="CATEGORY_HIERARCHY1"/>
> <Level attribute="CATEGORY_HIERARCHY2"/>
> </Hierarchy>
> </Hierarchies>
> </Dimension>
> <Dimension name="DEFAULT.KYLIN_CAL_DT" table="KYLIN_CAL_DT"
> key="CAL_DT">
> <Attributes>
> <Attribute name="CAL_DT" keyColumn="CAL_DT" />
> <Attribute name="CAL_DT1" keyColumn="WEEK_BEG_DT" />
> </Attributes>
> </Dimension>
> </Dimensions>
> <MeasureGroups>
> <MeasureGroup name="measures" table="KYLIN_SALES" >
> <Measures>
> <Measure name="GMV_SUM" column="PRICE"
> aggregator="sum" formatString="#,###.00" />
> <Measure name="GMV_MIN" column="PRICE"
> aggregator="min" formatString="#,###.00" />
> <Measure name="GMV_MAX" column="PRICE"
> aggregator="max" formatString="#,###.00" />
> <Measure name="TRANS_CNT" aggregator="count"
> formatString="#,####" />
> <Measure name="SELLER_CNT_HLL" column="SELLER_ID"
> aggregator="distinct-count" dataType="Integer" />
> <Measure name="SELLER_FORMAT_CNT"
> column="LSTG_FORMAT_NAME" aggregator="distinct-count" dataType="Integer"
> />
> <Measure name="TOP_SELLER" column="PRICE"
> aggregator="sum" formatString="#,###.00" />
> </Measures>
> <DimensionLinks>
> <FactLink dimension="DEFAULT.KYLIN_SALES" />
> <ForeignKeyLink
> dimension="DEFAULT.KYLIN_CATEGORY_GROUPINGS" attribute="LEAF_CATEG_ID1">
> <ForeignKey>
> <Column name="LEAF_CATEG_ID" />
> <Column name="LSTG_SITE_ID" />
> </ForeignKey>
> </ForeignKeyLink>
> <ForeignKeyLink foreignKeyColumn="PART_DT"
> dimension="DEFAULT.KYLIN_CAL_DT" />
> </DimensionLinks>
> </MeasureGroup>
> </MeasureGroups>
> </Cube>
> </Schema>
>
>
>
>
>
>
>
>
> On Fri, Nov 18, 2016 at 3:56 PM, 杨海乐 <ya...@letv.com> wrote:
>
>> Hello all,
>> when we use saiku to connect kylin, We found that mondrian not seem to
>> support multiple fields association which kylin use. do someone know how
>> to
>> solve this problem ? thanks very mush.
>>
>> --
>> View this message in context: http://apache-kylin.74782.x6.
>> nabble.com/mondrian-and-kylin-s-multiple-fields-association-tp6343.html
>> Sent from the Apache Kylin mailing list archive at Nabble.com.
>>
Re: mondrian and kylin's multiple fields association
Posted by Jian Zhong <zh...@apache.org>.
Actually, you can do it.
This is mondrian schema for kylin_sales_cube
I mark composite key as red.
FYI.
<?xml version="1.0" encoding="UTF-8"?>
<Schema name="kylin_sales_cube_desc" metamodelVersion="4.0">
<PhysicalSchema>
<Table name="KYLIN_SALES" schema="DEFAULT" ></Table>
<Table name="KYLIN_CAL_DT" schema="DEFAULT" ></Table>
<Table name="KYLIN_CATEGORY_GROUPINGS" schema="DEFAULT" ></Table>
</PhysicalSchema>
<Cube name="kylin_sales_cube_desc" defaultMeasure="TRANS_CNT">
<Dimensions>
<Dimension name="DEFAULT.KYLIN_SALES" table="KYLIN_SALES"
key="LSTG_FORMAT_NAME">
<Attributes>
<Attribute name="LSTG_FORMAT_NAME"
keyColumn="LSTG_FORMAT_NAME" />
<Attribute name="LEAF_CATEG_ID"
keyColumn="LEAF_CATEG_ID" />
<Attribute name="LSTG_SITE_ID" keyColumn="LSTG_SITE_ID"
/>
<Attribute name="PART_DT" keyColumn="PART_DT" />
<Attribute name="TOP_SELLER_SELLER_ID"
keyColumn="SELLER_ID" />
</Attributes>
</Dimension>
<Dimension name="DEFAULT.KYLIN_CATEGORY_GROUPINGS"
table="KYLIN_CATEGORY_GROUPINGS" key="LEAF_CATEG_ID1">
<Attributes>
<Attribute name="LEAF_CATEG_ID1" >
<key>
<Column name="LEAF_CATEG_ID" />
<Column name="SITE_ID" />
</key>
<Name>
<Column name="LEAF_CATEG_ID" />
</Name>
</Attribute>
<Attribute name="SITE_ID" >
<key>
<Column name="SITE_ID" />
</key>
</Attribute>
<Attribute name="CATEGORY_USER_DEFINED_FIELD1" >
<key>
<Column name="USER_DEFINED_FIELD1" />
</key>
</Attribute>
<Attribute name="CATEGORY_USER_DEFINED_FIELD3" >
<key>
<Column name="USER_DEFINED_FIELD3" />
</key>
</Attribute>
<Attribute name="CATEGORY_UPD_DATE" >
<key>
<Column name="UPD_DATE" />
</key>
</Attribute>
<Attribute name="CATEGORY_UPD_USER" >
<key>
<Column name="UPD_USER" />
</key>
</Attribute>
<Attribute name="CATEGORY_HIERARCHY"
hasHierarchy='false' >
<key>
<Column name="META_CATEG_NAME" />
</key>
</Attribute>
<Attribute name="CATEGORY_HIERARCHY1"
hasHierarchy='false' >
<key>
<Column name="META_CATEG_NAME" />
<Column name="CATEG_LVL2_NAME" />
</key>
<Name>
<Column name="CATEG_LVL2_NAME"/>
</Name>
</Attribute>
<Attribute name="CATEGORY_HIERARCHY2"
hasHierarchy='false' >
<key>
<Column name="META_CATEG_NAME" />
<Column name="CATEG_LVL2_NAME" />
<Column name="CATEG_LVL3_NAME" />
</key>
<Name>
<Column name="CATEG_LVL3_NAME"/>
</Name>
</Attribute>
</Attributes>
<Hierarchies>
<Hierarchy name='CATEGORY_HIERARCHY_Hierarchy'
hasAll='true'>
<Level attribute="CATEGORY_HIERARCHY"/>
<Level attribute="CATEGORY_HIERARCHY1"/>
<Level attribute="CATEGORY_HIERARCHY2"/>
</Hierarchy>
</Hierarchies>
</Dimension>
<Dimension name="DEFAULT.KYLIN_CAL_DT" table="KYLIN_CAL_DT"
key="CAL_DT">
<Attributes>
<Attribute name="CAL_DT" keyColumn="CAL_DT" />
<Attribute name="CAL_DT1" keyColumn="WEEK_BEG_DT" />
</Attributes>
</Dimension>
</Dimensions>
<MeasureGroups>
<MeasureGroup name="measures" table="KYLIN_SALES" >
<Measures>
<Measure name="GMV_SUM" column="PRICE"
aggregator="sum" formatString="#,###.00" />
<Measure name="GMV_MIN" column="PRICE"
aggregator="min" formatString="#,###.00" />
<Measure name="GMV_MAX" column="PRICE"
aggregator="max" formatString="#,###.00" />
<Measure name="TRANS_CNT" aggregator="count"
formatString="#,####" />
<Measure name="SELLER_CNT_HLL" column="SELLER_ID"
aggregator="distinct-count" dataType="Integer" />
<Measure name="SELLER_FORMAT_CNT"
column="LSTG_FORMAT_NAME" aggregator="distinct-count" dataType="Integer"
/>
<Measure name="TOP_SELLER" column="PRICE"
aggregator="sum" formatString="#,###.00" />
</Measures>
<DimensionLinks>
<FactLink dimension="DEFAULT.KYLIN_SALES" />
<ForeignKeyLink
dimension="DEFAULT.KYLIN_CATEGORY_GROUPINGS" attribute="LEAF_CATEG_ID1">
<ForeignKey>
<Column name="LEAF_CATEG_ID" />
<Column name="LSTG_SITE_ID" />
</ForeignKey>
</ForeignKeyLink>
<ForeignKeyLink foreignKeyColumn="PART_DT"
dimension="DEFAULT.KYLIN_CAL_DT" />
</DimensionLinks>
</MeasureGroup>
</MeasureGroups>
</Cube>
</Schema>
On Fri, Nov 18, 2016 at 3:56 PM, 杨海乐 <ya...@letv.com> wrote:
> Hello all,
> when we use saiku to connect kylin, We found that mondrian not seem to
> support multiple fields association which kylin use. do someone know how
> to
> solve this problem ? thanks very mush.
>
> --
> View this message in context: http://apache-kylin.74782.x6.
> nabble.com/mondrian-and-kylin-s-multiple-fields-association-tp6343.html
> Sent from the Apache Kylin mailing list archive at Nabble.com.
>