Friday 14 March 2014

Installing and Configuring Oracle BI Applications 11.1.1.7.1

In this example, we will install Oracle BI Applications 11.1.1.7.1 into a fresh Microsoft Windows 7 environment that has access to an Oracle 11g R2 (11.2.0.3+) database to hold the various repository schema and the BI Applications data warehouse.

Installation Prerequisites

For full details of the platform and software pre-requisites for Oracle Business Intelligence Applications 11.1.1.7.1, see the System Requirements and Supported Platforms for Oracle Business Intelligence Applications Release 11.1.1.7.x document on OTN, currently located at http://www.oracle.com/technetwork/middleware/ias/downloads/fusion-certification-100350.html

For the purposes of this article, you will need access the following system requirements:
·         8 GB RAM
·         300 GB HDD

Note that the examples in this article assume that you have given this environment the host name Sagar-PC, so if you can change the host-name to this, it will make the examples easier for you to follow.

Note: Make you IP Address static:
Configure the Windows environment to use the Microsoft Loopback / Network adapter to set your IP static, and edit the "hosts" file to resolve the machine name to the loop-back adapter's IP address by following these steps.

Refer the following fig. to configure your IP.
1. Go to the path, C:\Windows\System32\drivers\etc where the host file is located. Edit this hosts file  as below.
(If you got the error saying, Please check if this file is opened in another program.
Change the file permissions. Allow read, write, execute access to the your you are currently logged in.)

2. Finally, test that these new network settings work as expected by selecting Start > Command Prompt, then typing ping [machine name], so that the output looks like this:

Downloading Oracle BI Applications 11.1.1.7.1 Software.
We're now ready to install the software for your Oracle BI Applications 11.1.1.7.1 development environment. Before you start, make sure you have downloaded the following items from the Oracle Technology Network website, in this example all for the Windows Server 64-bit platform:

• Oracle 11gR2 (11.2.0.3+) database

• Oracle Fusion Middleware Repository Creation Utility 11.1.1.7
http://download.oracle.com/otn/nt/middleware/11g/111170/ofm_rcu_win_11.1.1.7.0_64_disk1_1of1.zip

• Oracle Business Intelligence 11.1.1.7
http://download.oracle.com/otn/nt/bi/111170/bi_windows_x86_111170_64_disk1_1of2.zip
http://download.oracle.com/otn/nt/bi/111170/bi_windows_x86_111170_64_disk1_2of2.zip
http://download.oracle.com/otn/nt/bi/111170/bi_windows_x86_111170_64_disk2_1of2.zip
http://download.oracle.com/otn/nt/bi/111170/bi_windows_x86_111170_64_disk2_2of2.zip

• Oracle Business Intelligence Applications Repository Creation Utility 11.1.1.7.1
http://download.oracle.com/otn/nt/bi/111171/Bus_Analytics_Apps_Suite_RCU_Win64.zip

• Oracle Business Intelligence Applications Disk 1
http://download.oracle.com/otn/nt/bi/111171/Bus_Analytics_Apps_Suite_Installer_11.1.1.7.1.zip

• Oracle Fusion Middleware Platform Patches for Oracle Business Intelligence Applications
http://download.oracle.com/otn/nt/bi/111171/BI_Apps_11.1.1.7.1_generic_part_1of2.zip
http://download.oracle.com/otn/nt/bi/111171/BI_Apps_11.1.1.7.1_generic_part_2of2.zip
http://download.oracle.com/otn/nt/bi/111171/BI_Apss_11.1.1.7.1_win64.zip

• Oracle Data Integrator 11gR1 (11.1.1.7.0) http://download.oracle.com/otn/nt/middleware/11g/111170/ofm_odi_generic_11.1.1.7.0_disk1_1of2.zip
http://download.oracle.com/otn/nt/middleware/11g/111170/ofm_odi_generic_11.1.1.7.0_disk1_2of2.zip

Also, part of the installation process will require you to upgrade Oracle WebLogic Server from version 10.3.5 to 10.3.6, and to do this you will need to download the upgrade installer for WebLogic Server 10.3.6 from My Oracle Support, by loading download patch no. 13529623 and following article 1421307.1, to give you a file called wls1036_upgrade_generic.jar.

