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
…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 errorsMetaData
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 offOther 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 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 OKAll the scripts mentioned in this blog have been included in my nVision Github repository.
@go-faster.co.uk on BlueSky
Posts
