Extend the time range of Resource Capacity data

The Resource Capacity data for resources that do not have an end date are generated based on the ETL start and end dates during the initial load. You can use the extend data script (sample_extend_data.bat or sample_extend_data.sh) to extend this time interval so that you can compare resource capacity and demand over time. Suppose, for example, that the last full ETL populated the Operational Reporting database with data through 2011. You can use the extend data script to include data for additional years, for example, through the calendar year two years in the future.

The start date for the data loaded using the extend data script is the day after the end year boundary. The end year boundary is based on the end date that you specify and the fiscal calendar's year end. (See Table 7-1. Effect of PPM Center fiscal year on the calculated ETL start date and Table 7-2. Effect of PPM Center fiscal year on the calculated ETL end date.)

The extend data script runs the full ETL for capacity and demand for the extended time period and performs the incremental ETL for COMMON, RM, FM, and TM universes. If an incremental ETL job started by the extend script fails, you must run the incremental ETLs again. There is no need to run the extend data script again.

Recommendations for running the Extend Data Script

To minimize the performance impact of running the extend data script, consider the following:

  • Specify a data extension of just one year at a time instead of specifying multiple years.

  • When you run sample_extend_data.bat, the script first drops all of the bitmap indexes in the Resource Management fact tables, and then recreates the indexes after loading the data. We recommend that you not run reports during the extend data script run.

To run the extend data script, do the following:

  1. Gather the information listed in the following table.

    Variable in the Extend Data Script

    Description

    Reporting DB Schema Name

    Operational Reporting database schema name

    Reporting DB TNS Name

    Operational Reporting database TNS name

    Reporting DB index_tablespace_name

    Name of the index tablespace for the Operational Reporting database

    ETL end date (mm-dd-yyyy)

    End date for the OpenText PPM data to extract, transform, and load into the Operational Reporting database schema.a

        a.    We recommend you not to set the value for the ETL_END_DATE parameter too far away from the current fiscal year, otherwise performance issues may occur in incremental ETL. You must also extend the data and date to that before the ETL_END_DATE value; otherwise the data in the
    RPT_FCT_RM_RESOURCE_EFFORT table might be lost.

  2. Log on to the machine that can connect to PPM database and Reporting database.

  3. Do one of the following:

    • On a Windows system, go to the <Op_Reports_Home>/install/sample directory and open the sample_extend_data.bat file in a text editor.

    • On a UNIX system, go to the <Op_Reports_Home>/install/sample directory and open the sample_extend_data.sh file in a text editor.

  4. Replace each of the variables in the extend data script with the values you prepared for Step 1, and then save and close the file.

  5. Depending on your operating system, do one of the following:

    • On a Windows system, run sample_extend_data.bat.

    • On a UNIX system, run sample_extend_data.sh.

  6. During the extend data script run, provide the Operational Reporting database schema password when prompted.

  7. The script creates the extend_data.log file in the <Op_Report_Home>/install/log directory. Log data are also captured in the RPT_EVENT tables. Review the log files and data.