Once you have downloaded all of these installer and patch files, create a directory called E:\OBIA_Installers in your Windows environment and copy all of them to this new directory, so that the contents it look similar to the directory listing below (note that file names and sizes may change as Oracle release updated versions of the installers and patches).

Finally, you will also need to download the 7-zip archive utility, to use during the patching process for Oracle Business Intelligence applications, which you can download for free from http://7-zip.org/.  Make sure that, your are downloading setup for your Windows x64 bit box, it's mandatory that you install 64 bit version of 7 Zip utility.

Now that you have downloaded the software and patch files, we can start the actual installation process. In the following steps, we're assuming that you are already familiar with the installation process for Oracle Business Intelligence 11g, and the setup of the various repository schema using Oracle Fusion Middleware Repository Creation Utility 11g, and therefore we will only provide the key settings that you need to select when installing these products. For those products that are likely to be new to you with Oracle Business Intelligence 11.1.1.7, we will provide step-by-step instructions for their installation and configuration.

1. Installing Oracle Database 11.2.0.3+ with the following settings:

  • System Class : Server Class
  • Grid Installation Options : Single instance database installation
  • Select Install Type : Advanced Install
  • Select Database Edition : Enterprise Edition
  • Select Configuration Type : Data Warehousing
  • Specify Configuration Options : Character Sets > Use Unicode (AL32UTF8)
  • Specify Configuration Options : Security > Uncheck Assert all new security settings
  • Specify Schema Passwords : Use the same password for all accounts ("Welcome1")
Note that in this initial, 11.1.1.7.1 release of Oracle BI Applications with Oracle Data Integrator 11g support, only source systems running on Oracle Database are supported, and Oracle Fusion Applications are not yet supported as a source. In addition, only Oracle Database is supported as a target, with these restrictions likely to be lifted in a future release.

2. Repository Creation Utility for OBIEE 11.1.1.7.0:

Start by unzipping the Oracle Fusion Middleware Repository Creation Utility installer, and starte the Repository Creation Utility (RCU) installer, like this:
  • unzip ofm_rcu_win_11.1.1.7.0_64_disk1_1of1
  • go to Installers\OBIEE_11.1.1.7.0\RCU\rcuHome\BIN path
  • double click on rcu.bat file.
Then choose the following options when running the RCU:
  • Create a new repository
  • Database Type: Oracle Database
  • Host Name: Sagar-PC
  • Port: 1521
  • Service Name: orcl
  • Username: Sys
  • Password: Welcome1
  • Role: SYSDBA
  • Repository schema prefix: DEV_
  • At the Select Components screen, select Oracle Business Intelligence so that the Business Intelligence Platform, and the Metadata Services options are pre-selected for you, as per the following fig.

  • Do not select Oracle Data Integrator as a second Oracle BI Applications 11.1.1.7.1-specific Repository Creation Utility will do that for you later on.
  • When prompted at the end of the process, select Use same passwords for all schema and use "Welcome1" as the password.

3. Install Oracle Business Intelligence 11g 11.1.1.7.1

Next, unzip the Oracle Business Intelligence 11g 11.1.1.7 installer files and start the installer, like this:
bi_windows_x86_111170_64_disk1_1of2
bi_windows_x86_111170_64_disk1_2of2
bi_windows_x86_111170_64_disk2_1of2
bi_windows_x86_111170_64_disk2_2of2

Go to Installers\OBIEE_11.1.1.7.0\bishiphome\Disk1
Double click on setup.exe file.

Then, install Oracle Business Intelligence using the following main options, leaving other installation choices at their default value:
  • Installation Type: Enterprise Install


  • Create New BI System. Give credentials as, weblogic/Welcome1

  • Middleware Home: c:\Middleware

  • Choose all products for installation, including Essbase.










4. Install Oracle Data Integrator 11.1.1.7.0

