Hi Shaofeng, Thanks so so so ... much for your detailed and brilliant explanation. I've learnt a lot of cutting-edge technologies today. :-).
One more quick question, can I say that the primary keys of the fact table and lookup tables must be included in the cube. What about the foreign keys? Have a wonderful day! Best regards, Zhong On Thu, Jan 7, 2016 at 9:19 PM, ShaoFeng Shi <[email protected]> wrote: > 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 <[email protected]>: > > > 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 <[email protected]> > > 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 <[email protected]>: > > > > > > > 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:[email protected]] > > > > Sent: Thursday, January 07, 2016 1:53 PM > > > > To: [email protected] > > > > 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:[email protected]] > > > > Sent: Thursday, January 07, 2016 7:30 AM > > > > To: [email protected] > > > > 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 <[email protected] > > > > > > 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/%[email protected]%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 >
