Running nVision in a Mixed OpenXML/Excel mode

This blog post follows on from a series that discusses how to get optimal performance from PeopleSoft nVision reporting as used in General Ledger.

Updated 20.4.2021: OpenXML is an alternative method for running PeopleSoft nVision introduced in PeopleTools 8.54.  It is recommended by Oracle and is the default configuration in the process scheduler.  It generally outperforms Excel because it is a lightweight process and it evades Excel's concurrency limitations.

Only one Excel nVision will run concurrently on a Windows server because Excel itself is a single-threaded process since Microsoft Excel 2010.  If you need to run 10 concurrent nVision reports you would need 10 windows servers each running a process scheduler that will run a single nVision report in Excel mode.  One of the major advantages of OpenXML is that it is not subject to this restriction.  See also:

• Support Note: E-NV: nVision Performance using Microsoft Office 2010 and above (Doc ID 1537491.1)
• Oracle's PeopleSoft Technology Blog: OpenXML Mode in Peoplesoft nVision Looks Hard, but it Isn't!
• PeopleSoft DBA Blog: nVisionPerformance Tuning: 11. Excel -v- OpenXML

What is the Problem?

I have worked with PeopleSoft Financials customers where it became apparent that some of their nVision layouts did not run correctly on OpenXML, and until they could be redeveloped they had to run on Excel.  The problems seem to be around pagination and nPlosion.  Also, some variables are not populated when the layouts are run in OpenXML.  For example, the report on the left was run in OpenXML, and the one on the right was run in Excel.

I have also found a very few layouts, that contrary to expectation, perform better on Excel than OpenXML.

Reverting to Excel is the only other option, but to do so across the board would have a significant performance impact.  However, my experience suggests that customers can run the majority of their layouts perfectly successfully on OpenXML, but a relatively small proportion still has to be run on Excel.  

A mixed mode of operation is therefore a good option, where nVision is run on OpenXML by default, but certain reports and report books are run on Excel.  It avoids having to train end-users to run certain reports on certain process schedulers.  

This blog sets out a method by which reports using certain nVision layouts are automatically redirected to certain process schedulers that run Excel nVision by a combination of configuring process categories and two database triggers to adjust the request records before they are picked up by the process schedulers.

Excel nVision Process Schedulers and OpenXML nVision process schedulers

Whether nVision is run in Excel or OpenXML mode is determined by the UseExcelAutomation variable in the nVision section of the process scheduler configuration file (psprcs.cfg).  It applies to all nVision process run by that scheduler.  However, this variable is not documented well in the PeopleTools manual.  It takes the following values:

• 0 = Open XML Mode (see Oracle Support Note 1317246.1)
• 1 = Excel Mode  
• 2 = Cross-Platform OpenXML (see Oracle Support Note 1304907.1)

…
[nVision]
;=========================================================================
; General settings for nVision
;=========================================================================
…
UseExcelAutomation=2
…

Today, most PeopleSoft systems run most of their batch processes on Linux/Unix servers.  nVision is one of the few PeopleSoft process types that had to run on Windows, and that is still the case for Excel nVision.  It is typical for these PeopleSoft systems to have several Windows servers dedicated to nVision execution.  To run in a mixed-mode, it is necessary to have an Excel nVision process scheduler and an OpenXML nVision process scheduler on each server.

Process Categories 

In the past, I have suggested creating additional process types, but I think it is easier and more in keeping with the process scheduler structure to create additional process definitions and process categories.  It is necessary to create two new process categories (in PeopleTools->Process  Scheduler->System Settings)

Servers

Process Schedulers (or Servers) should be created in pairs for each physical server available.  In this example, I have created PSNT_X1 to run the OpenXML category and PSNT_E1 to run the Excel category.  

Note that:

• Maximum concurrency for the Excel server has been reduced to 1.
• The maximum number of API aware processes for PSNT_E1 has also be reduced to 1.
• The 'Default' category is disabled on both PSNT_E1 and PSNT_X1.
• These servers only run nVision and PSJob process types,
• They load balance within the same operating system (although this is not appropriate for PSNT_X1 if you also run OpenXML on other non-Windows process schedulers).

Process Definitions

The various nVision process definitions are allocated to the nVisionOpenXML process category so they run on those process schedulers.

The whole configuration can be set up manually through the Process Scheduler components. However, I have also written a couple of scripts to manage the configuration that should ensure consistency.

• nvisionprcsdefnclone.sql updates process definitions and creates the cloned server definitions.  It can also be configured to create multiple pairs of server definitions etc.
• nvision_prcscategory.sql updates the concurrency on existing process definitions.

Scripts

When a process request is submitted either in the PeopleSoft application or an enterprise scheduling tool (such as AutoSYS or Control-M), request records are inserted into:

