When you have to configure BIP to work completely with OBIEE, you will have then three main tasks to perform:
- the configuration of the security through the OBIEE BI Server
- 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.
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 ?
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
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.
The administrator password must only contain alphanumeric characters.
- Username/Password : SuperUser/SuperUser
|Type||Oracle BI server|
|Database Driver Clase||oracle.bi.jdbc.AnaJdbcDriver|
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|
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.
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 thenode.
<CredentialStore> <CredentialStorage type="file" path="OracleBIData_HOME/web/config/credentialstore.xml" passphrase="secret_passphrase"/> </CredentialStore>
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.
|Database Driver Class||oracle.bi.jdbc.AnaJdbcDriver|
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|
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 !
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 @, !.
- Application Log
- OC4J Console/Log
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.