SAP Commissions: Pipeline Runtime Summary

Dear All,

This article will help you to evaluate Pipeline Performance Improvement with a runtime summary.

Calculation Stages Overview Documentation

/********************************************************************

Use this SQL Query to get a pipeline summary.
This SQL query will get the pipeline summary runtime for pipelinerunseq input

`Yogananda Muthaiah

*********************************************************************/

--- Oracle ----

SELECT PE.NAME PERIOD_NAME,PU.NAME PU,ST.NAME STAGE,ST.DESCRIPTION,SS.STARTTIME,SS.STOPTIME,TRUNC((SS.STOPTIME-SS.STARTTIME)*24*60) DIFF_IN_MINS,
                SS.STATUS,NUMERRORS,NUMWARNINGS
                 FROM CS_PLRUN PR, CS_STAGESUMMARY SS, CS_STAGETYPE ST,CS_PROCESSINGUNIT PU,CS_PERIOD PE
                WHERE     PR.PIPELINERUNSEQ = SS.PIPELINERUNSEQ
                      AND SS.STAGETYPESEQ = ST.STAGETYPESEQ
                      AND PR.PROCESSINGUNITSEQ=PU.PROCESSINGUNITSEQ
                      AND PE.REMOVEDATE>SYSDATE
                      AND PE.PERIODSEQ=PR.PERIODSEQ
                      AND PR.PIPELINERUNSEQ= XXXXXXXXXXXXXX  ---- Pipelinerunseq
                      ORDER BY SS.STARTTIME ASC


---- HANA ----

SELECT PE.NAME PERIOD_NAME,PU.NAME PU,ST.NAME STAGE,ST.DESCRIPTION,SS.STARTTIME,SS.STOPTIME,SECONDS_BETWEEN(SS.STOPTIME,SS.STARTTIME) / 60 DIFF_IN_MINS,
                SS.STATUS,NUMERRORS,NUMWARNINGS
                 FROM CS_PLRUN PR, CS_STAGESUMMARY SS, CS_STAGETYPE ST,CS_PROCESSINGUNIT PU,CS_PERIOD PE
                WHERE     PR.PIPELINERUNSEQ = SS.PIPELINERUNSEQ
                      AND SS.STAGETYPESEQ = ST.STAGETYPESEQ
                      AND PR.PROCESSINGUNITSEQ=PU.PROCESSINGUNITSEQ
                      AND PE.REMOVEDATE>TO_DATE('2200-01-01', 'YYYY-MM-DD')
                      AND PE.PERIODSEQ=PR.PERIODSEQ
                      AND PR.PIPELINERUNSEQ= 20547673299878223  ---- Pipelinerunseq
                      ORDER BY SS.STARTTIME ASC​

Analyze for Performance Tuning

From the above SQL statement, will help to analyze pipeline runtime for Performance tunning.

• Perform a multiple performance test run, and take note of the pipeline runtime achieved as well as the actual runtime used to be and with parallel runs.
• It helps to identify Long-running stage with a runtime start time
• Number of errors and warning count will help users to fix