ok, thanks, Billy Liu !
------------------ ???????? ------------------ ??????: "Billy Liu"<[email protected]>; ????????: 2019??12??26??(??????) ????8:44 ??????: "user"<[email protected]>; ????: 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 <[email protected]> ??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"<[email protected]>; Date: Wed, Dec 25, 2019 01:45 PM To: "user"<[email protected]>; 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 :
