You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@kylin.apache.org by "胡志华 (万里通科技及数据中心商务智能团队数据分析组)" <HU...@pingan.com.cn> on 2016/11/01 08:30:26 UTC
How to filter dimension from look-up table
Hi all,
Our analyst are faced a problem. When she built a cube, she wanted to filter dimensions from look-up table.
But KYLIN don’t let it, we can only put dimensions from fact table into filter fields. Therefore I can only set dimensions from look-up table which I want to filter as mandatory dimension, which will increase the cardinality.
So my question is how I can filter dimensions from look-up table when building cube. Thank you all.
********************************************************************************************************************************
The information in this email is confidential and may be legally privileged. If you have received this email in error or are not the intended recipient, please immediately notify the sender and delete this message from your computer. Any use, distribution, or copying of this email other than by the intended recipient is strictly prohibited. All messages sent to and from us may be monitored to ensure compliance with internal policies and to protect our business.
Emails are not secure and cannot be guaranteed to be error free as they can be intercepted, amended, lost or destroyed, or contain viruses. Anyone who communicates with us by email is taken to accept these risks.
收发邮件者请注意：
本邮件含保密信息，若误收本邮件，请务必通知发送人并直接删去，不得使用、传播或复制本邮件。
进出邮件均受到本公司合规监控。邮件可能发生被截留、被修改、丢失、被破坏或包含计算机病毒等不安全情况。
********************************************************************************************************************************
Re: 答复: 答复: How to filter dimension from look-up table
Posted by ShaoFeng Shi <sh...@apache.org>.
Great, enjoy!
2016-11-03 16:21 GMT+08:00 胡志华(万里通科技及数据中心商务智能团队数据分析组) <
HUZHIHUA160@pingan.com.cn>:
> Thank you shaofeng,
>
> Done successfully as your tips
>
> -----邮件原件-----
> 发件人: ShaoFeng Shi [mailto:shaofengshi@apache.org]
> 发送时间: 2016年11月2日 17:24
> 收件人: dev
> 主题: Re: 答复: How to filter dimension from look-up table
>
> "but cube ran error when built at step 1 or step 2, telling us it can't
> find the dimension ,such as " invalid table alias or column reference"."
>
> There is a little tricky: when you enter where condition to the "filter",
> use table name (without database name); e.g, the table is
> "DEFAULT.KYLIN_CATEGORY_GROUPINGS",
> then use "KYLIN_CATEGORY_GROUPINGS" it to represents; Just take a try.
>
> 2016-11-02 9:16 GMT+08:00 胡志华(万里通科技及数据中心商务智能团队数据分析组) <
> HUZHIHUA160@pingan.com.cn>:
>
> > Hi shaofeng,
> >
> > In recently version,1.5.3, kylin support it, I can put dimension
> > from look-up table into Filter on Data Model's "setting" page, but
> > cube ran error when built at step 1 or step 2, telling us it can't
> > find the dimension ,such as " invalid table alias or column reference".
> >
> > After upgrading to 1.5.4.1, I haven't try it , I'll try it later,
> > then tell the answer.
> >
> > -----邮件原件-----
> > 发件人: ShaoFeng Shi [mailto:shaofengshi@apache.org]
> > 发送时间: 2016年11月1日 22:46
> > 收件人: dev
> > 主题: Re: How to filter dimension from look-up table
> >
> > hi zhihua,
> >
> > "can only put dimensions from fact table into filter fields": do you
> > mean the "Filter" attribute on Data Model's "setting" page? It can
> > support look-up table I think. Can you provide a sample?
> >
> > 2016-11-01 16:30 GMT+08:00 胡志华(万里通科技及数据中心商务智能团队数据分析组) <
> > HUZHIHUA160@pingan.com.cn>:
> >
> > > Hi all,
> > > Our analyst are faced a problem. When she built a cube, she
> > > wanted to filter dimensions from look-up table.
> > > But KYLIN don’t let it, we can only put dimensions from fact table
> > > into filter fields. Therefore I can only set dimensions from look-up
> > > table which I want to filter as mandatory dimension, which will
> > > increase the cardinality.
> > >
> > > So my question is how I can filter dimensions from look-up
> > > table when building cube. Thank you all.
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > > ************************************************************
> > > ********************************************************************
> > > The information in this email is confidential and may be legally
> > > privileged. If you have received this email in error or are not the
> > > intended recipient, please immediately notify the sender and delete
> > > this message from your computer. Any use, distribution, or copying
> > > of this email other than by the intended recipient is strictly
> > > prohibited. All messages sent to and from us may be monitored to
> > > ensure compliance with internal policies and to protect our business.
> > > Emails are not secure and cannot be guaranteed to be error free as
> > > they can be intercepted, amended, lost or destroyed, or contain
> > > viruses. Anyone who communicates with us by email is taken to accept
> > these risks.
> > >
> > > 收发邮件者请注意：
> > > 本邮件含保密信息，若误收本邮件，请务必通知发送人并直接删去，不得使用、传播或复制本邮件。
> > > 进出邮件均受到本公司合规监控。邮件可能发生被截留、被修改、丢失、被破坏或包含计算机病毒等不安全情况。
> > > ************************************************************
> > > ********************************************************************
> > >
> >
> >
> >
> > --
> > Best regards,
> >
> > Shaofeng Shi 史少锋
> >
> > ************************************************************
> > ********************************************************************
> > The information in this email is confidential and may be legally
> > privileged. If you have received this email in error or are not the
> > intended recipient, please immediately notify the sender and delete
> > this message from your computer. Any use, distribution, or copying of
> > this email other than by the intended recipient is strictly
> > prohibited. All messages sent to and from us may be monitored to
> > ensure compliance with internal policies and to protect our business.
> > Emails are not secure and cannot be guaranteed to be error free as
> > they can be intercepted, amended, lost or destroyed, or contain
> > viruses. Anyone who communicates with us by email is taken to accept
> these risks.
> >
> > 收发邮件者请注意：
> > 本邮件含保密信息，若误收本邮件，请务必通知发送人并直接删去，不得使用、传播或复制本邮件。
> > 进出邮件均受到本公司合规监控。邮件可能发生被截留、被修改、丢失、被破坏或包含计算机病毒等不安全情况。
> > ************************************************************
> > ********************************************************************
> >
>
>
>
> --
> Best regards,
>
> Shaofeng Shi 史少锋
>
> ************************************************************
> ********************************************************************
> The information in this email is confidential and may be legally
> privileged. If you have received this email in error or are not the
> intended recipient, please immediately notify the sender and delete this
> message from your computer. Any use, distribution, or copying of this email
> other than by the intended recipient is strictly prohibited. All messages
> sent to and from us may be monitored to ensure compliance with internal
> policies and to protect our business.
> Emails are not secure and cannot be guaranteed to be error free as they
> can be intercepted, amended, lost or destroyed, or contain viruses. Anyone
> who communicates with us by email is taken to accept these risks.
>
> 收发邮件者请注意：
> 本邮件含保密信息，若误收本邮件，请务必通知发送人并直接删去，不得使用、传播或复制本邮件。
> 进出邮件均受到本公司合规监控。邮件可能发生被截留、被修改、丢失、被破坏或包含计算机病毒等不安全情况。
> ************************************************************
> ********************************************************************
>
--
Best regards,
Shaofeng Shi 史少锋
答复: 答复: How to filter dimension from look-up table
Posted by "胡志华 (万里通科技及数据中心商务智能团队数据分析组)" <HU...@pingan.com.cn>.
Thank you shaofeng,
Done successfully as your tips
-----邮件原件-----
发件人: ShaoFeng Shi [mailto:shaofengshi@apache.org]
发送时间: 2016年11月2日 17:24
收件人: dev
主题: Re: 答复: How to filter dimension from look-up table
"but cube ran error when built at step 1 or step 2, telling us it can't find the dimension ,such as " invalid table alias or column reference"."
There is a little tricky: when you enter where condition to the "filter", use table name (without database name); e.g, the table is "DEFAULT.KYLIN_CATEGORY_GROUPINGS",
then use "KYLIN_CATEGORY_GROUPINGS" it to represents; Just take a try.
2016-11-02 9:16 GMT+08:00 胡志华(万里通科技及数据中心商务智能团队数据分析组) <
HUZHIHUA160@pingan.com.cn>:
> Hi shaofeng,
>
> In recently version,1.5.3, kylin support it, I can put dimension
> from look-up table into Filter on Data Model's "setting" page, but
> cube ran error when built at step 1 or step 2, telling us it can't
> find the dimension ,such as " invalid table alias or column reference".
>
> After upgrading to 1.5.4.1, I haven't try it , I'll try it later,
> then tell the answer.
>
> -----邮件原件-----
> 发件人: ShaoFeng Shi [mailto:shaofengshi@apache.org]
> 发送时间: 2016年11月1日 22:46
> 收件人: dev
> 主题: Re: How to filter dimension from look-up table
>
> hi zhihua,
>
> "can only put dimensions from fact table into filter fields": do you
> mean the "Filter" attribute on Data Model's "setting" page? It can
> support look-up table I think. Can you provide a sample?
>
> 2016-11-01 16:30 GMT+08:00 胡志华(万里通科技及数据中心商务智能团队数据分析组) <
> HUZHIHUA160@pingan.com.cn>:
>
> > Hi all,
> > Our analyst are faced a problem. When she built a cube, she
> > wanted to filter dimensions from look-up table.
> > But KYLIN don’t let it, we can only put dimensions from fact table
> > into filter fields. Therefore I can only set dimensions from look-up
> > table which I want to filter as mandatory dimension, which will
> > increase the cardinality.
> >
> > So my question is how I can filter dimensions from look-up
> > table when building cube. Thank you all.
> >
> >
> >
> >
> >
> >
> >
> > ************************************************************
> > ********************************************************************
> > The information in this email is confidential and may be legally
> > privileged. If you have received this email in error or are not the
> > intended recipient, please immediately notify the sender and delete
> > this message from your computer. Any use, distribution, or copying
> > of this email other than by the intended recipient is strictly
> > prohibited. All messages sent to and from us may be monitored to
> > ensure compliance with internal policies and to protect our business.
> > Emails are not secure and cannot be guaranteed to be error free as
> > they can be intercepted, amended, lost or destroyed, or contain
> > viruses. Anyone who communicates with us by email is taken to accept
> these risks.
> >
> > 收发邮件者请注意：
> > 本邮件含保密信息，若误收本邮件，请务必通知发送人并直接删去，不得使用、传播或复制本邮件。
> > 进出邮件均受到本公司合规监控。邮件可能发生被截留、被修改、丢失、被破坏或包含计算机病毒等不安全情况。
> > ************************************************************
> > ********************************************************************
> >
>
>
>
> --
> Best regards,
>
> Shaofeng Shi 史少锋
>
> ************************************************************
> ********************************************************************
> The information in this email is confidential and may be legally
> privileged. If you have received this email in error or are not the
> intended recipient, please immediately notify the sender and delete
> this message from your computer. Any use, distribution, or copying of
> this email other than by the intended recipient is strictly
> prohibited. All messages sent to and from us may be monitored to
> ensure compliance with internal policies and to protect our business.
> Emails are not secure and cannot be guaranteed to be error free as
> they can be intercepted, amended, lost or destroyed, or contain
> viruses. Anyone who communicates with us by email is taken to accept these risks.
>
> 收发邮件者请注意：
> 本邮件含保密信息，若误收本邮件，请务必通知发送人并直接删去，不得使用、传播或复制本邮件。
> 进出邮件均受到本公司合规监控。邮件可能发生被截留、被修改、丢失、被破坏或包含计算机病毒等不安全情况。
> ************************************************************
> ********************************************************************
>
--
Best regards,
Shaofeng Shi 史少锋
********************************************************************************************************************************
The information in this email is confidential and may be legally privileged. If you have received this email in error or are not the intended recipient, please immediately notify the sender and delete this message from your computer. Any use, distribution, or copying of this email other than by the intended recipient is strictly prohibited. All messages sent to and from us may be monitored to ensure compliance with internal policies and to protect our business.
Emails are not secure and cannot be guaranteed to be error free as they can be intercepted, amended, lost or destroyed, or contain viruses. Anyone who communicates with us by email is taken to accept these risks.
收发邮件者请注意：
本邮件含保密信息，若误收本邮件，请务必通知发送人并直接删去，不得使用、传播或复制本邮件。
进出邮件均受到本公司合规监控。邮件可能发生被截留、被修改、丢失、被破坏或包含计算机病毒等不安全情况。
********************************************************************************************************************************
Re: 答复: How to filter dimension from look-up table
Posted by ShaoFeng Shi <sh...@apache.org>.
"but cube ran error when built at step 1 or step 2, telling us it can't
find the dimension ,such as " invalid table alias or column reference"."
There is a little tricky: when you enter where condition to the "filter",
use table name (without database name); e.g, the table is
"DEFAULT.KYLIN_CATEGORY_GROUPINGS",
then use "KYLIN_CATEGORY_GROUPINGS" it to represents; Just take a try.
2016-11-02 9:16 GMT+08:00 胡志华(万里通科技及数据中心商务智能团队数据分析组) <
HUZHIHUA160@pingan.com.cn>:
> Hi shaofeng,
>
> In recently version,1.5.3, kylin support it, I can put dimension from
> look-up table into Filter on Data Model's "setting" page, but cube ran
> error when built at step 1 or step 2, telling us it can't find the
> dimension ,such as " invalid table alias or column reference".
>
> After upgrading to 1.5.4.1, I haven't try it , I'll try it later, then
> tell the answer.
>
> -----邮件原件-----
> 发件人: ShaoFeng Shi [mailto:shaofengshi@apache.org]
> 发送时间: 2016年11月1日 22:46
> 收件人: dev
> 主题: Re: How to filter dimension from look-up table
>
> hi zhihua,
>
> "can only put dimensions from fact table into filter fields": do you mean
> the "Filter" attribute on Data Model's "setting" page? It can support
> look-up table I think. Can you provide a sample?
>
> 2016-11-01 16:30 GMT+08:00 胡志华(万里通科技及数据中心商务智能团队数据分析组) <
> HUZHIHUA160@pingan.com.cn>:
>
> > Hi all,
> > Our analyst are faced a problem. When she built a cube, she
> > wanted to filter dimensions from look-up table.
> > But KYLIN don’t let it, we can only put dimensions from fact table
> > into filter fields. Therefore I can only set dimensions from look-up
> > table which I want to filter as mandatory dimension, which will
> > increase the cardinality.
> >
> > So my question is how I can filter dimensions from look-up
> > table when building cube. Thank you all.
> >
> >
> >
> >
> >
> >
> >
> > ************************************************************
> > ********************************************************************
> > The information in this email is confidential and may be legally
> > privileged. If you have received this email in error or are not the
> > intended recipient, please immediately notify the sender and delete
> > this message from your computer. Any use, distribution, or copying of
> > this email other than by the intended recipient is strictly
> > prohibited. All messages sent to and from us may be monitored to
> > ensure compliance with internal policies and to protect our business.
> > Emails are not secure and cannot be guaranteed to be error free as
> > they can be intercepted, amended, lost or destroyed, or contain
> > viruses. Anyone who communicates with us by email is taken to accept
> these risks.
> >
> > 收发邮件者请注意：
> > 本邮件含保密信息，若误收本邮件，请务必通知发送人并直接删去，不得使用、传播或复制本邮件。
> > 进出邮件均受到本公司合规监控。邮件可能发生被截留、被修改、丢失、被破坏或包含计算机病毒等不安全情况。
> > ************************************************************
> > ********************************************************************
> >
>
>
>
> --
> Best regards,
>
> Shaofeng Shi 史少锋
>
> ************************************************************
> ********************************************************************
> The information in this email is confidential and may be legally
> privileged. If you have received this email in error or are not the
> intended recipient, please immediately notify the sender and delete this
> message from your computer. Any use, distribution, or copying of this email
> other than by the intended recipient is strictly prohibited. All messages
> sent to and from us may be monitored to ensure compliance with internal
> policies and to protect our business.
> Emails are not secure and cannot be guaranteed to be error free as they
> can be intercepted, amended, lost or destroyed, or contain viruses. Anyone
> who communicates with us by email is taken to accept these risks.
>
> 收发邮件者请注意：
> 本邮件含保密信息，若误收本邮件，请务必通知发送人并直接删去，不得使用、传播或复制本邮件。
> 进出邮件均受到本公司合规监控。邮件可能发生被截留、被修改、丢失、被破坏或包含计算机病毒等不安全情况。
> ************************************************************
> ********************************************************************
>
--
Best regards,
Shaofeng Shi 史少锋
答复: How to filter dimension from look-up table
Posted by "胡志华 (万里通科技及数据中心商务智能团队数据分析组)" <HU...@pingan.com.cn>.
Hi shaofeng,
In recently version,1.5.3, kylin support it, I can put dimension from look-up table into Filter on Data Model's "setting" page, but cube ran error when built at step 1 or step 2, telling us it can't find the dimension ,such as " invalid table alias or column reference".
After upgrading to 1.5.4.1, I haven't try it , I'll try it later, then tell the answer.
-----邮件原件-----
发件人: ShaoFeng Shi [mailto:shaofengshi@apache.org]
发送时间: 2016年11月1日 22:46
收件人: dev
主题: Re: How to filter dimension from look-up table
hi zhihua,
"can only put dimensions from fact table into filter fields": do you mean the "Filter" attribute on Data Model's "setting" page? It can support look-up table I think. Can you provide a sample?
2016-11-01 16:30 GMT+08:00 胡志华(万里通科技及数据中心商务智能团队数据分析组) <
HUZHIHUA160@pingan.com.cn>:
> Hi all,
> Our analyst are faced a problem. When she built a cube, she
> wanted to filter dimensions from look-up table.
> But KYLIN don’t let it, we can only put dimensions from fact table
> into filter fields. Therefore I can only set dimensions from look-up
> table which I want to filter as mandatory dimension, which will
> increase the cardinality.
>
> So my question is how I can filter dimensions from look-up
> table when building cube. Thank you all.
>
>
>
>
>
>
>
> ************************************************************
> ********************************************************************
> The information in this email is confidential and may be legally
> privileged. If you have received this email in error or are not the
> intended recipient, please immediately notify the sender and delete
> this message from your computer. Any use, distribution, or copying of
> this email other than by the intended recipient is strictly
> prohibited. All messages sent to and from us may be monitored to
> ensure compliance with internal policies and to protect our business.
> Emails are not secure and cannot be guaranteed to be error free as
> they can be intercepted, amended, lost or destroyed, or contain
> viruses. Anyone who communicates with us by email is taken to accept these risks.
>
> 收发邮件者请注意：
> 本邮件含保密信息，若误收本邮件，请务必通知发送人并直接删去，不得使用、传播或复制本邮件。
> 进出邮件均受到本公司合规监控。邮件可能发生被截留、被修改、丢失、被破坏或包含计算机病毒等不安全情况。
> ************************************************************
> ********************************************************************
>
--
Best regards,
Shaofeng Shi 史少锋
********************************************************************************************************************************
The information in this email is confidential and may be legally privileged. If you have received this email in error or are not the intended recipient, please immediately notify the sender and delete this message from your computer. Any use, distribution, or copying of this email other than by the intended recipient is strictly prohibited. All messages sent to and from us may be monitored to ensure compliance with internal policies and to protect our business.
Emails are not secure and cannot be guaranteed to be error free as they can be intercepted, amended, lost or destroyed, or contain viruses. Anyone who communicates with us by email is taken to accept these risks.
收发邮件者请注意：
本邮件含保密信息，若误收本邮件，请务必通知发送人并直接删去，不得使用、传播或复制本邮件。
进出邮件均受到本公司合规监控。邮件可能发生被截留、被修改、丢失、被破坏或包含计算机病毒等不安全情况。
********************************************************************************************************************************
Re: How to filter dimension from look-up table
Posted by ShaoFeng Shi <sh...@apache.org>.
hi zhihua,
"can only put dimensions from fact table into filter fields": do you mean
the "Filter" attribute on Data Model's "setting" page? It can support
look-up table I think. Can you provide a sample?
2016-11-01 16:30 GMT+08:00 胡志华(万里通科技及数据中心商务智能团队数据分析组) <
HUZHIHUA160@pingan.com.cn>:
> Hi all,
> Our analyst are faced a problem. When she built a cube, she wanted
> to filter dimensions from look-up table.
> But KYLIN don’t let it, we can only put dimensions from fact table into
> filter fields. Therefore I can only set dimensions from look-up table which
> I want to filter as mandatory dimension, which will increase the
> cardinality.
>
> So my question is how I can filter dimensions from look-up table
> when building cube. Thank you all.
>
>
>
>
>
>
>
> ************************************************************
> ********************************************************************
> The information in this email is confidential and may be legally
> privileged. If you have received this email in error or are not the
> intended recipient, please immediately notify the sender and delete this
> message from your computer. Any use, distribution, or copying of this email
> other than by the intended recipient is strictly prohibited. All messages
> sent to and from us may be monitored to ensure compliance with internal
> policies and to protect our business.
> Emails are not secure and cannot be guaranteed to be error free as they
> can be intercepted, amended, lost or destroyed, or contain viruses. Anyone
> who communicates with us by email is taken to accept these risks.
>
> 收发邮件者请注意：
> 本邮件含保密信息，若误收本邮件，请务必通知发送人并直接删去，不得使用、传播或复制本邮件。
> 进出邮件均受到本公司合规监控。邮件可能发生被截留、被修改、丢失、被破坏或包含计算机病毒等不安全情况。
> ************************************************************
> ********************************************************************
>
--
Best regards,
Shaofeng Shi 史少锋