OBIEE12c Integration with Oracle EBS Security


Integration of the Oracle Business Intelligence Enterprise Edition and Oracle E-Business Suite provides a seamless controlled flow between the systems, allowing for drill down and drill back from key metrics to underlying detail. Theoretically, this can be done between any OBIEE form and any EBS form with pass-thru to any connected EBS subsystem.

If you are integrating OBIEE with EBS, you are likely to be using OBIA, Oracle Business Intelligence Analytics, although this is certainly not a requirement. OBIA is a pre-built, pre-packaged BI solution that delivers role-based intelligence to the organization. It is a set of OBIEE dashboards and reports that run from a pre-built warehouse previously serviced by Informatica/DAC, while the next generation of the OBIA warehouse utilizes Oracles Data Integrator, ODI, which runs high-volume batch load plans, event-driven load plans, and even SOA data services.

1. OBIEE 12c Configuration

While configuring an initialization block to retrieve data from EBS, make sure that Row-wise initialization is checked, as this allows multiple results to be stored in the variable, regardless of whether the variable is static or dynamic; otherwise you will only be able to retrieve the last item in the result set. Be sure to set the Execution Precedence of the EBS Integration init block that attaches the session through the session cookie, so that it executes before any attempt is made to retrieve security information.

Figure 1: EBS Configuration

Figure 1: EBS Configuration

Two files must be modified in order for WebLogic to find, accept and attach to the EBS session.

• instanceconfig.xml

• authenticationschema.xml

To configure external authentication, you will need to modify instanceconfig.xml as follows:
Path to instanceconfig.xml:


Note: Take a backup of the file before editing.

Add “EBS-ICX” in the EnabledSchemas xml tag.

<!--This Configuration setting is managed by Oracle Enterprise Manager Fusion 
Middleware Control-->

Middleware recognizes internal schema name for interface to EBS "EBS-ICX". The Oracle already worked on that. It is only necessary to let the system know that this is to be utilized. Then, let the system know the name of the session cookie that EBS writes.
To configure authenticationschema.xml it is necessary to know the Oracle EBS instance cookie name.
Follow the steps to get the cookie name.

1. Login to Oracle EBS

2. If you are using Google Chrome or Firefox then open Dev Tools ➜ Web Console and write the following command:




ALERT command will pop up a dialog box as follows, while DOCUMENT.WRITE will display the cookie information in browser window.

Figure 2: Alert command pop up

Figure 2: Alert command pop up

Notice that key value pair of the cookie, ERPU1 is the cookie name of Oracle EBS Test instance and the value dynamically generated for each user after each login. We only required the key from it which is “ERPU1”. Now we will use this value in authenticationschema.xml file.

Path to authenticationschema.xml file:


Note: Take a backup of the file before editing.

Edit the following tags in the file:

<SchemaKeyVariable source="cookie" forceValue="EBS-ICX" nameInSource="ERPU1" />


<RequestVariable source="cookie" type="auth" nameInSource="ERPU1" 

As per Oracle Doc ID 2141505.1
"Access Prohibited" When Logging In To Analytics In EBS Integrated Environment.
Following tag need to be added in the file under “AuthenticationSchema”.

<RequestVariable source="constant" type="auth" nameInSource="ssi" 

That’s it for OBIEE configuration!


2. RPD Changes using Administration Tool

Here comes the part that is familiar to every OBIEE administrator, the RPD modifications. If you are following the document, the sample EBS connection pool can be used or create a new one just for the initialization process and retrieving security.
Create database objects and connection pools for Oracle EBS database.

Figure 3: EBS Connection Pool

Figure 3: EBS Connection Pool

Note: APPS user should have all READ and EXECUTE permissions to run PL/SQL queries. If not, grant the privileges to the APPS user.

Now, create an init block which will use this connection pool to retrieve the EBS context and set that into OBIEE session variables. The init block will use the just defined connection pool and will send a data source query to the EBS database:


Figure 4: EBS Security Context

Figure 4: EBS Security Context

Referring to another Oracle document, 1539742.1, create these static session variables to hold the context:


Note: You have to create another init block named “EBS Security Context – ROLES – Row wise” only for ROLES as a user will have more than one Role in Oracle EBS and the init block will be set for row-wise initialization.

Figure 5: EBS Security Context - Roles

Figure 5: EBS Security Context - Roles

Figure 6: EBS Security Context - Roles - Row Wise

Figure 6: EBS Security Context - Roles - Row Wise

The following query will be used to fetch all the Responsibilities of log-in user and assign it to the variable ROLES.



3. Oracle EBS Configuration

Now we need to introduce a responsibility for OBIEE through which a user can navigate to OBIEE from Oracle EBS.

1. Create a Function, using Application in EBS:

Figure 7: Oracle EBS Configuration - Form Functions - Description

Figure 7: Oracle EBS Configuration - Form Functions - Description

2. In the Properties Tab, add as follows:

Function: OBIEE
Type: SSWA jsp function
Maintenance Mode Support: None
Context Dependence: Responsibility

Figure 8: Oracle EBS Configuration - Form Functions - Properties

Figure 8: Oracle EBS Configuration - Form Functions - Properties

3. In the Web HTML tab, add the following link:

Function: OBIEE
HTML Call: OracleOasis.jsp?mode=OBIEE&function=Dashboard

Figure 9: Oracle EBS Configuration - Form Functions - Web HTML

Figure 9: Oracle EBS Configuration - Form Functions - Web HTML

4. Create a Menu in Oracle EBS named “OBIEE Dashboard” and add the Function created in step 1:

Figure 10: Oracle EBS Configuration - Menus

Figure 10: Oracle EBS Configuration - Menus

Note: Only create Menu for OBIEE Dashboard

