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

    ODI & Hyperion planning

    Dattatray

      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

          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
          • 2. Re: ODI & Hyperion planning
            Dayalan Punniyamoorthy

            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
            • 3. Re: ODI & Hyperion planning
              Dattatray

              i am getting error @ step 7

               

              step7.jpg

               

              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

              • 4. Re: ODI & Hyperion planning
                rpc1

                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
                • 5. Re: ODI & Hyperion planning
                  Dattatray

                  hi,

                  i set log_error to true,

                  but what is path for this log file.?

                   

                  i am using odi 11g

                  • 7. Re: ODI & Hyperion planning
                    Dattatray

                    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

                    agent.jpg

                    • 8. Re: ODI & Hyperion planning
                      rpc1

                      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
                      • 9. Re: ODI & Hyperion planning
                        Dattatray

                        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

                        • 10. Re: ODI & Hyperion planning
                          rpc1

                          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
                          • 11. Re: ODI & Hyperion planning
                            Dattatray

                            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.

                            • 12. Re: ODI & Hyperion planning
                              JohnGoodwin

                              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
                              • 13. Re: ODI & Hyperion planning
                                Dattatray

                                hello John,

                                after applying patch  patch 20957183 , now i am able to load the records,

                                odi records.jpg