Analytics Software

Hi All,

Now I'm tring to extract data from essbase by ODI, and at the sametime, I need to do some transformations, such as join with other relational tables or flat files to get more information about metadata. But unfortunately, once I add join into the interface, an error occurs. And it works fine if I extract directly (with no joins).
Is there any other way except using staging table? Our data vloume is large, using staging table will waste lots of storages.
The messages as below.

2011-07-12 23:34:00,931 INFO [DwgCmdExecutionThread:null:49]: ODI Hyperion Essbase Adapter Version 9.3.1.1
2011-07-12 23:34:00,931 INFO [DwgCmdExecutionThread:null:49]: Connecting to Essbase application [CORPPROD] on [172.18.93.150]:[1423] using username [admin].
2011-07-12 23:34:00,962 INFO [DwgCmdExecutionThread:null:49]: Successfully connected to the Essbase application.
2011-07-12 23:34:00,962 INFO [DwgCmdExecutionThread:null:49]: Essbase data extract LKM option EXTRACTION_QUERY_FILE = D:\ODI_ESS_QUERIES\QUERY.TXT
2011-07-12 23:34:00,962 INFO [DwgCmdExecutionThread:null:49]: Essbase data extract LKM option EXTRACTION_QUERY_TYPE = ReportScript
2011-07-12 23:34:00,962 INFO [DwgCmdExecutionThread:null:49]: Essbase Load IKM option PRE_CALCULATION_SCRIPT = null
2011-07-12 23:34:00,962 INFO [DwgCmdExecutionThread:null:49]: Essbase data extract LKM option EXT_COL_DELIMITER =
2011-07-12 23:34:00,962 INFO [DwgCmdExecutionThread:null:49]: Essbase Load IKM option PRE_EXTRACT_MAXL =
2011-07-12 23:34:00,962 INFO [DwgCmdExecutionThread:null:49]: Essbase Load IKM option POST_EXTRACT_MAXL =
2011-07-12 23:34:00,962 INFO [DwgCmdExecutionThread:null:49]: Essbase Load IKM option ABORT_ON_PRE_MAXL_ERROR = true
2011-07-12 23:34:00,962 DEBUG [DwgCmdExecutionThread:null:49]: inside getAppData()
2011-07-12 23:34:00,962 DEBUG [DwgCmdExecutionThread:null:49]: Got Source Metadata
2011-07-12 23:34:01,119 ERROR [DwgCmdExecutionThread:null:49]: The number of columns returned by script [13] is less than the source data columns exposed [14]
com.hyperion.odi.essbase.ODIEssbaseException: The number of columns returned by script [13] is less than the source data columns exposed [14]
at com.hyperion.odi.essbase.wrapper.EssbaseReportDataIterator.validateColumns(Unknown Source)
at com.hyperion.odi.essbase.wrapper.EssbaseReportDataIterator.init(Unknown Source)
at com.hyperion.odi.essbase.ODIEssbaseDataReader.getAppData(Unknown Source)
at com.hyperion.odi.essbase.AbstractEssbaseReader.extract(Unknown Source)
at sun.reflect.GeneratedMethodAccessor769.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.python.core.PyReflectedFunction.__call__(PyReflectedFunction.java)
at org.python.core.PyMethod.__call__(PyMethod.java)
at org.python.core.PyObject.__call__(PyObject.java)
at org.python.core.PyInstance.invoke(PyInstance.java)
at org.python.pycode._pyx85.f$0(<string>:1)
at org.python.pycode._pyx85.call_function(<string>)
at org.python.core.PyTableCode.call(PyTableCode.java)
at org.python.core.PyCode.call(PyCode.java)
at org.python.core.Py.runCode(Py.java)
at org.python.core.Py.exec(Py.java)
at org.python.util.PythonInterpreter.exec(PythonInterpreter.java)
at org.apache.bsf.engines.jython.JythonEngine.exec(JythonEngine.java:144)
at com.sunopsis.dwg.codeinterpretor.k.a(k.java)
at com.sunopsis.dwg.dbobj.SnpSessTaskSql.scripting(SnpSessTaskSql.java)
at com.sunopsis.dwg.dbobj.SnpSessTaskSql.execScriptingOrders(SnpSessTaskSql.java)
at com.sunopsis.dwg.dbobj.SnpSessTaskSql.execScriptingOrders(SnpSessTaskSql.java)
at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTaskTrt(SnpSessTaskSql.java)
at com.sunopsis.dwg.dbobj.SnpSessTaskSqlC.treatTaskTrt(SnpSessTaskSqlC.java)
at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTask(SnpSessTaskSql.java)
at com.sunopsis.dwg.dbobj.SnpSessStep.treatSessStep(SnpSessStep.java)
at com.sunopsis.dwg.dbobj.SnpSession.treatSession(SnpSession.java)
at com.sunopsis.dwg.cmd.DwgCommandSession.treatCommand(DwgCommandSession.java)
at com.sunopsis.dwg.cmd.DwgCommandBase.execute(DwgCommandBase.java)
at com.sunopsis.dwg.cmd.e.k(e.java)
at com.sunopsis.dwg.cmd.g.A(g.java)
at com.sunopsis.dwg.cmd.e.run(e.java)
at java.lang.Thread.run(Unknown Source)
2011-07-12 23:34:01,119 INFO [DwgCmdExecutionThread:null:49]: Logging out and disconnecting from the essbase application.


From the working steps, I found a redundant column (Entity) was added while creating work table. Is it the reason?

create table HYP.C$_0CUX_ABC_HYP_INTERFACE
(
C14_HSP_RATES VARCHAR2(80) NULL,
C3_ACCOUNT VARCHAR2(80) NULL,
C10_PERIOD VARCHAR2(80) NULL,
C7_SCENARIO VARCHAR2(80) NULL,
C9_CURRENCY VARCHAR2(80) NULL,
C15_YEAR VARCHAR2(80) NULL,
C6_DATATYPE VARCHAR2(80) NULL,
C2_VERSION VARCHAR2(80) NULL,
C1_CATEGORY VARCHAR2(80) NULL,
C12_ENTITY VARCHAR2(80) NULL,
C13_ENTITY VARCHAR2(80) NULL,
C11_DEPARTMENT VARCHAR2(80) NULL,
C8_PROJECT VARCHAR2(80) NULL,
C5_DATA NUMBER(18,8) NULL
)

Appriciated!