13 Replies Latest reply on Jul 18, 2016 5:58 AM by Dattatray

ODI & Hyperion planning

Dattatray Jul 7, 2016 6:33 PM

hello all,

I am doing small POC before actual implementation on one of our planning server EPM
11.1.2.4

I am trying to load data from flat file i.e .csv file to hyperion planning.

I have created data server, logical schema , models and mapping

but when I try to run interface I am getting following error.

PFA for screenshots for all steps.

ODI-1228: Task SrcSet0 (Loading) fails on the target SUNOPSIS_ENGINE connection SUNOPSIS_MEMORY_ENGINE.

Caused By: java.sql.SQLException: user lacks privilege or object not found: C$_0Account in statement [drop table "C$_0Account"]

at org.hsqldb.jdbc.Util.sqlException(Unknown Source)

at org.hsqldb.jdbc.JDBCPreparedStatement.fetchResult(Unknown Source)

at org.hsqldb.jdbc.JDBCPreparedStatement.execute(Unknown Source)

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:2925)

at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTask(SnpSessTaskSql.java:2637)

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:2093)

at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor$2.doAction(StartSessRequestProcessor.java:380)

at oracle.odi.core.persistence.dwgobject.DwgObjectTemplate.execute(DwgObjectTemplate.java:216)

at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor.doProcessStartSessTask(StartSessRequestProcessor.java:312)

at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor.access$0(StartSessRequestProcessor.java:304)

at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor$StartSessTask.doExecute(StartSessRequestProcessor.java:874)

at oracle.odi.runtime.agent.processor.task.AgentTask.execute(AgentTask.java:133)

at oracle.odi.runtime.agent.support.DefaultAgentTaskExecutor$2.run(DefaultAgentTaskExecutor.java:84)

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

Caused by: org.hsqldb.HsqlException: user lacks privilege or object not found: C$_0Account

at org.hsqldb.error.Error.error(Unknown Source)

at org.hsqldb.StatementSchema.getResult(Unknown Source)

at org.hsqldb.StatementSchema.execute(Unknown Source)

at org.hsqldb.Session.executeCompiledStatement(Unknown Source)

at org.hsqldb.Session.execute(Unknown Source)

... 19 more

1. Re: ODI & Hyperion planning

rpc1 Jul 8, 2016 6:00 AM (in response to Dattatray)

Did you have this warning only on Drop Work Table... ???
If yes - than it's all right... For example we have the similar warning.. Because work table doesn't exists on this step
1 person found this helpful
Like Show 1 Likes(1)

Actions
2. Re: ODI & Hyperion planning

Dayalan Punniyamoorthy Jul 8, 2016 6:40 AM (in response to rpc1)

Yes, as rpc1 sates if the drop table is not present it will throw this error rather a warning. You have to concentrate on the steps which actually perform the data load.

HTH,
Dayalan P.
1 person found this helpful
Like Show 0 Likes(0)

Actions
3. Re: ODI & Hyperion planning

Dattatray Jul 10, 2016 10:54 AM (in response to Dattatray)

i am getting error @ step 7

org.apache.bsf.BSFException: exception from Jython:
Traceback (most recent call last):
File "<string>", line 2, in <module>
Planning Writer Load Summary:
Number of rows successfully processed: 0
Number of rows rejected: 2

