Thursday 26 June 2014

Installing BI Apps 11.1.1.8.1: Part 3 – Loading Data


This is the third part of my example walk-through of performing an installation of the newly released Oracle BI Applications 11.1.1.8.1.

I’m performing a completely fresh installation on our Exalytics/Exadata servers using an E-Business Suite 12.1.3 database as the data source.

In part 1 (http://paulcannon-bi.blogspot.co.uk/2014/06/installing-bi-apps-111181-part-1-pre.html) I went through the pre-requisites required before BI Apps can be installed, including installing OBIEE, ODI and creating the warehouse and repositories using the RCU.

In Part 2 (http://paulcannon-bi.blogspot.co.uk/2014/06/installing-bi-apps-111181-part-2.html) I covered the installation of BI Applications itself, including its configuration and integration with OBIEE and ODI.

In Part 3 below I will demonstrate a simple example of load data into the warehouse and viewing it in OBIEE.

So having now performed Steps 1 and 2 I now have BI Apps fully installed and ready to use.
The loading of data is all controlled from the BI Applications Configuration Manager (BIACM).  So let’s start by logging in with the biapps administrator user-id & password created in step 2.

Open a browser and enter the following URL:
http://<host>.<port>/biacm
e.g. http://biapps_server:9704/biacm


To load data into the warehouse you need to create and run a Load Plan. 

Load Plans define what data is to be loaded. You can have one plan that loads the entire warehouse or many small plans which load specific parts. These plans can then be run manually or scheduled and be run as full or incremental loads. 

I’m going to create a load plan to load some of the HR data.

From the Tasks pane in the ACM, click on ‘Manage Load Plans’:

As this is the first time I’ve done this, there are no plans available. 

In the Load Plans toolbar click on the add (green cross) icon.

In the first screen to define a load plan you enter a name and description followed by the type of load – this can be a full end to end load (SDE, SIL and PLP) or just part loads (such as just the SDE extract) and finally the source for the load process.

Leave the Load Plan Type at the default ‘Source Extract and Load (SDE, SIL and PLP)’.
I’ve only setup the one data source in my environment – VIS, the E-Business Suite “Vision” demo database.


Then click Next (or the ‘Select Fact Groups’ link):

I could now just select the ensure Oracle Human Resource Analytics fact group, but I don’t want all modules with HR, so I’ve expanded it and select five specific modules.  You could if you want expand each module and select individual fact groups.
By clicking on the ‘Selected Fact Groups’ tab you can see a summary of what has been selected, along with any dependant Fact Groups:

Now the Load Plan has been defined click Save.
Before a Load Plan can be run it needs to be generated – which creates the Plan in ODI, setting up the scehdule of tasks required, ensuring all individual elements are processed in the correct order, creating the data load paramater list etc…
You can do this in one of two ways. You could click the Generate button in the above screen instead of Save (it will save automatically), or you can run the generation manaully as follows.
Back in the Manage Load Plans screen the new Load Plan appears:

In the Generation Status field there is the red-recycle icon indicating that the Plan has not yet been generated.
Because of this the icons in the task bar for running the Plan are greyed out. The only option available is to Generate the Plan.

So click this icon and the Generation will begin.
The Generation Status field will now change to the ‘starting ‘ icon:

Clicking the ‘blue recycle arrows’ refresh icon after a few seconds will see this switch to the ‘In Progress’ icon:

You must now wait for this to finish. Click the refresh icon regularly to refresh the display.  How long it takes to generate is of course dependant on how many fact groups are in the plan, in my case it only took a couple of minutes.


Once a Plan has been generated, the name field turns into a link which you can click on to access the ODI console to see the details of the Plan:

You’ll need to login with the BI Apps administrator user-id & password when the ODI console when the login screen appears:

Once in you’ll be able to see the details of the plan, including all of the steps (over 3,500 for my HR Load Plan), along with their current status, the parameter variable settings and details of executions (which so far there hasn’t been any).
You can visit this screen at any time, including during Load runs to see the status of the steps and how far the load has progressed.

For now though we need to run the load. Returning back to the Manage Load Plan screens we can run it by selecting the Load Plan and clicking the ‘Execute’ icon:

You need to specify the ODI detail to run the plan under:


Select the “Global” context, your local agent for ODI (which will be OracleIDAgent as setup during the BI Apps configuration) and the ODI work repository.  You can change the log level as required, the default 6 is fine. Click OK to start the load.
Use the refresh icon to update the Manage Load Plans screen to see the Plan is executing:

You can now return to the ODI Console (by clicking on the Load Plan name again) and view the current status of the load.
In the ‘Browse’ tab click the blue refresh icon then expand ‘Runtime’, then  ‘Sessions/Load Plan Executions’, then ‘Load Plan Executions’:


The current execution will appear with the executing status icon. Now click on this to select it and click the glasses ‘view’ icon in the menu bar above to see its details in the main pane:

For each individual step we can see the current status, the start, end and duration timings. If you scroll to the right you’ll also see  row/update/insert/delete counts and any error messages.
By scrolling down you can see how far the load has progressed so far:



So my load is currently less than 10% complete (there being over 3500 steps), with multiple steps currently being worked on.
Load Plan executions can also be viewed from the summary ‘Overview’ screen, which is probably the easiest place to keep an eye on it whilst it runs:


After an hour and a half the status of the job changed to failed:

To find out why click on the white-on-red error icon in the execution status field. This takes you straight to the error in the ODI Console:



The error messages are a list of failures, all which originated from the bottom error:
ODI-1228: Task TABLE_MAINT_PROC
(Procedure) fails on the target ORACLE connection BIAPPS_DW.
Caused By: java.sql.SQLException: ORA-20000: Error creating
Index/Constraint :W_EMPLOYEE_DS_U1 => ORA-01452: cannot CREATE UNIQUE
 INDEX; duplicate keys found
This error is actually caused by some dodgy data in the E-Business suite Vision demo database – there are some duplicate employee records. I’m not going to go through fixing this – that’s way beyond the scope of this blog! – I deliberately showed the error which I knew would happen just to demonstrate what happens with a failure and how a plan can be restarted.
Restarting a Plan can obviously only be performed if you are able to fix the problem without altering the ETL mappings – often in the early stages of deploying BI Apps errors occur because of customisations in the source system which BI Apps isn’t aware of, so a development phase is required to replicate the customisations in the BI Apps ETL code.
However in this case the error is caused by data and we can ‘fix’ the data and restart the Plan – allowing the plan to pick up where the error happened and carry on rather than starting the whole load from scratch.
As these particular errors are known issues with the data in the Vision database the following sql code can be run in the warehouse to fix the data that has been loaded already. Connect to the warehouse database via sql*plus or your preferred development tool:

DELETE FROM w_employee_ds WHERE integration_id='PER~2901' AND datasource_num_id=1;
DELETE FROM w_position_ds WHERE integration_id='PER~6272' AND datasource_num_id=1;
ALTER TABLE W_MCAL_PERIOD_D  MODIFY (ROW_WID NULL);
COMMIT;

Now back in the ODI Console screen, in the Browse tab expand ‘Runtime’ -> ‘Sessions/Load Plan Executions’ -> ‘Load Plan Executions’ and finally click on the failed Plan:

When you do this the ‘restart’ icon un-greys in the menu bar:

So click it and then select ‘Restart’ in the Execution dialog box that appears:


 Interestingly the log level changes in this screen – when we first ran the Plan above it defaulted to log level 6 and we left it at that, now the log level defaults to level 5 – I don’t know why!...change it if you want, but level 5 is probably fine.


Click passed the successfully restarted message. Oddly you are now taken back to the Execution dialog box above:


However the plan has restarted and you can cancel out of this screen.
In the browse window click the refresh icon and you’ll see a second execution has appeared:

This second Plan is simply picking up from where the first left off, i.e. skipping the successful steps and carrying on with the remaining.
If you return to the Manage Load Plans screen you’ll see the Plan execution status has returned to executing:

And then only takes another 15 minutes to finish the outstanding steps:

If you want to see exactly what the load process has done you can return to the ODI Console and view the execution, against each step is displayed the number of rows inserts, updated, delete etc…

In this plan the PLP_WORKFORCEEVENTFACT_MONTH inserted 589263 records to the table (W_WRKFC_EVT_MONTH_F) which now contains 589263 records. If we look in the database, sure enough that is what we see:


Ok, so we’ve loaded data, but we don’t load it for the fun of it (well ok, some of us do…),  we need to see the data in reports. Logging into OBIEE and viewing the HR dashboards nicely displays the data that was loaded:

So there we have it, we’ve performed a data load using the BI Applications Control Manager and can see the data in OBIEE. This is a very simply example of using BI Apps, I’ve barely scratched the surface of what would be required for a real-life installation, but this demonstrates some of the main steps required.

Enjoy!


Installing BI Apps 11.1.1.8.1: Part 2 – Installing & Configuring BI Apps



This is the second part of my example walk-through of performing an installation of the newly released Oracle BI Applications 11.1.1.8.1.

I’m performing a completely fresh installation on our Exalytics/Exadata servers using an E-Business Suite 12.1.3 database as the data source.

In part 1 (http://paulcannon-bi.blogspot.co.uk/2014/06/installing-bi-apps-111181-part-1-pre.html) I went through the pre-requisites required before BI Apps can be installed, including installing OBIEE, ODI and creating the warehouse and repositories using the RCU.

Part 2 below will cover installing and configuring BI Apps

Part 3 (http://paulcannon-bi.blogspot.co.uk/2014/06/installing-bi-apps-111181-part-3.html) will demonstrate a simple example of load data into the warehouse and viewing it in OBIEE.

Before beginning the pre-requisites in Part 1 must have been performed – they install the OBIEE platform onto which BI Apps will be installed as well as setup ODI and the database warehouse & repository schemas.

Carrying on then, we are now at the point of starting the BI Apps installation.

Install Oracle BI Applications


From the files downloaded in Part 1, unzip “Oracle Business Intelligence Applications (11.1.1.8.1).zip” into a temporary directory. From within here go to the \biappsshiphome directory and run:

./runInstaller -jreLoc <OBIEE_HOME>/Oracle_BI1/jdk

 

Skip past the welcome screen when it appears:


Confirm all the pre-requisite checks pass:

 
The Oracle Home Directory must be the same directory under which OBIEE is installed – I seen this screen default to the next number up (Oracle_BI2) sometimes in previous installations  of BI Apps, so double check it and change it back to the original OBIEE directory if necessary.  If you don’t change this the installer just hangs, no messages, no files being copied, it just sits there indefinetly!



Next Confirm you’ve selected oracle home correctly:


Let the installation proceed:


 And wait for it to finish.



This has installed the BI Apps software into the OBIEE 11g home directory, but as yet it is not configured or ready to use.

Apply Fusion Middleware Platform Patches.

There are a number of patches to various Fusion Middleware components that need to be applied before we can proceed. These patches are all supplied as part of the BI Apps media pack within three zip files.

Before beginning ensure OBIEE, ODI and Weblogic is stopped – including the node manager.

Download and unzip to a temporary directory three patch files:

Oracle Fusion Middleware Platform Patches for Oracle Business Intelligence Applications (Part 1 of 2).zip



Oracle Fusion Middleware Platform Patches for Oracle Business Intelligence Applications (Part 2 of 2) .zip



Oracle Fusion Middleware Platform Patches for Oracle Business Intelligence Applications for Microsoft Windows x86 (64bit).zip

Your temporary patch directory should contain 5 sub-directories, biappsshiphome, odi, oracle_common, soa & weblogic



These directories contain more zip files - these will be unzipped by the script that performs the patch installation.

Now the patch script can be run. This uses perl and needs to be run from a command window (in Windows open with the Run as Administrator option)

Change directory to the <OBIEE_HOME>\Oracle_BI1\biapps\tools\bin directory.


The APPLY_PATCHES.pl script uses a parameter file, apply_patches_import.txt, to control its process and this is located in the same directory.

You need to edit this file in a text editor and amend all of the paths specified. The original file looks like this:

# Required - Java home
JAVA_HOME=/scratch/aime/work/mw_home/Oracle_BI1/jdk
# Required - Inventory Location
INVENTORY_LOC=/scratch/aime/oraInventory
ORACLE_HOME=/scratch/aime/work/mw_home/Oracle_BI1
MW_HOME=/scratch/aime/work/mw_home
COMMON_ORACLE_HOME=/scratch/aime/work/mw_home/oracle_common
WL_HOME=/scratch/aime/work/mw_home/wlserver_10.3
SOA_HOME=/scratch/aime/work/mw_home/Oracle_SOA1
ODI_HOME=/scratch/aime/work/mw_home/Oracle_ODI1
# Required - An existing writable directory to create logs and temp patch files
WORKDIR=/scratch/aime/tmp/work
# Required - Path of patch directory till the release patch-set folder which is inside patches4fa/dist
# i;e the patch-set folder for a particular release to apply patch from
# eg; /scratch/aime/tmp/ps1/biappsshiphome/patches4fa/dist/ps6rc3
PATCH_ROOT_DIR=/scratch/aime/tmp/patches4fa/dist/ps6rc3

My amended file is:

JAVA_HOME=/u01/app/biapps/Oracle_BI1/jdk
INVENTORY_LOC=/home/oracle/oraInventory
ORACLE_HOME=/u01/app/biapps/Oracle_BI1
MW_HOME=/u01/app/biapps
COMMON_ORACLE_HOME=/u01/app/biapps/oracle_common
WL_HOME=/u01/app/biapps/wlserver_10.3
SOA_HOME=/u01/app/biapps/Oracle_SOA1
ODI_HOME=/u01/app/biapps/Oracle_ODI1
WORKDIR=/home/oracle/temp
PATCH_ROOT_DIR=/home/oracle/Downloads/patches


Essentially do the following:
 
Replace /scratch/aime/work/mw_home with your <OBIEE_HOME> directory.
Set INVENTORY_LOC to your oracle inventory directory
Set WORKDIR to a temporary directory
Set PATCH_ROOT_DIR to the directory where you unzipped the patch files above 

The command is:  perl APPLY_PATCHES.pl <PARAM_INPUT_FILE_PATH>

Replacing <PARAM_INPUT_FILE_PATH> with the full path to the above parameter file. E.g.:

cd /u01/app/biapps/Oracle_BI1/biapps/tools/bin
perl APPLY_PATCHES.pl /u01/app/biapps/Oracle_BI1/biapps/tools/bin/apply_patches_import.txt

Upon completion a patching report called final_patching_report.log  will be created in the WORKDIR directory specified above

----------START OF PATCHING REPORT------------------

Current PLATFORM Detected :linuxx8664

* BIAPPSSHIPHOME Patching Report ..........
Patch Succeded: fsmlitedb_rel8pmlr2_17937188.zip
Patch Succeded: p17463314_111170_Generic.zip
Patch Succeded: p16913445_111170_Generic.zip
Patch Succeded: fsmlite_rel8pmlr2_18529692.zip
Patch Succeded: fsclite_rel8pmlr2_17937189.zip
Patch Succeded: p17463403_111170_Generic.zip
Patch Succeded: p17300045_111170_Generic.zip
Patch Succeded: p16997936_111170_Generic.zip
Patch Succeded: atglite_rel8mlr12_17657541.zip
Patch Succeded: atglite_rel8mlr11_database_17533000.zip
Patch Succeded: BIFNDNEPM_11_1_1_7_0OBIEEBP_LINUX_X64_130816_0640_17314326.zip
Patch Succeded: BISERVER_11_1_1_7_0OBIEEBP_LINUX_X64_130919_2217_17463395.zip
Patch Succeded: BIFNDN_11_1_1_7_0OBIEEBP_LINUX_X64_131008_1224_5_17463376.zip
Patch Succeded: p16569379_111170_Linux-x86-64.zip

* Oracle Common Patching Report ..........
Patch Succeded: idmpatch-1310a-ps6-17447428-owsmrel8.zip
Patch Succeded: SOACOMMON_REL8B7_17549390.zip
Patch Succeded: EM_p14670290_111170_Generic.zip
Patch Succeded: Webservices_Patch-2-PS6REL8_bug-17629596.zip
Patch Succeded: J2EE_Patch-3-PS6RC3_bug-17237952.zip
Patch Succeded: p16080773_111170_Generic.zip
Patch Succeded: WebCenter_BP8REL8_2_17483105.zip
Patch Succeded: p16433036_111170_Generic.zip
Patch Succeded: p16830801_111170_Generic.zip
Patch Succeded: appstrace_17363039.zip
Patch Succeded: T2P_FW_REL8_Patch2_17353546.zip
Patch Succeded: p16433147_111170_Generic.zip
Patch Succeded: idmpatch-1311a-ps6-17665823-opss.zip
Patch Succeded: JDEVADF_REL8MLR9-RT_17611288.zip
Patch Succeded: fmw_diagnostics_17714493.zip
Patch Succeded: idmpatch-1309a-ps6-17440204-urapi.zip

* SOA Patching Report ..........

* ODI Patching Report ..........
Patch Succeded: p18204886_111170_Generic.zip

* Weblogic Patching Report ..........
Successfully installed Patch ID:  2GH7
Successfully installed Patch ID:  W3Q6
Successfully installed Patch ID:  RJNF
Successfully installed Patch ID:  FKGW
Successfully installed Patch ID:  JEJW
Successfully installed Patch ID:  LJVB
Successfully installed Patch ID:  IHFB
Successfully installed Patch ID:  6AEJ
Successfully installed Patch ID:  EAS7
Successfully installed Patch ID:  TN4A
Successfully installed Patch ID:  KPFJ

----------END OF PATCHING REPORT------------------



If any of the patches fail you can see individual patch logs in the WORKDIR directory which may help resolve any issues.

Updating ATGLite & FSM

ATGLite and FSM are two components of the BI Apps toolset are upgraded by the patch set in the previous set but which may also contain database objects in the BI Apps repository which also needs updating.

This updating is performed by calling the biappsrepositoryutil.jar file twice with a long list of parameters. 
biappsrepositoryutil.jar exists in the <OBIEE_HOME>/Oracle_BI1/biapps/tools/lib directory.

It requires the following parameters:

  • upgradeATG or upgradeFSM:  the tasks being performed.
  • bi.oracle.home:  the location of the Oracle_BI1 directory
  • work.dir: a temporary working directory
  • biacomp.user:  the BI Apps repository schema created in the RCU, e.g. BIAPPS_BIACOMP
  • biacomp.password: the password to the BI APPs repository
  • db.conn.sid.url: the connection string for the database in host:port:sid format
  •  atg.db.seed.url or fsm.db.seed.url: the connection string for the database in host:port/service_name format
  • db.sys.user: A db user with sysdba privileges, e.g. sys
  • db.sys.password: the password to the db sysdba user.

Change directory to the <OBIEE_HOME>/Oracle_BI1/biapps/tools/lib directory.

Run this first for the ATG update:

/u01/app/biapps/Oracle_BI1/jdk/bin/java -jar biappsrepositoryutil.jar upgradeATG bi.oracle.home=/u01/app/biapps/Oracle_BI1 work.dir=/home/oracle/temp biacomp.user=BIAPPS_BIACOMP biacomp.password=welcome1 db.conn.sid.url=dbserver:1521:ora12c atg.db.seed.url=dbserver:1521/ora12c.local.com db.sys.user=sys db.sys.password=welcome1

Then run it for the FSM update:

/u01/app/biapps/Oracle_BI1/jdk/bin/java -jar biappsrepositoryutil.jar upgradeFSM bi.oracle.home=/u01/app/biapps/Oracle_BI1 work.dir=/home/oracle/temp biacomp.user=BIAPPS_BIACOMP biacomp.password=welcome1 db.conn.sid.url= dbserver:1521:ora12c  fsm.db.seed.url=dbserver:1521/ora12c.local.com db.sys.user=sys db.sys.password=welcome1

Both commands generate a log of steps which should end with ‘Completed component upgrade’:


Weblogic and OBIEE can now be restarted by running your normal start script (biapps.sh if you used the one from part 1 of this blog).

You can see the impact of the patching by logging into OBIEE with the Weblogic admin user and clicking in the Administration link at the top of the page. In the Administration screen you can see that the version of OBIEE has changed from the 11.1.1.7.0 that was originally installed to 11.1.1.7.131017:



Configure BI Applications.

At this point the BI Apps and ODI software has been installed but not yet deployed to the weblogic server.  So the next step is run the configuration wizard to perform the deployments.

In summary the following components are about to be deployed:

o   Oracle BI Applications Configuration Manager
o   Functional Setup Manager
o   ODI
o   Java EE Agent
o   ODI Console
o   Load Plan Generator

Also an Administrator user for BI Apps will be created and the ODI repository will be configured.


To start the configuration wizard locate and run the following command:

<OBIEE_HOME>/Oracle_BI1/bin/configApps.sh

Skip past the welcome screen:



Confirm the prerequisite checks pass ok:


Enter the password for the Weblogic admin user (the other fields should be default filled):



The next screen shows you the home location details, but you can’t change anything:


Select ‘BI Application Components’ to install:


You now need to specify a password for the OBIEE repository – a new RPD file will be installed by this process.


Next you need to create a new admin user for BI Apps. This will be the main admin user for both BI Apps and ODI:

Now you need to provide the location and user-id/password for the OBIEE MDS schema – created by the RCU when OBIEE was installed.

Next comes the BI Apps repository schema – BIAPPS_BIACOMP, created by the BI Apps RCU:



Then it needs the connection details for the data warehouse schema, BIAPPS_DW:






And finally the ODI Repository schema, BIAPPS_ODI_REPO:


Next you can specify the ports, but again I’m keeping defaults – 9704 for BIACM and 15001 for the ODI Console:



Then confirm the details are correct before starting the configuration:



Let the configuration progress…



Take a note of the installation details (URLs etc) and save them before closing the Configuration tool.



Copying Source Files

The source ETL files used by BI Apps are located under the OBIEE home directory as part of the installation, however they must now be moved to somewhere outside the OBIEE home otherwise they risk being upgraded automatically in the future if patches are applied and thereby overwriting any custom ETL developments.

The new location must be somewhere on the same OBIEE server where the ODI agent can access them, but outside of the OBIEE (or Weblogic) home directories. Also avoid locating them somewhere with a long directory path.

In this example I’ve chosen /u01/app/etl


Install ODI Studio


To install on client pc, unzip the same zip files for the full ODI install on the server and run the setup.exe with the –jreLoc parameter (install java JDK on your client pc first) from the Disk1 directory:

setup.exe -jreLoc C:\Progra~1\Java\jdk1.7.0_51

Skip past the welcome screen:



Choose to Skip software updates:




In the Installation Type screen just select ODI Studio:


Confirm the prerequisites pass ok:

Take a note of the install location in the following screen:


Again choose to skip the Repository Configuration – in this case because it’s already been done.


Choose whether to receive email updates, I chose not to:

Confirm what is about to be installed:



Let the installation progress:



Check the configuration happens ok:



Close the installer once it’s compete.



Do not launch studio yet.


The next step is to synchronize the security between Studio and the Weblogic server. This needs to be performed for all client Studio installations and the Studio installed on the server (if you intend to use it).

Firstly Copy the files cwallet.sso and jps-config-jse.xml from:


<OBIEE_Home>/user_projects/domains/bifoundation_domain/odi-client-config/embedded

On the server to:

<ODI_Home>/oracledi/client/odi/bin



For the client studio installation FTP these two files from the OBIEE server to this directory on the client machine.

Note these files will need to be regenerated and copied whenever the bi apps administrator password, or any ODI users passwords, are changed in weblogic. To regenerate these files run:



ORACLE_HOME/common/bin/wlst.sh <MW_Home>/Oracle_
BI1/bifoundation/install/createJPSArtifactsODI.py embedded --ADMIN_USER_NAME
<Administrator> --DOMAIN_HOSTNAME <Hostname> --DOMAIN_PORT 7001 --DOMAIN_HOME_
PATH <MW Home>/user_projects/domains/bifoundation_domain



Now edit the file <ODI_Home>/oracledi/client/odi/bin/odi.conf

vi /u02/app/biapps/Oracle_ODI1/oracledi/client/odi/bin/odi.conf

Locate the line:

AddVMOption -Doracle.security.jps.config=./jps-config.xml

And change it to:

AddVMOption -Doracle.security.jps.config=./jps-config-jse.xml

Here’s my odi.conf file:

IncludeConfFile ../../ide/bin/ide.conf
AddVMOption -XX:MaxPermSize=256M
AddVMOption -Doracle.core.ojdl.logging.config.file=ODI-logging-config.xml
AddVMOption -Dorg.apache.commons.logging.Log=org.apache.commons.logging.impl.Jdk14Logger
AddVMOption -Djava.util.logging.config.class=oracle.core.ojdl.logging.LoggingConfiguration
AddJavaLibFile ../../../../oracledi.sdk/lib/ojdl.jar
AddJavaLibFile ../../../../oracledi.sdk/lib/dms.jar
AddJavaLibFile ../../jdev/extensions/oracle.odi.navigator/lib/log4j-1.2.8.jar
AddJavaLibFile ../../jdev/extensions/oracle.odi.navigator/lib/odi_hfm.jar
AddJavaLibFile ../../jdev/extensions/oracle.odi.navigator/lib/odihapp_common.jar
AddJavaLibFile ../../jdev/extensions/oracle.odi.navigator/lib/ess_es_server.jar
AddJavaLibFile ../../jdev/extensions/oracle.odi.navigator/lib/ess_japi.jar
AddJavaLibFile ../../jdev/extensions/oracle.odi.navigator/lib/odihapp_essbase.jar
AddJavaLibFile ../../jdev/extensions/oracle.odi.navigator/lib/odihapp_planning.jar
AddVMOption -Dnative.canonicalization=false
AddVMOption -Doracle.security.jps.config=./jps-config.xml
AddVMOption -Doracle.odi.studio.ess=false
AddVMOption -Dide.AssertCheckingDisabled=true
AddVMOption -Dide.AssertTracingDisabled=true
AddVMOption -DLOG_FILE=studio.log
AddVMOption -Dsun.java2d.noddraw=true
SetJavaHome /u01/app/biapps/Oracle_BI1/jdk




Load Plan Generator Plug-in

The Load Plan Generator is used by the BIACM to control the creation of load plans, however it possible to use it directly in ODI, indeed there are times when it’s best to run it ODI – the logging is far better when investigating issues.

The load plan generator plug-in for ODI is supplied in a zip file and can be found in

<OBIEE_HOME>/Oracle_BI1/clients/biappsclients/odi/OBIA_LPG_Plugin.zip

Copy this file to the <ODI_Home>/oracledi/client/ide/extensions folder in all studio installations and un-zip it:


Again repeat this for client studio installations.

Now in Studio the ‘Oracle BI Load Plan Generator’ menu option appears under the ODI menu:



Perform system setup tasks.

Now there are a number of tasks to prepare the system for use.

Setup repository connection in ODI Studio.


 Each Studio client installation will need a connection setup to the BI Apps ODI repository
Launch the ODI studio. If running from the server this can run from:

<OBIEE_HOME>/Oracle_ODI1/oracledi/client/odi.sh

From a client pc, launch it from:

<Install_Home>\Oracle_ODI_1\oracledi\client\odi.exe or odi64.exe

Once ODI has started click ‘Connect To Repository’:


Click the add (green cross) icon on the login screen:




Now fill in the details of the repository, using the biapps user-id created above during the BI Apps configuration and the BIAPPS_BIA_ODIREPO repository.

To make like easier you should select the work repository now as well (BIAPPS_WORKREP was setup by the BI Apps configuration above) and set it as the default connection. Click Test to ensure all is ok before saving.


Now back in the login screen connect with the biapps user-id:




Changing the ETL Location

Earlier on we moved the ETL directories out from under the <OBIEE_HOME>/ directory to somewhere else (/u01/app in my case). We now need to tell ODI where we’ve moved it to, otherwise it will continue to edit & run the ETL from the original location.

In Studio select the Topology tab then expand Technologies -> File then BIAPPS_DW_FILE:


This will by default be pointing at the /etl directory under the OBIEE home directory. This needs changing to the new directory you copied the ETL files to above.

Double click on BIAPPS_DW_FILE./u01/app/biapps/Oracle_BI1…


 
Then change the Directory (Schema) and Directory (Work Schema) to point to the new ETL location including the /data_files/src_files/BIA_11 directory, in my case:

/u01/app/etl/data_files/src_files/BIA_11
 Then click save (the disk icon in the menu bar)

 Now exit from ODI Studio.

Change the Weblogic start/stop script

The script you use to start & stop Weblogic now needs to be amended to include the odi_server1 service which has now been added. 
If you are using the script I used in Part 1 on this blog then do then edit this file (biapps.sh) and perform the following:
After the line:
BIEE_SERVER=bi_server1

Add the line:
ODI_SERVER=odi_server1

Then locate the line:
echo  password=$BIEE_PASSWD >>$ORACLE_FMW/user_projects/domains/$BIEE_DOMAIN/servers/$BIEE_SERVER/security/boot.properties
and add the following two lines after it:
echo  username=$BIEE_USER >$ORACLE_FMW/user_projects/domains/$BIEE_DOMAIN/servers/$ODI_SERVER/security/boot.properties
echo  password=$BIEE_PASSWD >>$ORACLE_FMW/user_projects/domains/$BIEE_DOMAIN/servers/$ODI_SERVER/security/boot.properties

Next locate the section:
echo "********************************************************************************"
echo "OBIEE start sequence completed on $(date)"
echo "********************************************************************************"

and just above this add the section:
echo "********************************************************************************"
echo "Starting Managed Server $ODI_SERVER on $(date)"
echo "********************************************************************************"
$BIEE_PATH/startManagedWebLogic.sh $ODI_SERVER http://$BIEE_MANAGER_URL &
wait_for "Server started in RUNNING mode"

Finally locate the section:
echo "********************************************************************************"
echo "Stopping Node Manager on $(date)"
echo "********************************************************************************"

and just above it add the following section:
echo "********************************************************************************"
echo "Stopping Managed Server $ODI_SERVER on $(date)"
echo "********************************************************************************"
$BIEE_PATH/stopManagedWebLogic.sh $ODI_SERVER t3://$BIEE_MANAGER_URL $BIEE_USER $BIEE_PASSWD

Save and test it by performing a full stop and start:
./biapps.sh stop
./biapps.sh start

If you are not using my script you will need to create the boot.properties file under:
 <OBIEE_HOME>/ user_projects/domains/bifoundation_domain/servers/odi_server1/security

for your own script to work. This can be copied from:
<OBIEE_HOME>/user_projects/domains/bifoundation_domain/servers/bi_server1/security/boot.properties
As the details are the same.

To confirm everything has started correctly login to the Weblogic console (http://servername:7001/console) and check the servers page:



Registering Source Systems and Propagating Connection Details to ODI 

 

We now need to setup the connection details for the source data systems - in my case I’m using the Vision demo in an E-Business suite 12.1.3 database. 

Open a browser and enter the following URL:

http://<host>.<port>/biacm

e.g. http://biapps_server.local.com:9704/biacm

 
Login with the biapps user created earlier.
In the navigation pane click on ‘Define Business Intelligence Applications Instance’ under ‘System Setups’.

Click the Add (green cross) icon in the Source Systems tab:


In this screen you specify which Product you are using as your data source and it’s version. I’m using E-Business Suite 12.1.3 so that’s what I’ve selected. 
The source instance name and description are what the connections will be called in BI Apps, so anything appropriate will do. 
The data source number is used in the warehouse to identify where data records come from, useful if you have more than one source. Enter a 1 in this field as it’s the first. If you add additional data sources later on these will increment to 2, 3 and so on.

Click next.


You now need to enter the connection details to the source system
Firstly in the middle ‘context details’ section select ‘Global’ as the context. This is the default context used in the ODI ETL.
In the Connection details section give the connection a name and fill out the driver, url, username and password fields appropriately. For an E-Business suite connection it should connect with the APPS user.
Click the Test button to ensure all is ok:


Next in the Connection details section click on the “Technology: File System” tab.
In here you need to specify the location of the data files for BI Apps. These are located under the /etl directory created above, under data_files/src_files, then the directory applicable to the source system, in my case EBS_12_1_3
Enter a name for the server, its host name and then the full path to this directory.
Then click “Save and Close”



Enabling offerings for Deployment

The next step is to switch on the BI Apps modules that you are deploying, which of course will depend on which modules have been purchased and which are applicable to the data source(s) you are using for this installation.
In the Task pane click on Manage Business Intelligence Applications:


Scroll down the list and select all the Functional Areas you require.
As you check each one you will get a window informing you of shared functional areas that are also required and therefore also selected:


You can expand the modules and individually include/exclude selected functional areas. Here I have drilled into Financial Analysis and de-selected Federal Financials:

Once you have selected all the required Module and Functional Areas click save.
Now click on the Business Intelligence Application Offerings and Associated Sources tab:

This is where you map each functional area to a data source. By default all functional areas will map to all sources. I have only created one source (E-Business Suite), so that is all that appears, but in reality you may have multiple sources each used for different functional areas, so you must disable those combinations which don’t apply.
Click save.


Set preferred Currency Names

In the Task pane click on manage Preferred Currencies:


In this screen you can change the Preferred Currency Name for each Currency Code used by BI Apps.  As you click on each currency you can see which Functional Areas it is used by in the bottom window (e.g. contract currency is only used by Project Analytics). The currency names are displayed on the dashboard in the Account dialog\Preferences settings window for each user.

To change a Currency Name click on it, then click the edit icon. Click save when done.


Set Languages for Data load into the warehouse

In the tasks pane now Click on ‘Manage Warehouse Languages’.
In here you can install all required languages for this BI Apps installation. Not quite sure why there’s not a British English (it’s our language after all…), so I’ll settle for keeping American English.


If you install more than one language you can set the default base language by highlighting the required language then clicking ‘Set Base Language’  icon in the toolbar above.
Click save when done.

Enabling Initialization Blocks 

Initialization blocks are small pieces of code which run upon either server startup or user login startup to set variables which control the user experience. Apart from the Fusion Apps modules, these are disabled by default, so needs to be enabled for the required modules. 

Start by copying the sample config.txt template file from 

<ORACLE_HOME>/Oracle_BI1/biapps/biarpdutil/bin 

to 

<ORACLE_HOME>/Oracle_BI1/biapps/biarpdutil/lib 

And rename it with a capital C – Config.txt 

Now copy the Oracle BI repository file for BI Applications from <OBIEE_HOME>/instances/instance1/bifoundation/OracleBIServerComponent/coreapplication_obis1/repository

To:

<BI_ORACLE_HOME>/biapps/biarpdutil/lib

This will be called OracleBIApps_BI000x.rpd   the x may vary, but is usually a 2 if BI Apps has been installed on a clean new installation of OBIEE. Whatever it is, take the file with the latest x-number.


Next execute the bi-init.sh script file.  This initialises various environment variables for the OBIEE instance.

This file is located in:

<OBIEE_HOME>/ instances/instance1/bifoundation/OracleBIApplication/coreapplication/setup

As it set variables under Linux make sure you run it with a .-space command, e.g.:




In addition to the OBIEE enbironment variables you also need to add the following directory to the PATH variable:

<OBIEE_HOME>>/Oracle_BE1/bifoundation/server/bin

export PATH=$PATH:/u01/app/biapps/Oracle_BI1/bifoundation/server/bin



 
Next edit the Config.txt file that you copied above as follows:

RPD File Name <RPD file name>
RPD Password <RPD password>
RPD Database List <Comma separated list of database names in RPD physical layer for which the initialization block has to be enabled>

The RPD file will be the RPD file you copied just above, it’s password was created earlier on in the BI Apps configuration (screen ‘Step 6 of 15’)

Note: The values in the RPD Database List must include all initialization blocks related to the source system that need to be enabled. "Oracle BI Server" must be included as a value in the RPD Database List if an initialization block that needs to be refreshed from the Oracle BI EE Server is to be enabled.
The full list is:

RPD Database List
Oracle Data Warehouse,
Oracle BI Application Configuration,
Oracle DB InitBlock Initialization,
Oracle EBS OLTP,
PeopleSoft OLTP,
Siebel OLTP,
Oracle BI Server,
oracle.apps.crm.model.analytics.applicationModule.CrmAnalyticsAM_CrmAnalyticsAMLocal,
oracle.apps.fscm.model.analytics.applicationModule.FscmTopModelAM_FscmTopModelAMLocal,
oracle.apps.hcm.model.analytics.applicationModule.HcmTopModelAnalyticsGlobalAM_HcmTopModelGlobalAMLocal,
CRM_OLTP

I changed my list to the following:

Oracle Data Warehouse,
Oracle BI Application Configuration,
Oracle DB InitBlock Initialization,
Oracle EBS OLTP,
Oracle BI Server


My final file looked like this:

RPD File Name<OracleBIApps_BI0002.rpd>
RPD Password<Admin123>
RPD Database List<Oracle Data Warehouse,Oracle BI Application Configuration,Oracle DB InitBlock Initialization,Oracle EBS OLTP,Oracle BI Server>

Next run the ManageInitBlock.jar utility, located in <OBIEE_HOME>/Oracle_BI1/biapps/biarpdutil/lib, using the following command:
  
<OBIEE_HOME>/Oracle_BI1/jdk/bin/java -classpath ManageInitBlock.jar oracle.apps.biarpdutil.ManageInitBlock >log.txt

This takes a little while to run. 

This created a new RPD file called Output_RPD.rpd with all relevant initialization blocks enabled. There is a log file, log.txt, which lists all the changes made to the RPD.

Rename this file to OracleBIApps.rpd and copy it back to the OBIEE repository directory. It won’t be used by OBIEE yet, but it will be ready for the next steps.





The OBIEE Administration Tool & Editing the RPD


The next three steps all involve the OBIEE repository file OracleBIApps.rpd,  created by the previous step.
This file can only be edited using the OBIEE Administration Tool, which is a windows-only utility and must be installed first. This is part of the Oracle Business Intelligence client tools; however you need to ensure you install the correct version.  Right at the start of the BI Apps installation we installed OBIEE 11.1.1.7.0 and we have the client zip file for that. However later on OBIEE was patched to 11.1.1.7.131017 and we do not have a client zip for this version.
Instead the client installer can be downloaded from OBIEE itself.  Launch a browser and login to OBIEE (http://servername:9704/analytics) with the Weblogic admin user-id.
If you already have the client tools installed you can check the version with the about screen in the Administration tool:


In the ‘Home’ page in the left hand pane there is a section called ‘Get Started…’ and under here a drop-down option called ‘Download BI Desktop Tools’. Under here choose the Oracle BI Client Installer (either 32 or 64 bit depending on your client windows version):



Save the file, biee_client_install_???.exe, to your pc (it’ll probably end up in your downloads folder) then run it. If this is on a server version of windows you may need to use the ‘run as administrator’ option by right clicking on the file.
Click past the splash screen:
 

Then the welcome screen:



Choose a location to install the software – the default is “C:\Program Files\Oracle Business Intelligence Enterprise Edition Plus Client Tools”, but this is a bit long-winded and you will need to access this directory in a dos command window later on, so I usually shorten it to C:\Program Files\OBIEE_Client


Decide where you want to create the product icons:


Then start the installation:


This will only take a few minutes:




When you close the installer it will automatically open the window ODBC Data Source Administrator:


The reason for this is to setup an ODBC link using the Oracle BI Server driver to the OBIEE server to enable on-line editing of the repository. This is something I generally discourage – off-line editing and keeping versioned copies of the RPD file is a much safer way of maintaining the repository – especially with very large complex repositories like the BI Apps one. I therefore usually cancel out of the ODBC Admin tool without creating the data source.

You now need to copy (ftp) the repository OracleBIApps.rpd file from the BI Apps server to your client pc.
From above this repository file is stored in:
<OBIEE_HOME>/instances/instance1/bifoundation/OracleBIServerComponent/coreapplication_obis1/repository
Now launch the Administration Tool from the start menu:


Once open, use File -> Open Offline to open the RPD file. The password when it prompts for it is the one you entered during the BI Apps configuration above (screen “Step 6 of 15”).
It will take a short while to open, it is very large – the new version for 11.1.1.8.1 now contains the mappings for Fusion Apps, so it’s almost twice the size as the previous version. Later on I’ll cover trimming out the unneeded mappings to reduce its size.


Once opened it’s ready for the next steps.

Enable Document Currency.


The first issue to address is the “Document” Currency (aka Entered Currency). This is disabled by default, but is usually required (if not by the users, it make testing easier) .To enable it you need to amend some filters in the RPD file.

The filters are stored by Security Groups and there is one per functional area:
OBIA_CRM_CURRENCY_PREFERENCES
OBIA_FINANCIAL_CURRENCY_PREFERENCES
OBIA_HCM_CURRENCY_PREFERENCES
OBIA_MARKETING_CURRENCY_PREFERENCES
OBIA_MFG_CURRENCY_PRFERENCES
OBIA_PARTNER_CURRENCY_PREFERENCES
OBIA_PROCUREMENT_CURRENCY_PREFERENCES
OBIA_PROJECT_CURRENCY_PREFERENCES
OBIA_SALES_CURRENCY_PREFERENCES
OBIA_SCM_CURRENCY_PREFERENCES

From in the open repository, select ‘Manage’ -> ‘Identity’ from the menu.
In the list of Application Roles locate each of the above roles one at a time:




Double click on the role:


Then click on Permissions:

Copy the contents of the Data Filter field into a text editor (re-format across multiple lines to make it easy to read):


 You need to remove the last condition:
AND
"Core"."Fact - Preference List"."Currency Preference Code" <>  'Document Currency'



Then paste it back into the data filter field (you need to edit back into a single line before pasting)
Click ok to save and then repeat for all of the other currency roles.



Trimming the RPD


The RPD contains all of the modules available in BI Apps, which may be more than have been purchased on a deployment. Whilst these can be left in (on the basis that they won’t be used) it is best to remove them to minimise the OBIEE start-up time and memory requirements for the RPD. It will also make it easier to maintain going forwards.
Additionally the size of the RPD is too big to deploy using the Enterprise Manager. This means that if you are using all the modules you will have to manually deploy the RPD after each change rather than using the Enterprise Manager upload & restart features. 
The RPD can be trimmed immediately upon install or later on after further customisations have been performed. The latter is a more complex task as you will need to merge in the customisations, so it’s best to do it during the installation.
To do this the RPD needs to be copied down to the client pc where Administrator is installed. We can start with the OracleBIApps.rpd edited above.
This step needs to be run in a command window. There is a batch file called bi-init.bat file installed with the BI Client installation which sets the necessary environment variables before we begin.
This will be found in the <client home>\oraclebi\orahome\bifoundation\server\bin  directory, in my case:
C:\Program Files\OBIEE_Client\oraclebi\orahome\bifoundation\server\bin
if you are running this on a windows server where OBIEE was installed run bi-init.cmd from the <obiee_home>\instances\instanceX\bifoundation\OracleBIApplication\coreapplication\setup directory.

Note this doesn’t appear to do much, but it alters the path and environment variables ready for the next commands.
Now run the ExtractProjects.exe command from the same directory.
ExtractProjects -B input_rpd  -O output_rpd -I "project_name"
Repeating the  -I "project_name" parameter for each project that has been purchased.
To see a full list of the projects included in this release see the ‘Supported Source Applications’ tab of the certification spread sheet downloaded at the start of the install process in part 1 of this blog. The ‘Oracle BI Application’ column lists the projects:


 However this spread sheet does not contain the actual project names, for this look in the RPD in the ‘Manage’ -> ‘Projects’ window:



In this case I just want the Financial,HR, Procurement & Spend and Supply Chain & Order Management analytics projects, which are called:
               Financial Analytics Fusion Edition
               Human Resources Analytics Fusion Edition
   Procurement and Spend Analytics Fusion Edition
   Supply Chain and Order Management Analytics Fusion Edition
I also want to retain the usage training module.

So the ExtractProjects command I need is:
ExtractProjects -B OracleBIApps.rpd  -O OracleBIApps_trimmed.rpd -I "Financial Analytics Fusion Edition" -I "Human Resources Analytics Fusion Edition" -I "Procurement and Spend Analytics Fusion Edition" -I "Supply Chain and Order Management Analytics Fusion Edition" -I "Usage Tracking"
Change directory to where the RPD file is before running.
You will be prompted for the Repository password.

The new RPD is considerably smaller than the original:


If you then open the new RPD in the Administration Tool you see it opens a lot faster and contains less content:

All that remains is to deploy this RPD to OBIEE. First rename the file back to OracleBIApps.rpd (deleting or renaming the original one).
 
Now open a browser and enter the URL for the enterprise manager: http://servername:7001/em and login with as Weblogic admin user.
In the left hand pane expand “Business Intelligence” and click on coreapplication:

Now click on the Deployment tab and Repository Sub-tab. Then click ‘Lock and Edit Configuration’ and wait for the confirmation screen to appear.



Click the Browse button in the ‘Upload BI Server Repository’ section and navigate to and select the new OracleBIApps.rpd file.  Wait a few seconds for the new file to be upload – this has completed when the Browse button disappears and an ‘Update…’ button appears in its place:

Now enter the repository password twice then click the Apply button.  Note that the Default RPD file name as incremented to OracleBIApps_BI0003.  Now click ‘Activate Changes’

Once the changes have been activated the OBIEE services need to be restarted. Click on the link to ‘Restart to apply recent changes’.
Now in the overview screen click the ‘Restart’ button:

Let the Restart commence and wait for it to complete.
You can login to OBIEE now and see the available dashboards and reports (use the Weblogic admin user again). We haven’t loaded any data yet, so the reports will not display anything, but you can see the full set of dashboards that have been installed:




That’s it! BI Apps is now installed and ready for use.  In Part 3 I’ll run through the steps to perform a basic data load from my E-Business Suite source system and give access to the reports in OBIEE.