SCD Type2 in ODI

SCD Type2 in ODI:

Hi folks!! Today we will discuss about SCD Type 2 implementation in ODI. So lets get started.

ODI Version: 11.1.1.6
OS: Windows 7

Create source table:
CREATE TABLE EMP_SCD_SRC AS SELECT * FROM EMP

Create Target Table:
CREATE TABLE SCOTT.EMP_SCD_TEST
(
EMPNO NUMBER(4),
ENAME VARCHAR2(10 BYTE),
JOB VARCHAR2(9 BYTE),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2),
START_DATE DATE,
END_DATE DATE,
CURRENT_FLAG NUMBER
)

Now reverse these two tables in ODI and create one interface as INT_SCD_TYPE2
scd1
Do the normal mapping in interface. Don’t forget to select the key column that is EMPNO 😀 .For last three column put
START_DATE= sysdate
END_DATE= to_date(‘29990101’, ‘YYYYMMDD’)
CURRENT_FLAG= 0 (0:old record, 1: Latest record)

scd2
Now open your target datastore and proceed as given in below screenshots. Select the OLAP type as Slowly Changing Dimension.
scd2_3
Then expand your datastore to get all columns. Open required column one by one and do the changes as given in below screenshots.
scd2_4

Next save everything and select IKM Oracle Slowly Changing Dimension in the flow tab. Similarly CKM Oracle in the control tab.
scd3
Save interface and run it.
Now check the loaded data on target.
scd2_2
Hmmm. There are 14 rows inserted. Lets do some modification in the source table to know whether its working properly or not. Here I am modifying SMITH name to SMITHHHHHH. Its means a new row/record should be added when name changes. Similarly update the salary of SCOTT to 9000. Here new record should not be added instead it should be updated as we have selected overwrite on change. After changes commit the datastore.
scd2_5
Run the interface again. Here you can see the same thing reflected as per our expectation. Its your turn now. Do the changes on source side and run the interface to get these data on target.
scd2_6

Thats all for today 🙂 Have a good day.

0

About the author