• PSPRCSQUE is polled by the process schedulers when looking for work.
• PSPRCSRQST reports the status of processes and is exposed in the Process Monitor component in PeopleSoft.

These two tables are siblings, both keyed on PRCSINSTANCE.  Both have the following columns:

• PRCSNAME determines the name of the process to be run.
• PRCSCATEGORY specifies the category of the process in the request.

Triggers

I have created two pairs of almost identical database triggers that fire on insert into each of these tables that will make certain changes to request for nVision layouts that should be run on Excel:

• One pair of triggers updates requests for nVision-Report and nVisionReportBook process types.  The other pair update the PSJob request record where nVision reports are run in a Jobset.
• The triggers change the process category from nVisionOpenXML to nVisionExcel where necessary.  It is changing the process category rather than the name of the process that makes the difference to which process scheduler picks up the request.
• If the process request specifies a particular process scheduler (specified in SERVERNAMERQST) that cannot run the updated process category, then the requested server name is blanked out so that the master process scheduler reassigns the request.

If any one layout in a ReportBook is listed as requiring to be run on Excel, then the whole ReportBook is moved to Excel because the report book runs as a single process.  This may become a reason to split ReportBooks where only some layouts require to run on Excel.

Script gfc_nvsion_excel_redirect_triggers.sql creates the meta-data table PS_NVS_REDIR_EXCEL, and the triggers on PSPRCSRQST and PSPRCSQUE.

• The corresponding triggers on PSPRCSRQST and PSPRCSQUE are almost identical except for the name of the table and one additional column that is updated on PSPRCSQUE (apart from the conditionally compiled debug code).  I will only exhibit the trigger on PSPRCSRQST in this post.
• Normally, one would not code WHEN OTHERS THEN NULL exceptions handlers in PL/SQL.  However, I do this on triggers on process scheduler tables to prevent any error being raised causing the scheduler to crash.
• Conditionally compiled debug code in the trigger is not compiled if the mydebug flag is set to FALSE.

ALTER SESSION SET PLSQL_CCFLAGS = 'mydebug:FALSE';

CREATE OR REPLACE TRIGGER sysadm.gfc_nvision_excel_redirect_rqst 
BEFORE INSERT ON s.psprcsrqst
FOR EACH ROW
WHEN (new.prcstype IN('nVision-Report','nVision-ReportBook'))
DECLARE
  l_excel INTEGER := 0;
  l_maxconcurrent INTEGER := 0;
  k_prcscategory CONSTANT VARCHAR2(15) := 'nVisionExcel';
BEGIN
  $IF $$mydebug $THEN dbms_output.put_line('Entering Trigger psoft.gfc_nvision_excel_redirect_rqst'); $END

  IF :new.prcstype = 'nVision-ReportBook' THEN
    --check for reportbook running report that uses layout on Excel list
    SELECT 1
    INTO   l_excel
    FROM   psnvsbookrequst b
    ,      ps_nvs_report n
    ,      ps_nvs_redir_excel e
    WHERE  b.oprid = :new.oprid
    AND    b.run_cntl_id = :new.runcntlid
    AND    b.eff_status = 'A'
    AND    n.business_unit = b.business_unit
    AND    n.report_id = b.report_id
    AND    n.layout_id = e.layout_id
    AND    e.eff_status = 'A'
    AND    rownum=1;
  ELSE
    --look in command line for report running layout on Excel list
    SELECT 1
    INTO   l_excel
    FROM   psprcsparms p
    ,      ps_nvs_report n
    ,      ps_nvs_redir_excel e
    WHERE  p.prcsinstance = :new.prcsinstance
    AND    n.report_id = substr(regexp_substr(p.parmlist,'-NRN[^ ]+'),5)
    AND    n.layout_id = e.layout_id
    AND    e.eff_status = 'A'
    AND    rownum=1;
  END IF;

--set category of request
  :new.prcscategory := k_prcscategory;

  --get max concurrency of new category on new server
  SELECT maxconcurrent
  INTO   l_maxconcurrent
  FROM   ps_servercategory
  WHERE  prcscategory = :new.prcscategory
  AND    servername = :new.servernamerqst;

  --if request assigned to server where it cannot run blank out server assignment and allow load balancing to determine it
  IF l_maxconcurrent = 0 THEN
    :new.servernamerqst := ' ';
  END IF;

  $IF $$mydebug $THEN dbms_output.put_line('set process name to '||:new.prcsname); $END
EXCEPTION
  WHEN no_data_found THEN 
    $IF $$mydebug $THEN dbms_output.put_line('No excel redirect found'); $ELSE NULL; $END
  WHEN others THEN 
    $IF $$mydebug $THEN dbms_output.put_line('Other Error'); $ELSE NULL;  $END
