This discussion is archived
10 Replies Latest reply: Nov 22, 2012 1:38 AM by RR RSS

EBS R12 Integration with OBIEE11g

RR Newbie
Currently Being Moderated
Hi all,

Kindly share some links or document for EBS R12 Integration with OBIEE11g.

Thanks in advance,
RR
  • 1. Re: EBS R12 Integration with OBIEE11g
    vidyas Explorer
    Currently Being Moderated
    Hello,

    You should be first looking into the Security Guide - [http://download.oracle.com/docs/cd/E14571_01/bi.1111/e16364/ebs_actions.htm#CJAFDEIB]

    NOTE "555254.1" Enabling Action Links in Oracle Business Intelligecne Applications for Oracle E-Business Suite"

    NOTE:755768.1 - OBIEE integrated with ebs: Directly logon to OBIEE without going via EBS route

    To troubleshoot login issues : Integrating OBIEE with E-Business Suite gives message "You are not currently logged in to the Oracle BI Server" [ID 749190.1]

    Please mark if helpful. Let me know if you have any questions.

    Thanks,
    -VS
  • 2. Re: EBS R12 Integration with OBIEE11g
    RR Newbie
    Currently Being Moderated
    Thanks.

    I have followed that link. In admin tool i have created new database and connection pool.

    There i mentioned data source of EBS Suite and user name and password for this EBS.

    While assigning connection pool for the Initialization block i am getting error like

    *'Connection pool name ' which is used for report queries. This may impact query performance*

    After finish that document i cannot open repository in online.

    Please help me regarding this.
  • 3. Re: EBS R12 Integration with OBIEE11g
    vidyas Explorer
    Currently Being Moderated
    Did you create a dedicated connection pool for the EBS Authentication pool ?

    Also make sure you have enabled the "Allow first connection pool for Init Blocks" when you go to Tools > Options > General tab > Check the box where it says "Allow first connection pool for Init Blocks"

    I am not sure if we can open the Repsitory in the Online mode once you have enabled this Init block with that connect script.

    Let me know.

    Thanks,
    -VS
  • 4. Re: EBS R12 Integration with OBIEE11g
    RR Newbie
    Currently Being Moderated
    Thanks Vidya.

    Yes,I have created separate connection pool for the script.

    After giving this call APP_SESSION.validate_icx_session('valueof(NQ_SESSION.ICX_SESSION_COOKIE)') I am getting Unable to connect database.

    I cannot open repository in online.

    valueof(NQ_SESSION.ICX_SESSION_COOKIE) is this value is cookie name whether we got from EBS?

    Please guide me regarding this.
  • 5. Re: EBS R12 Integration with OBIEE11g
    vidyas Explorer
    Currently Being Moderated
    Hello,

    Looks like there is problem with Connect Script , please follow the steps in per the metalink on How To Diagnose "You are not currently logged in to the Oracle BI Server.." Error When Login From EBS to OBI [ID 758392.1].

    Below are the troubleshooting steps as per the above note. Please marks if helps.

    1) Make sure that E-Business Suite is on the correct patch level needed to integrate with OBIEE.
    2) Make sure that E-Business Suite and OBIEE are on the same domain
    3) Make sure that the ICX_SESSION_COOKIE is passed correctly from E-Business Suite to OBIEE. You can do this by the following step:

    On the browser page where you get the error, type the following in the url address bar:
    javascript:document.write(document.cookie)

    For example:
    JSESSIONID=0aa764a75401d37ced9448c3426f9b6da394adc646d4.e34SaNyRaN0NbO0LaN8Ob38Mb3iPe0;
    VIS_pses=ZGA6F2FD9FF49E82DD112C0292BDC02F59B51D4E25C2907894F55C95E36A3D93459F520C3AA97CE73C26719C7E7330816BFA;
    oracle.uix=0^^GMT-0:00^p; VIS=eu1tJjl_J13s2-27dz85HGLG:S; s_cc=true; s_sq=%5B%5BB%5D%5D;
    OHS-<myEBSServer>-8001=301B2D3DA910ED3AA2E901FC2DECBCCC2C3BE3D87173AE8F048F932C4D5D034FB1DA87E53D9984F6E58D70
    E6311F6FBA1012DF10AE8E51EBC7636A8AC5E00D1D68CEB77D67F9E8E5090FC371D892E5C80E2A467F904C1FD5202FA9E1FB1831AB964
    A8986DC175D897A6930C150B9DD4C108867D5CEB13BB6221F5B1ACD5E88DD2AA628CE2A120EC6FA89849E84D69CF08094986F0406D1965
    D274D5BF7E683E4598293F6FD3A807BD0ACB78CDD4DF3F444AAFEAE0B730850F615F504624B8E43231BAC043C244A312FA6655AD0F92F
    E51A4BE4F9FF99F9D4429424A939802624719F679F9148E1524701E2347E4DCDED44460F630BE2BFA9D634104288AE59FE4BD3C11AC1A6
    B12209D8F952F8F65D15; ORA_ML_STATE=3FE43264DA877F62


    The cookie name to look for is "VIS".

    VIS=eu1tJjl_J13s2-27dz85HGLG:S

    This is identified by the fact that this cookie value ends with "S".

    So if this value is got correctly, it means E-Business Suite is sending the correct cookie information to OBIEE. If, for some reason, the cookie value is not passed from E-Business Suite, then please check the following:

    a) E-Business Suite and OBIEE are on the same domain
    b) E-Business Suite and OBIEE are both using the same security protocol. When E-Business Suite and OBIEE are configured for different security protocols, for example, E-Business Suite for https and OBIEE for http, then the cookie will not be passed and hence cannot be seen from the OBIEE URL browser and the integration will fail. So E-Business Suite and OBIEE need to be either on http or https . (For setting up SSL on OBIEE its not necessary to setup ssl for all the components - setting up https for webserver/oc4j should be enough)

    4) Validate the entry in the \\OracleBIData\web\config\instaneconfig.xml file. For example:
    <Auth>
    <ExternalLogon enabled="true">
    <ParamList>
    <Param name="NQ_SESSION.ICX_SESSION_COOKIE"
    source="cookie"
    nameInSource="VIS"/>
    <Param name="NQ_SESSION.ACF"
    source="url"
    nameInSource="acf"/>
    </ParamList>
    </ExternalLogon>
    </Auth>


    As you can see from the above sample nameInSource is VIS which is the variable that contains the cookie information. Typically nameInSource value is same as the connect string entry in the tnsnames.ora file for your E-Business Suite database. Make sure the nameInSource has the exact same case as in point 1.
    For example, if VIS=eu1tJjl_J13s2-27dz85HGLG:S, then the nameInSource is equal to "VIS"

    5)In the OBIEE repository the E-Business Suite connection pool has the following PL\SQL script that is called when invoking OBIEE:
    call /* valueof(NQ_SESSION.ACF) */ APP_SESSION.validate_icx_session('valueof(NQ_SESSION.ICX_SESSION_COOKIE)')


    You can validate that this PL\SQL package returns correct value by executing it directly against the E-Business Suite database.
    To do this, first log in to E-Business Suite and click on OBIEE Answers link.
    Using document.cookie note down the value of icx_session_cookie (see step 3 for details).

    Log in to the E-Business Suite database (via SQL*Plus or some such tool) as the user defined in the E-Business Suite connection pool (typically apps/apps)

    Execute these SQL statements:
    SQL> call app_session.validate_icx_session(' eu1tJjl_J13s2-27dz85HGLG:S ');
    Call completed.

    SQL> select fnd_global.user_name from dual;

    USER_NAME
    --------------------------------------------------------------------------------
    SYSADMIN


    Note : you need to substitute the value for icx_session_cookie based on your environment.


    If this returns no rows or error then you need to first validate the PL\SQL in the E-Business Suite database and make sure it was created successfully and granted appropriate privileges for the E-Business Suite user.

    6) In the repository, you need to then ensure that the Initialization Block for E-Business Suite populates the USER session variable.
    The SQL for the Initialization Block is:
    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;


    FND_GLOBAL.USER_NAME should populate the USER variable. You can set this by clicking on Edit Data Target.

    Note: In the out-of-the-box OBIApps repository, the USER variable is populated by the Authentication Initialization Block, which connects to the Siebel OLTP Database. If you want the user to be authenticated with the E-Business Suite Initialization Block, then you must modify the Authentication Initialization Block to re-name the USER variable to something else, or delete the USER variable from the Authentication Initialization Block.

    Also if you want both Siebel Authentication and E-Business Suite Authentication, this is currently not possible to accomplish using one webcat and one repository. In this scenario you need two webcatalogs and two rpeositories each configured individually for Siebel and E-Business Suite, respectively.


    General troubleshooting:
    When you get the error, capturing sawlog.log is quite helpful in knowing what is causing this error. You need to increase the log level for saw by following these steps :

    a) Stop Presentation Server
    b) Edit logconfig.xml file located in \\OracleBIData\web\config folder. Add these lines in between the <Filters> </Filters> node:

    <FilterRecord writerClassGroup="File" path = "saw.httpserver.request" information="100" warning="100" error="100" security="100"/>
    <FilterRecord writerClassGroup="File" path = "saw.httpserver.response" information="100" warning="100" error="100" security="100"/>
    <FilterRecord writerClassGroup="Cout" path = "saw" information="100" warning="100" error="100" security="100"/>
    <FilterRecord writerClassGroup="File" path = "saw" information="100" warning="100" error="100" security="100"/>


    c) Save the changes and re-start the Presentation Server
    d) Re-test and review the sawlog.log file generated in \\OracleBIData\web\log folder.
  • 6. Re: EBS R12 Integration with OBIEE11g
    RR Newbie
    Currently Being Moderated
    Thanks Vidya.

    I got clear idea about that connection script. My connection pool doesn't get that cookie value.

    When i am giving that connection script in connection pool i am getting error like NQ_SESSION.ICX_SESSION_COOKIE has no value definition

    I got cookie value from this script javascript:document.write(document.cookie).

    I thought that connection pool doesn't get that cookie value.

    And i tried to hot code that cookie value like call app_session.validate_icx_session('ZcEJeoLNVqcHGiGYvCpzTx3N:S') directly to the connection script.I got username too.

    In EBS i created user same as OBIEE user like 'weblogic/admin123'. And attached OBIEE Answers and Dashboard.

    When i navigate from EBS to obiee i am getting login page for the answers.

    Is it correct? Are we get login page or directly on home page?

    Please guide me.
  • 7. Re: EBS R12 Integration with OBIEE11g
    vidyas Explorer
    Currently Being Moderated
    Hello,

    Check the nqserver.log to see if there are any errors there regarding validating this init block. If you are getting errors there , they need to be debugged first.

    Did you update the authenticationschemas.xml and instanceconfig.xml per the document to use EBS ICX based authentication ?

    Also try enabling the SSO in EM Security tab.

    Make sure you have assigned the OBIEE Responsibility to the weblogic user you have created.

    Please marks if helpful.

    Thanks,
    -VS
  • 8. Re: EBS R12 Integration with OBIEE11g
    RR Newbie
    Currently Being Moderated
    Hi,

    I have updated authenticationschemas.xml and instanceconfig.xml as per the document.

    I removed 'Required for authentication' in init block in admin tool. After it i can open repository in online.

    In em i enabled sso through clicking that check box.

    Is there any changes have to made in sso?

    When i navigate from EBS i am getting Login page for the BI answers and Dashboard.

    Now in nqsever log file getting message like *[nQSError: 23006] The session variable, NQ_SESSION.ICX_SESSION_COOKIE, has no value definition*

    Guide me regarding this.

    Thanks in advance.
  • 9. Re: EBS R12 Integration with OBIEE11g
    vidyas Explorer
    Currently Being Moderated
    Hello,

    When you are not selecting the "Required for Authentication" in the init block EBS SSO will not be enabled.

    Use the connect script below in connection pool.

    call /* valueof(NQ_SESSION.ACF) */ APP_SESSION.validate_icx_session('valueof(NQ_SESSION.ICX_SESSION_COOKIE)')

    If SSO is working you should not see the Presentation services login page.

    Did you log in SR with Oracle , to open repository online when you have enabled the "Required for Authentication" ?

    Initially when we had this issue troubleshooting the connect script resolved the issue.

    Thanks,
    VS
  • 10. Re: EBS R12 Integration with OBIEE11g
    RR Newbie
    Currently Being Moderated
    Thanks.

    I have enabled Required for authentication in Repository and i have hot code the connection script like this

    call APP_SESSION.validate_icx_session('jyOwxbQrN3SRtRxTZLMLI22FVp')

    After this no error in repository level.

    But when i navigate from ebs its showing login page for OBIEE.

    How can i avoid this?

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points