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.
Hi All , I am back after long time.
Oh my Gosh I got into trouble after BISystem User got deleted accidently.
When I was trying to logon to BI Presentation server it was throwing error as invalid User name and password. Also while opening the RPD online mode it error out saying Invalid User name and Password.
[2014-03-28T11:22:50.921-04:00] [bi_server1] [ERROR] [OBI-SEC-00026] [oracle.bi.security.service] [tid: [ACTIVE].ExecuteThread: ‘1’ for queue: ‘weblogic.kernel.Default (self-tuning)’] [userId: BISystemUser] [ecid: 0000KK9H3s1F^64LvUs1yX1JDP9m000002,0:48:1:6:1] [WEBSERVICE_PORT.name: SecurityServicePort] [APP: bimiddleware#11.1.1] [J2EE_MODULE.name: bimiddleware/security] [WEBSERVICE.name: SecurityService] [J2EE_APP.name: bimiddleware_11.1.1] System user has not been granted required permission oracle.bi.server.impersonateUser
[2014-03-28T11:22:50.921-04:00] [bi_server1] [ERROR] [OBI-SEC-00003] [oracle.bi.security.service] [tid: [ACTIVE].ExecuteThread: ‘1’ for queue: ‘weblogic.kernel.Default (self-tuning)’] [userId: BISystemUser] [ecid: 0000KK9H3s1F^64LvUs1yX1JDP9m000002,0:48:1:6:1] [WEBSERVICE_PORT.name: SecurityServicePort] [APP: bimiddleware#11.1.1] [J2EE_MODULE.name: bimiddleware/security] [WEBSERVICE.name: SecurityService] [J2EE_APP.name: bimiddleware_11.1.1] Error during initialization[[
oracle.bi.security.service.SecurityServiceException: SecurityService::checkSystemUserPermissionsSystem user has not been granted required permission oracle.bi.server.impersonateUser
[2014-03-28T11:22:50.921-04:00] [bi_server1] [ERROR] [OBI-SEC-00004] [oracle.bi.security.service] [tid: [ACTIVE].ExecuteThread: ‘1’ for queue: ‘weblogic.kernel.Default (self-tuning)’] [userId: BISystemUser] [ecid: 0000KK9H3s1F^64LvUs1yX1JDP9m000002,0:48:1:6:1] [WEBSERVICE_PORT.name: SecurityServicePort] [APP: bimiddleware#11.1.1] [J2EE_MODULE.name: bimiddleware/security] [WEBSERVICE.name: SecurityService] [J2EE_APP.name: bimiddleware_11.1.1] Unable to initialize oracle.bi.security.service.SecurityWebService
[2014-03-28T11:22:50.921-04:00] [bi_server1] [WARNING]  [oracle.j2ee.ws.common.jaxws.JAXWSMessages] [tid: [ACTIVE].ExecuteThread: ‘1’ for queue: ‘weblogic.kernel.Default (self-tuning)’] [userId: BISystemUser] [ecid: 0000KK9H3s1F^64LvUs1yX1JDP9m000002,0:48:1:6:1] [WEBSERVICE_PORT.name: SecurityServicePort] [APP: bimiddleware#11.1.1] [J2EE_MODULE.name: bimiddleware/security] [WEBSERVICE.name: SecurityService] [J2EE_APP.name: bimiddleware_11.1.1] Exception while executing the business logic: oracle.bi.security.service.SecurityServiceException: SecurityService::checkSystemUserPermissionsSystem user has not been granted required permission oracle.bi.server.impersonateUser
To resolve this we needed to follow below steps :-
- Create the BISystem Role in EM(Enterprise Manager).
- Assign member as BI System User
- Create BISystem Policies and add BISystem Role to that.
- In the permission add following:-
When you are not finding Permission in the search, Please go ahead and continue.
- Restart the Services to take this in effect.
The error gets resolved and you are all set.
Please see the Oracle documentation on this :-
Once we install OBIEE 10g with default installation it uses 9704 as default presentation port. Here we will see how to change this default port to any other port. (I am going to consider port 80 here.)
Assumption:- OBIEE 10g installed with default OC4j application Server.
Step 1. Stop Oc4j service.
Step 2. Look for “default-web-site.xml“ file in the following directory in OBIEE home
Step3. Edit the “default-web-site.xml” file and change the “9704” port to “80”.
Step4. Start the oc4j service.
Now the presentation url would be like this :-
Soon .. I am going to update on OBIEE 11g.
Today I had got a requirement from one of my customer to display different pivot views in single report with out any space. One of my colleague (Suyasha) helped me achieve the desired format. I would like to share the information that might help others :-
The changes has to be done in View.css file that resides in two folders :-
1. Edit the file view.css and change the following code for Pivot table.
margin-bottom: 0px; – Changed to Zero (0), default was 8.
border-width: 1px 1px 0 1px;
margin-top: 0px; – Changed to Zero (0) default was 8.
2. Restart OC4j and OBIEE presentation services.
I am also attaching the screen shot of the results.
Note :- Please use the below patch for OBIEE 10g installation.
Patch 8565823 Modifying Views.css .PTSECTSTABLE PADDING OPX DOES NOT HAVE ANY IMPACT.
For those who would like to install OBIEE 11g on the existing OBIEE 10g machine need to configure port during the installation process.
Once we kick off the OBIEE 11g set up we need to select Enterprise Install that enable to change the port for Oracle Application server , Weblogic and OBIEE.
Please click here to see the sample configuration port file. Save the file as .ini file. Staticports
I was going through the OBIEE 11g installation as per prerequisite and selected simple install. I was surprise to see that few of the configuration failed. see the screen shot.
Because of this after the installation i was not able to login to see the OBIEE 11g front end. I received an error that my userid and password could not be authorized.
When i login to enterprise manager i could see that BI cluster services was not running as cluster configuration was not correctly defined during configuration.
So after changing the OBIEE ODBC (DSN name coreapplication_OH1731537899) to non cluster and restarting BI services I could login to OBIEE 11g Dashbaords.
Actually after getting this error i decided to install OBIEE 11g with Enterprise Install on my laptop where OBIEE 10g was already installed. See my previous blog.
Fusion Middle ware BI 11 G Installation:-
I have been desperately looking for the new release of OBIEE 11g and wanted to see the new feature of OBIEE 11g . It made me little disappointed when I came to know my 2 GB RAM Laptop is not meeting the minimum requirement , but I still decided to install on my laptop DELL Latitude 5400 with below configuration.
Core2 duo 2.4 GHZ , 2 GB of RAM
After complete installation, services took little time to start up but once started the performance of presentation services / Dashboard /Reports is awesome.
Note: – It recommends having atleast 4 GB of Ram to start with.
OBIEE 11g software can be downloaded from here –
The install comes in 4 files:
Optional Software: (RCU –Repository creation Utility)
Fusion Middle ware BI installation following steps:-
1. Install a database
Note :- I have installed Oracle 11g R1 for this purpose.
2. Use RCU( Repository creation Utility ) to create BIPLATFORM & MDS needed for BI and Oracle Application server during installation.
With this release of 11gr1 ,RCU can be used to create schema for Oracle 9i /10g/11g , MS SQL server or IBM DB2 only.
Note :- I have used Oracle 11gr1 to create DEV_BIPLATFORM and DEV_MDS schema.
3. Kick off Fusion middle ware installation.
Run the BI11g setup to start 11g installation.
Now I will start with the step by step installation of OBIEE 11GR1 :-
1.Please unzip the optional software downloaded from OTN
Now navigate to \rcuHome\BIN and run rcu.bat .
Note:- you can drop the schema using RCU if in case your installation did not happen successful.
7. Once you provide the password the password for the schema the RCU wizard will create the schema in the database. It will create a table space for the newly created shema . You can also change the table space for the scema.
Once RCU creates the required schema and table spaces in database we are good to go with OBIEE 11g Installion.
2. Installing OBIEE 11g fusion Middle ware.
Now navigate to unzip disk 1 and start the set up
There are 3 type of installation this release comes with.
Simple Install: – install Oracle Business Intelligence Enterprise Edition, Oracle Business Intelligence Publisher, or Oracle Real-Time Decisions with the default settings on a single computer in the minimum number of steps.
Enterprise Install: – install a new Oracle Business Intelligence system for a hosted deployment on one or more computers. When you select the Enterprise Install type for a new installation, Oracle Business Intelligence 11g Installer installs the Oracle Business Intelligence components that you select, such as Oracle Business Intelligence Enterprise Edition, Oracle Business Intelligence Publisher, and Oracle Real-Time Decisions.
Software Only Install :- install software binary files in an Oracle home for later configuration as part of a Fusion Middleware deployment.
It is recommended to go for Simple install for Single development machine. I have installed both Simple Install and Enterprise install in two different machine. During installation I figure out that simple install will give you error while configuration but Enterprise install is a clean installation. So I would recommend going for Enterprise installation if you are installing first time.
After selecting Enterprise Install the next screen will prompt for Password for Weblogic. Here we are creating a new BI system with User specified parameters.
Next screen will prompt for Middle ware home . I have selected c:\Middleware for this installation.
Next screen will prompt you which product we want to install.
Next Screen will prompt for DatabaseType , Connection string , BIPLATFORM Schema and Password .
Connection string – Hostname : Port:database
BIPLATFORM Schema User Name – DEV_BIPLATFORM (created by RCU)
BIPLATFORM Schema password – ***** (created by RCU)
Next screen will prompt for configuring port.
- Port configuration for first time installation
If you are installing OBIEE 11g for the first time without any prior installation then select “Auto port Configuration”
- Port configuration for existing OBIEE10g machine
if you are installing OBIEE 11g on the same machine where OBIEE 10g is already installed then specify the ports using configuration file.
Note :- For this installation I already had previously installed OBIEE 10.1.3.4 in my machine so I had to provide the configuration file for ports as OBIEE 10g also uses the same port i.e. 9704,9703,9710 etc. If you are not providing the configuration file and going for “Auto port configuration” in existing OBIEE 10g system then there are fair chances of failure of OBIEE 11g installation.
Next screen is the summary of newly creating Oracle BI system.
Now when I click on next the installation and configuration will start and be patient it will take lot of time depending upon the system configuration. It took 113 minutes for me to complete the installation(can be seen in screen shot).
Now once installation 100 % complete please save the install_config log to local drive.
Once you click on finish the next window will open for OBIEE 11g Presentation services.
Now i could see the flash enabled exciting OBIEE 11g front end.:)
and services look like –