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.
Oracle BI Applications Release 18.104.22.168
Oracle BI Enterprise Edition 11g (22.214.171.124.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
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)
RAM: 16 GB
Storage Space: 100 GB free space
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.
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).