Tag Archives: performance

Technical Content – BI Statistics

Reference documents

Process Chain Statistics Extractor – 0TCT_IS21

0TCT_IS21  is a “Technical Content” extractor that will retrieve statistics from the Process Chainws execution logs. These logs are stored in the following SAP Tables:

  • RSPCPROCESSLOG
  • RSPCLOGCHAIN

The 0TCT_IS21 extractor is a FM based extractor calling the function module RSDDK_BIW_GET_DATA.

The FM is a ‘multi purpose’ function that is used by several TCT extractors. Based on the Data Source it will call the appropriate data extraction subroutine; the one for 0TCT_IS21 is  “PERFORM get_fact_ds21”.

Analysing the  extractor in my system ( I am working on SAP BW 7.30 -SAPKW73008), I noticed the following errors in the extractor itself and in the Transfer Rules / Update Rules to cube 0TCT_C21:

  • Inconsistent conversion of start / end timestamps to start / end date and times
  • Calculation of the overall duration of the process chain is incorrect
  • Calculation of the number of steps in is incorrect

 

Inconsistent conversion of start / end timestamps to start / end date and times

The PC Log Tables provide start and end timestamps. The transfer rule from Data Source 0TCT_DS21 to Infocube 0TCT_C21 derives the start and end time via abap conversion routines. The proble is that start time is converted to the local time zone while end time is converted to UTC.

Code of the start time routine:

FORM COMPUTE_TCTSTRTTIM
  USING    RECORD_NO LIKE SY-TABIX
           TRAN_STRUCTURE TYPE TRANSFER_STRUCTURE
           G_S_MINFO TYPE RSSM_S_MINFO
  CHANGING RESULT TYPE /BI0/OITCTSTRTTIM
           G_T_ERRORLOG TYPE rssm_t_errorlog_int
           RETURNCODE LIKE SY-SUBRC
           ABORT LIKE SY-SUBRC. "set ABORT <> 0 to cancel datapackage
*$*$ begin of routine - insert your code only below this line        *-*
* DATA: l_s_errorlog TYPE rssm_s_errorlog_int.
  DATA: l_start_time TYPE t.

  CONVERT TIME STAMP TRAN_STRUCTURE-starttimestamp TIME ZONE sy-zonlo
          INTO TIME l_start_time.

  RESULT = l_start_time.
* returncode <> 0 means skip this record
  RETURNCODE = 0.
* abort <> 0 means skip whole data package !!!
  ABORT = 0.
*$*$ end of routine - insert your code only before this line         *-*
ENDFORM.

Code of the end time routine:

FORM COMPUTE_TCTENDTIM
  USING    RECORD_NO LIKE SY-TABIX
           TRAN_STRUCTURE TYPE TRANSFER_STRUCTURE
           G_S_MINFO TYPE RSSM_S_MINFO
  CHANGING RESULT TYPE /BI0/OITCTENDTIM
           G_T_ERRORLOG TYPE rssm_t_errorlog_int
           RETURNCODE LIKE SY-SUBRC
           ABORT LIKE SY-SUBRC. "set ABORT <> 0 to cancel datapackage
*$*$ begin of routine - insert your code only below this line        *-*
* DATA: l_s_errorlog TYPE rssm_s_errorlog_int.
  DATA: l_end_time TYPE t,
        l_tzone TYPE sy-zonlo.

  l_tzone = 'UTC'.  " This needs to be changed to sy-zonlo.
  CONVERT TIME STAMP TRAN_STRUCTURE-endtimestamp TIME ZONE l_tzone
          INTO TIME l_end_time.

  RESULT = l_end_time.
* returncode <> 0 means skip this record
  RETURNCODE = 0.
* abort <> 0 means skip whole data package !!!
  ABORT = 0.
*$*$ end of routine - insert your code only before this line         *-*
ENDFORM.

The transfer rule routines converts the timestamps in similar ways for Start Time (0TCTSTRTTIM) , End Time (0TCTENDTIM), Start Time as Key Figure (0TCTSTIMEK) and Time (0TIME) and also for the dates 0CALDAY, 0TCTSTRTDAT, 0TCTENDDAT.

So if you want to have consistency between all times and dates, you should check all the routines for the conversions of the following elements and replace ‘UTC’ by sy-zonlo on each of them (7 routines in total).

Calculation of the number of steps in is incorrect

