Integrating Oracle eBS R12 and Oracle BI 11g

I have made a blogpost in the past about integrating Oracle eBS R12 and Oracle BI 10g. In the course of an upgrade of Oracle BI Applications (OBIA) to OBIA 7.9.6.3, I came to the subject of integrating  Oracle eBS R12 and Oracle BI 11g. Of course you should start with the documentation. Next to that, Oracle provides a note on Oracle Support (ID 1343143.1). A lot of integration steps are equal to the 10g version.

In short:

There are two sides (Oracle eBS & Oracle BI) where you need to make some preparations.

Oracle eBS:

You need to define the link from to Oracle eBS to Oracle BI. This functionality hasn’t changed and I have described that process here. Combined with the ‘FND: Oracle Business Intelligence Suite EE base URL’-profile option in Oracle eBS, you now are ready to navigate from Oracle eBS to Oracle BI.

Oracle BI:

Now the Oracle eBS side is ready, you’ll have to prepare Oracle BI for accepting login requests from Oracle eBS. This parts differs a little from 10g. The changes in the Repository are still the same and consist of validation of the ICX-cookie in the Oracle eBS Connection Pool and the Authentication via Session variables. You can choose to either authenticate via the GROUP- system variable or directly via the new 11g ROLES-system variable.

After that you need to change the Oracle BI configuration;

  • authenticationschemas.xml (ORACLE_HOME/bifoundation/web/display)

authenticationschemas.xml (SchemaKeyVariable)

authenticationschemas.xml

  • instanceconfig.xml (ORACLE_INSTANCE/config/OracleBIPresentationServicesComponent/coreapplication_obips1)

instanceconfig.xml

Note: Don’t get mislead by the following sentence; ‘<!–This Configuration setting is managed by Oracle Enterprise Manager Fusion Middleware Control–>’. You must adjust these settings directly in the instanceconfig.xml itself.

This should (all) be sufficient to log into Oracle BI via a selected responsibility in Oracle.

In a following post I will cover the subject of applying Data Security in Oracle BI, based on the Oracle eBS Responsibility.

Oracle BI Applications – Security

I recently had to digg into the standard Oracle BI Applications Security Oracle delivers out of the box. The clients had two security requirements.

The first one was a Data Security requirement. When a user logs in he is presented with his / her organization’s data only. So a user from organization ‘001’  only sees data from organization ‘001’ . Organization user ‘002’ only sees organization ‘002’ and so on.

The second requirement was Object Security. Each function has access to a group of objects, regardless of their organization. So all ‘General Ledger Super Users’ have access to the same objects whether they are in organization ‘001’ or ‘002’

 

The client has the following installation:

  • Oracle eBS R12 (12.1.1)
  • Oracle BI Apps  (7.9.6)
  • Oracle BI EE (10.1.3.4.1)

In general the standard Oracle BI Applications security solution is built around;

  • Groups (Repository, Web Catalog)
  • Session Variables
  • Business Model Filters
  • Permissions
  • Priviliges

In more detail the following steps have to be performed:

Set the application context

The Oracle BI Applications session should get the same security context as Oracle eBS, where you navigate from.

 During logon the ‘EBS Security Context’-Initialization Block is called and executed. The Oracle eBS session cookie is used to set the context. The Initialization block ‘fills’ the variables with information about which user / responsibility combination is logged on. These variable will be used in other Initialiation Blocks along the road.

call APP_SESSION.validate_icx_session('valueof(NQ_SESSION.ICX_SESSION_COOKIE)')

 If all goes well, the Oracle BI Apps session will get the same context as Oracle eBS. Otherwisse there are 3 options :

  • SESSION_DOES_NOT_EXIST,
  • SESSION_NOT_VALID,
  • SESSION_EXPIRED.

Repository Groups

There are two important Groups;

  • Responsibility Groups (Should the Responsibilities in Oracle eBS)
  • Security Groups (These will be used for the Data Security)  
    • –> Examples:
    • Ledger-based Security
    • Inventory Org-based Security
    • Operating Unit Org-based Security

 Variables

The security group someone belongs to is detemined by session variables, which are set during logon.

Initialization Blocks and Variables are the necessary objects to examine. If we relate to the example in the previous section, we could state that the following three Initialization Blocks are most important;

  • Ledgers
  • Inventory Organizations
  • Operating Unit Organizations
  •  

    Data Security

    Data Security is being set up via, Security Groups and Business Model Filters.

     

    As from now on, each query which is composited with a table linked to a Security Group a “Where-clause” is added.

    Presentation Catalog Groups

    The groups as they are created in the repository should also be created in the Web Catalog.

     

     Object Security

    You can use the Catalog Groups to grant or revoke acces to certain objects (Folders, Answers, Dashboards, etc) in the Web Catalog. The same groups an be used to mange the privilliges within the Web Catalog. Use the Security Groups in the Repository to control the Access to the Subject Area’s in the Presentation Layer.

    Check my previous post about navigating from Oracle eBS to Oracle BI EE.