at org.apache.bsf.engines.jython.JythonEngine.exec(JythonEngine.java:146)
at com.sunopsis.dwg.codeinterpretor.SnpScriptingInterpretor.execInBSFEngine(SnpScriptingInterpretor.java:322)
at com.sunopsis.dwg.codeinterpretor.SnpScriptingInterpretor.exec(SnpScriptingInterpretor.java:170)
at com.sunopsis.dwg.dbobj.SnpSessTaskSql.scripting(SnpSessTaskSql.java:2473)
at oracle.odi.runtime.agent.execution.cmd.ScriptingExecutor.execute(ScriptingExecutor.java:47)
at oracle.odi.runtime.agent.execution.cmd.ScriptingExecutor.execute(ScriptingExecutor.java:1)
at oracle.odi.runtime.agent.execution.TaskExecutionHandler.handleTask(TaskExecutionHandler.java:50)
at com.sunopsis.dwg.dbobj.SnpSessTaskSql.processTask(SnpSessTaskSql.java:2925)
at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTask(SnpSessTaskSql.java:2637)
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:2093)
at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor$2.doAction(StartSessRequestProcessor.java:380)
at oracle.odi.core.persistence.dwgobject.DwgObjectTemplate.execute(DwgObjectTemplate.java:216)
at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor.doProcessStartSessTask(StartSessRequestProcessor.java:312)
at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor.access$0(StartSessRequestProcessor.java:304)
at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor$StartSessTask.doExecute(StartSessRequestProcessor.java:874)
at oracle.odi.runtime.agent.processor.task.AgentTask.execute(AgentTask.java:133)
at oracle.odi.runtime.agent.support.DefaultAgentTaskExecutor$2.run(DefaultAgentTaskExecutor.java:84)
at java.lang.Thread.run(Thread.java:662)
Caused by: Traceback (most recent call last):
File "<string>", line 2, in <module>
Planning Writer Load Summary:
Number of rows successfully processed: 0
Number of rows rejected: 2

at org.python.core.PyException.fillInStackTrace(PyException.java:70)
at java.lang.Throwable.<init>(Throwable.java:181)
at java.lang.Exception.<init>(Exception.java:29)
at java.lang.RuntimeException.<init>(RuntimeException.java:32)
at org.python.core.PyException.<init>(PyException.java:46)
at org.python.core.PyException.doRaise(PyException.java:219)
at org.python.core.Py.makeException(Py.java:1166)
at org.python.core.Py.makeException(Py.java:1170)
at org.python.pycode._pyx2.f$0(<string>:2)
at org.python.pycode._pyx2.call_function(<string>)
at org.python.core.PyTableCode.call(PyTableCode.java:165)
at org.python.core.PyCode.call(PyCode.java:18)
at org.python.core.Py.runCode(Py.java:1204)
at org.python.core.Py.exec(Py.java:1248)
at org.python.util.PythonInterpreter.exec(PythonInterpreter.java:172)
at org.apache.bsf.engines.jython.JythonEngine.exec(JythonEngine.java:144)
... 19 more
Like Show 0 Likes(0)

Actions
4. Re: ODI & Hyperion planning

rpc1 Jul 10, 2016 6:13 PM (in response to Dattatray)

Report Statistics is information step which throws information about rejected and inserted rows
you have rejected rows and you have to check log files for hyperion adapter
(enable ERROR LOG if it was not enabled)
1 person found this helpful
Like Show 0 Likes(0)

Actions
5. Re: ODI & Hyperion planning

Dattatray Jul 10, 2016 6:17 PM (in response to rpc1)

hi,
i set log_error to true,
but what is path for this log file.?

i am using odi 11g
Like Show 0 Likes(0)

Actions
6. Re: ODI & Hyperion planning

Dattatray Jul 10, 2016 6:21 PM (in response to Dattatray)

JohnGoodwin
Like Show 0 Likes(0)

Actions
7. Re: ODI & Hyperion planning

Dattatray Jul 10, 2016 6:38 PM (in response to rpc1)

i am trying to give path of error file to a physical text file but it always gets back to
<default>:<?=java.lang.System.getProperty("java.io.tmpdir")?>/<%=snpRef.getTargetTable("RES_NAME")%>.err

also i am getting error on agent as
Like Show 0 Likes(0)

Actions
8. Re: ODI & Hyperion planning

rpc1 Jul 11, 2016 5:48 AM (in response to Dattatray)

Dattatray wrote:

hi,

i set log_error to true,

but what is path for this log file.?

