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. > > > ---------------------------------------------------------------------------------------------------------------------------------------------------- >
