Performance Analytics - Intelligence Operational Data Store 2.2.1
For Oracle Fusion Middleware 11g

Performance Analytics Installation

©2025 Copyright ReadSoft AG (publ). All rights reserved. The contents of this document are subject to change without notice. ReadSoft is a registered trademark of ReadSoft AB. Other product and company names herein may be the trademarks or registered trademarks of their respective owners.
Questions or comments about this document may be emailed to documentation@readsoft.com.

ReadSoft AB (Head office) | Södra Kyrkogatan 4 | SE-252 23 Helsingborg | Sweden | Phone: +46 42 490 21 00 | Fax: +46 42 490 21 20
ReadSoft AG | Falkstrasse 5 | 60487 Frankfurt | Germany | Phone: +49 69 1539402-0 | Fax: +49 69 1539402-13
info@readsoft.com | www.readsoft.com

Performance Analytics InstallationInstallation of the PROCESSIT Performance Analytics Operational Data Store (pi_ods)Download ScriptsRun ScriptsCreate Database LinkPopulate the DatabaseCreate D4 triggersEnabling Microsoft SQL Server ReplicationOracle ReplicationInstalling the Performance Analytics BI ServerMail Settings for Performance AnalyticsInstallation CompleteData Recovery

Performance Analytics Installation

Installation of the PROCESSIT Performance Analytics Operational Data Store (pi_ods)

Installation of Performance Analytics requires an account on or access to the ReadSoft ftp server. All files referenced in this document are available on the ftp server at ftp://ftp.readsoft-us.com/Software/Oracle/PROCESSIT/Separately_Licensed_Add-ons/performance_analytics/PA_2.2.1

It is assumed that the database link EBSDB exists on the server where PI_ODS is being installed.

Download Scripts

Download these files found in the /Software/Oracle/PROCESSIT/Separately_Licensed_Add-ons/performance_analytics/PA_2.2.1 folder.

  • create_PI_ODS_user.sql
  • grants_to_PI_ODS.sql.

Download one of these four scripts, depending on your technical infrastructure. This script is used to create the PI_ODS database.

  • You are running INVOICES on a SQL Server database, and the D4 schema and PI_ODS schema are on the same database: SQL_SERVER_NO_DB_LINK_performance_analytics_2.2.1.sql
  • You are running INVOICES on a SQL Server database, and the D4 schema and PI_ODS schema are not on the same database: SQL_SERVER_WITH_DB_LINK_performance_analytics_2.2.1.sql
  • You are running INVOICES on an Oracle database, and the D4 schema and PI_ODS schema are on the same database: Oracle_INVOICES_NO_DB_LINK_performance_analytics_2.2.1.sql
  • You are running INVOICES on an Oracle database, and the D4 schema and PI_ODS schema are not on the same database: Download Oracle_INVOICES_WITH_DB_LINK_performance_analytics_2.2.1.sql

Run Scripts

  1. Run the script create_PI_ODS_user.sql, which creates the PI_ODS user.
  2. If the PROCESSIT database is on the same serve as PI_ODS, run the script grants_to_PI_ODS which grants the necessary permissions on D4 objects to PI_ODS.
  3. Create the PI_ODS database using the appropriate script
  4. If your script does does not utilize a db_link, meaning PI_ODS and D4 schemas reside on the same database, execute the script grants_to_d4_no_db_link.sql. This script grants to D4 the necessary permissions on PI_ODS objects and may be run by the PI_ODS user.

The table DB_PARAMETERS contains the following parameters, that must be changed to contain appropriate values.

  • retention - The number of months for which data is retained in the PI_ODS database. The default is 36.
  • nls_date_language - The default is the value of the NLS_SESSION_PARAMETERS parameter NLS_DATE_LANGUAGE.
  • currency - The default currency used for reporting. Default is USD.

Create Database Link

If PROCESSIT is running on a remote server then you must create the db link D4DB. PI_ODS does not have CREATE DATABASE LINK privileges. Run the following code, where connection_string is in the format [ip]:[port]/ORCL;


	CREATE PUBLIC DATABASE LINK D4DB
	 CONNECT TO D4
	 IDENTIFIED BY <PWD>
	USING '<connection_string>'