i am using odi 11g

Hi, Look at my previous screenshot.... I use a variable to set log path...
I have to set it manually .. for example c:/temp/log in windows or /var/logs in linux
1 person found this helpful
Like Show 0 Likes(0)

Actions
9. Re: ODI & Hyperion planning

Dattatray Jul 11, 2016 6:26 AM (in response to rpc1)

from error file , found following entries

Account,Data Load Cube Name,FY15,Point-of-View,Error_Reason
AC0006,plan1,800,""Draft1,Jan,EN28,USD,CT00,L0002"",Cannot load dimension member, error message is: RemoteException occurred in server thread; nested exception is:
java.rmi.UnmarshalException: unrecognized method hash: method not supported by remote object
AC0007,plan1,10,""Draft1,Jan,EN28,USD,CT00,L0002"",Cannot load dimension member, error message is: RemoteException occurred in server thread; nested exception is:
java.rmi.UnmarshalException: unrecognized method hash: method not supported by remote object
Like Show 0 Likes(0)

Actions
10. Re: ODI & Hyperion planning

rpc1 Jul 11, 2016 6:31 AM (in response to Dattatray)

Look document and try solution
Oracle Support Document 1678759.1 (Planning 11.1.2.3.500 with PSE 18709758 and ODI 11G, Unable to Load: 'Unrecognized Method Hash') can be found at: https://support.oracle.com/epmos/faces/DocumentDisplay?id=1678759.1
1 person found this helpful
Like Show 0 Likes(0)

Actions
11. Re: ODI & Hyperion planning

Dattatray Jul 11, 2016 7:24 PM (in response to rpc1)

the patch was for odi 11.1.1.7 and epm 11.1.2.3.500
but we have odi 11.1.1.9 and epm 11.1.2.4

even though i tried to apply patch 18687916 but getting message as

D:\oracle\ODI11g\OPatch>opatch.bat apply D:\oracle\ODI11g\OPatch\18687916\oui -j
dk D:\hyperion\Oracle\Middleware\jdk160_35
Oracle Interim Patch Installer version 11.1.0.11.0
Copyright (c) 2016, Oracle Corporation. All rights reserved.

Oracle Home       : D:\oracle\ODI11g
Central Inventory : C:\Program Files\Oracle\Inventory
   from           : n/a
OPatch version    : 11.1.0.11.0
OUI version       : 11.1.0.11.0
Log file location : D:\oracle\ODI11g\cfgtoollogs\opatch\18687916_Jul_11_2016_23_
48_31\apply2016-07-11_23-48-30PM_1.log

Applying interim patch '18687916' to OH 'D:\oracle\ODI11g'
Verifying environment and performing prerequisite checks...
OPatch system modification phase did not start:
Patch "18687916" is not needed since it has no fixes for this Oracle Home. Pleas
e see log file for details.
Log file location: D:\oracle\ODI11g\cfgtoollogs\opatch\18687916_Jul_11_2016_23_4
8_31\apply2016-07-11_23-48-30PM_1.log

OPatch stopped on request.
Like Show 0 Likes(0)

Actions
12. Re: ODI & Hyperion planning

JohnGoodwin Jul 11, 2016 7:52 PM (in response to Dattatray)

For 11.1.2.4 you want patch 20957183 which is aimed at 11.1.1.7, not sure if there is a 11.1.1.9 version, if there is not I usually open the inventory.xml and actions.xml in the patch and update 11.1.1.7.0 to 11.1.1.9.0
I wrote all about it here - More to life...: Planning 11.1.2.4 with ODI 11.1.1.7/9
1 person found this helpful
Like Show 2 Likes(2)

Actions
13. Re: ODI & Hyperion planning

Dattatray Jul 18, 2016 5:58 AM (in response to JohnGoodwin)

hello John,
after applying patch patch 20957183 , now i am able to load the records,
Like Show 0 Likes(0)

Actions

Go to original post