OBIA 7.9.6.3 Pre-requisites, System Configuration, Performance improvements (Server Side & Development Side), Query Performance Tuning, Maximum connections

Hi friends,

Today i got the below small requirement from my Client and i know its known post to everyone. Please correct me if anything wrong. Thanks to all.

OBIA Pre-requisites:

Oracle BI Applications Release 7.9.6.3

Oracle BI Enterprise Edition 11g (11.1.1.7.0)

Informatica Power Center Version 9.0.1 Hot fix 2

OBI Data Warehouse Administration Console (DAC) 10.1.3.4.1

Oracle database 11g Release 2

EBS R12.1.3

System Configuration:

Operation System: Linux (For better Security)

Maintain 2 Linux Servers (if possible) to install Servers of Informatica, DAC, OBIEE, and Database

Linux Server-1 : Database, OBIEE Servers

Linux Server-2 : Informatica, DAC Servers

Windows: Install all Clients Software’s on Windows machine (Informatica, DAC, OBIEE, OBIA)

For Servers:

RAM: 16 GB

Storage Space: 100 GB free space

For Clients:

RAM: 4 GB

Storage Space: 50 GB free space

Performance improvements (Server Side):

Performance is improved by using faster processors and additional memory, particularly in environments where users run multiple applications simultaneously.

Since a virtual environment is not native, there may be a possible performance impact when running Informatica products in a virtual environment. For best performance, Informatica recommends running in non-virtual environments.

 

BI APPS Performance Tuning (Development Side):

• The OBIA Version 7.9.6.x Performance Recommendations document provides good guidance on improving ETL performance

Notable recommendations include:

– In Informatica, use Custom Connections for long running mappings

– Allows you to set custom session parameters suitable for the requirements of the mapping

– Increasing the Commit Intervals

– Use Informatica 64-bit version

– Disable redundant bitmap indexes

– Wide table structure optimization (over 255 columns)-> move NULLs to the end

– Disable / Enable query indexes

– Joining Staging tables to lookup tables in Informatica Lookups

Query Performance Tuning

1. Partition Large Tables based on Fiscal Calendar or Operating Unit (based on client needs)

– Partition by Fiscal Month for Project Cost Line Fact and Project Revenue Line Fact

– Make sure the DAC is configured to handle indexing on partitioned tables using Custom Actions. Otherwise, Index Creation during the ETL WILL FAIL.

2. Use Materialized Views for creating custom aggregate tables, Hints in Rpd Level

– You can use the RPD to configure the MV as an aggregate logical table source or rely on MV query rewrites

– Integrate the refresh of the MV in the DAC Execution Plan as a Task Action or as a Custom Stored Procedure

– Register the MV as a table in the DAC and define as a related table to the original fact

3. For Oracle DBs, enable Star Transformations at the DB level or at the session level through the RPD Connection Pool.

Maximum connections:

By default in OBIEE Rpd (OracleBIApps.rpd) can gives 100 connections in Connection Pool and it allows up to 32767 Connections (max no. of connection pools).