Now you should install Oracle Data Integrator 11g, making sure that you DO NOT choose the option to configure repositories, as a later stage in the configuration process will do this for you.
ofm_odi_generic_11.1.1.7.0_disk1_1of2
ofm_odi_generic_11.1.1.7.0_disk1_2of2

  • Open command prompt,
  • Go to Installers\ODI_11.1.1.7.0\Disk1
  • setup.exe -jreLoc C:\Java\jdk1.6.0_45
  • On the Select Installation Type page, tick the Developer Installation check-box and both of its options, and the Java EE installation check-box and both of its options. 
  • Then, at the Specify Installation Location screen, select the same Middleware home that you used for Oracle Business Intelligence (for example, C:\Middleware). 
  • Finally, as mentioned previously, at the Repository Configuration page select Skip Repository Configuration.

5. Repository Creation Utility for OBIA 11.1.1.7.1:

The next step in this installation process is to use an additional, special version of the Repository Creation Utility to create the additional repository and other schema used by Oracle BI Applications 11.1.1.7.1.
Start by unzipping and then running the Oracle BI Applications Repository Creation Utility installer, like this:
  • unzip Bus_Analytics_Apps_Suite_RCU_Win64
  • go to installers\RCU\rcuHome\BIN path
  • double click on rcu.bat file.
Then choose the following options when running the RCU:
  • Database Type: Oracle Database
  • Host Name: Sagar-PC
  • Port: 1521
  • Service name: orcl
  • Username: Sys
  • Password: Welcome1
  • Role: SYSDBA
At the Select Components page as shown in Figure 4, check the Oracle Business Intelligence Applications checkbox and its options, and use the following (default) repository schema names:
Oracle Business Analytics Warehouse : DEV_DW
Oracle Business Intelligence Applications Components : DEV_BIACOMP
Oracle Data Integration Master and Work Repositories : DEV_BIA_ODIREPO

The Map Tablespaces screen displays the default and temporary tablespaces for the schema. On this screen, do the following:
  1. Leave the default values for Default Tablespace and Temp Tablespace for each schema.
  2. Set the temporary tablespace size for the data warehouse tablespaces according to the values in the table below:
  • Click Manage Tablespaces.
  • In the left-side navigation panel, select the appropriate tablespace name.
  • In the main window, select the tablespace name under the data file heading.
  • Click the Edit icon to display the data file dialog.
  • In the Edit data file dialog, set the temporary tablespace size according to the table below:
  • Click OK to exit the Edit Datafile dialog. 
  • Then, click Next in the Map Tablespaces screen, and click OK in the message dialog to create the tablespaces for the schemas.
  • When prompted to set passwords for these schema, use the option to use the same password for all schema, setting it to "Welcome1".
  • At the Custom Variables page shown in fig below, enter the same value below for the three database export file locations:
    C:\installers\biappsrcu\rcu\integration\biapps\schema
  • Once you have entered these details, creation of the relevant tablespaces and schema along with importing of the schema' database objects, usually takes around fifteen minutes or so. Once complete, you are now ready to install the Oracle Business Intelligence Applications software.

5. Install Oracle Business Intelligence Applications 11g 11.1.1.7.0

To unzip the installer files for Oracle Business Intelligence Applications and then run the installer, run the following from the Windows Command Prompt:
unzip Bus_Analytics_Apps_Suite_Installer_11.1.1.7.1
  • Open comman prompt,
  • Go to Installers\obia\Disk1
  • setup.exe -jreLoc C:\Java\jdk1.6.0_45
  • At the Specify Installation Location dialog, select the existing middleware home (C:\Middleware), and change the Oracle Home Directory value from the default Oracle_BI2 back to the existing one used by the previous Oracle Business Intelligence install, Oracle_BI1.
  • Once done, press Install to start the installation process.

6. Apply patches

The previous step installed the Oracle Business Intelligence Applications software, and now you need to apply various patches to it and the other components you installed earlier on. Oracle have supplied a script that uses the 7-zip archive utility you downloaded earlier on to apply the patches, and this script uses a parameter file that allows you to specify where the patch files are located. 

Before you start the patching process though, shut down all of your BI-related products by selecting Start > All Programs > Oracle Business Intelligence > Stop BI Services, and use the Windows Services applet to stop the Node Manager and Oracle Process Manager services, if they are running.

