The below post shows the an easy method to create a SCD TYPE 3 functionality.
Step 1 . Create a copy of the IKM Oracle Incremental Update and rename to IKM Oracle Incremental SCD Type 3
Step 2 . Add the following codes as a command and name it ” Historical Update Rows ” and scroll the level to before ” Update existing Rows ”
UPDATE <%=odiRef.getTable("L", "TARG_NAME", "A")%> T set <%=odiRef.getColList("", "T.[COL_NAME]", ",nt", "", "(UD2)")%> = <%=odiRef.getColList("", "T.[COL_NAME]", ",nt", "", "(UD1)")%> where (<%=odiRef.getColList("","T.[COL_NAME]", ", ", "", "UK")%>) in ( select <%=odiRef.getColList("","T.[COL_NAME]", ",nttt", "", "UK")%> from <%=odiRef.getTable("L", "TARG_NAME", "A")%> T , <%=odiRef.getTable("L", "INT_NAME", "A")%> S where <%=odiRef.getColList("","T.[COL_NAME]", ", ", "", "UK")%> = <%=odiRef.getColList("","S.[COL_NAME]", ", ", "", "UK")%> AND <%=odiRef.getColList("", "T.[COL_NAME]", ",nt", "", "(UD1)")%>!= <%=odiRef.getColList("", "S.[COL_NAME]", ",nt", "", "(UD1)")%> )
Step 3. Go to your interface and assign UD1 to Column having current Value say for ex Salary and UD2 to column to be maintained for historical value say for ex Previous Salary (Uncheck Insert and Update and check only UD2)
Step 4 . Lets take a look at an example for a complete overview.
Here i have two columns Salary and Previous Salary . I have marked the Salary as UD1 and previous_salary as UD2(uncheck Insert and Update and check only UD2)
I am updating the salary to 8210 and 7710 respectively at the source side .
Step 5 . Save and run the interface .
Let me change salary data for two records and lets see if it comes through.
It does. Have fun and look for odiexperts for more fun and interesting ideas.
November 26, 2016 at 4:37 AM
Hi
I need to implement SCD 3 in ODI 11g 11.1.1.7
business requirenment is such for column Activeindicator , OFFICIALINDICATOR ,EXPIRATIONDATE that if it is new record than
New_rec: Activeindicator = ‘Y’, OFFICIALINDICATOR = ‘Y’, EXPIRATIONDATE = NULL
and if existing record than
Existing_rec_1 load: Activeindicator = ‘N’, OFFICIALINDICATOR = ‘Y’, EXPIRATIONDATE = SYSDATE
Existing_rec_2 load: Activeindicator = ‘Y’, OFFICIALINDICATOR = ‘N’, EXPIRATIONDATE = NULL
Existing_rec_3 load: Activeindicator = ‘N’, OFFICIALINDICATOR = ‘N’, EXPIRATIONDATE = SYSDATE
I am not sure how to implement it.
Thanks
Ankit
November 28, 2016 at 12:45 PM
Hi,
I believe when you set the columns behavior all of this can be achieved.
Did you set the JKM and test it?
February 23, 2016 at 10:08 AM
Hi Aaravind,
Make sure you have checked “UK” in the interface > Quick Edit > CHeck “UK” unique key column
Make sure you have take the “Oracle Incremental Update” KM only to customize, else it would throw errors.
Hope this helps.
Thanks for the information Kshitiz…
Regards
Chandrashekar.Ch
August 6, 2015 at 8:59 AM
when i use ur code i am getting following error
ODI-1228: Task SCD_TYPE3 (Integration) fails on the target ORACLE connection ODI_STAGE.
Caused By: java.sql.SQLSyntaxErrorException: ORA-01747: invalid user.table.column, table.column, or column specification
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:462)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:405)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:931)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:481)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:205)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:548)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:217)
at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:1115)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1488)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3769)
at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3954)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1539)
at oracle.odi.runtime.agent.execution.sql.SQLCommand.execute(SQLCommand.java:163)
at oracle.odi.runtime.agent.execution.sql.SQLExecutor.execute(SQLExecutor.java:102)
at oracle.odi.runtime.agent.execution.sql.SQLExecutor.execute(SQLExecutor.java:1)
at oracle.odi.runtime.agent.execution.TaskExecutionHandler.handleTask(TaskExecutionHandler.java:50)
at com.sunopsis.dwg.dbobj.SnpSessTaskSql.processTask(SnpSessTaskSql.java:2913)
at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTask(SnpSessTaskSql.java:2625)
at com.sunopsis.dwg.dbobj.SnpSessStep.treatAttachedTasks(SnpSessStep.java:577)
at com.sunopsis.dwg.dbobj.SnpSessStep.treatSessStep(SnpSessStep.java:468)
at com.sunopsis.dwg.dbobj.SnpSession.treatSession(SnpSession.java:2128)
at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor$2.doAction(StartSessRequestProcessor.java:366)
at oracle.odi.core.persistence.dwgobject.DwgObjectTemplate.execute(DwgObjectTemplate.java:216)
at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor.doProcessStartSessTask(StartSessRequestProcessor.java:300)
at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor.access$0(StartSessRequestProcessor.java:292)
at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor$StartSessTask.doExecute(StartSessRequestProcessor.java:855)
at oracle.odi.runtime.agent.processor.task.AgentTask.execute(AgentTask.java:126)
at oracle.odi.runtime.agent.support.DefaultAgentTaskExecutor$2.run(DefaultAgentTaskExecutor.java:82)
at java.lang.Thread.run(Thread.java:745)
August 10, 2010 at 12:39 PM
Would appreciate if you can explain the type2 scd in ODI. i am facing problems in executing the same.
August 10, 2010 at 2:38 PM
Hi babu ,
Can you please tell us what is the error you are getting and what are steps you are doing.