BeyeBLOGS | BeyeBLOGS Home | Get Your Own Blog

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: del.icio.us Digg Furl ma.gnolia Netscape Newsvine reddit StumbleUpon Yahoo MyWeb  

Posted by Gerard Braat at 3:15 PM | Comments (36)

Oracle BI Applications Integration Characteristics

Introduction
While the market of Business Intelligence is emerging, the field of Business Intelligence Applications is very popular. Many enterprises have both back office and front office systems to support their daily business. Many of these companies have invested in business intelligence products or at least in the technology. A relatively small subset is convinced about the benefits of pre-packaged, integrated business intelligence applications based on best-practices and domain expertise provided by the same vendor providing the back and front office application software.

Business intelligence applications are pre-packed or predefined for one or more source system. Not rarely a combination of CRM and Financial General Ledger for example or CRM, Enterprise Telephony Customer Contact Centers and Supply Chain Management. The business benefits are clear: short implementation times and quick time to market result into a quick return on investment and high adoption rate by the business user community.

Oracle acquired Siebel Analytics, a pre-packaged BI application, integrated with Siebel CRM. These BI applications have been extended and integrated with other source systems like Oracle E-Business Suite, Peoplesoft, JD Edwards and SAP. The underlying technology, Oracle BI Enterprise Edition, is improved to support the business and technology requirements of application integration, not only from a user and data perspective, but also in the light of business process integration: moving from reactive looking-back-to-what-happened reporting to a pro-active, guiding, alerting intelligent business, business intelligence.

In this blog, I would like to discuss the most important integration characteristics of the Oracle BI Applications and the supporting technology platform Oracle BI Enterprise Edition (OBIEE or in short OBI). In order to support the business users in their daily activity, Oracle BI Applications integrate with the primary transactional applications, like Siebel CRM or Oracle E-Business Suite (EBS) on several aspects:

User Interface Integration
Because of the 100% web-based and standards based architecture (SOAP) of the OBIEE platform it is very easy to integrate the OBI Presentation Services with a web based application like Siebel CRM or EBS. Siebel CRM up to version 8.0 use their Portal Framework and SOAP based integration technology to integrate the Siebel CRM screen, views and applets with dashboards and reports respectively, giving the end user the perception of using one application only. The Siebel Portal Framework takes care of the Single-Sign-On requirement. On the OBIEE side, several technologies support this integration varying from the Go API in the Presentation Server to Initialization Blocks and Session Variables on the BI Server side.

Action Links are another example of User Interface integration. This JavaScript based technology enables the user to drill down from and OBIEE request or dashboard to a transactional application view including the record of focus. A typical flow in sales force automation for example is: pipeline management -> drill down on a problem stage -> obtain a list of opportunities in this stage including expected revenue and probability of close -> drill down to the specific opportunity in the transactional application including all activities, account information, account service requests, quotes, proposals and sales team at hand.

Another example of user interface application integration is in the marketing area: The OBI Presentation Server incorporates a database marketing segmentation module which can be integrated with any campaign management and execution tool via the SOAP standard for web services integration. This has already been preconfigured for Siebel Marketing.

Security Integration
Besides the critical requirement of single sign on and authentication, an important and time saving characteristic of a BI Application is the integration with the base application security and visibility rules. Typically large organizations have the requirement to secure company information and transactional data based on not only the user's role or responsibility (Sales Rep or Sales Manager) in the organization but also on his or her position (CFO Division Consumer Electronics vs CFO Medical Systems). Based on the user's responsibility, he should have access to certain analytical content, i.e. dashboards, reports, alerts, subscriptions etc.; based on the user's position, he should have acess to certain areas of the database, e.g. the data belonging to division A or B, team 1 or 2.

