Hi Guys,

Today we are going to talk about an old question that we already got several, hundreds, thousands (lol) of questions but, at true, is pretty easy!

I would like to indroduce you to the Mr. SCD!!!!!

In few lines, you will be able to configurate and use it…. Let’s start:

This post defines the necessary setting to use the Oracle Slowly changing dimension .

Set the columns with required properties namely,

SURROGATE_KEY – Sequence

NATURAL_KEY – Primary or Unique Key

OVERWRITE ON CHANGE – Column for which data needs to be updated

ADD ROW ON CHANGE – Column for which a new Surrogate Key and New rows to generated when the data for this Column changes

CURRENT RECORD FLAG – 0 or 1  .  The KM populates this column so no need to populate this column .(1 : Current Record , 0 : Past Records)

STARTING TIMESTAMP – Column  indicating the beginning of a Record’s availability. use SYSDATE or SYSTIMESTAMP depending on the datatype.

ENDING TIMESTAMP -Column indicating the end of the Record’s availability. The value is a default of “01 January 2400″

Map the columns in this way where the Surrogate Key needs to be sequence .  The ending timestamp and Current Flag columns  need to be mapped even that the  KM will populate those column accordingly. Just add any value to them.

Note :

1. If in case you don’t  have any ‘Overwrite on change‘ column , please check the option ignore errors on Update Existing row., since in the select statement it wont find the column to do query and will error out.

2. If the Target datastore is table , Flow control or static control have to yes and the appropriate CKM have to be selected , else there will be error in Historize old rows saying ‘invalid user.table.column, table.column, or column specification”.

Well, as you can see, no secrets to use it!!!!

There are lots of time , we have issues in dealing with huge load  and we break the process in to multiple steps like First unloading using OdiSqlunload or some native Unloader and then using the SQLLDR to load the file . The process can be time consuming and some time we don’t get the exact result.

To solve these part we have integrated the complete OdiSqlUnload with SQLLDR into one LKM with support to load directly into Target table or into the Work table as required. We have tried to make these as smart and easy it can be  like single KM for both unix and windows environment , Sqlldr log in the operator so you dont have to log into the server to check what is the error due to , Filter or join multiple tables yet getting the performance of odisqlunload .

This KM is created to move huge data from source to Target using ( odisqlunload to unload table into File ) and Loading this file using SQLLDR to oracle target , and is based on the integration of OdiSqlUnload and LKM File to Oracle ( SQLLDR ) .

The LKM can be used to load into C$ or directly into the Target Table.

The Options  are as follows

CREATE_WORK_TABLES
This option enables you to create appropriate Control File ,so that SQLLDR can load directly into the Target Table else into Work Table ( C$_). By Default  WORK_TABLES  value is  NO (ie. No C$ will be created will be loaded directly into Target table using Sqlldr )
IKM Dummy is required if  WORK_TABLES=NO else select the appropriate IKM.

DIR_PATH
Please  select  the appropriate Directory where the OdiSqlUnload can unload(File)  the data from Source Tables.

DELETE SQLLDR FILES
This options enables you to delete the SQLLDR  and OdiSqlUnload Files .

DATE_FORMAT
Please enter the valid Date format used by SQLLDR to convert the date into appropriate Format.
The default Format is YYYY-MM-DD HH24:MI:SS

TIMESTAMP_FORMAT

Please enter the Valid Timestamp format used by SQLLDR to convert the Timestamp into appropriate Format.
The default Format is YYYY-MM-DD HH24:MI:SS.FF

UNIX_ENVIRONMENT
Please select the  Environment  where the OdiSqlUnload will unload  ( Unix / Windows ) used into CTL file.
This options enables to have for Unix  (ODISQLUNLOAD) ROW_SEP =\n and in(CTL file) =str X’0A’
and for Windows  (ODISQLUNLOAD) ROW_SEP =\n\r and in(CTL file) =str X’0D0A’ .

