You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@kylin.apache.org by "bo.hao" <bo...@foxmail.com> on 2019/12/25 05:45:15 UTC
Dimension data often changes, and the historical dimensions data need to be queried. What is the recommended solution?
Business requirement:
Data in some dimension tables often changes. And we need to be able to query the historical data, both fact table and dimension table.
The problem I encountered:
If the cube is not merged, everything is ok. But after merging, the query result is incorrect.
This only happens on the derived dimensions, and the normal dimensions are ok.
After consulting colleagues, I learned that when querying a derived dimension, the data comes from a snapshot.
When merging, only the latest sanpshot is retained, and other snapshots are thrown away.
So what is the recommended solution for this scenario?
For example:
Fact table : FACT_DTAL
Dimension table : ORG_TREE (often changes) , BUS_TYP (not often changes)
sql:
select a.dte, a.org, a.bus_typ, b.org as org_id, b.org_nam, b.sup_org, b.org_nam_1, c.cod, c.nam
from FACT_DTAL a
left join ORG_TREE b
on a.dte=b.dte and a.org=b.org
left join BUS_TYP c
on a.bus_typ=c.cod
order by a.dte, a.org, a.bus_typ;
before meging :
after merging :
RE: Dimension data often changes, and the historical dimensions data need to be queried. What is the recommended solution?
Posted by "bo.hao" <bo...@foxmail.com>.
ok, thanks, Billy Liu !
------------------ 原始邮件 ------------------
发件人: "Billy Liu"<billyliu@apache.org>;
发送时间: 2019年12月26日(星期四) 上午8:44
收件人: "user"<user@kylin.apache.org>;
主题: Re: Dimension data often changes, and the historical dimensions data need to be queried. What is the recommended solution?
I think you have figured out the solution.SCD1 keeps the latest dimension value, in Kylin the derived dimension is designed for that, which query the dimension value from the latest snapshot.
SCD2 keeps the historical dimension value, in Kylin the normal dimension is designed for this, which build the historical value into segment directly.
With Warm regards
Billy Liu
bo.hao <bo.hao.abc@foxmail.com> 于2019年12月25日周三 下午2:19写道:
sorry, picture of the first mail may not be displayed... here is an explanation:
before merging:
All fields have results.
after merging:
Data of the derived dimensions is empty, and other fields still have results.
------------------ Original ------------------
From: "bo.hao"<bo.hao.abc@foxmail.com>;
Date: Wed, Dec 25, 2019 01:45 PM
To: "user"<user@kylin.apache.org>;
Subject: Dimension data often changes, and the historical dimensions data need to be queried. What is the recommended solution?
Business requirement:
Data in some dimension tables often changes. And we need to be able to query the historical data, both fact table and dimension table.
The problem I encountered:
If the cube is not merged, everything is ok. But after merging, the query result is incorrect.
This only happens on the derived dimensions, and the normal dimensions are ok.
After consulting colleagues, I learned that when querying a derived dimension, the data comes from a snapshot.
When merging, only the latest sanpshot is retained, and other snapshots are thrown away.
So what is the recommended solution for this scenario?
For example:
Fact table : FACT_DTAL
Dimension table : ORG_TREE (often changes) , BUS_TYP (not often changes)
sql:
select a.dte, a.org, a.bus_typ, b.org as org_id, b.org_nam, b.sup_org, b.org_nam_1, c.cod, c.nam
from FACT_DTAL a
left join ORG_TREE b
on a.dte=b.dte and a.org=b.org
left join BUS_TYP c
on a.bus_typ=c.cod
order by a.dte, a.org, a.bus_typ;
before meging :
after merging :
Re: Dimension data often changes, and the historical dimensions data
need to be queried. What is the recommended solution?
Posted by Billy Liu <bi...@apache.org>.
I think you have figured out the solution.
SCD1 keeps the latest dimension value, in Kylin the derived dimension is
designed for that, which query the dimension value from the latest
snapshot.
SCD2 keeps the historical dimension value, in Kylin the normal dimension is
designed for this, which build the historical value into segment directly.
With Warm regards
Billy Liu
bo.hao <bo...@foxmail.com> 于2019年12月25日周三 下午2:19写道:
> sorry, picture of the first mail may not be displayed... here is an
> explanation:
>
> before merging:
> All fields have results.
>
> after merging:
> Data of the derived dimensions is empty, and other fields still have
> results.
>
>
>
> ------------------ Original ------------------
> *From:* "bo.hao"<bo...@foxmail.com>;
> *Date:* Wed, Dec 25, 2019 01:45 PM
> *To:* "user"<us...@kylin.apache.org>;
> *Subject:* Dimension data often changes, and the historical dimensions
> data need to be queried. What is the recommended solution?
>
> Business requirement:
> Data in some dimension tables often changes. And we need to be able to
> query the historical data, both fact table and dimension table.
>
> The problem I encountered:
> If the cube is not merged, everything is ok. But after merging, the query
> result is incorrect.
> This only happens on the derived dimensions, and the normal dimensions are
> ok.
> After consulting colleagues, I learned that when querying a derived
> dimension, the data comes from a snapshot.
> When merging, only the latest sanpshot is retained, and other snapshots
> are thrown away.
>
> So what is the recommended solution for this scenario?
>
>
> For example:
> Fact table : FACT_DTAL
> Dimension table : ORG_TREE (often changes) , BUS_TYP (not often changes)
> sql:
> select a.dte, a.org, a.bus_typ, b.org as org_id, b.org_nam, b.sup_org,
> b.org_nam_1, c.cod, c.nam
> from FACT_DTAL a
> left join ORG_TREE b
> on a.dte=b.dte and a.org=b.org
> left join BUS_TYP c
> on a.bus_typ=c.cod
> order by a.dte, a.org, a.bus_typ;
>
> before meging :
>
>
> after merging :
>
>
>
Re:Dimension data often changes, and the historical dimensions data need to be queried. What is the recommended solution?
Posted by "bo.hao" <bo...@foxmail.com>.
sorry, picture of the first mail may not be displayed... here is an explanation:
before merging:
All fields have results.
after merging:
Data of the derived dimensions is empty, and other fields still have results.
------------------ Original ------------------
From: "bo.hao"<bo.hao.abc@foxmail.com>;
Date: Wed, Dec 25, 2019 01:45 PM
To: "user"<user@kylin.apache.org>;
Subject: Dimension data often changes, and the historical dimensions data need to be queried. What is the recommended solution?
Business requirement:
Data in some dimension tables often changes. And we need to be able to query the historical data, both fact table and dimension table.
The problem I encountered:
If the cube is not merged, everything is ok. But after merging, the query result is incorrect.
This only happens on the derived dimensions, and the normal dimensions are ok.
After consulting colleagues, I learned that when querying a derived dimension, the data comes from a snapshot.
When merging, only the latest sanpshot is retained, and other snapshots are thrown away.
So what is the recommended solution for this scenario?
For example:
Fact table : FACT_DTAL
Dimension table : ORG_TREE (often changes) , BUS_TYP (not often changes)
sql:
select a.dte, a.org, a.bus_typ, b.org as org_id, b.org_nam, b.sup_org, b.org_nam_1, c.cod, c.nam
from FACT_DTAL a
left join ORG_TREE b
on a.dte=b.dte and a.org=b.org
left join BUS_TYP c
on a.bus_typ=c.cod
order by a.dte, a.org, a.bus_typ;
before meging :
after merging :