The point is that this access control business rules are administered in the transactional application. A prepackaged BI Application must integrate with these business rules without duplicating these rules to the BI repository. Oracle BI EE fully supports this role and position based security rules using the technique of row-wise initialized session variables and initialization blocks. Upon login and after authentication, queries are being executed against the transactional database (OLTP) in a certain order to retrieve the security information which is stored in session variables like GROUP or POSITION. These variables are then used to control access to objects in the OBIEE repository or web catalog but also to build business model filters to restrict access to critical records. This integration becomes critical in a volatile organization where user change role and/or position frequently.

Data Integration
The main difference between a BI Application and a BI Tool is the availability of pre-packed business intelligence content, being reports, dashboards, metrics, drill down paths, guided navigation, alerts, action links, traffic light alerts, etc.. The Oracle BI Applications pre-packaged content comes with three main pre-built repositories:

1. the OBIEE repository and catalog
2. the ETL repository tailored for the source applications
3. the Data Warehouse model

The current version of the Oracle BI Applications is 7.9.2 relying on the Oracle BI EE 10gR3 Platform supports combinations of Siebel CRM starting with version 6.3, SAP R/3, Oracle E-Business Suite, Peoplesoft and JD Edwards. Several areas of functionality is supported, like CRM, ERP, SCM, Human Resources and Contact Centers (i.e. switches and telephony data). In some areas vertical industry solutions are provided as well. For non-supported source systems, universal adaptors can be utilized. Basically with the Universal Adaptors the initial steps of the ETL, being the changed data capture and extract to staging area must be coded and implemented manually.

The pre-built ETL repository includes not only routines for changed data capture, extract to staging area and the load into data warehouse tables, but also seed data for common dimensions like the time dimension or dimension lookup files with domain values or dimension value sets to support sources independent metric calculation (new in version 7.9.2). Note that the ETL environment and also the ETL repositories consist of two main components, Informatica which is the ETL engine and the DAC which is the orchestra leader of the entire ETL process.

The pre-built Business Analytics Warehouse (BAW) is one single but modular data warehouse model to support one or a combination of the source systems mentioned. The BAW is compliant with the Ralph Kimball dimensional modeling methodology and supports slowly changing dimensions, aggregate tables, hierarchy tables and many others.

The pre-built OBIEE repository and web catalog are aligned with the Business Analytics Warehouse and contain the domain specific and end user facing dashboards, reports and KPI's.

Supporting Technology
Next to the importance of integration, the success of a Business Intelligence Application is dependent on the supporting technology platform. Without going into detail the most important characteristics of the Oracle BI EE Platform to support successful BI Applications are:

1. the caching technology which enables a significant performance improvement for standard dashboards and reports
2. the open repository ODBC API which enables other enterprise ODBC compliant tools to access the OBIEE repository presentation layer and therefore the pre-built KPI's, dimensional attributes, preserving the security business rules
3. the ability to join multiple different data sources and data source types in the OBIEE repository
4. the multilingual capabilities of the OBIEE platform. Because of their enterprise, international usage, transactional systems are designed to be used in a multi-lingual environment. For Business Intelligence tools this is not that evident at all. Translating application strings, repository metadata strings, report and dashboard names and last but not least the data itself in a data warehouse and business intelligence environment is a huge effort. Oracle BI EE platform however supports the dynamic translation, based on the user's profile, of the application strings, web catalog and presentation layer metadata (like presentation columns and descriptions) relatively easily. For the pre-packed metadata, the translation for the common languages are available out-of-the-box. Translation of dimensional data, like service request types, complain severity, product catalog names etc require configuration of the data warehouse and Oracle BI EE repository.

It was my intention with this contribution to give a relatively high level overview of the integration points involved and requirements of the Oracle BI Applications with the respective source systems. Many of the topics discussed may lead to more (technically) detailed postings in this blog, for example the open intelligence interface of the repository or the multilingual capabilities.

Keep you posted -

Gerard Braat

Share: del.icio.us Digg Furl ma.gnolia Netscape Newsvine reddit StumbleUpon Yahoo MyWeb  

Posted by Gerard Braat at 3:15 PM | Comments (6)