SCD Type3 in ODI

SCD Type3 in ODI:

Today we will discuss SCD Type3 in ODI. Before moving to ODI we need to understand what is SCD Type3.

In Type 3 Slowly Changing Dimension, there will be two columns to indicate the particular attribute of interest, one indicating the original value, and one indicating the current value. There will also be a column that indicates when the current value becomes active.

In our example, recall we originally have the following table:

EmpNo Name Sal
1001 Scott 3000

To accommodate Type 3 Slowly Changing Dimension, we will now have the following columns:

  • EmpNo
  • Name
  • CurSal
  • PrevSal
  • Effective Date

After Scott salary changed to 5000, the original information gets updated, and we have the following table (assuming the effective date of change is January 1, 2010):

EmpNo Name CurSal PrevSal Effective Date
1001 Scott 5000 3000 1-JAN-2010

Advantages:
– This does not increase the size of the table, since new information is updated.
– This allows us to keep some part of history.
Disadvantages:
– Type 3 will not be able to keep all history where an attribute is changed more than once. For example, if salary changed 4 times in 4months, the last 3 salary information will be lost.
Usage:
Type 3 is rarely used in actual practice.
When to use Type 3:
Type III slowly changing dimension should only be used when it is necessary for the data warehouse to track historical changes, and when such changes will only occur for a finite number of time.

Okay. I hope you are clear now. Its time to implement this in ODI. Create the target table as given in below query.
CREATE TABLE EMP_SCD_T3
(
EMPNO NUMBER(4) PRIMARY KEY,
ENAME VARCHAR2(10 BYTE),
JOB VARCHAR2(9 BYTE),
MGR NUMBER(4),
HIREDATE DATE,
CURSAL NUMBER(7,2),
PREVSAL NUMBER(7,2),–Column going to store historical data
COMM NUMBER(7,2),
DEPTNO NUMBER (2)
)
Now reverse EMP and EMP_SCD_T3 in your model.Create an interface INT_SCD_TYPE3 and drag source datastore EMP and target datastore EMP_SCD_TYPE3 to their respective place in the interface.Do the normal mapping and select CKM oracle and IKM sql incremental Update.

Now certain changes you have to do as per below screenshot.Open your IKM Sql Incremental Update KM and go to step 140.Duplicate it to create a new step.

In the above step (Update previous Data of T3 Column) put below codes.

( Please download the KM from here. If you are copying the code then replace exiting single,double quote in your keyboard.)

UPDATE <%=odiRef.getTable(“L”, “TARG_NAME”, “A”)%> T
set <%=odiRef.getColList(“”, “T.[COL_NAME]”, “,\n\t”, “”, “(UD2)”)%> =<%=odiRef.getColList(“”, “T.[COL_NAME]”, “,\n\t”, “”, “(UD1)”)%>
where (<%=odiRef.getColList(“”,”T.[COL_NAME]”, “, “, “”, “UK”)%> )
in (
select <%=odiRef.getColList(“”,”T.[COL_NAME]”, “,\n\t”, “”, “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]”, “,\n\t”, “”, “(UD1)”)%>!=<%=odiRef.getColList(“”, “S.[COL_NAME]”, “,\n\t”, “”, “(UD1)”)%>
)

Now click on command on source and delete whatever codes you have there. These codes have no use now.

After modification save it.Then select the PREVSAL column on targetside. In the property inspector deselect INSERT & UPDATE checkbox. Select UD2 user defined flag.
Similarly select CURSAL column on target side and mark the checkbox for UD1.Now save everything.

Now run the interface and check loaded data on target table. You can see the PREVSAL column is blank because this is the 1st run.
This is the code generated by our edited update step in the IKM.
 Now change the salary of SMITH to 9000. After the commit run the interface.

Here it is.You can see the PREVSAL column updated with 800 as this is the old salary for SMITH. Similarly we changed the salary on source side and it got reflected on target side.

That’s all friends. Its pretty simple.

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

16 comments

Thanks buddy, I really appreciate the time you take to post this.

Hey, what about SCD4 ?

Good post and nice information.

Hi

Is there any alternate method to implement scd type 3 without customozing the IKM

hi…thanx for post but when i m trying for same it give following error:

ODI-1227: Task SCD3_INF (Integration) fails on the source ORACLE connection NEW_PROJECT_ASHVINI_TRG.
Caused By: java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist

