Slowly Changing Dimension stage: Anybody encountered this?
Posted: Thu Nov 08, 2018 5:40 pm
I have a dimension table (Dim1) being updated using Type 2. The existing data of Dim1 (based on certain criteria) is being used as the Reference set (Ref1).
Between runs of the DataStage job to load Dim1, some of the rows got updated in Ref1 (Dim1 based on certain criteria). Now, in the next run of the DataStage job to load the dimension, rows which are not in the Source set (Src1) are being inserted into Dim1 and existing rows are being expired.
For example:
Reference set (Ref1): (Select * from Dim1 where DataCol2=X): BK1=1,2,3,4,5,6,7
Source set (Src1): BK1=2,3,4
After the run of the DataStage job, BK1=5 record is being inserted into Dim1 (even though it doesn't exist in Src1) and the previous record is being expired.
Any idea why?
Between runs of the DataStage job to load Dim1, some of the rows got updated in Ref1 (Dim1 based on certain criteria). Now, in the next run of the DataStage job to load the dimension, rows which are not in the Source set (Src1) are being inserted into Dim1 and existing rows are being expired.
For example:
Reference set (Ref1): (Select * from Dim1 where DataCol2=X): BK1=1,2,3,4,5,6,7
Source set (Src1): BK1=2,3,4
After the run of the DataStage job, BK1=5 record is being inserted into Dim1 (even though it doesn't exist in Src1) and the previous record is being expired.
Any idea why?