OBIEE 11g Calling Teradata Stored Procedure
Problem Introduction: – Teradata Procedure is not working in OBIEE
Description: – OBIEE unable to call Teradata Stored Procedure.
OBIEE11g was used in conjunction with DB2 as data warehouse. We had planned to migrate the DWH from DB2 to Teradata. We have updated the requirerd parameter and OBIEE connection pool settings to connect Terataadata databse.
All reports are working except those which was using stored procedure as source. The report /Analysis based on stored procedure returns the below error.
OBIEE Error Code in Reports:-
Odbc driver returned an error (SQLExecDirectW).
Error Codes: OPR4ONWY:U9IM8TAC:OI2DL65P
State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 43113] Message returned from OBIS. [nQSError: 43119] Query Failed: [nQSError: 59140] The evaluation of the index 0 expression on row count -1 failed. [nQSError: 59015] The requested column Actv_Flg does not exist in this table. (HY000)
The error is generic and when we remove the error column from the report (Actv_Flg column ) the error shift to other columns.
There are below reasons which causes Stored Procedure data not to return results on Dashboards
- Teradata stored procedures create result sets as volatile tables. Volatile tables are only available to the session that created it. Because OBIEE uses connection pools the session that created the volatile table probably isn’t the same session that is going to read the table. Therefore XSPs don’t work without ensuring session consistency.
- While running the Query in Teradata database query is been executing twice, once for parsing and next time the actual execution, this causes the result to not return to API (Answers/Analyis/Dashboard).
- Unable to parse the database result to reporting API, if Teratada ICU and GSS home is not set correctly.
- ODBC setup is incorrect as per the Teradata properties.
Resolution steps performed in order to resolve the issue
Changes in OPMN.xml file
Path :- Oracle BI/Instance/Instance1/Config/OPMN/opmn.xml
Task 1: – Setting up Teradata Home /ICU/GSS home in OBIEE home opmn.xml config file.
<variable id=”TERA_HOME” value=”C:/Program Files/Teradata/Client/14.10/ODBC Driver for Teradata nt-x8664″ />
<variable id=”TERAGSS_HOME” value=”C:/Program Files/Teradata/Teradata GSS/nt-i386/14.10.01.06″ />
<variable id=”TERAICU_HOME” value=”C:/Program Files/Teradata/Client/14.10/Shared ICU Libraries for Teradata” />
<variable id=”PATH” value=”$TERA_HOME/lib$:$TERAICU_HOME/lib$:$TERAGSS_HOME/bin$
Task 2: – Setting up NQUIRE_EXECDIRECT=1 value in OPMN to invoke one query in database
<variable id=”NQUIRE_EXECDIRECT” value=”1″/>
Task 3: – Setting up latest data Driver 7.0.1 for merant connection.
Change the OPMN.XML to point to latest Data Driver. It has to be changed in all places in OPMN.XML file.
Changes in ODBC settings:-
After we create a DSN on server we need to ensure the following settings to be apply on ODBCAD32.
- Disable Call to EXEC Conversion – Yes
- Return Output Parameters As Result set – Yes
Enable “Return Output Parameters as ResultSet” can also be done ODBC.ini file.
- Disable parsing – Yes
Below is the screenshot of the properties changes in ODBC.
After making the above changes
enable to execute and return the data set on OBIEE dashboards in ODBC setting as below.
Hope this helps to the group who is trying to configure OBIEE with Teradata or Calling Teradata Stored Procedure. The same error behavior was clearly explained in below blog.