BIP 10G - Configuration with OBIEE 10G

About

When you have to configure BIP to work completely with OBIEE, you will have then three main tasks to perform:

  1. the configuration of the security through the OBIEE BI Server
  2. the configuration of the JBDC driver of OBIEE Bi Server to be able to send a logical sql
  3. the configuration of the OBIEE Presentation Service to be able to get data through Answers.

This article goes first through this steps and then ends up with the configurations that are not dependent of OBIEE such as the BIP scheduler and report location configurations.

Pre-Requirement

Access to the BIP Admin tab

If you can't access BIP of the admin tab, see this article : BIP - How to access to BIP with the administrator privileges ?

Database User

BIP use a database user to schedule all the report. The best is to use the same user for the scheduler of tracking fonctions of OBIEE. Thus, connect to the database with the sys account and perform this code.

create user BIP identified by BIP quota unlimited on users;
grant connect, resource to BIP;
GRANT CREATE TABLE TO BIP;

In all configuration parameter, you must change this parameters by your own data :

  • host_of_obiee by the DNS name of OBIEE
  • Account_Administrator and Password_Administrator_of_OBIEE by the credential of the OBIEE Administrator
  • host_of_database by the DNS name of the database host.
  • sid_of_database by the SID name of the database

Warning points

Authentication Blocks

If you are using Authentication Blocks to authenticate the OBIEE users in an external table, don't forget to use the NQS_PASSWORD clause in the SQL Query of the INIT Block. For example:

SELECT USERNAME, GROUP FROM SOME_TABLE WHERE USERNAME=':USER'
NQS_PASSWORD_CLAUSE(AND PASSWORD=':PASSWORD')NQS_PASSWORD_CLAUSE

Without this step, you may be able to log in Presentation Services (Dashboard) but not in BIP.

Administrator password

The administrator password must only contain alphanumeric characters.

OBIEE Integration Configuration

Security Configuration with OBIEE

In BIP

In the BIP Admin page, with the link Security Center / Security Configuration, you can configure the security with this parameters.

SuperAdmin
  • Username/Password : SuperUser/SuperUser
Security Model
Type Oracle BI server
Connection String jdbc:oraclebi://host_of_obiee:9703/
Username/Password Account_Administrator/Password_Administrator_of_OBIEE
Database Driver Clase oracle.bi.jdbc.AnaJdbcDriver

You must set the OBIEE 10G/11G - SA System subject area up if you use BIP in a previous release than 10.1.3.3 otherwise you can get an HTTP 500 error on some pages :

  • the JDBC connection configuration
  • the Security > Roles and Permissions Tab

In Oracle BI Server Repository

According to the BIP User Guide in the paragraph “Integrating with Oracle BI Server Security”, in the BI Server Administration tool, you must create the following groups to correspond to the BI Publisher functional roles:

XMLP_ADMIN this is the administrator role for the BI Publisher server
XMLP_DEVELOPER allows users to build reports in the BIP system
XMLP_SCHEDULER allows users to schedule reports in the BIP system
XMLP_ANALYZER_EXCEL allows users to use the Excel analysis feature in the BIP system
XMLP_ANALYZER_ONLINE allows users to use the online analysis feature in the BIP system
XMLP_TEMPLATE_DESIGNER allows users to connect to the BI Publisher server from the Template Builder and to upload and download templates

Integration of Oracle BI Presentation Service

BIP towards Oracle BI presentation Service

To be able to use OBIEE Answers to select data, you must perform this operation. Go to the Admin Page / Integration / Oracle BI Presentation Service and fill this parameters.

Server Protocol http
Server Version v4
Server host_of_obiee
Port 9704
Administrator Username/Password Account_Administrator/Password_Administrator_of_OBIEE
URL Suffix analytics/saw.dll

Oracle BI presentation Service towards BIP

Oracle BI Presentation Services must be able to identify the Publisher administrator and obtain the credentials to successfully authenticate in BI Publisher. The Presentation Services server stores the credentials that it uses in a Presentation Services Credential Store file. The Publisher administrator credentials must be added to the credential store. These credentials are stored under an alias called bipublisheradmin. To obtain the Publisher administrator credentials, Presentation Services searches the credential store for a username-password credential with an alias of bipublisheradmin.

First, perform a backup of this directory : “OracleBIData_HOME/web/config/”

If it's the first time, you run the command below or you add credential, it's recommended to delete the credentialstore.xml file.

If Oracle BI Scheduler have already been configured, the secret Passphrase must be the same used and you must not modify the instanceconfig.xml in the last stage.

In all stage, replace the parameters : OracleBIData_HOME and secret_passphrase by your own.

In the OracleBI_HOME\web\bin, run this dos command :

cryptotools credstore -add -infile OracleBIData_HOME/web/config/credentialstore.xml 

And follow the example below (The last step can differ if the credential store are present or not)

cryptotools credstore -add -infile OracleBIData_HOME/web/config/credentialstore.xml
>Credential Alias: bipublisheradmin
>Username: Administrator
>Password: Password_Administrator_of_OBIEE
>Do you want to encrypt the password? y/n (y):
>Passphrase for encryption: secret_passphrase
>Do you want to write the passphrase to the xml? y/n (n):
>File "OracleBIData_HOME/web/config/credentialstore.xml" exists. Do you want to
overwrite it? y/n (y):

