SCD: Record Expire Issue

Kirtikumar · Post by **Kirtikumar** » Tue Dec 13, 2011 5:21 am

Hi,

I searched before posting but could not find any solution to the problem I am facing.

I am trying to implement SCD Type on Empl data just for checking how the stage works. The table in Oracle has following columns:
SurrKey (SurrogateKey)
ENo (Buss Key)
Ename (Non Key - Type 2)
Address(Non Key - Type2)
EffStartDt
EffEndDt.

I am not using currnet indicator here. When I provide the properties as mentioned in the fastpath and run the job, it is not creating the record to be closed on the DimUpd link.

E.g.
Oracle table data

Code: Select all

surrKey   eno   ename   address   effStartDt    effEndDt
99001     1     A       Pune      01-Oct-2011   31-Dec-9999

From source I am getting a records as:

Code: Select all

surrKey   eno   ename   address
99001     1     ZZ      Pune

Now what I expect is I get two records at the output as:

Code: Select all

surrKey   eno   ename   address   effStartDt    effEndDt
99001     1     A       Pune      01-Oct-2011   13-Dec-2011
99111     1     ZZ      Pune      13-Dec-2011   31-Dec-9999

But I am just getting the second record on DimUpd link. I have made sure that on the step1 of the FastPath, eno is business key, ename is type2, address is type2, effStartDt is EffectiveStartDate(Type2) and effEndDate is EffectiveEndDate(Type2) along with a SurrKey column.

But still not getting the row that needs to be closed. Any suggestions?

Kirtikumar · Post by **Kirtikumar** » Tue Dec 13, 2011 9:58 pm

Any suggestion re the issue?

SURA · Post by **SURA** » Tue Dec 13, 2011 11:22 pm

Let us know what you have in fast path 1,2,4

DS User

Kirtikumar · Post by **Kirtikumar** » Tue Dec 13, 2011 11:41 pm

Fast path1: Link for output is FactLink.

Fast path2: Lookup page with keys as below:

SurrKey (SurrogateKey) - with empty Key expression
ENo (Buss Key) - Matched with Eno from SeqFile
Ename (Non Key - Type 2) - Matched with Ename from SeqFile
Address(Non Key - Type2) - Matched with Address from SeqFile
EffStartDt - Key expression empty
EffEndDt - Key Expression empty

Fast path3: SurrKey generation

Fast Path4:
SurrKey (SurrogateKey) - Derivation to routine NextSurrogateKey
ENo (Buss Key) - Derivation to Eno from seq file
Ename (Non Key - Type 2) - Derivation to Ename from seq file
Address(Non Key - Type2) - Derivation to Address from seq file
EffStartDt - CurrentDate()
EffEndDt - Derivation to '9999-12-31' and Expire Derivation to CurrentDate()

jwiles · Post by **jwiles** » Wed Dec 14, 2011 8:50 am

Where are you writing your dimension data to, a sequential file or directly to the database? If to the database, verify what the selected key column is in the input metadata to the Oracle stage. If it is the business key, you will see only the current record in the table after the job has run (if you are allowing the stage to generate your SQL statements).

The Upsert needs to use the surrogate key column as the key.

Regards,

Kirtikumar · Post by **Kirtikumar** » Thu Dec 15, 2011 3:05 am

I am writing to a dataset. And in that I do not see the row to be closed. I tried adding current indicator in addition to the ExpDate. But seems SCD stage allows only one of them to be used at a time.

Kirtikumar · Post by **Kirtikumar** » Fri Dec 16, 2011 2:27 am

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.

venkatvelpula · Post by **venkatvelpula** » Mon Jan 09, 2012 2:52 pm

What does that fact link contains? Under what circumstances we need to use that ouput? Is it mandatory output?