Home > OBIEE 10g, OBIEE 11g, Uncategorized > OBIEE 11g Calling Teradata Stored Procedure

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.

 

Root Cause

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.

$ORACLE_HOME/common/ODBC/Merant/7.0.1/Drivers

 

 

 

Changes in ODBC settings:-

 

After we create a DSN on server we need to ensure the following settings to be apply on ODBCAD32.

  1. Disable Call to EXEC Conversion – Yes
  2. Return Output Parameters As Result set – Yes

Enable “Return Output Parameters as ResultSet” can also be done ODBC.ini file.

  1. 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.

odbc

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.

https://developer.teradata.com/applications/articles/obiee-11g-integration-with-fuzzy-logix-db-lytix-on-teradata

 

 

 

 

 

 

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: