Setup Oracle eBS 2 Oracle BI SSO

I made several blogposts in the past. That’s why it could be useful to collect these posts into one document. This document covers all the steps needed to integrate Oracle eBS (R12) and Oracle BI (11g) with each other. SSO might be a little bit confusing. In this case it’s meant that you can log into Oracle BI via Oracle eBS without having to log in again and while maintaining context.

Oracle Business Analytics Version Releases

It has been a busy period over at Oracle with different Oracle Business Analytics software releases. For me personally the following releases attracted my attention:

As always, immediately after a new release of Oracle Business Analytics, there is a lot of buzz within the Oracle BA Community. What did impress me this time was that Oracle added some buzz as well in terms of various Youtube video’s. Next to that there is a lot of information online which should help you to get started. I will give an impression of some of the sources I have seen and used.

Oracle BI EE

There is lot of information online. There are two interesting links which are a little bit different than the others.

Oracle BIA

A few weeks ago Oracle BIA has gone GA.

This is a completely new version of Oracle BIA. There are a few interesting blog postings on this release. I provided links to the different blogs. There are probably more postings so have a look around.

Oracle Endeca 3.0

  • Oracle Endeca Information Discovery v3.0 Screencast Series
  • Oracle Endeca 123

Feel free to add if you would like.

Completely off-topic, but nevertheless interesting to add is the following.

Last year I had the privilege to review the following; ‘Oracle Business Intelligence Enterprise Edition 11g: A Hands-On Tutorial’. For more details on this book, check here.

Another interesting book on Oracle BI 11g is the one from Mark Rittman; ‘Oracle Business Intelligence 11g Developers Guide’

Check the Siebel Essentials site for two reviews about the books above.

One other thing I am working on is translating BITeamwork to the Dutch language. BITeamwork is all about Oracle Business Intelligence Collaboration.

As of this weekend the presentations of the RittmanMead BI Forum 2013 (Brighton and Atlanta) are online.

That’s it for now.

Setup Oracle BIA 7963 – Data Security

Customer wants to secure their data based on things like Ledger, Company, Operating Unit, etc. All users who login via Oracle eBS should inherit (based on the current responsibility) the same security settings in Oracle BI. The following is a possible solution.


  • Oracle eBS R12.1.1
  • Oracle BI EE
  • Oracle BIA

The setup consists of a few different parts

Data Security

The Data Security is based on different Roles and a Profile Option assigned to the responsibilities in Oracle eBS

Oracle eBS

  • Create ‘BI Type User’-profile option
  • Assign ‘BI Type User’-profile option to Responsibility
  • Assign Responsibility to User

Each Responsibility has either a specific ‘BI Type User’-profile option or a ‘BI Type User’-profile option on Site level. A view (xx_obia_user_groups_v) in Oracle eBS ‘holds’ the profile option information.

select fpov.level_value responsibility_id
 , fpov.level_value_application_id application_id
 , 'OBIA '
 || fpov.profile_option_value autorisatierol_code
 , fl.meaning autorisatierol
 from applsys.fnd_profile_option_values fpov
 , apps.fnd_profile_options_vl fpo
 , apps.fnd_lookups fl
 where fpo.profile_option_id = fpov.profile_option_id
 and fpo.application_id = fpov.application_id
 and fpov.profile_option_value = fl.lookup_code
 and fl.lookup_type = 'BI_TYPE_GEBRUIKER'
 and fpo.profile_option_name = 'XXBI_TYPE_GEBRUIKER'
 and fpov.level_id = 10003

Oracle BI

In Oracle BI, there is a Initialization Block which populates the; ROLES Session Variable

select ( select sector
 from apps.xx_obia_user_groups_v
 where responsibility_key = 'VALUEOF(NQ_SESSION.OLTP_EBS_RESP_KEY)'
 and responsibility_id = valueof ( nq_session.oltp_ebs_resp_id ) )
 || ';'
 || ( select autorisatierol_code "ROLES"
 from apps.xx_obia_user_groups_v
 where responsibility_key = 'VALUEOF(NQ_SESSION.OLTP_EBS_RESP_KEY)'
 and responsibility_id = valueof ( nq_session.oltp_ebs_resp_id ) )
 || ';'
 || ( select responsibility_key "ROLES"
 from apps.xx_obia_user_groups_v
 where responsibility_key = 'VALUEOF(NQ_SESSION.OLTP_EBS_RESP_KEY)'
 and responsibility_id = valueof ( nq_session.oltp_ebs_resp_id ) )
 from DUAL

Oracle Enterprise Manager (EM)

In the EM ‘all’ the different Application Roles, related to the Data Security are created.

There are a few different Application Roles;

  • Out-of the box
  • Data Security
  • eBS Profile


  • BIAdministrators
    • Administration privileges.
  • BIAuthors
    • Create, use or consume content.
  • BIConsumers
    • Use / consume content,
    • Every authenticated user.
  • BISystem
    • Component connections between products.

Data Security & eBS Profile

Oracle BI Administrator (Identity Manager)

In the Identity Manager, the Business Model Filters are applied to the Data Security Application Roles

The Business Model Filters are based on the Initialization Blocks. Some out-of-the-box, others custom.

This setup should be sufficient to apply Data Security to all queries, which query the Logical Tables with the Business Model Filters applied to it.

Note: Application roles data filters won’t apply for users with BI Administrator role.

By definition the BIAdministrator application role is granted the “” permission, which is equivalent to the 10g “Administrator” user who also had unrestricted access. Hence, data filters won’t affect users with BIAdministrator Role. (source: Oracle Support)

Integrating Oracle eBS R12 and Oracle BI 11g

I have made a blogpost in the past about integrating Oracle eBS R12 and Oracle BI 10g. In the course of an upgrade of Oracle BI Applications (OBIA) to OBIA, I came to the subject of integrating  Oracle eBS R12 and Oracle BI 11g. Of course you should start with the documentation. Next to that, Oracle provides a note on Oracle Support (ID 1343143.1). A lot of integration steps are equal to the 10g version.

In short:

There are two sides (Oracle eBS & Oracle BI) where you need to make some preparations.

Oracle eBS:

You need to define the link from to Oracle eBS to Oracle BI. This functionality hasn’t changed and I have described that process here. Combined with the ‘FND: Oracle Business Intelligence Suite EE base URL’-profile option in Oracle eBS, you now are ready to navigate from Oracle eBS to Oracle BI.

Oracle BI:

Now the Oracle eBS side is ready, you’ll have to prepare Oracle BI for accepting login requests from Oracle eBS. This parts differs a little from 10g. The changes in the Repository are still the same and consist of validation of the ICX-cookie in the Oracle eBS Connection Pool and the Authentication via Session variables. You can choose to either authenticate via the GROUP- system variable or directly via the new 11g ROLES-system variable.

After that you need to change the Oracle BI configuration;

  • authenticationschemas.xml (ORACLE_HOME/bifoundation/web/display)

authenticationschemas.xml (SchemaKeyVariable)


  • instanceconfig.xml (ORACLE_INSTANCE/config/OracleBIPresentationServicesComponent/coreapplication_obips1)


Note: Don’t get mislead by the following sentence; ‘<!–This Configuration setting is managed by Oracle Enterprise Manager Fusion Middleware Control–>’. You must adjust these settings directly in the instanceconfig.xml itself.

This should (all) be sufficient to log into Oracle BI via a selected responsibility in Oracle.

In a following post I will cover the subject of applying Data Security in Oracle BI, based on the Oracle eBS Responsibility.

Patching in Oracle BI 11g

Last week the announcement came that there has been a Patch (13742915) released for Oracle BI EE This Patch can be dowloaded via Oracle Support (Login required).

If you want to apply the Patch, you have to perform a few steps. These steps can differ, depending on which steps you may or may not already have executed.

Start by downloading the Patch first.

Download Patch 13742915

1. Navigate to ‘Patches & Updates’
2. Search for Patch 13742915
3. Select the required OS (I installed on Windows 64-bit)
4. Dowload the Patch

When the Patch has been dowloaded, go to your Oracle BI environment for the installation of the Patch. The following steps have to be performed.

Patch Installation for server
1. Stop all BI Services, including BI Server, BI Administration Tool, BI Javahost, BI Scheduler (Start->Programs->Oracle BI->Stop BI Services)
2. Backup ORACLE_HOME\bifoundation\server
3. Backup ORACLE_INSTANCE\bifoundation\OracleBIServerComponent\coreapplication_obis1\repository
4. Apply Patch using Opatch (See below)
5. Start all BI Services
6. Test the Patch
7. If needed, rollback the Patch (See below)

Apply Patch using Opatch

Note: Make sure all BI Services have stopped

Check Oracle Support; ‘OBIEE 11g: How to Apply Patches Using Opatch’ (Id – 1220799.1)

1. Verify whether you have (the latest) version of Opatch installed

a) If needed install a new version of the Opatch utility (See below)
Make sure ….

… the ORACLE_HOME variable exists –> set ORACLE_HOME=MIDDLEWARE_HOME\Oracle_BI1

Side-note from Oracle: “You are setting ORACLE_HOME for this command-prompt session only.
— This is set to only facilitate patch application/rollback/inventory, etc.
— Do not make this a permanent environment variable as it will affect other Oracle Products running
— on your system which may need a permanent ORACLE_HOME environment variable.”

… the OPATCH-directory is in your PATH –> set PATH=%PATH%;ORACLE_HOME\OPatch

2. Read through the README.txt
3. Download the Patch to the PATCH_TOP directory (in my case; Z:\Oracle\FMW\OracleBIAdmin\PATCH_TOP)
4. CD to Patch directory (in my case; Z:\Oracle\FMW\OracleBIAdmin\PATCH_TOP\13742915)
5. Apply Patch
a) run “opatch apply” (without quotes)
— If you are running a 64-bit OS: Windows64bit / Linux 64bit / AIX 64bit
b) run “patch apply -jre %ORACLE_HOME%/jdk/jre” (without quotes)
6. OPatch succeeded
7. Verify the succesful installation
a) run “opatch lsinventory” (without quotes)
— If you are running a 64-bit OS: Windows64bit / Linux 64bit / AIX 64bit
b) run “opatch lsinventory -jre %ORACLE_HOME%/jdk/jre” (without quotes)
8. OPatch succeeded

Note: All BI Services can be started

Rollback the Patch using Opatch
1. CD to Patch directory (in my case; Z:\Oracle\FMW\OracleBIAdmin\PATCH_TOP\13742915)
2 Rollback Patch
a) run “opatch rollback -id 13742915” (without quotes)
— If you are running a 64-bit OS: Windows64bit / Linux 64bit / AIX 64bit
b) run “opatch rollback -id 13742915 -jre %ORACLE_HOME%/jdk/jre” (without quotes)

How to install the Opatch utility

1. Navigate to ‘Patches & Updates’
2. Search for Patch 6880880
3. Select the required OS (I installed on Windows 64-bit)
4. Dowload the Patch
5. Extract the zip file directly under the ORACLE_HOME (MIDDLEWARE_HOME\Oracle_BI1).
6. Backup ORACLE_HOME\OPatch
7. Make sure no directory ORACLE_HOME\OPatch exist
8. Unzip the OPatch downloaded zip into the ORACLE_HOME directory
9. check the version of the OPatch utility
b) run “opatch version” (without quotes)

Good Luck!