You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@kylin.apache.org by Parkavi Nandagopal <pa...@hcl.com> on 2015/05/13 12:27:51 UTC
OLAP Cube Creation
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.
----------------------------------------------------------------------------------------------------------------------------------------------------
RE: OLAP Cube Creation
Posted by Parkavi Nandagopal <pa...@hcl.com>.
Thanks Li Yang.
I will create a cube according to your procedure.
-----Original Message-----
From: Li Yang [mailto:liyang@apache.org]
Sent: Monday, May 18, 2015 1:24 PM
To: dev@kylin.incubator.apache.org
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 <sh...@apache.org>
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 <pa...@hcl.com>:
>
> > 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.
> >
> >
> >
> ----------------------------------------------------------------------
> ----------------------------------------------------------------------
> --------
> >
>
Re: OLAP Cube Creation
Posted by Li Yang <li...@apache.org>.
>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 <sh...@apache.org>
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 <pa...@hcl.com>:
>
> > 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.
> >
> >
> >
> ----------------------------------------------------------------------------------------------------------------------------------------------------
> >
>
Re: OLAP Cube Creation
Posted by ShaoFeng Shi <sh...@apache.org>.
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 <pa...@hcl.com>:
> 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.
>
>
> ----------------------------------------------------------------------------------------------------------------------------------------------------
>