Prepare the Database Schema for Operational Reporting

The following parts provide instructions on how to prepare the Operational Reporting database schema.

Set up a database for Operational Reporting

Requirements and recommendations for setting up the database for Operational Reporting are as follows:

  • (Required) Configure the Operational Reporting database to use the same encoding as that for the OpenText PPM database.

  • (Required) Set the following Oracle parameters:

    • Set NLS_CHARACTERSET and NLS_NCHAR_CHARACTERSET parameters to use the same values as those for the OpenText PPM database.

    • Set NLS_LENGTH_SEMANTICS parameter to CHAR in both the Operational Reporting database and the OpenText PPM database.

  • We strongly recommend that you create an Oracle database specifically for Operational Reporting (independent of your OpenText PPM Oracle Database instance). Make sure that the OpenText PPM database and the Operational Reporting database can communicate over the database link.

  • We strongly recommend that you use the Enterprise Edition of Oracle Database for the Operational Reporting database. The advanced compression and partitioning featured in the Enterprise Edition significantly improve performance, especially if you report on a large and growing volume of data.

Configuring Oracle Database Parameters for Operational Reporting

We recommend that you use Oracle's automatic memory management (AMM) feature. To do this, set the value for either the memory_max_target parameter or the memory_target parameter, and then let Oracle manage the memory (SGA and the PGA) dynamically. For more information about how to optimize performance, see the Deployment Best Practices for PPM Operational Reporting document.

Back to top

Create tablespaces for the Operational Reporting schema

Before you can create the database schema for Operational Reporting, you must first create tablespaces (two data and two index tablespaces) for the star schema. This section sections provides instructions for performing this task.

To create the empty database schema (with tables to be populated during installation):

  1. Set up the required data and index tablespaces for the Operational Reporting database schema.

    Note: For information on the minimum size recommended for these tablespaces, see the System Requirements and Compatibility Matrix.

  2. Create two tablespaces that include the LOGGING option, as shown in the following examples:

    Example: CREATE TABLESPACE <RPT_DATA_TS>
datafile <'/u0/oracle/oradata/G1010/ppm_data01.dbf'>
size <Size>m
LOGGING
DEFAULT COMPRESS
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 32K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;
    CREATE TABLESPACE <RPT_INDEX_TS>
datafile <'/u0/oracle/oradata/G1010/ppm_index01.dbf'>
size <Size>m
LOGGING
DEFAULT COMPRESS
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 32K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;

  3. To improve performance and reduce resource consumption, create two tablespaces that include the NOLOGGING option, as shown in the following examples:

    Example: CREATE TABLESPACE <RPT_DATA_TS_NL>
datafile <'/u0/oracle/oradata/G1010/ppm_data01.dbf'>
size <Size>m
NOLOGGING
DEFAULT COMPRESS
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 32K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;
    CREATE TABLESPACE <RPT_INDEX_TS_NL>
datafile <'/u0/oracle/oradata/G1010/ppm_index01.dbf'>
size <Size>m
NOLOGGING
DEFAULT COMPRESS
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 32K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;

The Operational Reporting database schema is created automatically during deployment.

Back to top