END gfc_nvision_excel_redirect_rqst;
/
show errors

MetaData

The script gfc_nvsion_excel_redirect_metadata.sql populates a list of nVision layouts that must run on Excel into the metadata table PS_NVS_REDIR_EXCEL. You have to enter your own list of nVision layouts here.

REM gfc_nvsion_excel_redirect_metadata.sql
REM (c)Go-Faster Consultancy 2021
REM load metadata of layouts that have to run on Excel rather than OpenXML

spool gfc_nvsion_excel_redirect_metadata

INSERT INTO ps_nvs_redir_excel VALUES ('EXCELNVS','A');
commit;

spool off

Other Scripts

• excel_only_reportbooks.sql determines which nVision ReportBooks contain only some layouts that require to be run on nVision.  These are candidates to be split up.

                                                              ReportBooks with both Excel and OpenXML nVision layouts

                                                   Number of
                                              All      Excel
OPRID      RUN_CNTL_ID                    Layouts    Layouts Excel Layouts                  OpenXML Layouts
---------- ------------------------------ ------- ---------- ------------------------------ ----------------------------------------------------------------------
BATCH      ADHOC_NVISION                        8          1 GLXXXO21                       GLXYZD03, GLXXXO03, GLXXXO05, GLXXXO22, GLXXXO23, GLXXXO31, GLXXXO32
BATCH      ADHOC_09062016                       3          1 ZYXVIS14                       ZYXVBS14, ZYXVIS12
BATCH      GLXXX_GLXXXO02_ABDC_YYY              2          1 GLXXXO02                       GLXXXO28
BATCH      GLXXX_GLXXXO21_ABDC_YYY              3          2 GLXXXO21, GLXXXO98             GLXXXO71
BATCH      GLXXX_ZYXB4080_M000_ZZZ             10          2 ZYXVBS64, ZYXVIS14             ZYXVBS04, ZYXVBS14, ZYXVBS14_LED, ZYXVBS16, ZYXVBS16_LED, ZYXVBS54, ZB
                                                                                            UVIS04, ZYXVIS16

nvision_processsmonitor.sql reports on nVision ReportBooks scheduled in the last 24 hours, their process category and the excel layouts within them, so that you can verify that the configuration is working.

                                                       Process         Server   Server   Server
PRCSINSTANCE RUNDTTM                      PRCSNAME     Category        Request  Run      Assign   RUNCNTLID                      STA EXCEL_LAYOUT_IDS
------------ ---------------------------- ------------ --------------- -------- -------- -------- ------------------------------ --- ------------------------------
    12345680 31-MAR-21 07.42.51.000000 AM RPTBOOKE     nVisionExcel             PSNT_E1  PSNT_E1  GLXXX_GLXXXO10_ABDC_YYY        OK  GLXXXO10
    12345681 31-MAR-21 07.43.25.000000 AM RPTBOOKE     nVisionExcel             PSNT_E1  PSNT_E1  GLXXX_GLXXXO21_ABDC_YYY        OK  GLXXXO21, GLXXXO98
    12345683 31-MAR-21 08.06.42.000000 AM RPTBOOKE     nVisionExcel             PSNT_E1  PSNT_E1  GLXXX_GLXXXO98_ADHOC           OK  GLXXXO98
    12345684 31-MAR-21 08.32.12.000000 AM RPTBOOKE     nVisionExcel             PSNT_E1  PSNT_E1  GLXXX_GLXXXO21_ABDC_YYY        OK  GLXXXO21, GLXXXO98
    12345685 31-MAR-21 09.18.23.000000 AM FBRPTBK      nVisionOpenXML           PSNT_X1  PSNT_X1  GLXXX_GLXYAD06_ABDC_YYY        OK
    12345686 31-MAR-21 09.20.01.000000 AM RPTBOOK      nVisionOpenXML           PSNT_X1  PSNT_X1  GLXXX_GLXYBP00_ABDC_YYY        OK
    12345687 31-MAR-21 09.22.21.000000 AM RPTBOOK      nVisionOpenXML           PSNT_X1  PSNT_X1  GLXXX_GLXYAD03_ABDC_YYY        OK
    12345688 31-MAR-21 09.23.11.000000 AM RPTBOOK      nVisionOpenXML           PSNT_X1  PSNT_X1  GLXXX_GLVLAD03_ABDC_XXX        OK
    12345689 31-MAR-21 09.24.18.000000 AM RPTBOOK      nVisionOpenXML           PSNT_X1  PSNT_X1  GLXXX_ZYXAB001_M000_ZZZ        OK

All the scripts mentioned in this blog have been included in my nVision Github repository.