Finally after trying a log of things I was able to get proper results and here are the things I did to fix it. I was able to make it work for both Curr_Ind and Expiry Date wise Type 2.
The issue was the keys defined. Earlier I was matching eno, ename and address of Dimension table to the data from incoming file. When I changed this and only mapped eno, it worked fine for CurrInd. While doing for CurrInd I had removed EffStartDt and EffEndDt columns. So what happens is - the non key columns (type 2 or 1 with are not business/natural key), are matched based on their names, we do not need to provide the mapping for them. So the newly defined mapping is:
Code: Select all
SurrKey (SurrogateKey) - with empty Key expression
ENo (Buss Key) - Matched with Eno from SeqFile
Ename (Non Key - Type 2) - DO NOT MATCH. Key expression empty AND make column names same on dimension and seq file
Address(Non Key - Type2) - DO NOT MATCH. Key expression empty AND make column names same on dimension and seq file
CurrInd - Key expression empty
With this I got proper closed records as well a new records with update data and SurrKey.
After that I tried doing that for EffStartDt and EffEndDt. And I was getting an error "Lookup table is empty, no further warnings will be issued". The output was again not proper.
The issue was - As I was testing, The EffEndDt in dimension table was set to '3099-12-31' and the EffEndDt expire derivation was '9999-12-31'. So may be while construction the dimension lookup table only rows from dimension table where the EffEndDt matched with the value defined in "Expire" derivation column are considered. And as the value in table was not matching with '9999-12-31', the lookup was empty.
Then I updated dimension effEndDt to '9999-12-31' and it worked fine.
So two important things I learned to use this stage:
1. When defining mapping, only show mapping for Business/Natural key and make sure the not key columns/attributes have same column name on both the links.
2. Make sure the "Expire" derivation for EffEndDt or CurrInd columns is same as what used in the Dimension table.