Preinstallation and Deployment Requirements for Oracle BI Applications

2 Preinstallation and Deployment Requirements for Oracle BI Applications

This section describes preinstallation and deployment requirements for Oracle Business Intelligence Applications (Oracle BI Applications).

Topics:

General Guidelines for Setting Up Databases for Oracle BI Applications

These guidelines help you set up the data warehouse physical database and explain why a separate database is necessary for Oracle BI Applications.

Guidelines for Setting Up Oracle Business Analytics Warehouse Databases

These guidelines help you set up the data warehouse physical database for performance and growth.

Allocate approximately 50 to 70 percent of the total available server memory to the database, assuming no other application is running on the same server.
At a minimum, separate the data and index tablespaces. Create more tablespaces to separate heavily-used tables and their indexes.
Oracle recommends using 8 KB block size for Oracle warehouses.
If you're using multiple disk storage systems, stripe the tablespace containers and files across as many disks as possible.
Raw devices for tablespaces provide better performance as compared to cooked file systems.
RAID-5 is known to give a good balance of performance and availability.

Why Use a Separate Database for the Oracle Business Analytics Warehouse?

To avoid performance issues, don't put Oracle Business Analytics Warehouse in the same database as the transactional database. The transactional database is structured as an online transaction processing (OLTP) database, whereas the Oracle Business Analytics Warehouse is structured as an online analytical processing (OLAP) database, each optimized for its own purpose.

The reasons for not combining the two databases are:

ETL is configured to maximize hardware resources, therefore the warehouse shouldn't share any resources with any other projects.
The analytical queries interfere with normal use of the transactional database, which is entering and managing individual transactions.
The data in a transactional database is normalized for efficient updates. Transactional queries join several normalized tables and will be slow (as opposed to pre-joined, de-normalized analytical tables).
Historical data can't be purged from a transactional database, even if it's not required for current transaction processing, because you need it for analysis. (By contrast, the analytical database is the warehouse for historical as well as current data.) The transactional database slows down further.
Transactional databases are tuned for one specific application, and it's not productive to use these separate transactional databases for analytical queries that usually span more than one functional application.
The analytical database can be specifically tuned for the analytical queries and Extract-Transform-Load (ETL) processing. These are quite different from transactional database requirements.

Oracle-Specific Database Guidelines for Oracle Business Analytics Warehouse

These guidelines help you to optimize Oracle database performance and configure Oracle Business Analytics Warehouse on Oracle databases.

Topics:

General Guidelines for Oracle Databases

These additional suggestions help you to optimize performance for Oracle databases used for Oracle Business Analytics Warehouse.

Oracle BI Applications on Oracle databases support only binary sorting. If you're running an Oracle client, do one of the following:
- Set the NLS_SORT parameter to BINARY.
- Choose a NLS_LANG setting that includes binary.
These settings are required for adequate performance from the dedicated web client.
It's recommended that you gather workload system statistics.
Set the number of log file groups to 4.
To increase data throughput between Oracle BI Server and the Oracle database, configure the SDU and TDU settings as detailed below. The default is 8 KB and can be increased to 2 MB.
On the server side, make the following changes:
1. In the sqlnet.ora file, add applicable value for the DEFAULT_SDU_SIZE parameter. For example, DEFAULT_SDU_SIZE = 65535.
2. In listener.ora, modify your listener entry by adding SDU=. For example,
```
<your listener name > = 
(DESCRIPTION_LIST = 
(DESCRIPTION = (SDU = 65535) 
(ADDRESS = (PROTOCOL = TCP)(HOST = myhost)(PORT = 1521)) 
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) ) )
```
3. Restart the listener.
On the client side, make the following changes:
1. In the sqlnet.ora file, add applicable value for the DEFAULT_SDU_SIZE parameter. For example, DEFAULT_SDU_SIZE = 65535.
2. In Tnsnames.ora, modify the TNS alias by adding SDU= and TDU=. For example,
```
myhost_orcl.world= 
(DESCRIPTION= (SDU = 65535 ) (TDU = 65535) 
ADDRESS = (PROTOCOL = TCP)(HOST=myhost)(PORT=1521)) 
CONNECT_DATA=(SID=ORCL))
```

Guidelines for Using Oracle Template Files

To configure the Oracle Business Analytics Warehouse on Oracle databases more easily, refer to the parameter template file init11gR2_FusionApplications_template.ora file (this common template file applies to all sources in the Oracle BI Applications certified warehouse database) that is available in /ORACLE_HOME/bi/biapps/etl.

The parameter template file provides parameter guidelines based on the cost-based optimizer for the Oracle database version mentioned in the Certification Matrix. Use these guidelines as a starting point. Review the recommendations in the template file and make changes based on your specific database sizes, data shape, server size (CPU and memory), and the type of storage. Also, change the settings based on performance monitoring and tuning considerations. While making changes, ensure that you retain the following parameters:

db_block_size = 8192
processes = 2000
parallel_adaptive_multi_user = FALSE