OBIEE 11g Integration into Oracle E Business Suite

Creating a Database Object and Connection Pool for the Oracle
E-Business Suite Database & Launch reports from E Business Suite.

To enable integration with Oracle E-Business Suite, you must create a database object
and connection pool for the Oracle E-Business Suite database in the Oracle BI
repository.

To create a database object and connection pool for the Oracle E-Business Suite
database:

Creating the Database Object within RPD

1. In the Administration Tool, open the repository that you want to integrate with
Oracle E-Business Suite.
2. Right-click in the Physical layer and select New Database.
3. Enter a name for the new database (for example, Oracle E-Business Suite 12).
4. For Database, select the appropriate Oracle Database type for your Oracle
E-Business Suite database (for example, Oracle 10g R2 or Oracle 11g).
5. Click OK.
6. Right-click the new database object you just created and select New Object, then
select Connection Pool.
7. Enter a name for the connection pool (for example, Oracle E-Business Suite 12).
You must provide a unique name for this connection pool. Because of this
requirement, do not name the object 'Connection Pool.'
8. For Call interface, select OCI 10g/11g.
9. For Data source name, enter the TNS name of the Oracle E-Business Suite
database.
10. For User name and Password, enter the user name and password of the Oracle
E-Business Suite super user. (Apps/Apps)
11. Select the Connection Scripts tab.
12. Click New for Execute on connect.
13. Enter the following physical SQL, and then click OK:
call APP_SESSION.validate_icx_session('valueof(NQ_SESSION.ICX_SESSION_COOKIE)')
14. Click OK in the Connection Pool dialog.
Save ALL

Setting up Authentication

We need to start by setting up Session Variables for Authentication

To set up proper authentication , you must set up eight session variables and an initialization block in the Oracle BI repository.

To set up session variables for authentication in the Administration Tool:

1. In the Administration Tool, open the repository that you want to integrate with
Oracle E-Business Suite.
2. Select Manage, then select Variables.
3. From the Action menu, select New, then Session, then Initialization Block.
4. Enter a name for the initialization block (for example, Oracle E-Business SSO).
5. Click Edit Data Source.
6. For Default initialization string, enter the following:
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
FROM DUAL

This utalises the standard fnd_global package available within the E Business Suite to retrieve the
login credentials.

7. For Connection Pool , click Browse , select the connection pool you created for the Oracle E-Business Suite database (for example, Oracle E-Business Suite 12), and click Select.

Should look


8. Click OK.
9. Click Edit Data Target .
10. Create the following session variables:

EBS_RESP_ID
EBS_RESP_APPL_ID
EBS_SEC_GROUP_ID
EBS_RESP_NAME
EBS_USER_ID
EBS_EMPLOYEE_ID
USER

To do this, click New, enter the variable name, and then click OK. Click Yes when


you receive a warning about the special purpose of the USER variable.

You must ensure that the variables are listed in the given order. If necessary, select a variable and click Up or Down to reorder the list.

11. Click OK in the Session Variable Initialization Block Variable Target dialog.
12. In the Session Variable Initialization Block dialog, select Required for authentication.
13. Click OK.
14. Save the repository


Updating authenticationschemas.xml

You must update the authenticationschemas.xml file to add the name of the EBS ICX
authentication cookie.

To update authenticationschemas.xml:

1. Open the file authenticationschemas.xml for editing. You can find this file at:
ORACLE_HOME/bifoundation/web/display

2. Find the following element:
<AuthenticationSchema name="EBS-ICX"
Setting Up Authentication

3. Locate the sub-element RequestVariable source="cookie" and change the value of
the nameInSource attribute from ICX_SESSION to the name of the EBS ICX
authentication cookie prefix.

For example:
<RequestVariable source="cookie" type="auth" nameInSource="VIS" biVariableName=
"NQ_SESSION.ICX_SESSION_COOKIE" />

Do not update the RequestVariable source="url" sub-element.

“Cookie” can be found here

1. Log in to Oracle E-Business Suite.

2. Enter the following text in the address bar of your browser:
javascript:document.writeLn(document.cookie);