at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:91)
at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:133)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:206)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:455)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:413)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:1035)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:194)
at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:791)
at oracle.jdbc.driver.T4CPreparedStatement.executeMaybeDescribe(T4CPreparedStatement.java:866)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1188)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3386)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3430)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1491)
at com.sunopsis.sql.SnpsQuery.executeQuery(SnpsQuery.java:602)
at com.sunopsis.dwg.dbobj.SnpSessTaskSql.executeQuery(SnpSessTaskSql.java:3078)
at com.sunopsis.dwg.dbobj.SnpSessTaskSql.execCollOrders(SnpSessTaskSql.java:571)
at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTaskTrt(SnpSessTaskSql.java:2815)
at com.sunopsis.dwg.dbobj.SnpSessTaskSqlI.treatTaskTrt(SnpSessTaskSqlI.java:68)
at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTask(SnpSessTaskSql.java:2515)
at com.sunopsis.dwg.dbobj.SnpSessStep.treatAttachedTasks(SnpSessStep.java:534)
at com.sunopsis.dwg.dbobj.SnpSessStep.treatSessStep(SnpSessStep.java:449)
at com.sunopsis.dwg.dbobj.SnpSession.treatSession(SnpSession.java:1954)
at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor$2.doAction(StartSessRequestProcessor.java:322)
at oracle.odi.core.persistence.dwgobject.DwgObjectTemplate.execute(DwgObjectTemplate.java:224)
at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor.doProcessStartSessTask(StartSessRequestProcessor.java:246)
at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor.access$0(StartSessRequestProcessor.java:237)
at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor$StartSessTask.doExecute(StartSessRequestProcessor.java:794)
at oracle.odi.runtime.agent.processor.task.AgentTask.execute(AgentTask.java:114)
at oracle.odi.runtime.agent.support.DefaultAgentTaskExecutor$2.run(DefaultAgentTaskExecutor.java:82)
at java.lang.Thread.run(Thread.java:619)

Please download the KM instead copying the code. Test it and let me know if you face any issue.

Hi,

Thanks for your post. I tried with this but i am getting an error like
ODI-1217: Session SCD_TYPE3_TEST (110025) fails with return code 942.
ODI-1226: Step SCD_TYPE3_TEST fails after 1 attempt(s).
ODI-1240: Flow SCD_TYPE3_TEST fails while performing a Integration operation. This flow loads target table EMP_SCD_T3.
ODI-1227: Task SCD_TYPE3_TEST (Integration) fails on the source ORACLE connection DB.
Caused By: java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist

at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:457)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:405)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:889)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:476)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:204)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:540)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:217)
at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:924)
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1261)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1419)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3752)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3806)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1667)
at oracle.odi.query.JDBCTemplate.executeQuery(JDBCTemplate.java:189)
at oracle.odi.runtime.agent.execution.sql.SQLDataProvider.readData(SQLDataProvider.java:89)
at oracle.odi.runtime.agent.execution.sql.SQLDataProvider.readData(SQLDataProvider.java:1)
at oracle.odi.runtime.agent.execution.DataMovementTaskExecutionHandler.handleTask(DataMovementTaskExecutionHandler.java:67)
at com.sunopsis.dwg.dbobj.SnpSessTaskSql.processTask(SnpSessTaskSql.java:2906)
at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTask(SnpSessTaskSql.java:2609)
at com.sunopsis.dwg.dbobj.SnpSessStep.treatAttachedTasks(SnpSessStep.java:537)
at com.sunopsis.dwg.dbobj.SnpSessStep.treatSessStep(SnpSessStep.java:453)
at com.sunopsis.dwg.dbobj.SnpSession.treatSession(SnpSession.java:1740)
at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor$2.doAction(StartSessRequestProcessor.java:338)
at oracle.odi.core.persistence.dwgobject.DwgObjectTemplate.execute(DwgObjectTemplate.java:214)
at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor.doProcessStartSessTask(StartSessRequestProcessor.java:272)
at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor.access$0(StartSessRequestProcessor.java:263)
at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor$StartSessTask.doExecute(StartSessRequestProcessor.java:822)
at oracle.odi.runtime.agent.processor.task.AgentTask.execute(AgentTask.java:123)
at oracle.odi.runtime.agent.support.DefaultAgentTaskExecutor$2.run(DefaultAgentTaskExecutor.java:82)
at java.lang.Thread.run(Thread.java:662)
The same error which Ashvini patil goton above. Please let me know how to resolve this?.

Thanks in advance!

-Rakesh

Hi Ashvini,