Bhabani(http://dwteam.in) - Currently Bhabani is working as Sr Development Engineer at Harman International. He has good expertise on Oracle, Oracle Data Integrator, Pervasive Data Integrator, MSBI, Talend and Java. He is also contributing in ODI-OTN forum for last 5 years. He is from India. If you want to reach him then please visit contact us page. If you have any doubts or concerns on the above article, please put your question here. Dw Team will try to respond it as soon as possible. Also dont forget to provide your comments / suggestions / feedback for further improvement. Thanks for your time.

Similar Posts

46 comments

Great. In my 1st attempt it worked though I was not sure . LOL. Can i do the same in CDC. What about type3 ?

Thanks Lewis for visiting my Blog. Where are you from ? I will post on SCD Type3 as soon as possible.

Its very clearly stated than other sites. It seems that its not that much complex what i was thinking before.
Great work.

Thats bad if we need to change the fetch size to zero. It should be fixed by them in the new version.

hi bhabani,
i followed the same steps as u did in SCD type2 but it is throwing some error regarding current_flag as “INVALID IDENTIFIER”. could you please let me know if surrogate key and natural key are must for SCD type2 ?

Please try dropping your E$ table manually. Also check the number of columns in C$ table and I$ table. Run generated code in toad to get some clue.

KellyJeanne Pyne / Reply

I have tried what you suggested and am still getting this error. does the staging and target have to be in the same location or will this work using an LKM Oracle to Oracle as well?

Hi Kelly,
What error your are getting ?

Hi Bhabaniranjan,
It’s amazing. Thaks for the same.
I’d like to know if this approach is really used to implement the SCDs. I’ve worked in Informatica and Microsoft SSIS ETL tools. We don’t use the built-in transformation to do the same. We would normally follow the below steps :

get the changed data from the source and stage to a table.
And then use a Merge plsql proc to implement the same
also use look up transformation etc

Could you please let us know the best or normal way to implement this.

If I will be in your case I will go with above approach which is very flexible in ODI.. But still it depends on your requirement and business analyst..If you will ask me then I will go with ODI. More on this you can refer
http://docs.oracle.com/cd/E28271_01/integrate.1111/e12645/ikm.htm#BABJGDJD.

Thanks

Thanks Bhabani…ur blog is really helpful 🙂

hi,

I am following th same steps but getting an error: “Insert flow into I$ table”

ODI-1228: Task scd type 2 (Integration) fails on the target ORACLE connection twitter.
Caused By: java.sql.SQLSyntaxErrorException: ORA-00936: missing expression

Please help

Hi Richa,
Please copy the generated code to toad or sqldeveloper and run it. I am sure you will get where exactly the error lies. If you still cant get it solve then send me one mail. Thanks.

really nice..it will better if u explain it more like u have exaplained in scd type3..This will be helpful for freshers instead of searching the theory again in google.

It’s great with lucid instruction! Thanks a lot for your sharing. All best wishes to you.

thanks Bhabani,
ur post was helpful!!
it worked without error…
would u plz elaborate type 1 & 3 as well..

Hi Gauri, Welcome to my blog.
You can refer http://dwteam.in/scd-type3-in-odi/
for scd type3

Hi Bhabani,

I have followed the same procedure, but the CURRENT_FLAG NUMBER is showing as 1 both for old and new records. please suggest.

Regards,
PJP

Have you selected the current_flag in the slowly changing dimension behavior ? You might have missed it and the hard coded value might be coming everything time.
Please double check all the steps again as per my post.

Hi Bhabani,

I have followed the same procedure, but the CURRENT_FLAG NUMBER is showing as 1 both for old and new records.I Have selected the current_flag in the slowly changing dimension behavior AS CURRENT CORD FLAG also.
please suggest.

Regards,
vas

Vasu, I am not able to find the other possibilities for this issue. Last option would be the team viewer session. Sounds good?

Hi,

Can u pls clarify one thing,

example.
I have One column name like VERSION in one table , and have natural key column like INTEGRATION_ID in same table.
MAPPING CONDITION FOR INTEGRATION_ID IS (NAME_ID||’~’||VERSION)

I have applied VERSION AS ADD ROW ON CHANGE AND INTEGRATION_ID AS NATURAL KEY PROPERTY.

In this case , whenever VERSION GOT UPDATED FROM SOURCE, I’M ABLE STORE THE OLD AND NEW VERSION RECORDS BUT CURRENT_FLG POPULATING 1 FOR BOTH RECORDS.

Can we use ADD ROW ON CHANGE PROPERTY COLUMN(VERSION) IN THE natural key PROPERTY COLUMN(INTEGRATION_ID)?

Thanks,
Vas

Hi Bhabani,

Can you pls explain the way of processing xml files from remote server with ODI?

Please help me on this. if possibles explain with screen shots like SCD Type2.

Thanks,
Vas

Hi,

Can you pls help me on this.

Thanks,
Vasu

hi vasu, i dont have much time to write a post on this. Can you please tell me what challenges you are facing with xml files ? Make sure the file is accessible to your agent.

Hi,

While reverse engineering , i’m getting error with xml file …it shows that error like

ODI-20131 -can’t retrive the table list.verify that you write permissions in the directory.

Actually i have placed my xml files in the UNIX directory.
for this verification , what and how i need to check?

Could pls help on this.

Thanks,
Vasu

Give 777 permission to the file and directory and verify again.

Hi,

Thanks for Update.

I have provided 777 permissions already,even though i got this error.

Thanks,
Srinivas

Give full permission on the complete directory and let me know

Hi,

I’m trying to process multiple xml files.for

JDBC URL, I have given like below

#variable1/#variable2

Note:Variable1 for files path
:Variable2 for files name

and in my flow i’m using SYNCHRONIZE FROM FILE command in one procedure for refreshing, but while executing my package, it showing like

“ODI-1228: Task PROC_FILE_REFRESH (Procedure) fails on the target XML connection SPM_DS.
Caused By: java.sql.SQLException: unknown token ”

But when i tried with individual xml files instead of Variable, this procedure working fine.

Can u pls help me on this.

Thanks,
Vasu

Put these two variables as the 1st and second step of your package and make sure values are assigned to it.

Great Article, if I have to think of the pl/sql scripts I had to write to be able to load scd t2.

I am not sure if I am doing somethign wrong though. That data loads fine but it seems to me that when history is created that it calculated the end date of the previous record incorrectly.
For instance for a specific account I got a record that run from say 1/1/2013 till 3/1/2013 and then another till 5/1/2013 etc.

When the data is processed the records are as following: from 1/1/2013 till 3/1/2013
from 3/1/2013 till 5/1/2013
from 5/1/2013 till 12/31/9999

The problem with this is when you lookup a transaction for 3/1 or 5/1 two dimensional records will be returned.
The end date should always be 1 day less then the next records start date.

Any idea on how to fix this?

Corne

Great doc. Really helpful. thnks.

Hi,
I am using SCD type 2 in odi. My source and target table have same primary key defined.when i use SCD type2,it throws an error because of duplication of primary key.that means cant we have primary key in target table or surrogate key as primary key in target table??please suggest..
Thanks,
avani

Hi Bhabani,

very good post. i tried it as it is mentioned here and it worked successfully. thanks for sharing.
I am trying new stuff in ODI and following your posts.

Thank you Devika. Feel free to ask your doubts.

We are trying to load historical data and as Steve said in earlier thread: my source can contain multiple rows which need to be inserted once, and then updated several times in my target – this SCD KM will generate multiple inserts – and no updates (set the current flag =’0′ and eff_to_dt = SYSDATE to the previous record in this current load) – since at the time it determines insert/update, the record does not exist in the target – and therefore all source records get flagged for insert.
for we have PLCY_DIM target table with below fields:
PLCY_SK – Surrogate KEy
PLCY_NUM (Natural Key)
PLCY_TERM (Add row on Change)
PLCY_EFF_DT (Add row on Change)
PAY_PLAN_TYPE(Add row on Change)
ROW_EFFECTIVE_FROM_DATE (Starting Timestamp)
ROW_EFFECTIVE_TO_DATE (Ending Temestamp)
ROW_CURRENT_FLAG (1 for new record, 0 for old record)
I am trying to load history for the same PLCY_NUM (Nat Key) and all the records need to be inserted and update the below fields for old records and insert the new records for these updates:
ROW_EFFECTIVE_TO_DATE (Ending Temestamp) —> TRUNC(SYSDATE) to old record and ’31-DEC-9999′ for the new record (for the update)
ROW_CURRENT_FLAG (1 for new record, 0 for old record) —> 0 to old record and 1 to new (updated record).
when I use IKM Oracle Slowly Changing Dimension, I see all the records inserted for the same PLCY_NUM with ROW_EFFECTIVE_TO_DATE to ’31-DEC-9999′ and ROW_CURRENT_FLAG =1 but I want previous records to be updated on the fly and I want to see only one record as latest record.
could you please advice how to accomplish this?
Thanks in advance.
Janardhan

Hi,
In my SCD Type 2 logic, is there a way to track the version number also?

Ex: My target table should look like below:

cust_id vrsn_bgn_dt vrsn_end_dt flag vrsn_num
100 02/01 03/05 N 1
101 06/01 09/05 Y 1
102 04/07 08/05 Y 2

Everything is working, except the vrsn_num logic.

Any suggestions.

Thanks,
Divya

Gauri Chaudhari / Reply

Thanks Bhabani….
ur blog is awesome!

pplz post link for scd type1 and 3

Hi Gauri,
For scd type1, you just use incremental update KM.
For scd type3, http://dwteam.in/scd-type3-in-odi/

Hi Bhabani,
I have the below scenario:
My Source Data

Store_ID Store_street_address Store_city Store_state Store_region First_open_date

ST0001, ‘4, Candy Street’, BAN, KAR ,SOUTH ,1/9/2014 21:55

My Target Data

Store_Key Store_id Store_add, Store_city,Store_State,Eff_date,End_Date,Active_Flg

1,ST0001, ‘4, Candy Street’, BAN, KAR ,SOUTH ,1/9/2014 21:55,1/1/2400,1

When I modify Street Address, below will be the snapshot of target with the usage of IKM SCD:

Store_Key Store_id Store_add, Store_city,Store_State,Eff_date,End_Date,Active_Flg

1,ST0001, ‘4, Candy Street’, BAN, KAR ,SOUTH ,1/9/2014 21:55,1/15/2014,0 -> Here I want to populate End_Date as 1/14/2014(subtract 1 day from the effective date of next active record)

2,ST0001, ‘5, Food Street’, BAN, KAR ,SOUTH ,1/15/2014 21:55,1/1/2400,1 -> Here I want to populate null for End_Date

Can you please provide any pointers on the same.

My Source Data

Store_ID Store_street_address Store_city Store_state Store_region First_open_date

ST0001, ‘4, Candy Street’, BAN, KAR ,SOUTH ,1/9/2014 21:55

My Target Data

Store_Key Store_id Store_add, Store_city,Store_State,Eff_date,End_Date,Active_Flg

1,ST0001, ‘4, Candy Street’, BAN, KAR ,SOUTH ,1/9/2014 21:55,1/1/2400,1

When I modify Street Address, below will be the snapshot of target with the usage of IKM SCD:

Store_Key Store_id Store_add, Store_city,Store_State,Eff_date,End_Date,Active_Flg

1,ST0001, ‘4, Candy Street’, BAN, KAR ,SOUTH ,1/9/2014 21:55,1/15/2014,0 -> Here I want to populate End_Date as 1/14/2014(subtract 1 day from the effective date of next active record)

2,ST0001, ‘5, Food Street’, BAN, KAR ,SOUTH ,1/15/2014 21:55,1/1/2400,1 -> Here I want to populate null for End_Date
Could you please provide any pointers on the same

Hi Sharmila,
Open your IKM and go to Historize old rows step and put -1 as I have added here. Dont copy the whole code. Just put -1 in your KM. If you are not getting then control + F ( for -1).

update  <%=odiRef.getTable("L","TARG_NAME","A")%> T
set     (
        <%=odiRef.getColList("", "T.[COL_NAME]", ",\n\t", "", "(SCD_FLAG and REW)")%>
<%if ((odiRef.getColList("", "A", "", "", "(SCD_FLAG and REW)").length() != 0) && (odiRef.getColList("", "B", "", "", "(SCD_END and REW)").length() != 0)){out.print("  ,");}%>
        <%=odiRef.getColList("", "T.[COL_NAME]", ",\n\t", "", "(SCD_END and REW)")%>
        ) =     (
                select  <%=odiRef.getColList("", "0", ",\n\t\t\t", "", "(SCD_FLAG and REW)")%>
<%if ((odiRef.getColList("", "A", "", "", "(SCD_FLAG and REW)").length() != 0) && (odiRef.getColList("", "B", "", "", "(SCD_END and REW)").length() != 0)){out.print("                  ,");}%>
<%if (odiRef.getColList("", "C", "", "", "(SCD_END and REW)").length() != 0){
        out.print(odiRef.getColList("\t\t\t", "S.[COL_NAME]", ",\n\t\t\t", "", "(SCD_START and REW)"));
} %> 1
                from    <%=odiRef.getTable("L","INT_NAME","A")%> S
                where   <%=odiRef.getColList("", "S.[COL_NAME]\t= T.[COL_NAME]", "\n\t\tand\t", "", "SCD_NK")%>
                and     S.IND_UPDATE    = ‘I’
                )
where   (<%=odiRef.getColList("", "T.[COL_NAME]", ", ", "", "(SCD_NK)")%>)
        in      (
                select  <%=odiRef.getColList("", "X.[COL_NAME]", ", ", "", "(SCD_NK)")%>
                from    <%=odiRef.getTable("L","INT_NAME","A")%> X
                where   X.IND_UPDATE    = ‘I’
                )
<%=odiRef.getColList("and\t", "T.[COL_NAME]\t= 1", "\nand\t", "", "(SCD_FLAG)")%>
<%=odiRef.getColList("and\t","[COL_NAME]\t= to_date (’01-01-2400′, ‘mm-dd-yyyy’)","\nand\t","","SCD_END")%>

Thanks a lot Bhabani for quick response. Solution works out perfectly 🙂

Hello everyone,

i have a Question. How can i alter SCD2 behavior in a Knowledge Module. for example: i add a new column in my table in the knowledge Module and the behavior is set on undefined. i want to set it in my KM on Ending timestamp.

Thanks

Manjith Gunatilaka / Reply

Hi

I am having a Oracl SCD 2 performance issue in ODi 12c. Any thought of that. If a mapping has a more joiners and lookups the performance is really bad.

Leave a replay to Seshi Cancel reply

required*

Are you a human? *