Nolem的部落格

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

Author : Nolem

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=Answers
   dashboard 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_ADMIN
   Available From: Oracle Self Service Web Applications
   Data Group: Standard
   Application: 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 is PROD
 

                            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 Tab
      Enter connection pool name = ex:Ebs R12
      Call 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 tab
      Execute on connect ->New
      Physical 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 SSO
      Required 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_ID
   ROLES
   EBS_USER_ID
   EBS_EMPLOYEE_ID
   USER
   
    
   
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 is OBIEE_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