3. The cookie is displayed. For example:

ORA_BIPS_LBINFO=1262d6a5f9a; ORA_MOS_LOCALE=en%7CUS; ORA_UCM_
INFO=3~00027147766664614052270216870092~Taylor~Michael~mike.tay
lor@oracle.com~USA~en~~~~~1; ORA_UCM_VER=%2FMP%2F8kgic%2Cr_
wjmp%3Emp_ajc%2CamkMP%2F8iega*p%5Duhkn%3Ckn%5D_ha*_
kiMP%2F8%2F26%2C65%2C7%2C22MP%2F8-04*43*5*00; ORA_UCM_
SRVC=3*OTN~1~0~//~null~*OPN~1~0~//~SE1%3ASE1%3ASE1%3ASE1%3ASE1%
3ASE1%3ASE1%3ASE1%3A~*EMP~1~0~/34/~null~*GMO~1~0~//~null; ORA_
TAHITI_PREFS=-0--------------; VIS=ZcEJeoLNVqcHGiGYvCpzTx3N:S;
ADMINCONSOLESESSION=0yQmLP2D67vJKgtXLxsNl534QTWlThYkyvXfR0fjFK0
LPsD3Hh83!1322564050

The value you need to provide in authenticationschemas.xml is
the prefix of the EBS ICX authentication cookie. In the previous
example, the EBS ICX authentication cookie is
VIS=ZcEJeoLNVqcHGiGYvCpzTx3N:S;, and the prefix is VIS.

4. In the same entry (RequestVariable source="cookie"), ensure that the value of the
biVariableName attribute is the same as the value you entered as part of the
connection script when you created the connection pool for the Oracle E-Business
Suite database. “See Creating Database objects within RPD at start of document”

5. Find the following element:
<SchemaKeyVariable source="cookie"


6. Change the value of the nameInSource attribute from ICX_SESSION to the name
of the EBS ICX authentication cookie prefix (often VIS). For example:

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

7. Save and close the file.

Updating instanceconfig.xml

You must update the instanceconfig.xml file to add EBS-ICS as one of the enabled
schemas, and set it as the default.

To update instanceconfig.xml:

1. Open the file instanceconfig.xml for editing. You can find this file at:
ORACLE_INSTANCE/config/OracleBIPresentationServicesComponent/coreapplication_
obipsn

2. Locate the Authentication element.
3. Include EBS-ICX in the list of enabled schemas.

For example:
<EnabledSchemas>UidPwd,Impersonate,UidPwd-soap,Impersonate-soap,EBS-ICX</EnabledSchemas>

Ignore the comment in instanceconfig.xml that says this setting is centrally managed. EBS-ICX must be manually added to the EnabledSchemas element.
4. Save and close the file.
5. Restart Oracle Business Intelligence. From the Fusion Middleware Control






E Business Suite Functional Setup

Creating Menu within E Business Suite to Link to OBIEE.

To embed a link in Oracle E-Business Suite that opens Oracle Business Intelligence
dashboards, you need to create a form function and then assign menus and responsibilities.

Before you begin, log in to Oracle E-Business Suite as the system administrator (for example, sysadmin). Then, select the System Administrator responsibility from the responsibility navigator pane on the left. The available menus appear on the right.

Follow the steps in this section to create the following objects, in sequence:

■ Function
■ Menu
■ Responsibility – Attach to a User
■ Profile



Create a Form Function

This section explains how to create a form function in Oracle E-Business Suite.
To create a form function:

1. From the Application menu, select Function. The Form Functions dialog appears.
2. Enter the name of the function in the Function field (for example, OBIEE).
3. Enter the user function name (for example, OBIEE).
4. Enter a description (for example, Link to Oracle BI).
5. Save your changes using the Save button on the toolbar.
6. Select the Properties tab.
7. For Type, enter SSWA jsp function.
8. Select the Web HTML tab.
9. For HTML Call, enter one of the following options:
■ To link to Answers, enter:
OracleOasis.jsp?mode=OBIEE&function=Answers
■ To link to Dashboards, enter:
OracleOasis.jsp?mode=OBIEE&function=Dashboard
10. Save your changes using the Save button on the toolbar, and then close the Form
Functions dialog.