(Note: I personally suggest, restart your system to completely shut down BI process and then stop the Node Manager and Oracle Process Manager services)
  • MS Windows is having so many issues with file permissions, and this step will write a log to the file located in C:\Program Files\Oracle\Inventory\logs. 
  • For that, please give read-write-execute permission to Inventory folder.
  • Set both environmental variables (User variable and System variable) and Restart your system. 
    ORACLE_HOME= C:\Middleware\Oracle_BI1
    (You will delete both of this variables after this patching process.)
  • Create one temporary folder having read-write permission. Patching process will use this folder to save extracted files.
Then, with the same text editor application, open the APPLY_PATCHES.pl file in the same directory and modify the following lines within the download_patch routine, from the default:

$DOWNLOAD_EXE = "\"C:${DIRSEP}Program Files${DIRSEP}WinZip${DIRSEP}wzunzip.exe\" -ybc -d";
$cmd = "$DOWNLOAD_EXE $_[0] $PATCHES_HOME";

So that it now reads:

$DOWNLOAD_EXE = "\"C:${DIRSEP}Program Files${DIRSEP}7-Zip${DIRSEP}7z.exe\"";
$cmd = "$DOWNLOAD_EXE x $_[0] -o$PATCHES_HOME";

Then, from the Windows Command prompt type in the following to run the patch installer script:
  • Open command prompt,
  • Go to cd C:\Middleware\Oracle_BI1\perl\bin 
  • perl.exe C:\Middleware\Oracle_BI1\biapps\tools \bin\APPLY_PATCHES.pl C:\Middleware\Oracle_BI1\biapps\tools\bin\apply_patches_import.txt
Once the script completes, check the resulting output which should look similar to this:

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

* BIAPPSSHIPHOME Patching Report ..........
Patch Succeded: fsclite_rel7mlr2_16424872.zip
Patch Succeded: BISHIPHOME_11_1_1_7_0_GENERIC_130409_1210_16492220.zip
Patch Succeded: atglite_rel7mlr5A_database_16239380.zip
Patch Succeded: atglite_rel7mlr5_16480022.zip
Patch Succeded: fsmlitedb_rel7mlr2_16424869.zip
Patch Succeded: fsmlite_rel7mlr2_16424870.zip
Patch Succeded: BISERVER_11_1_1_7_0_WINDOWS_X64_130326_2045_4_16583998.zip
Patch Succeded: BIFNDN_11_1_1_7_0_WINDOWS_X64_130322_1000_5_16462147.zip

* Oracle Common Patching Report ..........
Patch Succeded: p16433147_111170_Generic.zip
Patch Succeded: p16080773_111170_Generic.zip
Patch Succeded: Webservices_Patch-3-PS6RC3_bug-16487786.zip
Patch Succeded: p16433036_111170_Generic.zip
Patch Succeded: JDEVADF_REL7MLR10-RT_16525527.zip
Patch Succeded: WebCenter_BP1REL7RC3_16311963.zip
Patch Succeded: fmw_diagnostics_14757820_2.zip
Patch Succeded: SOACOMMON_REL7B8_16438735.zip

* SOA Patching Report ..........
 Not applying SOA Patches since SOA Home (SOA_HOME) value is not specified

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

* Weblogic Patching Report ..........

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

Note that, no Weblogic patches will be applied in this step, as they only apply to WebLogic Server 10.3.6 (to which you will upgrade later on in this process) and Oracle no longer require these patches to be applied.
  • Delete the environmental variables  for ORACLE_HOME created before this step.
  • Restart your system.

7. configApps.bat process

The penultimate step in the installation process is to use the Configuration Utility to extend the Oracle BI Domain to include the new Oracle BI Applications Java components, and add an additional WebLogic Server managed server for the Oracle Data Integrator Java components. Before you do this though, make sure that the Node Manager and Oracle Process Manager services are started, and you have restarted your Oracle Business Intelligence installation using Start > All Programs > Oracle Business Intelligence > Start BI Services.

