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 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.
Table 4. Recommended Tablespace Configuration
|
|
DIM_STG |
W_*DS |
FACT_STG |
W_*FS |
DIM |
W_*D and W_*MD |
FACT |
W_*F |
AGG |
W_*A |
OTHER |
Remaining W* tables |
DIM_INDX |
Indices of W_*D tables (for example, other tables would include W*G and W*GS tables) |
FACT_INDX |
Indices of W_*F tables |
OTHER_INDX |
Remaining Indices of W* tables |
|