An Eye on Oracle BI

« Oracle BI Applications Integration Characteristics | Main

June 28, 2007

Oracle BI Fusion Intelligence integration with Oracle E-Business Suite

Introduction

Since Oracle acquired Siebel and embraced Siebel Analytics (nQuire) technology and applications, many improvements have been added to the product, currently known as the Oracle BI Enterprise Edition (OBIEE), the BI technology platform and Oracle BI Applications (OBIA), the BI application content being BI metadata and application integration. Significant progress has been made in the area of application integration with the E-Business Suite for ERP, CRM and SCM, also known as Oracle Applications.

Architecture

This article will discuss a specific integration technology of the Oracle E-Business Suite with Oracle BI EE. There are two main flavors of Oracle BI Applications for the EBS. The first is called Fusion Intelligence. This BI solution consists of an application integration from a user interface perspective: single sign on, integrated user interface, action links (Action link screens enable to drill back into the transactional application screen from an Oracle BI request or dashboard) and from a data source perspective: pre-built repository on top the result of the installation and configuration of DBI (Oracle Daily Business Intelligence). This result is a set of pre-configured database objects, most likely materialized views or tables in combination with normal database views optionally based on packages or functions, all configured in the EBS transactional database. The content of these database objects is prepared by running and scheduling the EBS Request Sets. This completes the prerequisite of running OBIEE Fusion Intelligence.

Integration of Fusion Intelligence

Fusion Intelligence is built with the Oracle BI technology and does not include a pre-built data warehouse nor ETL prepackaged routines in contrast to the BI Applications for CRM and ERP.

Integration Steps

The integration of Oracle e-Business Suite (EBS) en Oracle Fusion Intelligence (OBI) consists of the following steps:
1. Specification of Oracle BI EE base URL in EBS
2. Specify the authentication part of the integration in the instanceconfig.xml
3. Modify the connection pools in the repository
4. Specify Static Repository variables

Assumptions

It is assumed the following pre-requisites are fulfilled:
1. Oracle E-Business Suite (11.x) is installed
2. Daily Business Intelligence is installed and is configured
3. All necessary EBS patches have been applied by the EBS application DBA
3. Client Browser accepts cookies
4. Oracle Business Intelligence Platform (10.1.3.x) is installed in the same network domain

In DNS terminology this means that for both FQ (fully qualified) hostnames have the same network extension: machine1.domain.ext = machine2.domain.ext.

Base URL in EBS

The EBS administration screen for managing profile options (Home Page > System Administrator > Profile > System) allows to specify the hostname and location of the web application to integrate with. This will be the base for EBS to build up the dynamic URL pointing to the OBI environment. The URL will be interpreted by the OBI Presentation Server. Set the value of the following Profile Option Name, "FND: Oracle Business Intelligence Suite EE base URL" to: http://[hostname.domain_name]:[port_number] (No slash is required at the end of this URL)

Authentication Integration

For Fusion Intelligence the authentication integration is via http cookies combined with a URL. The cookie is also used for the action link integration (drill back to EBS screen from an OBIEE request). This integration is specified in the instanceconfig.xml. The integration and authentication steps are:

1. Login into EBS
2. When clicking on an OBI link in the EBS client browser, EBS builds up an URL starting with the OBI base URL specified in the previous step. This base URL is extended with:

[base_url]/analytics/saw.dll?[module_invoked]&acf=

Here [module_invoked] is for example Dashboard or Answers and [acf_id] is a 10 digit number generated by EBS. This number is used to retrieve other authentication and authorization information from the EBS session by the Oracle BI Server

3. EBS sends a cookie to the browser, most likely the cookie file name is equal to the ICX base domain.

Tip: Using Mozilla Firefox browser allows you to find the cookie named value pair you are looking for.

This cookie named value pair which indicates the user ICX session id. The name of this value pair is very important and needs to be captured for the instanceconfig.xml, i.e. the configuration file of the Presentation Server. The value is used for the actual authentication in OBI.

4. By the instanceconfig.xml for Fusion Intelligence, the OBI presentation server is programmed for external authentication (ExternalLogon enabled=true in the [Auth] tag area).

In order to complete the authentication the OBI session will try to resolve two parameters:
- NQ_SESSION.ICX_SESSION_COOKIE
- NQ_SESSION.ACF

The first is resolved from a cookie with the name specified in the instanceconfig.xml configuration file by the nameInSource attribute of the [Param] tag. The attribute value is the name of the cookie. The value of the cookie is the ICX session cookie ID stored in the OBI Server parameter NQ_SESSION.ICX_SESSION_COOKIE and is passed to the OBI Server. The second parameter is resolved from the URL by [acf_id].

The correct specification of the instanceconfig.xml [Auth] tag is as follows (after the ParamList tag):

Param name="NQ_SESSION.ICX_SESSION_COOKIE"
source="cookie"
nameInSource="[cookie name]"

and

Param name="NQ_SESSION.ACF"
source="url"
nameInSource="acf"

After the changes have been applied, the OBI Presentation Server needs to be rebooted.

Repository Configurations

Two authentication initialization blocks populating repository session variables are most important for the integration of EBS with OBI:

1. FndGetSecContext (Authentication)
2. FndGetResp (Authorization)

The first initialization block, , populates the following variables:
RESP_ID, RESP_APPL_ID, RESP_APPL_ID, RESP_APPL_ID, USER_ID, EMPLOYEE_ID, USER

using a database query:

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

with the following connection pool:

EBS_Authentication_Pool

This connection pool uses a static user name and password to connect to the EBS OLTP database, referenced by the following static repository variables:
- Static_USER_ID
- Static_DSN_OLTP

When connection is established, the first thing the OBI Server will invoke is the following package (stored procedure) call:

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

The value of the parameter NQ_SESSION.ICX_SESSION_COOKIE is passed through by the presentation server, obtained from the EBS session cookie.

If this package call fails the authentication fails. When the call is successful, the authentication session variables are populated, after which the next init block is executed for Authorization, FndGetResp. This initialization block will query the responsibilities from the EBS OLTP database:

SELECT RESPONSIBILITY_KEY
FROM FND_RESPONSIBILITY
WHERE RESPONSIBILITY_ID = 'valueof(NQ_SESSION.RESP_ID)'

This query will populate the (single valued) GROUP repository variable and assign the user to one of the preconfigured repository groups and web catalog WEBGROUPS. Based on these groups the user will have access to role-specific content.

Final Considerations

The objective of this blog posting is to help consultants in the field configuring Fusion Intelligence in an E-Business Suite environment. It is very important to realise that Fusion Intelligence can not be compared in with a fully featured BI Application that comes with a data warehouse and ETL environment. The integration of Fusion Intelligence is much tighter, more like an add-on to Oracle EBS than the OBI Applications.

Because of the tight and enforced EBS security integration, the OBIEE Fusion Intelligence repository is only accessible using EBS: an EBS session ID must exist in the EBS database in order to be authenticated. Each database query to the EBS database will check whether the session ID still exists. If not Authentication, Authorization or the Query fails.

Share:  

Posted by Gerard Braat at June 28, 2007 3:15 PM

Comments

Posted by: Vinoo at July 3, 2007 3:46 PM

Posted by: lchen at August 31, 2007 1:34 AM

Posted by: Biswa at December 26, 2007 5:03 AM

Posted by: Mallesh at March 17, 2008 2:28 PM

Post a comment

Name:

Email Address:

URL:
Remember Me? YesNo

Comments: