Thanks Li Yang. I will create a cube according to your procedure.
-----Original Message----- From: Li Yang [mailto:[email protected]] Sent: Monday, May 18, 2015 1:24 PM To: [email protected] Subject: Re: OLAP Cube Creation 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. > > > > > > > ---------------------------------------------------------------------- > ---------------------------------------------------------------------- > -------- > > >
