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 :
