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
Two files must be modified in order for WebLogic to find, accept and attach to the EBS session.
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.
<Authentication> <!--This Configuration setting is managed by Oracle Enterprise Manager Fusion Middleware Control--> <EnabledSchemas>UidPwd,Impersonate,UidPwd-soap,Impersonate-soap,EBS-ICX</EnabledSchemas> </Authentication>
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
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" biVariableName="NQ_SESSION.ICX_SESSION_COOKIE" />
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" biVariableName="NQ_SESSION.SERVICEINSTANCEKEY" />
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
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:
SELECT FND_GLOBAL.RESP_ID, FND_GLOBAL.RESP_APPL_ID, FND_GLOBAL.SECURITY_GROUP_ID, FND_GLOBAL.RESP_NAME, FND_GLOBAL.USER_ID, FND_GLOBAL.EMPLOYEE_ID, FND_GLOBAL.USER_NAME, FND_GLOBAL.RESP_NAME FROM DUAL
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 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.
SELECT DISTINCT 'ROLES', RESPONSIBILITY_NAME FROM FND_USER, FND_USER_RESP_GROUPS, FND_RESPONSIBILITY_VL WHERE FND_USER.USER_ID = FND_USER_RESP_GROUPS.USER_ID AND FND_USER_RESP_GROUPS.RESPONSIBILITY_ID = FND_RESPONSIBILITY_VL.RESPONSIBILITY_ID AND FND_USER_RESP_GROUPS.RESPONSIBILITY_APPLICATION_ID = FND_RESPONSIBILITY_VL.APPLICATION_ID AND FND_USER_RESP_GROUPS.START_DATE < SYSDATE AND (CASE WHEN FND_USER_RESP_GROUPS.END_DATE IS NULL THEN SYSDATE ELSE TO_DATE (FND_USER_RESP_GROUPS.END_DATE) END) >= SYSDATE AND FND_USER.USER_ID = (SELECT USER_ID FROM FND_USER WHERE UPPER (USER_NAME) = UPPER('VALUEOF(NQ_SESSION.USER)') )
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
2. In the Properties Tab, add as follows:
Type: SSWA jsp function
Maintenance Mode Support: None
Context Dependence: Responsibility
Figure 8: Oracle EBS Configuration - Form Functions - Properties
3. In the Web HTML tab, add the following link:
HTML Call: OracleOasis.jsp?mode=OBIEE&function=Dashboard
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
Note: Only create Menu for OBIEE Dashboard
5. Assign Menu to the relevant responsibility:
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
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!