[Oracle Tips] Changing Default End Date in SCD KM

ODI has an IKM for Loading SCD Type 2 dimensions. The IKM is 'IKM Oracle Slowly Changing Dimension'. The default value for Slow Changing Dimension (SCD) Type 2 End Date is "01 January 2400".
You may need to change this End Date value because of the source data that you are using to populate the Slowly Changing Dimension. In a recent project, I had to deliver an integration between Oracle Fusion HCM and an on-premise DWH. The End Date for Fusion HCM objects is "31-December-4712". So, if you want to use the ODI IKM to create SCDs you need to customize it.
To do so, first you need to create a copy of the default IKM.

Then open the duplicated object and locate the steps where the default End Date is used.
The step that needs to be customized is 'Insert flow into I$ table'. Edit the Target code to replace '2400-01-01' with '4712-12-31' or whichever value you want to use as End Date.

 

Once it is done, then you can use this customized IKM in your interfaces for populating SCDs Type 2. 

ODI Tips: ODI initialization hangs

I have been on the same OBIA/ODI project for almost two years now and every now and then I get this issue.
ODI Studio hangs on ODI initialization - Initialize Search Engine window.

No matter how much patiently you wait, it won't allow you to progress further.
The only option you have is to:

1. Kill ODI Studio process using Task Manager
2. Locate windowinglayout.xml file in your computer. Typically, you will find it under: C:\Users\<USER>\AppData\Roaming\odi\system11.1.1.9.0\o.ide.11.1.1.9.40.66.73   
   
   
3. Delete the file and restart ODI Studio

ODI Tip: Generating flat files with spaces in column names

I had a requirement of generating a .csv file with header out of a database table.
I had created the model as follows:

• File Format: Delimited
• Record Separator: Unix  
• Field Separator: , (comma)

In the interface, I had placed the file as Target Datasource and used 'IKM SQL to File Append' with the following options:

Unfortunately, the header columns were truncated at the first space and adding double quotes did not help. The column header were all truncated after the first space found.
After investigating, I came to the conclusion that there is was no quick or easy way to deal with spaces. I guess the only way is creating a custom version of the IKM.
Therefore, since I have very little time, I have replaced the spaces with underscores and added a OdiOSCommand in the package to replace the underscores with spaces in the output file.

ODIOSCommand is a more flexible version of OSCommand and allows you to invoke a sed command to do the replacement in place (-i) of the underscores with spaces.

sed -i 's/_/ /g' <filename>

Oracle Tip: Exception getObjectName("R", "OBI_EXEC_SESS_<$=Y$>", "D")

When running OBIA Load Plan, today I got into the following error when running SIL_DomainGeneral_CM_Source_Load scenario:


com.sunopsis.tools.core.exception.SnpsSimpleMessageException: ODI-17517: Error during task interpretation.
 Task: 6
java.lang.Exception: The application script threw an exception: com.sunopsis.tools.core.exception.SnpsSimpleMessageException: Exception getObjectName("R", "OBI_EXEC_SESS_<$=Y$>", "D") :  BSF info: Run Alter Session Commands-SRC (DB Link) at line: 0 column: columnNo


The solution to this error is given in Doc ID 1948605.1 from Oracle Support.
You should connect to ODI Physical Architecture in ODI Topology and check that BIAPPS_BIACOMP has got a Default Schema defined (and that connectivity is in place). If there is no Physical Schema defined, then check the option. 
 


 

If this does not solve the problem, check BIAPPS DWH Physical Connection. Make sure that at least one of the physical schemas is marked as Default Schema.

Somehow during migration activities, we lost the selection on one of them.

ODI Custom KMs Library

I have found this useful link for downloading "custom" ODI Knowledge Modules (KMs):

https://java.net/projects/oracledi/downloads/directory/ODI/Knowledge%20Modules

Here you can find customized KMs such as Exchange Partition option, DELETE function, etc. ready for downloading and installing.

Oracle Tip: Exception in thread "DwgCmdScheduler"

A customer had an issue with a StandAlone ODI Agent. Apparently the agent was up and running, but it was not possible to schedule any job.
ODI version was 11.1.1.6 installed on Unix Server.
I manually stopped the agent and restarted it using the following commands:


./agentstop.sh -NAME=<AGENT_NAME> -PORT=<PORT_NUMBER>

./agent.sh -NAME=<AGENT_NAME> -PORT=<PORT_NUMBER>

2014-03-11 10:36:33.325 NOTIFICATION ODI-1128 Agent <AGENT_NAME> is starting. Application Server: STANDALONE. Agent Version: 11.1.1.6.0 - 19/12/2011. Port: 20910. JMX Port: 21910.

2014-03-11 10:36:36.132 NOTIFICATION ODI-1136 Starting Schedulers on Agent <AGENT_NAME>.

2014-03-11 10:36:36.538 NOTIFICATION ODI-1111 Agent <AGENT_NAME> started. Agent version: 11.1.1.6.0 - 19/12/2011. Port: 20910. JMX Port: 21910.

Exception in thread "DwgCmdScheduler-WORK_REP_PRO" java.lang.NullPointerException

at com.sunopsis.timer.filterbased.SnpsBetweenHoursFilter.equals
(SnpsBetweenHoursFilter.java:112)

at java.util.AbstractList.equals(AbstractList.java:524)

at com.sunopsis.timer.filterbased.FilterBasedTaskScheduleDefinition.equals
(FilterBasedTaskScheduleDefinition.java:50)

at com.sunopsis.timer.SnpsTaskManager.updateDefinitionList(SnpsTaskManager.java:94)

at com.sunopsis.timer.SnpsAbstractScheduler.run(SnpsAbstractScheduler.java:161)

at oracle.odi.runtime.agent.scheduler.OdiAgentScheduler.run(OdiAgentScheduler.java:138)

at java.lang.Thread.run(Thread.java:679)

2014-03-11 10:36:36.805 NOTIFICATION ODI-1137 Scheduler started for work repository WORK_REP on Agent <AGENT_NAME>

The error shows an issue with starting scheduler components of the agent. I googled the error and I did not find anything useful. So I tried creating a new agent with the same parameters (server and port number) and I could start it and schedule jobs. Then, what was wrong with the original agent?
Thanks to Oracle Support, I found out that there is a bug that raises this issue whenever just one column out of S_BEGIN_HOUR and S_END_HOUR in table SNP_PLAN_AGENT of the Work Repository has values in it.

“Problem Description

-------------------

There are two columns in the SNP_PLAN_AGENT, S_BEGIN_HOUR and S_END_HOUR.

Generally, S_BEGIN_HOUR and S_END_HOUR columns either both shows the correct 

time value or both NULL.

However, our ODI customer has faced major problems due to only one of the 

columns showed NULL and the other column showed the time value.”

I connected to the Work Repository and executed the following query:

select *

from SNP_PLAN_AGENT

where LAGENT_NAME = <AGENT_NAME>

and I have detected a line having S_BEGIN_HOUR = '01/01/70' and S_END_HOUR = null. 

I have updated the line setting S_BEGIN_HOUR to null in order to have both columns set to null. 

update SNP_PLAN_AGENT 

set S_BEGIN_HOUR = NULL  

where LAGENT_NAME = <AGENT_NAME> AND S_BEGIN_HOUR IS NOT NULL;  

COMMIT;  

I then tried to start the agent and it worked. No warnings and scheduling back up and running!