The characteristic 0TCTSTAUIK (“Frequency”) counts the number of occurrences of steps in process chains. For individual records inthe 0TCT_C21 cube it should always be 1. For the record summarizing the PC execution (the one with 0TCTPRCSTYP = “#’), it should be the sum of the steps in the process chain. However the 0TCT_DS21 extractor performs the calculation incorrectly and returns a negative number equal to 1 minus the number of steps.

There could be several ways to correct this (apart from asking SAP to do the correction…) One way is by creating an user exit that recalculates the field in the extractor, or by correcting the value in the start routine of the update rule.

Calculation of the overall duration of the process chain is incorrect

Same as above. Characteristic 0TCTDURATION is incorrect for the record summarizing the whole PC execution.  Same solutions as above.

Line Item dimensions and High Cardinality

Flagging a dimension as line item dimension changes the data modell by that it removes the intermediate SID table that is normally put between fact tables and the actual dimension tables (for flexibility reasons) and joins the fact table directly with the dimension table.

It changes this :

[DIMENSION-TAB1] >-----<  (SID-TAB1) >---< [[FACT TABLE]] >---<  (SID-TAB2) >-----< [DIMENSION-TAB2]

to this:

 [DIMENSION-TAB1] >-----< [[FACT TABLE]]  >---<  (SID-TAB2) >-----< [DIMENSION-TAB2]

 

 

References:

 

BW OLAP Cache Mode 5 – “BLOB/Cluster Enhanced” – Is the recommended cache mode

With Support Package 16, a new cache mode with an optimized architecture was delivered for BW 7.0. This mode explicitly addresses the basic problems that were connected with the existing cache modes 1-4 up to now. In particular, these include cache entries that have been lost or were not assigned and the blocks due to enqueue locks that occur under a high load.
The new mode is managed in BW 7.0x as “BLOB/Cluster Enhanced” under number 5, or in higher versions of BW, with the same number as “Query Aggregate”.
Performance is usually noticeably better for mode 5, and is therefore recommended as the standard for most applications.

See related note – Note 1026944 – New cache mode with no directory for BI 7.0

See help on OLAP Cache Monitor – http://help.sap.com/saphelp_nw70/helpdata/en/41/b987eb1443534ba78a793f4beed9d5/content.htm

See help on  BW Cahce Mode – http://help.sap.com/saphelp_nw70/helpdata/en/d9/31363dc992752de10000000a114084/content.htm

Configuration and Utilization of the  OLAP Cache to Improve the Query Response Time  http://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/c0ebfba1-5a1a-2d10-82b5-fdddc1e5955c?quicklink=index&overridelayout=true

Get size of database table via ABAP

Check program Check program RSTABLESIZE
or following code.

Update for BW74: FM ‘EM_GET_NUMBER_OF_ENTRIES’ import parameter has changed. see new code below.

Code up to BW 73

REPORT ZTEST_ILESH.

TABLES : DD02L.

SELECT-OPTIONS : S_TABLES FOR DD02L-TABNAME.

DATA : GT_TABROWS LIKE STANDARD TABLE OF DDCDIM,
       WA_TABROWS LIKE DDCDIM.

SELECT TABNAME
  FROM DD02L
  INTO CORRESPONDING FIELDS OF TABLE GT_TABROWS
  WHERE TABNAME IN S_TABLES.

CALL FUNCTION 'EM_GET_NUMBER_OF_ENTRIES'
  TABLES
    IT_TABLES = GT_TABROWS.

LOOP AT GT_TABROWS INTO WA_TABROWS.
  WRITE : / WA_TABROWS-TABNAME , WA_TABROWS-TABROWS.
ENDLOOP.

Code from BW74

Download – PROG_ZBC_CHECK_SYSTEM_TABLES_74.slnk] – 1.3 kB – 269   downloads

REPORT  ZBC_CHECK_SYSTEM_TABLES.

TABLES : DD02L.

SELECT-OPTIONS : S_TABLES FOR DD02L-TABNAME.

DATA : GT_TABROWS LIKE STANDARD TABLE OF DDCDIM,
       WA_TABROWS LIKE DDCDIM.

SELECT TABNAME
  FROM DD02L
  INTO CORRESPONDING FIELDS OF TABLE GT_TABROWS
  WHERE TABNAME IN S_TABLES.

