OBIEE 11g Calling Teradata Stored Procedure

December 31, 2016 Leave a comment

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

OBIEE 11g :- BISystem User Role got deleted

March 28, 2014 5 comments

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

—————————————————————————————————————————————————————–

Resolution:-

To resolve this we needed to follow below steps :-

  1.        Create the BISystem Role in EM(Enterprise Manager).
  2.        Assign member as BI System User
  3.        Create BISystem Policies and add BISystem Role to that.
  4.        In the permission add following:-

i.      oracle.bi.scheduler.manageJobs

ii.      oracle.bi.server.queryUserPopulation

iii.      oracle.bi.server.impersonateUsers

iv.      oracle.bi.server.manageRepositories

v.      EPM_Essbase_Administrato

When you are not finding Permission in the search, Please go ahead and continue.

  1.        Restart the Services to take this in effect.

The error gets resolved and you are all set.

Please see the Oracle documentation on this :-

http://docs.oracle.com/cd/E23943_01/bi.1111/e10543/troubleshoot.htm#CHDEEFIF

 

 

 

Categories: Uncategorized Tags:

OBIEE 10g – Changing default port 9704 to web port 80 or any other port.

October 10, 2011 5 comments

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

OracleBI\oc4j_bi\j2ee\home\config

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 :-

Old – http://localhost:9704/analytics

New – http://localhost/analytics

Soon .. I am going to update on OBIEE 11g.

Categories: OBIEE 10g Tags:

OBIEE Reducing space between pivot views in the Report

January 20, 2011 3 comments

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 :-

OracleBI\oc4j_bi\j2ee\home\applications\analytics\analytics\res\s_oracle10\b_mozilla_4

and

OracleBI\web\app\res\s_oracle10\b_mozilla_4

Steps :

1. Edit the file view.css and change the following code for Pivot table.

.PivotTable {

font-size: 20pt;

margin-bottom: 0px; – Changed to Zero (0), default was 8.

border-style: solid;

border-width: 1px 1px 0 1px;

border-color: #c9cbd3;

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.

Cheers !!!

Categories: OBIEE 10g Tags:

Port Configuration for OBIEE 11g

August 22, 2010 Leave a comment

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.

then in the next step it will ask for port configuration.please specify the newly assign port configuration file .

I have configuered the new ports for 11g BI as i already had OBIEE10g in my machine that uses  9703 ,9704 , 9710 etc.

Please click here to see the sample configuration port file. Save the file as .ini file. Staticports

Troubleshooting Error in OBIEE 11g Simple Install

August 22, 2010 Leave a comment

Hi ,

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.

OBIEE 11gR1 Step by Step Installation

August 22, 2010 42 comments

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 –

http://www.oracle.com/technetwork/middleware/bi-enterprise-edition/downloads/bus-intelligence-11g-165436.html

The install comes in 4 files:

bi_windows_x86_111130_32_disk1_1of2.zip

bi_windows_x86_111130_32_disk1_2of2.zip

bi_windows_x86_111130_32_disk2_1of2.zip

bi_windows_x86_111130_32_disk2_2of2.zip

Optional Software: (RCU –Repository creation Utility)

 

ofm_rcu_win32_11.1.1.3.3_disk1_1of1.zip

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  :-

Pre installation, I have changed the virtual memory of system with (3000 – 6000) to rum the installer smoothly.Step 1. create BIPLATFORM and MDS schema using RCU(Repository creation utility).

1.Please unzip the optional software downloaded from OTN

ofm_rcu_win32_11.1.1.3.3_disk1_1of1.zip

Now navigate to \rcuHome\BIN and run rcu.bat .

2.In next screen batch file will open a java app with pretty self explanatory screens.

Note:-  you can drop the schema using RCU if in case your installation did not happen successful.

3. Here please provide the Host name , service name , user name and password for the database. You need to have a user created with DBA privilege.

4. Here please select the metadata services (under AS common Schemas) and Business Intelligence Platform (Under Oracle Business Intelligence). Name of schema would be auto configured by RCU.

6. This screen will allow user to select the password for the schema created.

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.

Please user the username and the password as weblogic (used at installation time) .

Now i could see the flash enabled  exciting OBIEE 11g front end.:)

SO here i have OBIEE 11g with existing OBIEE 10.1.3.4.1

and services look like –