<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 &lt;Dimension&gt; with more than one &lt;Attribute&gt; 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:&#39;Helvetica Neue&#39;,Helvetica,Arial,sans-serif;font-size:14px;line-height:20px"><li style="margin-left:15px"><code style="font-family:Monaco,Menlo,Consolas,&#39;Courier New&#39;,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,&#39;Courier New&#39;,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>       &lt;Dimension name=&#39;<font color="#3d85c6">time_dimension</font>&#39; table=&#39;KYLIN_CATEGORY_GROUPINGS&#39; &gt;</div><div>               &lt;Attributes&gt;</div><div>                   &lt;Attribute name=&#39;<font color="#ff0000">year</font>&#39; hasHierarchy=&#39;true&#39;&gt;</div><div>                       &lt;key&gt;</div><div>                           &lt;Column name=&quot;LEAF_CATEG_ID&quot;/&gt;</div><div>                           &lt;Column name=&quot;SITE_ID&quot;/&gt;</div><div>                       &lt;/key&gt;    </div><div>                       &lt;Name&gt;</div><div>                           &lt;Column name=&#39;<font color="#ff0000">year</font>&#39;/&gt;</div><div>                       &lt;/Name&gt;</div><div>                   &lt;/Attribute&gt;</div><div><br></div><div>                    &lt;Attribute name=&#39;<font color="#ff0000">month</font>&#39; hasHierarchy=&#39;true&#39;&gt;</div><div>                         &lt;key&gt;</div><div>                             &lt;Column name=&quot;LEAF_CATEG_ID&quot;/&gt;</div><div>                             &lt;Column name=&quot;SITE_ID&quot;/&gt;</div><div>                         &lt;/key&gt;    </div><div>                         &lt;Name&gt;</div><div>                             &lt;Column name=&#39;<font color="#ff0000">month</font>&#39;/&gt;</div><div>                         &lt;/Name&gt;</div><div>                     &lt;/Attribute&gt;</div></div><div>        &lt;/Dimension&gt;</div><div><br></div><div>  and the measure define like</div><div><br></div><div><div>               &lt;ForeignKeyLink dimension=&#39;<span style="color:rgb(61,133,198)">time_dimension</span>&#39; attribute=&#39;<font color="#ff0000">year</font>&#39;&gt;</div><div>                     &lt;ForeignKey&gt;</div><div>                         &lt;Column name=&quot;LEAF_CATEG_ID&quot;/&gt;</div><div>                         &lt;Column name=&quot;LSTG_SITE_ID&quot;/&gt;</div><div>                     &lt;/ForeignKey&gt;</div><div>               &lt;/ForeignKeyLink&gt;</div><div>               </div><div>                &lt;ForeignKeyLink dimension=&#39;<span style="color:rgb(61,133,198)">time_dimension</span>&#39; attribute=&#39;<font color="#ff0000">month</font>&#39;&gt;</div><div>                     &lt;ForeignKey&gt;</div><div>                         &lt;Column name=&quot;LEAF_CATEG_ID&quot;/&gt;</div><div>                         &lt;Column name=&quot;LSTG_SITE_ID&quot;/&gt;</div><div>                     &lt;/ForeignKey&gt;</div><div>               &lt;/ForeignKeyLink&gt;</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 &lt;Dimension&gt; , will throw log like </div><div>







<p class=""><span class=""> &quot; <font color="#ff0000">More than one link </font>for dimension &#39;<span style="color:rgb(61,133,198)">time_dimension</span>&#39; in measure group &#39;measures&#39; (in ForeignKeyLink) &quot;&lt;</span></p><p class="">if I try to add one [key] attribute to element &lt;Dimension&gt;, will throw log like</p><p class=""> &quot;  Mondrian Error:Dimension &#39;<span style="color:rgb(61,133,198)">time_dimension</span>&#39; omits a defined key, which is only valid for degenerate dimensions with a single attribute. (in Dimension &#39;<span style="color:rgb(61,133,198)">time_dimension</span>&#39;) (at line 0, column 461) &quot;</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&#39;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">&lt;<a href="mailto:julianhyde@gmail.com" target="_blank">julianhyde@gmail.com</a>&gt;</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 &lt;<a href="mailto:isaias.sanchez.l@gmail.com" target="_blank">isaias.sanchez.l@gmail.com</a>&gt; wrote:</div><br><div>
  
    
  
  <div bgcolor="#FFFFFF" text="#000000">
    I think that&#39;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">&lt;<a href="mailto:hellowode110@gmail.com" target="_blank">hellowode110@gmail.com</a>&gt;</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">&lt;<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>&gt;</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>
                                &lt;Table
                        name=&quot;KYLIN_CATEGORY_GROUPINGS&quot;&gt;<br>
                                    &lt;key&gt;<br>
                                        &lt;column
                        name=&quot;LEAF_CATEG_ID&quot;/&gt;<br>
                                    &lt;/key&gt;<br>
                                &lt;/Table&gt;  <br>
                        <br>
                        or <br>
                        <br>
                                &lt;Table
                        name=&quot;KYLIN_CATEGORY_GROUPINGS&quot;&gt;<br>
                                &lt;/Table&gt;  <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&#39;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
                                        &#39;KYLIN_CATEGORY_GROUPINGS&#39;. (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">&lt;<a href="mailto:julianhyde@gmail.com" target="_blank"></a><a href="mailto:julianhyde@gmail.com" target="_blank">julianhyde@gmail.com</a>&gt;</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&quot; fact table has a
                                        “productId” value that doesn’t
                                        exist in the “product&quot; 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 &lt;<a href="mailto:zhongjian@apache.org" target="_blank"></a><a href="mailto:zhongjian@apache.org" target="_blank">zhongjian@apache.org</a>&gt;

                                                    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&#39;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&#39;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&#39;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>