<div dir="ltr">Thank you, Julian<div><br></div><div>another question about composite key and hierarchy.</div><div><br></div><div>Is it possible to define a <Dimension> with more than one <Attribute> with hierarchy and join relation between the fact table is composite key.</div><div><br></div><div>Say in a star schema, I have lookup table KYLIN_CATEGORY_GROUPINGS, fact table KYLIN_SALES</div><div><br></div><div><p style="color:rgb(0,0,0);font-size:12.8px">the join relation like this.</p><p style="color:rgb(0,0,0);font-size:12.8px"></p><ul style="margin:0px;padding:0px;list-style:none;color:rgb(51,51,51);font-family:'Helvetica Neue',Helvetica,Arial,sans-serif;font-size:14px;line-height:20px"><li style="margin-left:15px"><code style="font-family:Monaco,Menlo,Consolas,'Courier New',monospace;font-size:12.6px;padding:2px 4px;color:rgb(199,37,78);white-space:nowrap;border-radius:4px;background-color:rgb(249,242,244)">KYLIN_SALES.LEAF_CATEG_ID = KYLIN_CATEGORY_GROUPINGS.LEAF_CATEG_ID</code> </li><li style="margin-left:15px"><code style="font-family:Monaco,Menlo,Consolas,'Courier New',monospace;font-size:12.6px;padding:2px 4px;color:rgb(199,37,78);white-space:nowrap;border-radius:4px;background-color:rgb(249,242,244)">KYLIN_SALES.LSTG_SITE_ID = KYLIN_CATEGORY_GROUPINGS.SITE_ID</code></li></ul><div><font color="#c7254e" face="Monaco, Menlo, Consolas, Courier New, monospace"><span style="font-size:12.6px;line-height:20px;white-space:nowrap"><br></span></font></div></div><div><br></div><div>I have hierarchy column <font color="#ff0000">year</font>,<font color="#ff0000">month</font> on KYLIN_CATEGORY_GROUPINGS, the dimension define like</div><div><br></div><div><div> <Dimension name='<font color="#3d85c6">time_dimension</font>' table='KYLIN_CATEGORY_GROUPINGS' ></div><div> <Attributes></div><div> <Attribute name='<font color="#ff0000">year</font>' hasHierarchy='true'></div><div> <key></div><div> <Column name="LEAF_CATEG_ID"/></div><div> <Column name="SITE_ID"/></div><div> </key> </div><div> <Name></div><div> <Column name='<font color="#ff0000">year</font>'/></div><div> </Name></div><div> </Attribute></div><div><br></div><div> <Attribute name='<font color="#ff0000">month</font>' hasHierarchy='true'></div><div> <key></div><div> <Column name="LEAF_CATEG_ID"/></div><div> <Column name="SITE_ID"/></div><div> </key> </div><div> <Name></div><div> <Column name='<font color="#ff0000">month</font>'/></div><div> </Name></div><div> </Attribute></div></div><div> </Dimension></div><div><br></div><div> and the measure define like</div><div><br></div><div><div> <ForeignKeyLink dimension='<span style="color:rgb(61,133,198)">time_dimension</span>' attribute='<font color="#ff0000">year</font>'></div><div> <ForeignKey></div><div> <Column name="LEAF_CATEG_ID"/></div><div> <Column name="LSTG_SITE_ID"/></div><div> </ForeignKey></div><div> </ForeignKeyLink></div><div> </div><div> <ForeignKeyLink dimension='<span style="color:rgb(61,133,198)">time_dimension</span>' attribute='<font color="#ff0000">month</font>'></div><div> <ForeignKey></div><div> <Column name="LEAF_CATEG_ID"/></div><div> <Column name="LSTG_SITE_ID"/></div><div> </ForeignKey></div><div> </ForeignKeyLink></div></div><div><br></div><div><br></div><div>then the problem comes,</div><div><br></div><div>if I do not defined [key] attribute on element <Dimension> , will throw log like </div><div>
<p class=""><span class=""> " <font color="#ff0000">More than one link </font>for dimension '<span style="color:rgb(61,133,198)">time_dimension</span>' in measure group 'measures' (in ForeignKeyLink) "<</span></p><p class="">if I try to add one [key] attribute to element <Dimension>, will throw log like</p><p class=""> " Mondrian Error:Dimension '<span style="color:rgb(61,133,198)">time_dimension</span>' omits a defined key, which is only valid for degenerate dimensions with a single attribute. (in Dimension '<span style="color:rgb(61,133,198)">time_dimension</span>') (at line 0, column 461) "</p><p class="">So, how can I define hierarchy column on table with composite key join relation between fact table?</p><p class="">Please correct me if you are available, I'm new to mondrian.</p><p class="">Thanks !</p><p class="">Best Regards !</p>
</div></div><div class="gmail_extra"><br><div class="gmail_quote">On Thu, May 26, 2016 at 12:34 AM, Julian Hyde <span dir="ltr"><<a href="mailto:julianhyde@gmail.com" target="_blank">julianhyde@gmail.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div style="word-wrap:break-word">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. <div><br></div><div>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.</div><span class="HOEnZb"><font color="#888888"><div><br></div><div>Julian</div></font></span><div><div class="h5"><div><br><div><div><blockquote type="cite"><div>On May 25, 2016, at 6:46 AM, Isaias Sanchez <<a href="mailto:isaias.sanchez.l@gmail.com" target="_blank">isaias.sanchez.l@gmail.com</a>> wrote:</div><br><div>
<div bgcolor="#FFFFFF" text="#000000">
I think that's not possible by now.<br>
<br>
<div>Jian Zhong wrote on 25/05/16 12:30:<br>
</div>
<blockquote type="cite">
<div dir="ltr">one more question,
<div><br>
</div>
<div>since the join relation is between tables.</div>
<div><br>
</div>
<div>in mondrian schema, i need to define composite keys in
every dimension from the table.<br>
</div>
<div><br>
</div>
<div>which cause many duplicate code.<br>
</div>
<div><br>
</div>
<div>Can I define composite key at table level?</div>
<div><br>
</div>
<div>thanks!</div>
<div><br>
</div>
<div><br>
</div>
</div>
<div class="gmail_extra"><br>
<div class="gmail_quote">On Wed, May 25, 2016 at 3:21 PM, Jian
Zhong <span dir="ltr"><<a href="mailto:hellowode110@gmail.com" target="_blank">hellowode110@gmail.com</a>></span>
wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<div dir="ltr">Thank you Isaias !
<div><br>
</div>
<div>you saved my day.</div>
</div>
<div>
<div>
<div class="gmail_extra"><br>
<div class="gmail_quote">On Tue, May 24, 2016 at 4:28
PM, Isaias Sanchez <span dir="ltr"><<a href="mailto:isaias.sanchez.l@gmail.com" target="_blank"></a><a href="mailto:isaias.sanchez.l@gmail.com" target="_blank">isaias.sanchez.l@gmail.com</a>></span>
wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<div bgcolor="#FFFFFF" text="#000000"> I guess:<br>
<br>
<Table
name="KYLIN_CATEGORY_GROUPINGS"><br>
<key><br>
<column
name="LEAF_CATEG_ID"/><br>
</key><br>
</Table> <br>
<br>
or <br>
<br>
<Table
name="KYLIN_CATEGORY_GROUPINGS"><br>
</Table> <br>
<br>
Will do the job.<br>
<br>
The composite key leave it only in dimension
part.<br>
<br>
Cheers,<br>
<br>
Isaias S.<br>
<br>
<div>Jian Zhong wrote on 24/05/16 10:12:<br>
</div>
<div>
<div>
<blockquote type="cite">
<div dir="ltr">Julian,
<div><br>
</div>
<div>Thank you!
<div><br>
</div>
<div>I'm using Mondrian 4.4, I follow
the guide. and I get error log like</div>
<div><br>
</div>
<div><p><span>016-05-24 15:07:47,851
ERROR
[org.saiku.web.core.SecurityAwareConnectionManager]
Error connecting:
kylin_sales_cal_compos_test</span></p><p><span>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)</span></p><p><span>We have table KYLIN_SALES
and KYLIN_CATEGORY_GROUPINGS.</span></p><p>the join relation like this.</p><div><span></span><br></div>
<ul>
<li><code>DEFAULT.KYLIN_SALES.LEAF_CATEG_ID
=
DEFAULT.KYLIN_CATEGORY_GROUPINGS.LEAF_CATEG_ID</code> </li>
<li><code>DEFAULT.KYLIN_SALES.LSTG_SITE_ID
=
DEFAULT.KYLIN_CATEGORY_GROUPINGS.SITE_ID</code></li>
</ul><p><span>Enclosure is the schema
file.</span></p><p>Coulld you coorect me?</p><p>Thanks.</p>
</div>
</div>
</div>
<div class="gmail_extra"><br>
<div class="gmail_quote">On Tue, May 24,
2016 at 11:57 AM, Julian Hyde <span dir="ltr"><<a href="mailto:julianhyde@gmail.com" target="_blank"></a><a href="mailto:julianhyde@gmail.com" target="_blank">julianhyde@gmail.com</a>></span>
wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<div style="word-wrap:break-word">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.
<div><br>
</div>
<div>See <a href="http://mondrian.pentaho.com/head/documentation/schema.php#Measures" target="_blank">http://mondrian.pentaho.com/head/documentation/schema.php#Measures</a>,
towards the end of the section.</div>
<div><br>
</div>
<div>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.</div>
<div><br>
</div>
<div>Julian</div>
<div><br>
<div><br>
<div>
<blockquote type="cite">
<div>
<div>
<div>On May 23, 2016,
at 7:21 PM, Jian
Zhong <<a href="mailto:zhongjian@apache.org" target="_blank"></a><a href="mailto:zhongjian@apache.org" target="_blank">zhongjian@apache.org</a>>
wrote:</div>
<br>
</div>
</div>
<div>
<div>
<div>
<div dir="ltr">
<div style="font-size:12.8px">Hi
all,</div>
<div style="font-size:12.8px"><span style="font-size:12.8px"><br>
</span></div>
<div style="font-size:12.8px"><span style="font-size:12.8px">I'm a developer of apache kylin.</span></div>
<div style="font-size:12.8px"><span style="font-size:12.8px"><br>
</span></div>
<div style="font-size:12.8px"><span style="font-size:12.8px">I'm trying to write a tool generate mondrian
schema for
Apache Kylin
cubes.</span><br>
</div>
<div style="font-size:12.8px"><br>
</div>
<div style="font-size:12.8px">As
I know, mondrian
does not support
left join, and
for composite
key.</div>
<div style="font-size:12.8px"><br>
</div>
<div style="font-size:12.8px">If
two tables join
with more than
one column,it
seems not well
supported.</div>
<div style="font-size:12.8px"><br>
</div>
<div style="font-size:12.8px">I'm
blocked on how
to define
multiple columns
join for tables.</div>
<div style="font-size:12.8px"><br>
</div>
<div style="font-size:12.8px">Could
anyone give me
some guide?<br>
</div>
<div style="font-size:12.8px"><br>
</div>
<div style="font-size:12.8px">Thanks</div>
<div style="font-size:12.8px"><br>
</div>
<div style="font-size:12.8px">Best
Regards!</div>
</div>
</div>
</div>
_______________________________________________<br>
Mondrian mailing list<br>
<a href="mailto:Mondrian@pentaho.org" target="_blank"></a><a href="mailto:Mondrian@pentaho.org" target="_blank">Mondrian@pentaho.org</a><br>
<a href="http://lists.pentaho.org/mailman/listinfo/mondrian" target="_blank"></a><a href="http://lists.pentaho.org/mailman/listinfo/mondrian" target="_blank">http://lists.pentaho.org/mailman/listinfo/mondrian</a><br>
</div>
</blockquote>
</div>
<br>
</div>
</div>
</div>
<br>
_______________________________________________<br>
Mondrian mailing list<br>
<a href="mailto:Mondrian@pentaho.org" target="_blank">Mondrian@pentaho.org</a><br>
<a href="http://lists.pentaho.org/mailman/listinfo/mondrian" rel="noreferrer" target="_blank">http://lists.pentaho.org/mailman/listinfo/mondrian</a><br>
<br>
</blockquote>
</div>
<br>
</div>
<br>
<fieldset></fieldset>
<br>
<pre>_______________________________________________
Mondrian mailing list
<a href="mailto:Mondrian@pentaho.org" target="_blank">Mondrian@pentaho.org</a>
<a href="http://lists.pentaho.org/mailman/listinfo/mondrian" target="_blank">http://lists.pentaho.org/mailman/listinfo/mondrian</a>
</pre>
</blockquote>
<br>
</div>
</div>
</div>
<br>
_______________________________________________<br>
Mondrian mailing list<br>
<a href="mailto:Mondrian@pentaho.org" target="_blank">Mondrian@pentaho.org</a><br>
<a href="http://lists.pentaho.org/mailman/listinfo/mondrian" rel="noreferrer" target="_blank">http://lists.pentaho.org/mailman/listinfo/mondrian</a><br>
<br>
</blockquote>
</div>
<br>
</div>
</div>
</div>
</blockquote>
</div>
<br>
</div>
<br>
<fieldset></fieldset>
<br>
<pre>_______________________________________________
Mondrian mailing list
<a href="mailto:Mondrian@pentaho.org" target="_blank">Mondrian@pentaho.org</a>
<a href="http://lists.pentaho.org/mailman/listinfo/mondrian" target="_blank">http://lists.pentaho.org/mailman/listinfo/mondrian</a>
</pre>
</blockquote>
<br>
</div>
_______________________________________________<br>Mondrian mailing list<br><a href="mailto:Mondrian@pentaho.org" target="_blank">Mondrian@pentaho.org</a><br><a href="http://lists.pentaho.org/mailman/listinfo/mondrian" target="_blank">http://lists.pentaho.org/mailman/listinfo/mondrian</a><br></div></blockquote></div><br></div></div></div></div></div><br>_______________________________________________<br>
Mondrian mailing list<br>
<a href="mailto:Mondrian@pentaho.org">Mondrian@pentaho.org</a><br>
<a href="http://lists.pentaho.org/mailman/listinfo/mondrian" rel="noreferrer" target="_blank">http://lists.pentaho.org/mailman/listinfo/mondrian</a><br>
<br></blockquote></div><br></div>