Populate the Database

Run one of the following stored procedures to populate the database. Te procedure should be run by the PI_ODS user.

  • If the PROCESSIT database is on the same server as PI_ODS, execute the stored procedure INITIALIZE_PI_ODS.load_PI_ODS
  • If they are not on the same server, execute INITIALIZE_PI_ODS_lnk.load_PI_ODS.

Create D4 triggers

Run one of the following scripts to create the D4 triggers. The script should be run by the D4 user.

  • If PROCESSIT is installed on a remote server execute create_d4_triggers_dblink.sql.
  • If PROCESSIT and PI_ODS are on the same server as D4 execute create_d4_triggers_no_dblink.sql.

If the triggers have been installed prior to the initialization of PI_ODS then they should be disabled prior to the initialization of PI_ODS. If they are not the initialization may fail with an ORA-30926 error.

Performance Analytics Operational Data Store (PI_ODS) installation is complete.

Enabling Microsoft SQL Server Replication

Enabling Microsoft SQL Server Replication is required if the ReadSoft INVOICES database resides in Microsoft SQL Server. PI_ODS supports INVOICES 5.7.

  1. Execute the script SQL_Server_alter_tables.sql. This adds primary keys to the processlog and processlog_invoicedata tables. Primary keys are required for replication.
  2. Create a system DNS called perf_analytics using the ODBC Data Source Administrator.
    • The TNS Service Name must be in the form 172.20.5.90:1521/orcl
    • The User ID is pi_ods
  3. Test the connection to make sure it is working.
  4. Execute the script SQL_Server_Create_Server_Script.sql
  5. Make sure to change @datasrc to the connection string for the PI_ODS server.
  6. Make sure to change @rmtpassword to the password for the PI_ODS user.
  7. Execute the script SQL_Server_create_publisher.sql and change all instances of 172.20.5.90 in any references to the address of the PI_ODS server.
  8. The script assumes that the SQL Server database name is INVOICES_57. Change it if necessary.

Replication is enabled. You can verify this by ensuring that the tables processlog and processlog_invoicedata are in pi_ods and are populated.

Run the script Triggers_for_SQL_Server.sql. This creates triggers on the processlog and processlog_invoicedata tables and populates their shadow tables.

Oracle Replication

Enabling Oracle Replication is required if the ReadSoft INVOICES database resides in Oracle. PI_ODS supports INVOICES 5.7.

  1. Compile the script PI_ODS_utilities_pkg in the INVOICES database.
  2. Execute the stored procedure PI_ODS_utilities.modify_invoices_tables. This modifies the tables processlog and processlog_invoicedata and allows them to be used for replication.
  3. Execute the stored procedure PI_ODS_utilities.merge_into_PI_ODS. This populates the INVOICES based tables in PI_ODS. When it finishes the PI_ODS tables processlog and processlog_invoice_data should contain data.

Installing the Performance Analytics BI Server

  1. Create the spagobi user on the SpagoBI server.
  2. Download the files SpagoBI-Server-4.1.zip and spagobi.dmp from /Software/Oracle/PROCESSIT/Separately_Licensed_Add-ons/performance_analytics/PA_2.2/SpagoBI
  3. Unzip SpagoBI-Server-4.1.zip.
  4. Verify that the owner of the files and directories under the SpagoBI-Server-4.1 folder is spagobi, and that the shell files in the bin and database folders have execute permissions.
  5. Make sure that ports 8080 and 8009 are open on the server.
  6. Update the following two lines in the file /conf/server.xml by changing 172.20.5.188 to the IP address of your Performance Analytics server. Do not set the url using localhost, as this does not get translated to the IP address. 8080 is the default address used by Tomcat and it should not be changed. 
    
	<Environment name="spagobi_service_url" type="java.lang.String" value="http://172.20.5.188:8080/SpagoBI"/> 
	<Environment name="spagobi_host_url" type="java.lang.String" value="http://172.20.5.188:8080"/>
  7. The server address should now be the IP address of the server SpagoBI is running on.
  8. Add an entry to the hosts file, usually found in /etc, for the server that contains the PI_ODS database, for instance xxx.x.x.x Readsoft.Analytics.
  9. Move spagobi.dmp into the data pump directory on the same server on which PI_ODS was installed.
  10. Create the SpagoBI metadata database by using the oracle import utility to import spagobi.dmp: impdp d4/d4@<connection_string> DIRECTORY=DATA_PUMP_DIR DUMPFILE=spagobi.dmp SCHEMAS=spagobi
  11. Start SpagoBI by executing . SpagoBIStartup.sh in /SpagoBI-Server-4.1/bin as the spagobi user.