To start the Configuration Utility, type in the following commands at the Windows command prompt:
  • cd c:\Middleware\Oracle_BI1\bin
  • configApps.bat
When the Configuration Utility starts:
At the Extend BI Domain page enter the following values:
  • Hostname: [pre-completed for you]
  • Port: 7001
  • User Name: weblogic
  • User Password: Welcome1
At the Configure Components page, check the BI Application Components check-box, and press Next, as shown.

At the RPD Encryption Password page, enter Admin123 as the password. 

At the BI Applications Administration User page, type in the following values, to create an administration user called "biadmin" that you will use later on in the second-half of this article.

At the MDS Schema page, enter the following values

At the BI Applications Components Repository Schema page, enter the following values:

At the Business Analytics Warehouse Schema page, enter these values:

At the BI Applications ODI Repository Schema page:

Once values for these settings have been provided, the configuration process will start, as shown in Figure below. Allow this process to complete and check that all steps have completed successfully before proceeding further.

Stop your BI Services and simply restart your system.

8. Upgrade Weblogic from 10.3.5 to 10.3.6

As Oracle Business Intelligence 11.1.1.7 comes with version 10.3.5 of Oracle WebLogic Server, you will now have to upgrade it to version 10.3.6 using the upgrade installer you obtained previously from My Oracle Support.
Before you do this though, start by shutting-down WebLogic Server and the various Oracle Business Intelligence system components by selecting Start > All Programs > Oracle Business Intelligence > Stop BI Services from the Windows Start menu, entering the WebLogic administration username and password you specified earlier (weblogic/Welcome1) when requested. Then, using the Windows Services utility, stop the Oracle WebLogic NodeManager (c_Middleware_wlserver_10.3) service.
Unzip the upgrade installer patch file and start the installer like this:
  • open command prompt,
  • cd C:\Java\jdk1.6.0_45
  • java -jar installers\wls1036_upgrade_generic.jar
  • Middleware Home Directory: Use an existing Middleware Home (c:\Middleware)
  • Products and Components: Select everything
Then, press Next until the upgrade starts. Ensure it completes successfully, and un-tick the "run quickstart" check-box before exiting the upgrade installer.

Restart your system.

9. Post Installation Steps:

Start your BI services. Open your EM at http://Sagar-PC:7001/em and check whether all the services are up and running.

A number of post-installation steps now have to be performed, starting by configuring Oracle Data Integrator to use Fusion Middleware rather than standalone security.
To perform this security configuration, run the following commands from the Windows command prompt:
  • cd c:\Middleware\Oracle_BI1\common\bin
  • wlst.cmd c:\Middleware\Oracle_BI1\bifoundation\install\createJPSArtifactsODI.py embedded --ADMIN_USER_NAME weblogic --DOMAIN_HOSTNAME Sagar-PC --DOMAIN_PORT 7001 --DOMAIN_HOME_PATH c:\Middleware\user_projects\domains\bifoundation_domain
When prompted, enter welcome1 as the password for the WebLogic Server administration user.
Then, issue the following command-line commands to copy across the wallet files created by the above steps to where Oracle Data Integrator is installed:
  • copy c:\Middleware\user_projects\domains\bifoundation_domain\odi-client-config\embedded\cwallet.sso c:\Middleware\Oracle_ODI1\oracledi\client\odi\bin\
  • copy c:\Middleware\user_projects\domains\bifoundation_domain\odi-client-config\embedded\jps-config-jse.xml c:\Middleware\Oracle_ODI1\oracledi\client\odi\bin\

Then, using a text editor edit the c:\Middleware\Oracle_ODI1\oracledi\client\odi\bin\odi.conf document and replace the line:
AddVMOption -Doracle.security.jps.config=./jps-config.xml
with the text:
AddVMOption -Doracle.security.jps.config=./jps-config-jse.xml

So that the new wallet file is used when authenticating from Oracle Data Integrator Studio.