5. Assign Menu to the relevant responsibility:

Figure 11: Oracle EBS Configuration - Users

Figure 11: Oracle EBS Configuration - Users

6. Set Profile

You need to enter the URL of the Oracle BI Server as part of a profile. You can set up a profile for a responsibility, a user, or a site. The following procedure shows how to set profile options for a responsibility:

Figure 12: Oracle EBS Configuration - Find System Profile Values

Figure 12: Oracle EBS Configuration - Find System Profile Values

You should use a fully-qualified host server.domain name rather than an IP address or just a host name. The OBIEE domain must be the same as the Oracle EBS domain, so that the EBS-ICX cookie is visible to OBIEE from the user's browser.

OBIEE 12c: Integrating OBIEE 12c with Oracle E-Business Suite (EBS) Security (Doc ID 2174747.1)
Chapter 9: Oracle® Fusion Middleware Integrator's Guide for Oracle Business Intelligence Enterprise Edition.

Click here if you would like to receive more information about the topic or if you need help with your EBS-OBIEE configuration!

Oracle EBS Responsibilities to OBIEE migration using WebLogic Scripting Tool (WLST)


Previously if you wanted to log onto your Oracle E-Business Suite as well as your Oracle Business EE/BI it was necessary to use two different usernames and passwords. In this blog we will show you how to create one username and password for both.

Keeping in mind that integration steps for many versions will vary, let’s focus on the following software versions:

  • ᵒ Oracle E-Business Suite R12.1.2
  • ᵒ Oracle BI EE 11g (
  • ᵒ Oracle Business Applications

For Oracle EBS R12, there are no pre-requisite patches required since base R12 has all the functionality that OBIEE leverages, namely the OBIEE profile defined in b5869360.ldt, OracleOasis.jsp and APP_SESSION package which are found in base R12.

You should backup all the files before making changes (i.e. repository, configuration files, catalog etc.)

NOTE: We also assume that you have configured the OBIEE responsibility in Oracle EBS as defined in the document Oracle Doc.

1.      What is Web Logic Scripting Tool (WLST)?

The WebLogic Scripting Tool (WLST) is a command-line scripting interface that system administrators and operators use to monitor and manage WebLogic Server instances and domains. The WLST scripting environment is based on the Java scripting interpreter, Jython. In addition to WebLogic scripting functions, you can use common features of interpreted languages, including local variables, conditional variables, and flow control statements. WebLogic Server developers and administrators can extend the WebLogic scripting language to suit their environmental needs by following the Jython language syntax. See

2.      What Does WLST Do?

WLST lets you perform the following tasks:

  1. Propagate a WebLogic Server domain to multiple destinations using predefined configuration and extension templates.  See Creating and Configuring WebLogic Domains Using WLST Offline
  2. Retrieve domain configuration and runtime information. See Navigating and Interrogating MBeans
  3. Edit the domain configuration and persist the changes in the domain's configuration files. See Editing onfiguration MBeans
  4. Edit custom, user-created MBeans and non-WebLogic Server MBeans, such as WebLogic Integration Server and WebLogic Portal Server MBeans. See Accessing Custom MBeans
  5. Automate domain configuration tasks and application deployment. See Automating WebLogic Server Administration Tasks
  6. Control and manage the server life cycle. See Managing Servers and Server Life Cycle
  7. Access the Node Manager and start, stop, and suspend server instances remotely or locally, without requiring  the presence of a running Administration Server. See Using WLST and Node Manager to Manage Servers.

WLST functionality includes the capabilities of these WebLogic Server command-line utilities: the weblogic.Admin utility that you use to interrogate MBeans and configure a WebLogic Server instance (deprecated in this release of WebLogic Server), the wlconfig Ant task tool for making WebLogic Server configuration changes, and the weblogic.Deployer utility for deploying applications. For more information about these command-line utilities, see:

  • ᵒ "Using Ant Tasks to Configure and Use a WebLogic Server Domain" in Developing Applications with WebLogic      Server, which describes using WebLogic Ant tasks for starting and stopping WebLogic Server instances and  configuring WebLogic Server domains.
  • ᵒ "Overview of Deployment Tools" in Deploying Applications to WebLogic Server, describes several tools that    WebLogic Server provides for deploying applications and stand-alone modules.

You can create, configure, and manage domains using WLST, command-line utilities, and the Administration Console interchangeably. The method that you choose depends on whether you prefer using a graphical or command-line interface, and whether you can automate your tasks by using a script. See Script Mode.

From now on we will define how we use script to load Oracle EBS Responsibilities into OBIEE.

3.       Extract list of Oracle EBS Responsibilities

The following is the generic query, which can be used to extract the list of all responsibilities from Oracle EBS.

a. Connect to Oracle EBS (DEV, UAT or PROD) and run the following query.

4.       Export results in excel sheet

If you are using Oracle SQL Developer following, this is the way to export to excel.

a. right click on the result grid and click export

b. select xls from the list format

5.       Transpose excel sheet rows to columns

a. Select columns that have a list of responsibilities

b. open new sheet and right click -> paste special

c. check on the Transpose checkbox

d. save this sheet as csv (comma separated values) and name it “EBS_Respo.csv”

6.      Copy csv  file (EBS_Respo.csv) from local computer to OBIEE server to the following:

7.      Create a script file as  as following

8.       Copy above file to the same directory

9.      Run the following command on bash

NOTE: After running this python script it will connect to WebLogic Server and create the Oracle EBS Responsibilities in OBIEE. You need to restart OBI service from WebLogic Enterprise Manager to reflect these changes to repository.

To learn more about WLST you can click on the following URL:

To learn more about OBIEE and WebLogic security navigate to oracle document:

privacy policy - Copyright © 2000-2010 ClearPeaks