ODIExperts.com

The blog for Oracle Data Integrator ( ODI )

SCD TYPE 3

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)")%>
        )

image

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)

image

image

image

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.

image

It does. Have fun and look for odiexperts for more fun and interesting ideas.

6 Comments

  1. 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

  2. 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

  3. 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)

  4. Would appreciate if you can explain the type2 scd in ODI. i am facing problems in executing the same.

Leave a Reply

Required fields are marked *.