Mail Settings for Performance Analytics

  1. Log into Performance Analytics as the user biadmin
  2. Select Server Settings → Configuration management
  3. Click Select Category → MAIL
  4. You should now see the mail profiles used by SpagoBI. These mail profile attributes must be configured:
    • MAIL.PROFILES.scheduler.smtphost
    • MAIL.PROFILES.scheduler.smtpport
    • MAIL.PROFILES.scheduler.from
    • MAIL.PROFILES.scheduler.user
    • MAIL.PROFILES.scheduler.password
    • MAIL.PROFILES.user.smtphost
    • MAIL.PROFILES.user.smtpport
    • MAIL.PROFILES.user.from
    • MAIL.PROFILES.user.user
    • MAIL.PROFILES.user.password
    • MAIL.PROFILES.kpi_alarm.smtphost
    • MAIL.PROFILES.kpi_alarm.smtpport
    • MAIL.PROFILES.kpi_alarm.from
    • MAIL.PROFILES.kpi_alarm.user
    • MAIL.PROFILES.kpi_alarm.password
  5. Populate the smtphost and smtpport lines with the appropriate values.
  6. The scheduler.from and user.from must contain valid email addresses.
  7. It is recommended that a global email address, for instance spagobi_user, be created and used for this purpose.
  8. If the smtphost uses authentication:
    1. The scheduler.user and the user.user must contain the user id associated with the email address.
    2. scheduler.password and user.password must contain the password associated with the user account.
    3. If no authentication is used, leave these blank.

Installation Complete

At this point the installation is considered complete, and the administrator should create the users and assign them roles.

Data Recovery

In the event of a hardware or network failure, the Oracle E-Business Suite and PROCESSIT based tables may be reloaded by running the stored procedure pi_ods_refresh.refresh_control(p_start_updt) where p_start_updt represents a starting date/time prior to the failure. This procedure refreshes the PI_ODS database with EBS and PROCESSIT records that have a created or updated timestamp that falls between p_start_dt and the time the procedure was started.

Recovery may be effected on Oracle based INVOICES installations by running the stored procedure pi_ods_utilities.merge_into_pi_ods(p_start_updt, p_end_updt), where p_start_updt represents a starting date/time prior to the failure and p_end_updt represents the end date/time of the period to be refreshed. If p_end_updt is null then it will default to the time the procedure was started. This procedure refreshes the PI_ODS database INVOICEIT based tables with the records that have a created or updated timestamp that falls between p_start_updt and p_end_updt.

SQL Server based INVOICES installations utilize transactional replication to pass data to PI_ODS. In the event of a network failure or hardware issue on the PI_ODS server the transactions should be "held" until such time as the Publisher can push the data to the subscriber. If there is an issue in the INVOICES database that has resulted in data not being pushed to the subscriber it may be necessary to reinitialize the subscription. This can be done by:

  • Connecting to the Publisher in Management Studio and then expand the server node.
  • Expand the Replication folder and then expand the Local Publications folder.
  • Right-click the publication with subscriptions you want to reinitialize, and then click Reinitialize All Subscriptions.
  • In the Reinitialize Subscription(s) dialog box, select options, and then click Mark for Reinitialization.