Then, install the Load Plan Generator plug-in for Oracle Data Integrator by unzipping the plugin archive file, and then copying the plugin files to the Oracle Data Integrator Studio client installation.

  • unzip C:\Middleware\Oracle_BI1\clients\biappsclients\odi
  • copy lpggui.jar :\Middleware\Oracle_ODI1\oracledi\client\ide\extensions
  • xcopy c:\Middleware\Oracle_BI1\clients\biappsclients\odi\* c:\Middleware\Oracle_ODI1\oracledi\client\ide\extensions /s /i /e
  • del c:\Middleware\Oracle_ODI1\oracledi\client\ide\extensions\OBIA_LPG_Plugin.zip
  • del c:\Middleware\Oracle_ODI1\oracledi\client\ide\extensions\Readme.txt
Almost there.!! There are just a few more patches to apply, and a new version of the Oracle BI Administrator application to install into your Windows environment.

Begin by stopping your Oracle Business Intelligence installation, by selecting Start > All programs > Oracle Business Intelligence > Stop BI Services

Then run the following SQL script to update the Oracle ATG repository data:
  • c:\Middleware\instances\instance1\bin>sqlplus
    dev_biacomp/Welcome1
    @C:\Middleware\Oracle_BI1\sdf\DW\ATGPF\sql\fndtbs_11170_upg.sql
I'm skipping the installation steps for updated version of the Oracle BI Administration tool as this is not mandatory.

The installation process also imported some files that the data loading process later on will require. To place these files in the correct location, again from the command prompt enter the following commands:
  • Mkdir c:\Middleware\Oracle_ODI1\oracledi\etl
  • xcopy c:\Middleware\Oracle_BI1\biapps\etl c:\Middleware\Oracle_ODI1\oracledi\etl /s /i /e

10. Test your installation

  • Delete temp, %temp%, prefetch data from your windows directories.
  • Restart your system.
  • Start BI services.
  • Check whether all the services are started successfully except odi_server1.
Finally, to try out Oracle Data Integrator Studio and set up a login connection, select Start > All Programs > Oracle > Oracle Data Integrator > ODI Studio. 
  • Click on the Connect to Repository link, 
  • When the Oracle Data Integrator log in dialog is shown, press the "+" (New) button to bring up the Repository Connection Information dialog. 
  • Enter the following details to create the connection:
Once you've created this connection and used it to log into Oracle Data Integrator Studio successfully, click on the BI Apps Project > Mappings folder, and then you'll see the pre-built data loading routines that you use to load the Oracle Business Analytics Data Warehouse, as shown in Figure 8. Congratulations! You have now performed the install part of the process.

References:
http://www.oracle.com/technetwork/articles/bi/mcginley-bi-apps-1993643.html
http://docs.oracle.com/cd/E38317_01/doc.11117/e35981/install.htm#

Note: I just followed the links mentioned above and added some few new step.

Tuesday 4 March 2014

Dynamic Column Headers OBIEE 11g

1. Create one presentation variable. e.g. Date - Use following screenshots as guidelines.



2. Create one dashboard level prompt. Calendar Date. Select Set a variable as presentation variable and give Date as the variable name.


3. Create one sample analysis, add date column on which you want to prompt and change the column heading.


4. Apply filter as "is prompted" to that date column.

5. Go to column properties of that date column.

6. Select "Custom Heading" and make it as, Selected : @{Date} - here Date is the presentation variable name.

7. Save it and drag it to the dashboard to test.


8. Change the prompt value and see column heading is as per the prompt value. e.g. If you have selected, 6/28/2012 then the column heading will be Selected : 6/28/2012

You are done. :-)





Monday 3 March 2014

NULL value in prompt OBIEE 11g

Problem: After OBI up-gradation our project from OBIEE 10g to OBIEE 11.1.1.6.0 or later, you can face one issue at Dashboard prompt level.

If you create Dashboard prompt on a column, its giving "NULL", "Unspecified" additionally. But, in 10g there were no extra values.

Answers:

1) Go to Admin Tool > Physical Layer.

2) Select Properties of column on which you are going to apply prompt.

3) Uncheck the check box which says "Nullable" as shown below.

4) Save the repository.

5) Check the changes are applied on dashboard. i.e. Null value is disappeared.

Note:
If you are making the changes in Online mode and saving it, don't forget to clear the cache.