Create a Menu That Invokes the Form Function

To create a menu that invokes the form function:

1. From the Application menu, select Menu. The Menus dialog appears.
2. Enter the name of the menu in the Menu field (for example, OBIEE).
3. Enter a User Menu Name (for example, OBIEE).
4. For Menu Type, enter Standard.
5. For Function, enter the name of the function you created earlier.
6. Save your changes using the Save button on the toolbar, and then close the Menus
dialog.


Assign the Menu to a Responsibility

To assign the menu to a new responsibility:

1. Select Responsibilities from the Top Ten List.
2. Enter a name for the responsibility (for example, OBIEE).
3. For Application, enter the application for which you created the menu.
4. For Responsibility Key, define any unique value. To ensure that this value is unique, the Responsibility Key is not translated.
5. For Available From, select Oracle Self Service Web Applications.
6. For Data Group, enter Standard for Name and re-enter the application name for Application.
7. For Menu, enter the name of the menu you created earlier.
8. Save your changes using the Save button on the toolbar, and then close the Responsibilities dialog.

Assign the Responsibility to a user.

Under System Administrator – Assign Resp to user

Create a Profile Option

To set profile options for a responsibility:

1. From the Application menu, select Profile
2. Select Responsibility, and then enter the name of the responsibility to which you
assigned the menu in.
3. Enter %Business Intelligence% in the Profile field.
4. Click Find.
5. On the resulting screen, under Responsibility, enter the Oracle Business
Intelligence URL. For example:
http://my_server.domain.com:port
For port, enter the Web server port where Oracle Business Intelligence is running
(for example, 9704).
6. Save your changes using the Save button on the toolbar.


Thats it clear your Functional Cache from the Functional Administrator Responsibility.
Login as user with Resp setup earlier and Launch from the the menu that you just set up !!.




Below Information from other blog

Integration OBIEE 11G with Oracle E-Business Suite R12 (EBS R12)

Demo environment

Oracle E-Business Suite 12.1.3

Oracle Database 11.2.0.3

Oracle business intelligence 11.1.1.5

Deploy method:

1. Login Oracle E-Business Suite and select System Administratorresponsibility
2. Create 2 function  
   Descriptions : create answers and dashboard function 
   Properties : both use SSWA jsp function 
    
   Web HTML: 
   answers use=OracleOasis.jsp?mode=OBIEE&function=Answersdashboard use =OracleOasis.jsp?mode=OBIEE&function=Dashboard 
    
3. Create menu
   create menu and add 2 prompt , prompt 1=Answers/Function=OBIEE ANSWER, prompt 2=Dashboard/Function=OBIEE DASHBOARD
     
4. Create Responsibility 
   create responsibility , ex : OBIEE_ADMINAvailable From: Oracle Self Service Web Applications
   Data Group: StandardApplication: chose that we want to use
    

1. Assign the Responsibility to user
    
2. Set Responsibility Profile
     
   select responsibility that we create, and filter profile %Business Intelligence% Edit profile :FND: Oracle Business Intelligence Suite EE base URL
   with responsibility or also with site level , input our OBIEE url ex: http://oel5u7-x64.nolem.net:9704
    
3. Clear EBS framework cache , Path: Functional Administrator->Home->Core Service tab->Caching Framework->Global configuration->Clear All Cache button  
    
4. Check ebs cookie
   1. login into R12 with your browser
   2. check browser cookie value of R12 seesion

                             i.                Firefox:
  
 
log cookie cookie name information (case sensitive) ,here isPROD 

                            ii.                Internet Explorer
input javascript:document.write(document.cookie) at address bar
 
and you will see as below , log cookie prefix information (case sensitive) ,here is PROD 

 Open shell console , and edit$BI_ORACLE_HOME/bifoundation/web/display/authenticationschemas.xml
 

