Introduction to Oracle Business Intelligence Discoverer-Part 2

Start Here

Get in touch with a
TriCore Solutions specialist

Blog | May 18, 2017

Introduction to Oracle Business Intelligence Discoverer-Part 2

If you don’t have an existing Discoverer End User Layer, you must create a new Discoverer 11.1.1 End User Layer. Fresh installations of e-Business Suite Release 12 Production do not contain a pre-installed Discoverer End User Layer. Only the E-Business Suite Release 12 Vision database contains a pre-installed Discoverer End User Layer. Click to read Part 1.

Introduction:
In part two, I will cover the details of integration along with discoverer EUL upgrade. 

Integrate e-Business Suite R12 with Discoverer 11g
database connector file (.dbc file), tnsnames.ora configuration.

On your Discoverer node, include the tnsnames entry to connect to your EBS R12 database in file $ORACLE_INSTANCE/config/tnsnames.ora.

Use the same entry that exists in the tnsnames.ora file on your Oracle E-Business Suite Release 12 application tier server node. The database name must match the two_task entry in the dbc file. 

Upgrade from earlier versions of Discoverer End User Layers to Discoverer 11g

If you do not have an existing Discoverer End User Layer, skip this step and proceed with Step 9. 

If you have an existing Discoverer End User Layer and it is from Discoverer 10.1.2 then you are not required to upgrade as Discoverer Version 11.1.1 uses the same EUL version as Discoverer 10.1.2. The database connection (dbc) file will not be transferred during the upgrade. After the upgrade, you must copy the Database Connection (dbc) file manually as explained above. 

If you have an existing Discoverer End User Layer and its version is earlier to Discoverer 10.1.2, upgrade it to Discoverer 11g by using the following command on the standalone application server where Oracle Fusion Middleware Discoverer 11g is installed.

Before running the following command to upgrade an earlier version of discoverer End User Layer to discoverer 11g, you must source the following environment script: 

source $ORACLE_INSTANCE/Discoverer/Discoverer_<ias-instance>/util/discenv.sh 

Run the following command to upgrade the earlier version of discoverer End User Layer to discoverer 11g: 

$ORACLE_HOME/bin/eulapi -CONNECT <EUL User>/<Password>@<db> -AUTO_UPGRADE 

Create new Discoverer 11.1.1 End User Layer 

If you don’t have an existing Discoverer End User Layer, you must create a new Discoverer 11.1.1 End User Layer. Fresh installations of e-Business Suite Release 12 Production do not contain a pre-installed Discoverer End User Layer. Only the E-Business Suite Release 12 Vision database contains a pre-installed Discoverer End User Layer.

% sqlplus /NOLOG

 SQL> connect sys/<sys_password> as sysdba

 SQL> create tablespace DISCOVERER datafile \

 '[DB_ORACLE_HOME]/dbf/discoverer01.dbf' size 200M reuse \

 extent management local uniform size 128K;

 SQL> /

 Statement Processed 

Create the Discoverer End-User Layer before running command line scripts, you must source the following environment script.

source $ORACLE_INSTANCE/Discoverer/Discoverer_<ias-instance>/util/discenv.sh

Platform Specific Environment Settings:

If you are using Discoverer on 64 bit platforms (Oracle Solaris on SPARC 64 bit, HP-UX PA-RISC 64 bit, HP-UX Itanium 64 bit, IBM AIX 64 bit, Linux x86-64), find the line in $ORACLE_INSTANCE/Discoverer/Discoverer_<ias-instance>/util/discenv.sh that defines variable LIB_PATH and set it as follows:

LIB_PATH=$OH/discoverer/lib:$OH/lib:/usr/lib:$OH/lib32

If you are using Discoverer on 64 bit platform Linux x86-64, find the line that defines variable LD_ASSUME_KERNEL and comment it:

#export LD_ASSUME_KERNEL=2.4.19

$ORACLE_HOME/bin/eulapi \

 -CREATE_EUL \

 -APPS_MODE \

 -CONNECT system/<password>@<db> \

 -USER <EUL_User_Prefix>_US \

 -PASSWORD <password> \

 -DEFAULT_TABLESPACE <default tablespace> \

 -TEMPORARY_TABLESPACE <temp tablespace> \

 -EUL_LANGUAGE US \

 -APPS_GRANT_DETAILS <FNDNAM>/<FNDNAM password>

Apply AD Patch containing adupdeul.sh and adrfseul.sh

For 12.1 its Patch 9394002

For 12.0 its Patch 9384228

Apply the patch using adpatch option

Set Applications Profile Options for Discoverer using AutoConfig 

Update the var s_disco_url in CONTEXT_FILE and run autoconfig 