Also for deleting files according to the environment( rm / del )

The commands of the LKM are as follows -

UNLOADING TABLE

Unloading the source table into file ,delimited by (,) comma using OdiSqlUnload

OdiSqlUnload "-FILE=<%=odiRef.getOption("DIR_PATH")%>/
<%=odiRef.getSrcTablesList("","[TABLE_NAME]","","")%>.dat"
"-DRIVER=<%=odiRef.getInfo("SRC_JAVA_DRIVER")%>"
"-URL=<%=odiRef.getInfo("SRC_JAVA_URL")%>"
"-USER=<%=odiRef.getInfo("SRC_USER_NAME")%>"
"-PASS=<%=odiRef.getInfo("SRC_ENCODED_PASS")%>"
"-FILE_FORMAT=VARIABLE" "-FIELD_SEP=,"
"-ROW_SEP=<% if (odiRef.getOption("UNIX_ENVIRONMENT").equals("1"))
{ %>\n<%} else { %>\r\n<% } %>"
"-CHARSET_ENCODING=ISO8859_1" "-XML_CHARSET_ENCODING=ISO-8859-1"
"-FETCH_SIZE=<%=odiRef.getOption("FETCH_SIZE")%>"
"-QUERY=select <%=odiRef.getColList("", "[EXPRESSION]", ", ", "", "")%>
<%=odiRef.getColList("", "[EXPRESSION]", "||','||", "", "")%> from <%=snpRef.getFrom()%>
where (1=1) <%=snpRef.getJoin()%>
<%=snpRef.getFilter()%> <%=snpRef.getGrpBy()%> <%=snpRef.getHaving()%>"

GENERATE CTL

If the work tables option – yes or No , then accordingly the CTL is created ie. whether the table to be loaded is C$ or Target table.

SnpsOutFile "-File=<%=odiRef.getOption("DIR_PATH")%>/
<%=odiRef.getSrcTablesList("", "[TABLE_NAME].ctl", "", "")%>"
OPTIONS (
	SKIP=<%=odiRef.getSrcTablesList("", "[FILE_FIRST_ROW]", "", "")%>,
	ERRORS=<%=odiRef.getUserExit("LOA_ERRORS")%>,
	DIRECT=<%=odiRef.getUserExit("LOA_DIRECT")%>
	)