Of assuming that the credential of the administrator are Administrator/Administrator

OracleBIData\web\config>cryptotools credstore -add -inFile credentialstore.xml -alias bipublisheradmin
-username Administrator -password Administrator -passphrase admin

In the directory OracleBI_Data\web\config, open the instanceconfig.xml file for editing and add this instruction in the <ServerInstance> node.

<CredentialStore>
     <CredentialStorage type="file" path="OracleBIData_HOME/web/config/credentialstore.xml" passphrase="secret_passphrase"/>
</CredentialStore>

BI Server database Source Configuration

In the admin page, go through “Data Source\JDBC Connection\Oracle BI EE” and full the the correct logging data of the Jdbc OBIEE driver from the BI Server.

Connection String jdbc:oraclebi://host_of_OBIEE:9703/
Username Administrator
Password Password_Administrator_of_OBIEE
Database Driver Class oracle.bi.jdbc.AnaJdbcDriver

Independent OBIEE configurations

Scheduler Configuration

Report Repository

The report are save in an directory. You can manage it if you follow this path : Admin Page/System Maintenance/Report Repository.

Repository Type File System
Path OracleBI_HOME/xmlp/XMLP

The report are located onder this directory in :

  • the directory “Reports” for the shared reports
  • the directory “Users” for the users reports

Restart OC4J, the Oracle BI Presentation Service and enjoy !

Support

oracle.apps.xdo.security.ValidateException

If you get an oracle.apps.xdo.security.ValidateException, check the warningpoints.

Example of problem with a @ sign in the passowrd:

2012-05-18 17:26:37.762 NOTIFICATION connect to NQSSECONDARYCCS=;PORT=;SSLKEYSTOREPASSWORD=***;PRIMARYCCS=;USER=gerar600;PRIMARYCCSPORT=;TRUSTANYSERVER=;LOGFILEPATH=/var/tmp/;SECONDARYCCSPORT=;TRUSTSTOREPASSWORD=***;LOGLEVEL=;SSL=;HOST=sz0057.app.gen.local;CATALOG=;PASSWORD=***;
2012-05-18 17:26:38.687 NOTIFICATION connect to NQSSECONDARYCCS=;PORT=;SSLKEYSTOREPASSWORD=***;PRIMARYCCS=;USER=Administrator;IMPERSONATE=gerar600;PRIMARYCCSPORT=;TRUSTANYSERVER=;LOGFILEPATH=/var/tmp/;SECONDARYCCSPORT=;TRUSTSTOREPASSWORD=***;LOGLEVEL=;SSL=;HOST=sz0057.app.gen.local;CATALOG=;PASSWORD=***;
2012-05-18 17:26:39.527 WARNING java.io.IOException: [nQSError: 43001] Authentication failed for gerar600 in repository Star: invalid user/password.
2012-05-18 17:26:39.528 NOTIFICATION connect to NQSSECONDARYCCS=;PORT=;SSLKEYSTOREPASSWORD=***;PRIMARYCCS=;USER=Administrator;IMPERSONATE=gerar600;PRIMARYCCSPORT=;TRUSTANYSERVER=;LOGFILEPATH=/var/tmp/;SECONDARYCCSPORT=;TRUSTSTOREPASSWORD=***;LOGLEVEL=;SSL=;HOST=sz0057.app.gen.local;CATALOG=;PASSWORD=***;
2012-05-18 17:26:39.530 WARNING java.io.IOException: [nQSError: 43001] Authentication failed for gerar600 in repository Star: invalid user/password.
[051812_052639532][][EXCEPTION] java.sql.SQLException: java.io.IOException: [nQSError: 43001] Authentication failed for gerar600 in repository Star: invalid user/password.
	at oracle.bi.jdbc.AnaJdbcDriver.connectToNQSNode(AnaJdbcDriver.java:414)
	at oracle.bi.jdbc.AnaJdbcDriver.connect(AnaJdbcDriver.java:451)
	at java.sql.DriverManager.getConnection(DriverManager.java:582)

In this log of the OC4J log console file, we can see that the login of the user succeeded but not the impersonate login with the administrator credential. Then check and recheck that the OBIEE admin password contains only alphanumeric characters and not special characters such as @, !.

Diagnostic Log

  • Application Log
OracleBI/oc4j_bi/j2ee/home/application-deployments/xmlpserver/application.log
  • OC4J Console/Log

HTTP 500 error

With the use of the BI Server security, you can get an HTTP 500 error on some page :

  • the JDBC connection configuration
  • the Security > Roles and Permissions Tab

You can also find this entry in the application log file (OracleBI_Home\oc4j_bi\j2ee\home\application-deployments\xmlpserver\application.log)

  08/06/16 14:25:58.469 xmlpserver: Servlet error
  oracle.apps.xdo.security.ValidateException: java.io.IOException: 
prepare query failed[nQSError: 27004] Unresolved table: "SA System".

To resolve this problem, you must to set the OBIEE 10G/11G - SA System subject area up if you use BIP in a previous release than 10.1.3.3.

Documentation / Reference


Powered by ComboStrap