1. 
   
   1. Find <SchemaKeyVariable source="cookie"
       
      change nameInSource= “ICX_SESSION” to our cookie prefix ,here is “PROD”
       
   2. Find tag <AuthenticationSchema name="EBS-ICX"
       

Find sub-element <RequestVariable source="cookie" type="auth" nameInSource="ICX_SESSION" biVariableName="NQ_SESSION.ICX_SESSION_COOKIE"/>
change nameInSource to our EBS cookie prefix (here is “PROD”)
<RequestVariable source="cookie" type="auth" nameInSource="PROD" biVariableName="NQ_SESSION.ICX_SESSION_COOKIE"/>
 

1. Open shell console , and edit$INSTANCE_HOME/config/OracleBIPresentationServicesComponent/coreapplication_obips1/instanceconfig.xml
    
   1. Find tag <Authentication> 
   2. Add EBS-ICX schema to EnabledSchemas’s line <EnabledSchemas>UidPwd,Impersonate,UidPwd-soap,Impersonate-soap</EnabledSchemas>
      to
      <EnabledSchemas>UidPwd,Impersonate,UidPwd-soap,Impersonate-soap,EBS-ICX</EnabledSchemas> 
       
2. Install “Business Intelligence Enterprise Edition Plus Client”and run Administration tool.
   you can download BIEE Client Admin tool from here
   
3. If you just want create only 1 connection pool from BIEE Client Admin tool , set “Allow first connection pool for Init BlockS” before you create RPD ,from admin tool menu: Tools\Options\General
     
4. Create new Repository for EBS use
   1. Input Repository name and Password ,ex: bi_dev
   2. At Phisical layer , right-click and select New Database.
      Enter a database name = ex:ebsR12
      Database type = ex: Oracle 11g/Exadata
   3. At database that we create , right-click -> new object->Connection pool
      General TabEnter connection pool name = ex:Ebs R12Call interface = OCI 10g/11g
      Data source name = TNS of the Oracle E-Business Suite , ex: PROD
      User name and Password = E-Business Suite apps user ,ex: apps/apps
      Connection Scripts tabExecute on connect ->NewPhysical SQL -> call /* valueof(NQ_SESSION.ACF) */ APP_SESSION.validate_icx_session('valueof(NQ_SESSION.ICX_SESSION_COOKIE)')
   4. Import E-Business Metadata for Business layer and presentation layer use later,ex:import app.FND_USER_RESP_GROUPS_ALL /apps.FND_USER_VIEW
   5. Create Business layer and presentation layer.
        
      
5. Create authentication variables ,path: menu\Manage\Variables
     
   1. Menu: Actions\New\Session\Initial Blocks
      name-> ex:EBS R12 SSORequired for authentication->Checked
   2. Edit Data Source-> Default initialization string , enter SQL like below :

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
FROM DUAL

1. Connection pool 
   select E-Business database and connection pool that we create in physical layer.
    

1. Edit Data Target ,create 7 new target variables (PS:ensure variables sequence order like below ),ignore “ROLES” and “USER” reserve warning.EBS_RESP_ID
   EBS_RESP_APPL_ID
   EBS_SEC_GROUP_IDROLES
   EBS_USER_ID
   EBS_EMPLOYEE_IDUSER
   
    
2. Check global consistency and save repository.
   
   
3. Upload repository to Business Intelligence server
   1. Login EM console ,ex:http://oel5u7-x64.nolem.net:7001/em , Path : Business Intelligence\coreapplication\ repository->lock and edit config .
       
   2. Upload our RPD file and set RPD password , then apply 
        
      
4. Set Business Intelligence security and create role of EBS OBIEE responsibility ,here isOBIEE_ADMIN
   1. Path : Business Intelligence\coreapplication\Security\Set application role
       
   2. Create role OBIEE_ADMIN same with EBS responsibility name ,and assign members to this role .
        
      
5. Restart Business Intelligence service
    
   1. Test integration result ,login ebs and run OBIEE responsibility ,check result .
        
      Answers result:
       
      Dashboard Result: 
      Account OBIEE_ADMIN role