Set Applications Profile Options in Oracle E-Business Suite

Navigate to the Profile > System form

Query the %Discoverer% profile options

ICX: Discoverer Launcher -- URL that points to Discoverer Plus Servlet.

ICX: Discoverer Viewer Launcher -- URL that points to Discoverer Viewer Servlet

ICX: Discoverer use Viewer -- Specify whether Discoverer Viewer should be launched instead of Discoverer Plus (default).

ICX: Discoverer Default End User Layer Schema Prefix -- The EUL prefix in combination with the Language code make up the EUL owner at runtime. For example EUL owner EUL_US has EUL prefix EUL.

ICX: Discoverer End User Layer Language Override -- Since the End User Layer content is currently available in US English only, it is possible to override the user's general language preference for the Discoverer End User Layer using this profile option. The specified End User Layer language will be used regardless of the individual user's language preferences

ICX: Discoverer Release -- This profile can be used to optionally pass additional URL parameters to Discoverer.

Discoverer DBC filename override -- This profile can be used to specify the DBC Filename Discoverer should be using to connect to the E-Business Suite database.

oracle system support

Run the "Generate Business Views by Application" Concurrent Program 

Regenerate your Business Views by running the "Generate Business Views by Application" concurrent program using the Business View Setup responsibility:

 Logon to Oracle E-Business Suite as SYSADMIN

 Choose the "Business Views Setup" responsibility

 Navigate to Reports > Run > Pick Single Request > "Generate ALL Business Views" 

In case you don't have "Business Views Setup" responsibility assigned to SYSADMIN user, please do the following:

 Logon to Oracle E-Business Suite as SYSADMIN

 Choose the "System Administrator" responsibility.

 Navigate to Security > User > Define and add responsibility "Business Views Setup" to user SYSADMIN. 

Recompile APPS objects 

Recompile all objects in the APPS schema using adadmin. 

Check Business Intelligence System Views 

Ensure that the Business Intelligence System views exist and all Business Intelligence System views are valid by issuing the following command in SQL*Plus: 

% sqlplus apps/<password>@<db>

SQL> select object_name from user_objects

where object_type = 'VIEW' and

status = 'INVALID' and

( object_name like '%FV_%' or object_name like '%FG_%' or

object_name like '%BV_%' or object_name like '%BG_%' )and

 object_name in (select sobj_ext_table from eul_us.eul5_objs); 

Grant End User Layer Administration Privileges 

sh eulapi > -CONNECT EUL_US/EUL_US@SID > -GRANT_PRIVILEGE > -USER SYSADMIN > -PRIVILEGE administration > -PRIVILEGE all_admin_privs > -LOG admin_priv.log

-connect <**********>

-grant_privilege

-user SYSADMIN

-privilege administration

-privilege all_admin_privs

-log <logfilename> 

Ensure that user SYSADMIN has full security access to all Business Areas) 

sh $ORACLE_HOME/bin/eulapi > -CONNECT EUL_US/***@sid > -GRANT_PRIVILEGE > -USER SYSADMIN > -BUSINESS_AREA_ADMIN_ACCESS % > -WILDCARD > -LOG full_sec_acc.log

-connect <**********>

-grant_privilege

-user SYSADMIN

-business_area_admin_access %

-wildcard

-log <logfilename> 

Access directory $AU_TOP/discover on your E-Business Suite Release 12 instance from your Oracle Business Intelligence Discoverer 11g BI instance 

$AU_TOP/discover directory copied to Discoverer server. 

Import Discoverer Content for E-Business Suite Release 12 using adupdeul.sh mode=complete 

Start the import process of the Discoverer loader files (.eex files) using the shell script adupdeul.sh.

Use the following syntax for the adupdeul.sh script: 

sh adupdeul.sh connect=sysadmin/sysadmin@SID resp="System Administrator" gwyuid=APPLSYSPUB/*** fndnam=APPS secgroup="Standard" topdir=<loc> language=US eulprefix=EUL iashome=loc eultype=OLTP mode=complete logfile=<logfilename> 

Reviewed the import log file. We can ignore the warnings while import as per DOC ID - 1074326.1 

Refresh the Discoverer 11.1.1 End User Layer –

sh adrfseul.sh connect=sysadmin/*******@SID resp="System Administrator" gwyuid=APPLSYSPUB/*** fndnam=APPS secgroup="Standard" eulschema=EUL_US eulpassword=***** twotask=sid iashome=<loc> logfile=<logfilename> 

Conclusion:
Using the above information you can configure/upgrade discoverer to 11.1.1.7 on RHEL 6. Please note, only until RHEL 6 is supported with Discoverer 11.1.1.6.0 and 11.1.1.7.0. For any questions click below.


Ask Abhishek