CALL FUNCTION 'EM_GET_NUMBER_OF_ENTRIES'
  TABLES
    IT_TABLES = GT_TABROWS.

LOOP AT GT_TABROWS INTO WA_TABROWS.
  WRITE : / WA_TABROWS-TABNAME , WA_TABROWS-TABROWS.
ENDLOOP.

BI_DELETE_OLD_MSG_PARM_DTPTEMP – Cleanup of RSBATCHDATA

rsbatchdata

I am running SAP BW 7.3 (SAPKW73004). I recently noticed that the daily job BI_DELETE_OLD_MSG_PARM_DTPTEMP takes 60000 to 100000 seconds to complete.

Below is the result of the investigation on the issue.

——————–

SAP recommends that a daily job be run to delete old messages and parameters related to running DTPs.

This is part of a large number of recommendations to avoid having table sizes get out of hand (see note Note 706478 – Preventing Basis tables from increasing considerably, see also the list of tables of the note in excel format )
See also recent post in SCN with list of relevant notes.
Table RSBATCHDATA Too Large – How To Delete Data
This cleans up the content of table RSBATCHDATA, that otherwise grows indefinitely.

The BW sap help site recommends running daily with a deletion of messages older than 30 days.  http://help.sap.com/saphelp_nw04s/helpdata/en/48/d2a551003c04e9e10000000a42189c/content.htm

For this you need create a variant for the job to be run with the following parameters

DEL_MSG 30 – delete all records older than nnn day (M-Records)
DEL_PAR 30 – delete all records older than nnn day (R- and P-Records)
DEL_DTP ‘ ‘ – useless/no function (unused parameter as of SAPKW73004, you can see that in the)

(note that not entering parameters or entering 1 or 2 as DEL_MSG/DEL_PAR defaults to deleting records  older than 180 days).

Other terms

RSBATCH, batch manager, RSBATCH_DEL_MSG_PARM_DTPTEMP,
BI_DELETE_OLD_MSG_PARM_DTPTEMP

Optimization of BEx Workbooks and Queries performance

  1. Optimizing BEx Analyzer 7x Performance – NEW
  2. (PPT)
    Proven techniques to infuse more interactivity and formatting to your reports
  3. SCN Article – Understanding OLAP Processor and RSRT – http://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/a03de316-17a6-2e10-5ea8-f9c7d117df92?QuickLink=index&overridelayout=true
  4. List of Statistics Events – http://help.sap.com/saphelp_nw73ehp1/helpdata/en/45/f0488a1aa03115e10000000a1553f7/content.htm?frameset=/en/1b/55d979fd5ca54d96fdb605f22b677e/frameset.htm
  5. Summary of BI/BW 7.0 Performance Imrpovements – http://scn.sap.com/people/jens.gleichmann/blog/2010/10/12/summary-of-bibw-70-performance-improvements
  6. http://sapbimeterials.blogspot.be/2009/01/troubleshooting-bi-70-bex-analyzer.html

RS Trace Tool

  1. Execute Transaction RSTT
  2. Click on Trace Tool  => User Activation => Activate your User
  3. Log on to the BEx Analyzer and perform the actions that you want to trace.
  4. Click on Trace Tool  => User Activation => Deactivate your User
  5. Click on Traces in the left hand navigation, click on the newest trace, which should be at the top of the trace history list, and then click the Display button.

ST13

A documentation on ST13 is available in PPT format with annotations here:  https://sapmats-de.sap-ag.de/download/download.cgi?id=6XQPY53FML9PPUEUAY6TGU0U6Q66NWTJEJ9IPEBDULMTJ2W49G (see note 1035990)

  1. Execute Transaction ST13
  2. Select BIIPTOOLS
  3. Select BW Statistics Analysis
  4. Select BW Event List

Additional info

Note 1056259 – Collective note: Performance of BI planning (general principles + notes applying to 7.0 7.1)

Note 1101143 – Collective note: BEx Analyzer performance

Note 1141850 – “Not assigned” time calculated from query RUNTIME

Tables containing Workbook in BW

RSRWBINDEX List of binary large objects (Excel workbooks)
RSRWBINDEXT Titles of binary objects (Excel workbooks)
RSRWBSTORE Storage for binary large objects (Excel workbooks)
RSRWBTEMPLATE Assignment of Excel workbooks as personal templates
RSRWORKBOOK ‘Where-used list’ for reports in workbooks