>From the combination list, it seems you need a 7 dimension cube. Col1-4 are always selected by query, while Col5-7 are optional.
Then you can mark "mandatory" for Col1-4, and put Col5-7 in an aggregation group. Kylin will produce the exact combinations you have listed. On Thu, May 14, 2015 at 4:03 PM, ShaoFeng Shi <[email protected]> wrote: > Hi Parkavi, first of all, you need have a clear concept of dimension > columns and measure columns; There are many publications on this so I will > not elaborate; To be brief, the dimension columns are the columns that will > group by; The measure columns are the columns that will be aggregated > (COUNT, SUM, MAX, MIN, etc); > > From your description, Col1, Col2, Col3 and Col4 should be dimension > columns; and Col5, Col6 and Col7 are meansure columns; In the cube you can > pick Col1 to Col4 as dimensions, and define 3 measures: SUM(Col5), > SUM(Col6) and SUM(Col7); > > After cube be built, you can run query: > select col1, col2, col3, col4, SUM(col5), SUM(col6), SUM(col7) from > facttable group by col1, col2, col3, col4; > > To get the SUM of col5, col6, col7, you can use sub query, like: > > select col1, col2, col3, col4, SUM(SUM5, SUM7) as SUM57, SUM(SUM5, SUM6, > SUM7) as SUM567 from ( > select col1, col2, col3, col4, SUM(col5) as SUM5, SUM(col6) as SUM6, > SUM(col7) as SUM7 from facttable group by col1, col2, col3, col4 > ) as temp; > > In your case I didn't see a hierachy structure, so just use "Normal" for > the dimensions; > > Kylin doesn't forward query to Hive so far; In the future it may integrate > with SparkSQL; > > 2015-05-13 18:27 GMT+08:00 Parkavi Nandagopal <[email protected]>: > > > Hi, > > > > Please explain how to create cube for the below scenario: > > > > Fact table: > > > > Col1 Col2 Col3 Col4 Col5 Col6 Col7 Value > > L1 10 2 3 Tt1 Tp1 > > Pm1 23.2 > > L1 10 2 3 Tt1 Tp1 > > Pm2 56.2 > > L1 10 2 3 Tt1 Tp2 > > Pm1 12.7 > > L1 10 2 4 Tt2 Tp1 > > Pm2 22.3 > > L2 10 3 1 Tt1 Tp3 > > Pm3 11.6 > > L2 11 3 1 Tt2 Tp1 > > Pm4 12.3 > > > > > > I want like below combination: > > > > Col1, Col2, Col3, Col4, Col5 + Col6 + Col7 = value > > Col1, Col2, Col3, Col4, Col5 + Col6 = value > > Col1, Col2, Col3, Col4, Col6 + Col7 = value > > Col1, Col2, Col3, Col4, Col5 + Col7 = value > > Col1, Col2, Col3, Col4, Col5 = value > > Col1, Col2, Col3, Col4, Col6 = value > > Col1, Col2, Col3, Col4, Col7 = value > > > > > > I tried with hierarchy dimension with Col1, Col2, Col3, Col4, Col5, Col6 > , > > Col7. > > It is creating with 7 dimensional cube, but I need only 3 dimension with > > Col5, Col6 , Col7. > > > > If I didn't select Col1, Col2, Col3, Col4 when I creating dimension, then > > I can't able to query Col1, Col2, Col3, Col4 details in query engine. > > Is Kylin 0.7.1 HOLAP? > > i.e data which is not in cube will redirect it to hive table? > > > > > > > > Thanks, > > Parkavi. > > > > > > > > ::DISCLAIMER:: > > > > > ---------------------------------------------------------------------------------------------------------------------------------------------------- > > > > The contents of this e-mail and any attachment(s) are confidential and > > intended for the named recipient(s) only. > > E-mail transmission is not guaranteed to be secure or error-free as > > information could be intercepted, corrupted, > > lost, destroyed, arrive late or incomplete, or may contain viruses in > > transmission. The e mail and its contents > > (with or without referred errors) shall therefore not attach any > liability > > on the originator or HCL or its affiliates. > > Views or opinions, if any, presented in this email are solely those of > the > > author and may not necessarily reflect the > > views or opinions of HCL or its affiliates. Any form of reproduction, > > dissemination, copying, disclosure, modification, > > distribution and / or publication of this message without the prior > > written consent of authorized representative of > > HCL is strictly prohibited. If you have received this email in error > > please delete it and notify the sender immediately. > > Before opening any email and/or attachments, please check them for > viruses > > and other defects. > > > > > > > ---------------------------------------------------------------------------------------------------------------------------------------------------- > > >
