Oracle^® Business Intelligence Applications Installation and Configuration Guide > Preinstallation and Predeployment Considerations for Oracle BI Applications >

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 is 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 prejoined, denormalized 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.
• Siebel 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.

The Informatica repository stores all of the Informatica object definitions for the ETL mappings that populate the Oracle Business Analytics Warehouse. It is a series of repository tables that are stored in a database, which can be transactional, analytical, or a separate database.

The Oracle Business Analytics Warehouse works with relational database management systems. In addition to the general requirements, there are additional database management systems (DBMS)-specific requirements depending on the DBMS you are using.

The following general guidelines will help you set up the data warehouse physical database for performance and growth:

• At a minimum, separate the data and index tablespaces. Create more tablespaces to separate heavily used tables and their indices.
• Use the maximum block and page size available for tablespaces ((for example, 32K), because it provides good overall performance and also does not impose low limits to the maximum size to which the tablespace can grow, as compared to 4K, 8K,16K sizes.
• If you are 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.
• Size the bufferpools based on content and size (number of tables and their sizes) of tablespaces.
• Allocate about 75 percent of the total available server memory to the database, assuming no other application is running on the same server.

During the Oracle Business Analytics Warehouse configuration process, when you create the data warehouse tables using the procedure Process of Creating Data Warehouse Tables, you can create tables in one tablespace and indices in another tablespace. However, for performance reasons, it is recommended that you create tablespaces as described in Table 4.