General Guidelines for Setting Up Oracle Business Analytics Warehouse
3-2
Oracle Business Intelligence Applications Installation Guide for Informatica PowerCenter Users
Section 3.4, "SQL Server-Specific Database Guidelines for Oracle Business
Analytics Warehouse"
Section 3.5, "Teradata-Specific Database Guidelines for Oracle Business Analytics
Warehouse"
Section 3.6, "Oracle-Specific Database Guidelines for Oracle Business Analytics
Warehouse"
Section 3.7, "Additional Suggestions for Optimizing Oracle Database Performance
in Oracle Business Analytics Warehouse"
Section 3.8, "Partitioning Guidelines For Large Fact Tables"
Section 3.9, "Miscellaneous Information About Oracle BI Applications
Deployments"
3.1 General Guidelines for Setting Up Oracle Business Analytics
Warehouse
The Oracle Business Analytics Warehouse is a database that contains dimensional
schemas. Although it is technically possible to put the Oracle Business Analytics
Warehouse in the same database as the transactional database, it is not recommended
for performance reasons. 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:
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 update efficiency.
Transactional queries join several normalized tables and will be slow (as opposed
to pre-joined, de-normalized analytical tables).
Historical data cannot be purged from a transactional database, even if 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.) This causes the transactional database to further slow down.
Transactional databases are tuned for one specific application, and it is 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.
On the transactional database, you should place the S_ETL tables in a separate
tablespace. These ETL tables are used by the Oracle Business Analytics Warehouse
and should not be part of the routine backup processes.
A complete listing of these tables is available in Oracle Business Analytics Warehouse
Data Model Reference.
To maximize ETL performance for Siebel CRM source systems running on DB2
databases, create three indexes on the Siebel OLTP database, using the following
SQL commands: