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 : > > >