Please download the KM from above mentioned link and extract the rar file then you will have one KM xml file there. Please import that xml file into your KM in odistudio. I did the same and it worked for me. Please you also try to do the same. here are the update codes

UPDATE T

set =

where ()

in (

select

from T , S

where = AND

!=

)

Thank you so much Bhabani for the help.

-Rakesh

Hi Bhabani, can you please tell me the use of User Defined Flags UDF0, UDF1 etc

Its very simple. Suppose you want to use one order by in the generated query. So what you can do here. 1. First you may want to hardcode the column like ” order by EMPNO ”
2. But 1st option is not good idea. Instead what you can do is that mark that column as UD1. Inside KM you can use < %=snpRef.getColList("","[EXPRESSION] ","","","UD1")%>. Now here i am not hardcoding anything.

So basically when you want to perform some action on selected column, use UD flag.

Thanks Bhabani for your explanation very much appreciated

Hi Bhabani,
I appreciate your efforts.
Would you please guide me how to understand and learn the odiRef APIs used in the KMs. I want to familiarize myself how to custom edit the KMs as per needs.

Thank you ,
Raghav

If you understand scriptlet in java it would be easier for you to understand. Scriptlet normally comes in servlet/jsp. Let me know how can I help you in this.
Thanks.

Hi Bhabani,

I tried your modified code but no luck but I used below code and it is working fine

update T
set =
where
in (
select
from
T, S
where = AND
!=
)

I am getting below error message when I used your code on Update process data T# column step on command on target

Error Message:
————–

BeanShell script error: Sourced file: inline evaluation of: “out.print(“UPDATE “) ; out.print(odiRef.getTable(\u201cL\u201d, \u201cTARG_NAME\ . . . ” Token Parsing Error: Lexical error at line 2, column 27. Encountered: “\u201c” (8220), after : “”:
BSF info: Update previous data T3 column at line: 0 column: columnNo
out.print(“UPDATE “) ;
out.print(odiRef.getTable(\u201cL\u201d, \u201cTARG_NAME\u201d, \u201cA\u201d)) ;
out.print(” T\nset “) ;
out.print(odiRef.getColList(\u201c\u201d, \u201cT.[COL_NAME]\u201c, \u201c,\\n\\t\u201d, \u201c\u201d, \u201c(UD2)\u201d)) ;
out.print(” =”) ;
out.print(odiRef.getColList(\u201c\u201d, \u201cT.[COL_NAME]\u201c, \u201c,\\n\\t\u201d, \u201c\u201d, \u201c(UD1)\u201d)) ;
out.print(“\nwhere (“) ;
out.print(odiRef.getColList(\u201c\u201d,\u201dT.[COL_NAME]\u201c, \u201c, \u201c, \u201c\u201d, \u201cUK\u201d)) ;
out.print(” )\nin (\nselect “) ;
out.print(odiRef.getColList(\u201c\u201d,\u201dT.[COL_NAME]\u201c, \u201c,\\n\\t\u201d, \u201c\u201d, \u201cUK\u201d)) ;
out.print(“\nfrom “) ;
out.print(odiRef.getTable(\u201cL\u201d, \u201cTARG_NAME\u201d, \u201cA\u201d)) ;
out.print(” T, “) ;
out.print(odiRef.getTable(\u201cL\u201d, \u201cINT_NAME\u201d, \u201cA\u201d)) ;
out.print(” S\nwhere “) ;
out.print(odiRef.getColList(\u201c\u201d,\u201dT.[COL_NAME]\u201c, \u201c, \u201c, \u201c\u201d, \u201cUK\u201d)) ;
out.print(” =”) ;
out.print(odiRef.getColList(\u201c\u201d,\u201dS.[COL_NAME]\u201c, \u201c, \u201c, \u201c\u201d, \u201cUK\u201d)) ;
out.print(” AND\n!=”) ;
out.print(odiRef.getColList(\u201c\u201d, \u201cS.[COL_NAME]\u201c, \u201c,\\n\\t\u201d, \u201c\u201d, \u201c(UD1)\u201d)) ;
out.print(“\n)\n”) ;

****** ORIGINAL TEXT ******
UPDATE T
set =
where ( )
in (
select
from T, S
where = AND
!=
)

Regards,
Phanikanth

If you see the error message, you can find out the culprit. It is the devil character. Please replace the double quote and single quote in your keyboard.

Hi Bhabani

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)

Leave a replay to Surya Cancel reply

required*

Are you a human? *