LOAD DATA
INFILE "<%=odiRef.getOption("DIR_PATH")%>/
<%=odiRef.getSrcTablesList("", "[RES_NAME]", "", "")%>" "str X'
<% if (odiRef.getOption("UNIX_ENVIRONMENT").equals("1")) { %>0A<%} else {%>0D0A<% } %>'"
BADFILE "<%=odiRef.getOption("DIR_PATH")%>/
<%=odiRef.getSrcTablesList("", "[TABLE_NAME].bad", "", "")%>"
DISCARDFILE "<%=odiRef.getOption("DIR_PATH")%>/
<%=odiRef.getSrcTablesList("", "[TABLE_NAME].dsc", "", "")%>"
DISCARDMAX <%=odiRef.getUserExit("LOA_DISCARDMAX")%>
<% if (odiRef.getOption("CREATE_WORK_TABLES").equals("1")) { %>
INTO TABLE <%=odiRef.getTable("L", "COLL_NAME", "W")%>
FIELDS TERMINATED BY X'2C'
TRAILING NULLCOLS
(
	<%=odiRef.getColList("", "[CX_COL_NAME]
<? if (\u0022[DEST_DT]\u0022.equals(\u0022DATE\u0022))
{?> DATE '"+snpRef.getOption("DATE_FORMAT")+ "' <?}?>
<? if (\u0022[DEST_DT]\u0022.equals(\u0022TIMESTAMP\u0022))
{?> TIMESTAMP '"+snpRef.getOption("TIMESTAMP")+ "' <?}?>" , " ,\n\t", "","")%>

)
<% } else {%>
INTO TABLE <%=odiRef.getTargetTable("SCHEMA")%>.<%=odiRef.getTargetTable("TABLE_NAME")%>
FIELDS TERMINATED BY X'2C'
TRAILING NULLCOLS
(
	<%=odiRef.getColList("", "[COL_NAME]
<? if (\u0022[DEST_DT]\u0022.equals(\u0022DATE\u0022))
{?> DATE '"+snpRef.getOption("DATE_FORMAT")+ "' <?}?>
<? if (\u0022[DEST_DT]\u0022.equals(\u0022TIMESTAMP\u0022))
{?> TIMESTAMP '"+snpRef.getOption("TIMESTAMP")+ "' <?}?>" , " ,\n\t", "","")%>

)
<% } %>

CHANGE FILE PERMISSION

Changing the file permission so that SQLLDR and ODI can read the files

import os
<% if (odiRef.getOption("UNIX_ENVIRONMENT").equals("1")) { %>
if os.system(r"chmod 770 <%=odiRef.getOption("DIR_PATH")%>/*.* ") <> 0:
	raise 'File Permission Issues'
<%} else { %>
raise ' File Permission Not required for Windows environment '
<% } %>

CALL SQLLDR

import os
if os.system(r"sqlldr userid=<%=odiRef.getInfo("DEST_USER_NAME")%>/
<%=odiRef.getInfo("DEST_PASS")%>@<%=odiRef.getInfo("DEST_DSERV_NAME")%>
control=<%=odiRef.getOption("DIR_PATH")%>/
<%=odiRef.getSrcTablesList("[TABLE_NAME].ctl","")%>
log=<%=odiRef.getOption("DIR_PATH")%>/
<%=odiRef.getSrcTablesList("[TABLE_NAME].log", "")%> ") <> 0:
	openlog=open('<%=odiRef.getOption("DIR_PATH")%>/
	<%=odiRef.getSrcTablesList("[TABLE_NAME].log", "")%>','r')
	raise 'OS command has signalled errors.Please see the log for details ',
	openlog.readlines()
	openlog.close()

Here if the process fails and the log will be shows into Operator , enabling to see the log without logging into Unix Box.

Although the display is not perfect still it shows great

DELETE FILES

import os
<% if (odiRef.getOption("UNIX_ENVIRONMENT").equals("1")) { %>
if os.system(r"rm -f <%=odiRef.getOption("DIR_PATH")%>/
<%=odiRef.getSrcTablesList("[TABLE_NAME].dat", "")%>
<%=odiRef.getOption("DIR_PATH")%>/
<%=odiRef.getSrcTablesList("[TABLE_NAME].ctl", "")%>
<%=odiRef.getOption("DIR_PATH")%>/
<%=odiRef.getSrcTablesList("[TABLE_NAME].bad", "")%>
<%=odiRef.getOption("DIR_PATH")%>/
<%=odiRef.getSrcTablesList("[TABLE_NAME].log", "")%> ") <> 0:
	raise ' ODI has failed to delete files Please remove them manually if required '
<%} else { %>
if os.system(r"cmd /c del <%=odiRef.getOption("DIR_PATH")%>\
<%=odiRef.getSrcTablesList("[TABLE_NAME].dat", "")%>;
<%=odiRef.getOption("DIR_PATH")%>\
<%=odiRef.getSrcTablesList("[TABLE_NAME].ctl", "")%>;
<%=odiRef.getOption("DIR_PATH")%>\
<%=odiRef.getSrcTablesList("[TABLE_NAME].bad", "")%>;
<%=odiRef.getOption("DIR_PATH")%>\
<%=odiRef.getSrcTablesList("[TABLE_NAME].log", "")%>") <> 0:
          raise 'ODI has failed to delete files Please remove them manually if required '
<% } %>

Lets look at an example ,

First run is No Work Tables , Delete Sqlldr files – No  and I am running in the Windows environment  so Unix _environment = No .

IKM Dummy have to selected if  Create Work Tables = No ( as we are loading directly into the Target table )

OdiSqlUnload

Control File

Files are not deleted

Now lets run the KM with the following option

Create Work Tables – Yes ,Delete SQLLDR Files – Yes , IKM SQL Control Append

Being the Create Work table option as yes the c$ is create and so the CTL is created with Flow table options and columns . Also the SQLLDR loads into the C$ table  and then finally using the selected IKM ( for my example IKM Sql control Append ) data is loaded from I$ to Target table.

Control File

Lets see the code in Unix Environment

With  Unix environment as Yes the row_sep is \n and Infile ‘0A’

Being an Unix environment , there are file permission issue so change the permission for SQLLDR to read the files.

Here since the delete SQLLDR Files are yes , rm command is used to remove them

Also in case the SQLLDR encounters errors , i have provide the code to display the log files into the Operator, this way you don’t need to login into Unix to view the log file for error. Although the display is not proper but still it can give you enough information on what is the error due to ?

If there is no error the log file wont be displayed.

I have included the codes to support Joins and Filter , as you can see  in the OdiSqlUnload codes where condition, have the join and the filter.

Download the LKM below.

LKM OdiSqlUnload(File) to Oracle (SQLLDR)

IKM Dummy

This KM is do UNION in ODI  which created based on IKM SQL Control Append and support Flow control too and is based on the full outer join concept

Map the column in this format

NVL( <SOURCE1.COLUMN1 , SOURCE2.COLUMN1>
NVL(<SOURCE1.COLUMN2,SOURCE2.COLUMN2>
and so on..

and do a simple join between the two source tables  and select this KM and execute the interface.

The codes are shown below.

INSERT FLOW INTO I$ TABLE

insert into <%=snpRef.getTable("L","INT_NAME","A")%>
(
<%=snpRef.getColList("", "[COL_NAME]", ",\n\t", "", "((INS and !TRG) and REW)")%>
)
select <%=snpRef.getPop("DISTINCT_ROWS")%>
<%=snpRef.getColList("", "[EXPRESSION]", ",\n\t", "", "((INS and !TRG) and REW)")%>
from <%=odiRef.getSrcTablesList("", "[SCHEMA].[TABLE_NAME]", " FULL OUTER JOIN ", "")%>
on <%=odiRef.getJoinList("","([EXPRESSION])"," and ","")%>
where <% if (snpRef.getPop("HAS_JRN").equals("0")) { %>
(1=1)
<%} else {%>
JRN_FLAG <> 'D'
<% } %>
<%=snpRef.getFilter()%>
<%=snpRef.getJrnFilter()%>
<%=snpRef.getGrpBy()%>
<%=snpRef.getHaving()%>

INSERT INTO NEW ROWS

<%if ( snpRef.getUserExit("FLOW_CONTROL").equals("1") ) { %>
insert into <%=snpRef.getTable("L","TARG_NAME","A")%>
(
<%=snpRef.getColList("", "[COL_NAME]", ",\n\t", "", "((INS and !TRG) and REW)")%>
<%=snpRef.getColList(",", "[COL_NAME]", ",\n\t", "", "((INS and TRG) and REW)")%>
)
select <%=snpRef.getColList("", "[COL_NAME]", ",\n\t", "", "((INS and !TRG) and REW)")%>
<%=snpRef.getColList(",", "[EXPRESSION]", ",\n\t", "", "((INS and TRG) and REW)")%>
from <%=snpRef.getTable("L","INT_NAME","A")%>
<% }
else { %>
insert into <%=snpRef.getTable("L","TARG_NAME","A")%>
(
<%=snpRef.getColList("", "[COL_NAME]", ",\n\t", "", "(INS and REW)")%>
)
select <%=snpRef.getPop("DISTINCT_ROWS")%>
<%=snpRef.getColList("", "[EXPRESSION]", ",\n\t", "", "(INS and REW)")%>
from <%=odiRef.getSrcTablesList("", "[SCHEMA].[TABLE_NAME]", " FULL OUTER JOIN ", "")%> on <%=odiRef.getJoinList("","([EXPRESSION])"," and ","")%>
where <% if (snpRef.getPop("HAS_JRN").equals("0")) { %>
(1=1)
<%} else {%>
JRN_FLAG <> 'D'
<% } %>
<%=snpRef.getFilter()%>
<%=snpRef.getJrnFilter()%>
<%=snpRef.getGrpBy()%>
<%=snpRef.getHaving()%>
<% } %>

Hope it does the work till latest ODI is released with UNION and MINUS logic.

Download the KM below.

Define your Model Folder and Models .

Right Click on the Target Models and select ‘Interface In‘

Select your Generation Folder and Optimization Context accordingly and Select the Data Store Name for which the interfaces needs to be generated else Click ok and all the Interfaces will be generated at selected Folder.

The sample Interfaces are generated as shown below according to the desired Data store Name.

Drag in your source and apply the LKM , IKM and CKM and run the interface. Now there is simple process to make the selection of the KMs automatically.

Considering for an Example -

Source – SQL Server and Target – Oracle , LKM as SQL to Oracle , IKM SQL Control Append with No Flow control and Truncate as yes for options and No CKM as Flow control is disabled in IKM.

Open your LKM – LKM Sql to Oracle – Check mark the option Default KM for this pair of Technologies.

Do the same for IKM SQL Control Append

Go to IKM options and change the required settting

[ Note : Please do not change it on the standard IKM , either make a duplicate and do the required changes or add change history with the required changes on both the conditions. This are some of the good practice of ODI KMs usage and Reusability ]

Go the CKM Oracle and SQL and uncheck the mart – ” Default KM for this pair of Technologies ” as CKM wont be used since we are disabling the flow control option.

Now drag your source and automatically your LKM , IKM and CKM will be selected. If not check with other option KMs , if they are overriding the above mentioned option of “Default KM for this pair of Technologies” .

Keep looking at ‘www.odiexperts.com ‘ for more wonderful tips and tricks.

This query lists all the KMs used in each Interfaces under a particular Folder or Project with Interface  Name , Interface Type ie. Permanent_Interface (Blue) or Temporary_Interface (Yellow) ,LKM , IKM , CKM and Source Set Number since there can be multiple Sources feeding a single target in an Interface.

SELECT SP.pop_name AS INTERFACE_NAME ,
CASE WHEN SP.WSTAGE=’E’ THEN ‘TEMP_INTERFACE’
ELSE ‘PERM_INTERFACE’
END AS INTERFACE_TYPE,
NVL(STL.trt_name,’None’) AS LKM_USED ,
NVL(STI.trt_name,’None’) AS IKM_USED ,
NVL(SSS.src_set_name,’None’) AS INPUT_SET_NAME ,
NVL(STC.TRT_NAME,’NONE’) AS CKM_USED
FROM snp_pop SP
LEFT OUTER JOIN snp_src_set SSS
ON SP.i_pop = SSS.i_pop
LEFT OUTER JOIN snp_trt STL
ON SSS.i_trt_klm = STL.i_trt
LEFT OUTER JOIN snp_folder SF
ON SP.i_folder = SF.i_folder
LEFT OUTER JOIN snp_trt STI
ON STI.i_trt = SP.i_trt_kim
LEFT OUTER JOIN SNP_TRT STC
ON stc.i_trt = SP.I_TRT_KCM
LEFT OUTER JOIN snp_project SPJ
ON SPJ.i_project = SF.i_project
WHERE SF.folder_name = ‘<FOLDER_NAME>’
AND spj.project_name = ‘<PROJECT_NAME>’
ORDER BY SP.pop_name, SSS.src_set_name;

Sample Output