Siebel Data Warehouse Installation and Administration Guide > Preinstallation Considerations for Siebel Data Warehouse >

General Guidelines for Setting Up Siebel Data Warehouse

The Siebel Data Warehouse is a database that contains dimensional schemas. Although it is technically possible to put the Siebel Data Warehouse in the same database as the Siebel transactional database, it is not recommended for performance reasons. The Siebel transactional database is structured as an online transaction processing (OLTP) database, whereas the Siebel Data Warehouse is structured as an online analytical processing 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.
• 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 Siebel Data Warehouse and should not be part of the routine backup processes.

  A complete listing of these tables is available in Siebel Data Warehouse Data Model Reference.

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

The Siebel Data 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 32K block/page size for tablespaces, 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 Siebel Data Warehouse configuration process, when you create the data warehouse tables using the procedure 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 8.