ok, for your question: Firstly, yes derived columns can only be from lookup table; And all the derived columns are "derived" from its PK value.
Secondly, as Kylin only supports star schema, it assume the lookup tables are small so it can load the table into memory; during the query time, Kylin will do a mapping from the PK to other dolumn values in memory; Let's take the "kylin_sales_cube" cube as example, in which the "WEEK_BEG_DT" is defined as derived from "CAL_DT", which means for a CAL_DT value, it can calculate a WEEK_BEG_DT: e.g, 2016-01-08 => 2016-01-04. In the cube building, only CAL_DT is built into the cube, like: CAL_DT, DIM-A, DIM-B... 2016-01-01, ..., ... 2016-01-07, ..., ... 2016-01-08, ..., ... In the query time, if user have a SQL group by WEEK_BEG_DT, Kylin will convert it to query the CAL_DT dimension, after the result be returned, mapping to get the WEEK_BEG_DT: CAL_DT, DIM-A, DIM-B, WEEK_BEG_DT 2016-01-01, ..., ..., 2015-12-28 2016-01-07, ..., ..., 2016-01-04 2016-01-08, ..., ..., 2016-01-04 The SQL engine will do the final round aggregation and then return to user: DIM-A, DIM-B, WEEK_BEG_DT ..., ..., 2015-12-28 ..., ..., 2016-01-04 Hope it helps. 2016-01-08 9:44 GMT+08:00 zhong zhang <zzaco...@gmail.com>: > Hi Shaofeng, > > Thanks so much. Yeah, I found them at the JSON(Model). > > Can you help me verify the two explanations in the first email? > > Best regards, > Zhong > > On Thu, Jan 7, 2016 at 8:34 PM, ShaoFeng Shi <shaofeng...@apache.org> > wrote: > > > The FK/PK between fact and lookup table can be found in the data model > > descriptor. > > > > 2016-01-08 3:11 GMT+08:00 Zhang, Zhong <zzh...@cardlytics.com>: > > > > > Hi Hongbin, > > > > > > For the table "KYLIN_CAL_DT", the primary key is "CAL_DT" and > > > The foreign_key is "PART_DT". > > > > > > For the table "KYLIN_CATEGORY_GROUPINGS", the primary > > > Key is "LEAF_CATEG_ID" and "SITE_ID", the foreign key > > > Is "LEAF_CATEG_ID" and "LSTG_SITE_ID". > > > > > > Best regards, > > > Zhong > > > > > > -----Original Message----- > > > From: Zhang, Zhong [mailto:zzh...@cardlytics.com] > > > Sent: Thursday, January 07, 2016 1:53 PM > > > To: dev@kylin.apache.org > > > Subject: RE: derived dimension > > > > > > Hi Hongbin, > > > > > > Thanks so so so ... much for your kind help. > > > The following is my understanding based on your excellent explanation: > > > Since both the foreign key and "WEEK_BEG_DT" are in the cube and > > > "WEEK_BEG_DT" can be derived from the foreign key, we mark the column > > > "WEEK_BEG_DT" as a derived dimension in the UI. The same case happens > for > > > "USER_DEFINED_FIELD1","USER_DEFINED_FIELD3","UPD_DATE" and "UPD_USER" > > > columns. Can I ask which column is the foreign key for the table > > > "KYLIN_CAL_DT"? > > > > > > The following is another understanding based on reference link [1]. In > > [1] > > > (page 10), "Dimensions on lookup table that can be derived by PK." It > > seems > > > that the primary Key is the column that other columns are derived from. > > > Back to the sample cube example, since both the primary key and > > > "WEEK_BEG_DT" are in the cube and "WEEK_BEG_DT" can be derived from the > > > primary key, we mark the column "WEEK_BEG_DT" as a derived dimension in > > the > > > UI. I assume the primary key in the table "KYLIN_CAL_DT" is "CAL_DT"? > > > > > > Please help me verify the above two explanations, thanks a million. > > > > > > [1] > > > > > > http://www.slideshare.net/YangLi43/design-cube-in-apache-kylin?next_slideshow=5 > > > > > > Best regards, > > > Zhong > > > > > > -----Original Message----- > > > From: hongbin ma [mailto:mahong...@apache.org] > > > Sent: Thursday, January 07, 2016 7:30 AM > > > To: dev@kylin.apache.org > > > Subject: Re: derived dimension > > > > > > if the dimension's not explicitly specifying, FK is the column that's > > > derived from. > > > > > > On Thu, Jan 7, 2016 at 11:15 AM, Zhang, Zhong <zzh...@cardlytics.com> > > > wrote: > > > > > > > Hi All, > > > > > > > > I'm confused by the derived dimension. The following two sentences > are > > > > the source that I found online to guide me use derived dimension. > It's > > > > kind of unclear to me. > > > > > > > > Dimensions on lookup table that can be derived by PK. > > > > -like User ID derives [Name, Age, Gender] from [1] at page 10 > > > > > > > > Given a value in DimA, the value of DimB is determined, so we say > dimB > > > > can be derived from DimA. When we build a cube that contains both > DimA > > > > and DimB, we simple include DimA, and marking DimB as Derived. > > > > from [2] > > > > > > > > Let us use the sample cube "kylin_sales_cube" as the example to > > > > discuss it. There are two derived dimensions: CAL_DT and CATEGORY. > > > > In CAL_DT, which column derives WEEK_BEG_DT? > > > > In CATEGORY, which column derives > > > > "USER_DEFINED_FIELD1","USER_DEFINED_FIELD3","UPD_DATE","UPD_USER"? > > > > > > > > Is derived dimension used only in lookup table? > > > > > > > > [1] > > > > > http://www.slideshare.net/YangLi43/design-cube-in-apache-kylin?next_sl > > > > ideshow=5 > > > > [2] > > > > > https://mail-archives.apache.org/mod_mbox/incubator-kylin-dev/201507.m > > > > box/%3c82073af5-ae07-4441-bfdf-e8b9d36ff...@163.com%3E > > > > > > > > Best regards, > > > > Zhong > > > > > > > > > > > > > > > > -- > > > Regards, > > > > > > *Bin Mahone | 马洪宾* > > > Apache Kylin: http://kylin.io > > > Github: https://github.com/binmahone > > > > > > > > > > > -- > > Best regards, > > > > Shaofeng Shi > > > -- Best regards, Shaofeng Shi
