You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@kylin.apache.org by PRABHU Reshma <re...@socgen.com> on 2016/08/05 06:48:03 UTC
Derived measures in Kylin
Hi,
Is it possible to have Derived Measures in Kylin?
For example, Fact Table has 2 columns ( PersonHra, PersonDa)
Is it possible to have Kylin cube created on the fact table and computing Salary measure as SUM of 'PersonHra' + 'PersonDa'?
Please let me know.
Regards,
Reshma
=========================================================
Ce message et toutes les pieces jointes (ci-apres le "message")
sont confidentiels et susceptibles de contenir des informations
couvertes par le secret professionnel. Ce message est etabli
a l'intention exclusive de ses destinataires. Toute utilisation
ou diffusion non autorisee interdite.
Tout message electronique est susceptible d'alteration. La SOCIETE GENERALE
et ses filiales declinent toute responsabilite au titre de ce message
s'il a ete altere, deforme falsifie.
=========================================================
This message and any attachments (the "message") are confidential,
intended solely for the addresses, and may contain legally privileged
information. Any unauthorized use or dissemination is prohibited.
E-mails are susceptible to alteration. Neither SOCIETE GENERALE nor any
of its subsidiaries or affiliates shall be liable for the message
if altered, changed or falsified.
=========================================================
Re: Derived measures in Kylin
Posted by Alberto Ramón <a....@gmail.com>.
Hi ¡¡, I'm not expert in Kylin :)
Perhaps, this can be useful for you:
https://issues.apache.org/jira/browse/KYLIN-976
The problem: this jira don't have example / doc or code changeSet ... but:
-
https://github.com/apache/kylin/blob/696fb89aa2eb684408d1009218f80fe323dffd3b/core-metadata/src/main/java/org/apache/kylin/measure/MeasureTypeFactory.java#L110
-
https://github.com/apache/kylin/blob/6c298fac2acd7d311e82002e0854e896037883c8/core-common/src/main/java/org/apache/kylin/common/KylinConfigBase.java#L455
and perhaps: the property in the "kylin.cube.measure.customMeasureType" you
can specify a list of strings of 'customFactory' of MeasureTypeFactory class
<https://github.com/apache/kylin/blob/696fb89aa2eb684408d1009218f80fe323dffd3b/core-metadata/src/main/java/org/apache/kylin/measure/MeasureTypeFactory.java#L66>
BR, Alberto
2016-08-18 10:13 GMT+02:00 Bhanu Prakash Sunkesula <bp...@gmail.com>
:
> Hello,
>
> we also had the same issue, where my business spokes has expected a Derived
> measure with normal measures, but as of now there is no direct solution for
> this in KYLIN (apart from SQL Query in Insight tab). Indirectly we had a
> workaround by creating a column TotalSalary = SUM(HRA) + SUM(DA)
> in hive and need to populate it in KYLIN Cube.
>
> Regards,
> Bhanu Prakash.
>
> --
> View this message in context: http://apache-kylin.74782.x6.
> nabble.com/Derived-measures-in-Kylin-tp5513p5592.html
> Sent from the Apache Kylin mailing list archive at Nabble.com.
>
Re: Derived measures in Kylin
Posted by Bhanu Prakash Sunkesula <bp...@gmail.com>.
Hello,
we also had the same issue, where my business spokes has expected a Derived
measure with normal measures, but as of now there is no direct solution for
this in KYLIN (apart from SQL Query in Insight tab). Indirectly we had a
workaround by creating a column TotalSalary = SUM(HRA) + SUM(DA)
in hive and need to populate it in KYLIN Cube.
Regards,
Bhanu Prakash.
--
View this message in context: http://apache-kylin.74782.x6.nabble.com/Derived-measures-in-Kylin-tp5513p5592.html
Sent from the Apache Kylin mailing list archive at Nabble.com.
Re: Derived measures in Kylin
Posted by Reshma <re...@socgen.com>.
Hi,
Thanks everybody for the explanation. It worked with sending query with
calculated parameters.
The implication is each user connecting to the Kylin cube has to build the
query with calculated fields to get the data.
somthing like select SUM(HRA) + SUM(DA) ...
Since i will have around 200 users using this, Is it possible to
pre-configure all calculated fields?
Something like TotalSalary = SUM(HRA) + SUM(DA)
So that i configure the formula as required and the users directly get the
answer by query on the calculated field
select TotalSalary from Person ==> where TotalSalary is calculated on the
fly depending upon the formula given by me
Please let me know.
Regards,
Reshma
--
View this message in context: http://apache-kylin.74782.x6.nabble.com/Derived-measures-in-Kylin-tp5513p5591.html
Sent from the Apache Kylin mailing list archive at Nabble.com.
Re: Derived measures in Kylin
Posted by Li Yang <li...@apache.org>.
@Reshma, you can try on a simple data set to verify. Your requirement shall
just work, no special configuration is needed.
On Thu, Aug 11, 2016 at 10:49 PM, ShaoFeng Shi <sh...@apache.org>
wrote:
> You don't need configure formula "(SUM(BaseSalary) + SUM(Hra))/SUM(DA)" as
> a measure; you just send your query to Kylin, like "select (SUM(BaseSalary)
> + SUM(Hra))/SUM(DA) from table group by dim1, dim2..."
>
>
>
> 2016-08-11 12:05 GMT+08:00 Reshma <re...@socgen.com>:
>
> > hi,
> >
> > the question is since the SUM(HRA), SUM(BaseSalary), SUM(DA) will be
> > configured and calculated by Kylin cube, how can i configure Cube to
> > calculate formula
> > (SUM(BaseSalary) + SUM(Hra))/SUM(DA)?
> >
> > Regards
> >
> >
> > --
> > View this message in context: http://apache-kylin.74782.x6.
> > nabble.com/Derived-measures-in-Kylin-tp5513p5555.html
> > Sent from the Apache Kylin mailing list archive at Nabble.com.
> >
>
>
>
> --
> Best regards,
>
> Shaofeng Shi
>
Re: Derived measures in Kylin
Posted by ShaoFeng Shi <sh...@apache.org>.
You don't need configure formula "(SUM(BaseSalary) + SUM(Hra))/SUM(DA)" as
a measure; you just send your query to Kylin, like "select (SUM(BaseSalary)
+ SUM(Hra))/SUM(DA) from table group by dim1, dim2..."
2016-08-11 12:05 GMT+08:00 Reshma <re...@socgen.com>:
> hi,
>
> the question is since the SUM(HRA), SUM(BaseSalary), SUM(DA) will be
> configured and calculated by Kylin cube, how can i configure Cube to
> calculate formula
> (SUM(BaseSalary) + SUM(Hra))/SUM(DA)?
>
> Regards
>
>
> --
> View this message in context: http://apache-kylin.74782.x6.
> nabble.com/Derived-measures-in-Kylin-tp5513p5555.html
> Sent from the Apache Kylin mailing list archive at Nabble.com.
>
--
Best regards,
Shaofeng Shi
Re: Derived measures in Kylin
Posted by Reshma <re...@socgen.com>.
hi,
the question is since the SUM(HRA), SUM(BaseSalary), SUM(DA) will be
configured and calculated by Kylin cube, how can i configure Cube to
calculate formula
(SUM(BaseSalary) + SUM(Hra))/SUM(DA)?
Regards
--
View this message in context: http://apache-kylin.74782.x6.nabble.com/Derived-measures-in-Kylin-tp5513p5555.html
Sent from the Apache Kylin mailing list archive at Nabble.com.
Re: Derived measures in Kylin
Posted by hongbin ma <ma...@apache.org>.
I still don't quite understand your problem. What exception are your
witnessing when you run queries containing (SUM(BaseSalary) +
SUM(Hra))/SUM(DA)?
On Wed, Aug 10, 2016 at 1:10 PM, Reshma <re...@socgen.com> wrote:
> Hi,To clarify, lets says we want feature likeI have following columns in my
> Person table,EmpNo, FirstName, LastName, Department, Phone, BaseSalary,
> Hra,
> DAPlease see attached Image1When i develop cube, i created
> Dimensions(FirstName, LastName, Department) and Measures(SUM(BaseSalary),
> SUM(Hra), SUM(DA), Max(BaseSalary))I can easily show it in a Tableau UI in
> a
> matrix like grouped by dimensions (FirstName, LastName)Measures -->
> SUM(BaseSalary), SUM(Hra), SUM(DA)Now i want another calculated measures
> Ratio like (SUM(BaseSalary) + SUM(Hra))/SUM(DA)Please see attached
> Image2Now
> this has to be calculated at runAlso, now we add another grouping dimension
> Department, the ratio will again change as SUM(BaseSalary), SUM(HRA) and
> SUM(DA) will change at group level.Please see attached Image3Let me know
> how
> i can configure Kylin do have this.
> <http://apache-kylin.74782.x6.nabble.com/file/n5534/Image2.png> time by
> Cube
> based on grouping selected(in this case FirstName, LastName).
> <http://apache-kylin.74782.x6.nabble.com/file/n5534/Image1.png>
> <http://apache-kylin.74782.x6.nabble.com/file/n5534/Image3.png>
>
> --
> View this message in context: http://apache-kylin.74782.x6.
> nabble.com/Derived-measures-in-Kylin-tp5513p5534.html
> Sent from the Apache Kylin mailing list archive at Nabble.com.
>
--
Regards,
*Bin Mahone | 马洪宾*
Re: Derived measures in Kylin
Posted by Reshma <re...@socgen.com>.
Hi,To clarify, lets says we want feature likeI have following columns in my
Person table,EmpNo, FirstName, LastName, Department, Phone, BaseSalary, Hra,
DAPlease see attached Image1When i develop cube, i created
Dimensions(FirstName, LastName, Department) and Measures(SUM(BaseSalary),
SUM(Hra), SUM(DA), Max(BaseSalary))I can easily show it in a Tableau UI in a
matrix like grouped by dimensions (FirstName, LastName)Measures -->
SUM(BaseSalary), SUM(Hra), SUM(DA)Now i want another calculated measures
Ratio like (SUM(BaseSalary) + SUM(Hra))/SUM(DA)Please see attached Image2Now
this has to be calculated at runAlso, now we add another grouping dimension
Department, the ratio will again change as SUM(BaseSalary), SUM(HRA) and
SUM(DA) will change at group level.Please see attached Image3Let me know how
i can configure Kylin do have this.
<http://apache-kylin.74782.x6.nabble.com/file/n5534/Image2.png> time by Cube
based on grouping selected(in this case FirstName, LastName).
<http://apache-kylin.74782.x6.nabble.com/file/n5534/Image1.png>
<http://apache-kylin.74782.x6.nabble.com/file/n5534/Image3.png>
--
View this message in context: http://apache-kylin.74782.x6.nabble.com/Derived-measures-in-Kylin-tp5513p5534.html
Sent from the Apache Kylin mailing list archive at Nabble.com.
Re: Derived measures in Kylin
Posted by ShaoFeng Shi <sh...@apache.org>.
Hi Reshma, sorry I didn't get; Could you please elaborate with detail
description? thanks.
2016-08-09 20:01 GMT+08:00 Reshma <re...@socgen.com>:
> Thanks for the response.
> but what if derived measures we require is something like
> SUM(personHRA)/SUM(personDA).
> In this case, SUM(personHRA) will vary based on what dimensions are
> selections and what filters are applied.
>
> Is there a possibility to have some measures which are calculated on cube
> side based on what dimensions are selected?
>
> Thanks,
> Reshma
>
> --
> View this message in context: http://apache-kylin.74782.x6.
> nabble.com/Derived-measures-in-Kylin-tp5513p5530.html
> Sent from the Apache Kylin mailing list archive at Nabble.com.
>
--
Best regards,
Shaofeng Shi
Re: Derived measures in Kylin
Posted by Reshma <re...@socgen.com>.
Thanks for the response.
but what if derived measures we require is something like
SUM(personHRA)/SUM(personDA).
In this case, SUM(personHRA) will vary based on what dimensions are
selections and what filters are applied.
Is there a possibility to have some measures which are calculated on cube
side based on what dimensions are selected?
Thanks,
Reshma
--
View this message in context: http://apache-kylin.74782.x6.nabble.com/Derived-measures-in-Kylin-tp5513p5530.html
Sent from the Apache Kylin mailing list archive at Nabble.com.
Re: Derived measures in Kylin
Posted by Luke Han <lu...@gmail.com>.
One option is to create a view first to combine these two column into one.
Then pickup this view as Fact table.
Thanks.
Best Regards!
---------------------
Luke Han
On Mon, Aug 8, 2016 at 11:46 AM, ShaoFeng Shi <sh...@apache.org>
wrote:
> So far the SUM measure only support 1 column; The workaround is defining an
> additional column with the value of 'PersonHra' + 'PersonDa', and then use
> that in the measure;
>
> 2016-08-05 14:48 GMT+08:00 PRABHU Reshma <re...@socgen.com>:
>
> > Hi,
> >
> > Is it possible to have Derived Measures in Kylin?
> > For example, Fact Table has 2 columns ( PersonHra, PersonDa)
> > Is it possible to have Kylin cube created on the fact table and computing
> > Salary measure as SUM of 'PersonHra' + 'PersonDa'?
> >
> > Please let me know.
> >
> > Regards,
> > Reshma
> > =========================================================
> >
> > Ce message et toutes les pieces jointes (ci-apres le "message")
> > sont confidentiels et susceptibles de contenir des informations
> > couvertes par le secret professionnel. Ce message est etabli
> > a l'intention exclusive de ses destinataires. Toute utilisation
> > ou diffusion non autorisee interdite.
> > Tout message electronique est susceptible d'alteration. La SOCIETE
> GENERALE
> > et ses filiales declinent toute responsabilite au titre de ce message
> > s'il a ete altere, deforme falsifie.
> >
> > =========================================================
> >
> > This message and any attachments (the "message") are confidential,
> > intended solely for the addresses, and may contain legally privileged
> > information. Any unauthorized use or dissemination is prohibited.
> > E-mails are susceptible to alteration. Neither SOCIETE GENERALE nor any
> > of its subsidiaries or affiliates shall be liable for the message
> > if altered, changed or falsified.
> >
> > =========================================================
> >
>
>
>
> --
> Best regards,
>
> Shaofeng Shi
>
Re: Derived measures in Kylin
Posted by ShaoFeng Shi <sh...@apache.org>.
So far the SUM measure only support 1 column; The workaround is defining an
additional column with the value of 'PersonHra' + 'PersonDa', and then use
that in the measure;
2016-08-05 14:48 GMT+08:00 PRABHU Reshma <re...@socgen.com>:
> Hi,
>
> Is it possible to have Derived Measures in Kylin?
> For example, Fact Table has 2 columns ( PersonHra, PersonDa)
> Is it possible to have Kylin cube created on the fact table and computing
> Salary measure as SUM of 'PersonHra' + 'PersonDa'?
>
> Please let me know.
>
> Regards,
> Reshma
> =========================================================
>
> Ce message et toutes les pieces jointes (ci-apres le "message")
> sont confidentiels et susceptibles de contenir des informations
> couvertes par le secret professionnel. Ce message est etabli
> a l'intention exclusive de ses destinataires. Toute utilisation
> ou diffusion non autorisee interdite.
> Tout message electronique est susceptible d'alteration. La SOCIETE GENERALE
> et ses filiales declinent toute responsabilite au titre de ce message
> s'il a ete altere, deforme falsifie.
>
> =========================================================
>
> This message and any attachments (the "message") are confidential,
> intended solely for the addresses, and may contain legally privileged
> information. Any unauthorized use or dissemination is prohibited.
> E-mails are susceptible to alteration. Neither SOCIETE GENERALE nor any
> of its subsidiaries or affiliates shall be liable for the message
> if altered, changed or falsified.
>
> =========================================================
>
--
Best regards,
Shaofeng Shi