Getting Started with SAP BW Request Housekeeping

[FrankRiesner]

Hi BW enthusiasts!
In this blog post, I would like to dive deeper into Housekeeping, an area sometimes a bit neglected in BW-systems. The focus here is actually on BW Request Housekeeping.

To start with, it is important to understand which kind of request management concepts are available and which one applies for your specific SAP BW environment:

1) The classic one: Request Status Management (RSSM) is the original concept from SAP NetWeaver BW and there in use for a long time already for all classic InfoProviders CUBE, ODSO, IOBJ and DEST.
The RequestID is based on a prefix and a following 25 characters long randomly generated unique identifier. Typical prefixes are REQU_* for InfoPackage requests, DTPR_* for DTP requests, and ODSR_* for ODSO activation requests.

2) The new one: Request Status and Process Management (RSPM) is the revised concept for ADSOs in SAP NetWeaver BW 7.50 on HANA. In SAP BW/4HANA RSPM is used for all InfoProviders (ADSO, IOBJ, DEST) and RSSM is decommissioned.
The Request ID is now called Request TSN (Transaction Sequence No), which is a timestamp and is derived from the ODQ Transaction ID.

As a first summary the focus of your individual BW request housekeeping concept is either on RSSM processes/tools or RSPM processes/tools. OR, in case you run BW on HANA, then often both approaches apply as classic objects and ADSOs often coexist side-by-side.

WHY bothering with BW request housekeeping?
In general, request meta information (all details your find in a request monitor like timestamps, runtime, package sizes, record amount, warnings etc) is stored in BW backend tables. These tables grow continuously based on the daily data processing activities. If housekeeping is not done, it will have a negative impact on the…

• overall system size,
• data management performance (loading, activation, delta determination etc.),
• waiting time for InfoProvider administration in Tr. RSA1/RSMNG, or BW/4HANA Cockpit, or
• preparation of a move to BW/4HANA or BW Bridge. Leveraging the SAP transfer tools (BW/4HANA inplace or remote approach / BW Bridge remote approach) requires the number or requests in classic InfoProviders to be reasonable (<1.000), see also warnings in your Readiness Check.

WHAT is done during BW request housekeeping?
Request Housekeeping deletes the request details (meta data) from the core runtime BW backend tables. This means, details of the request history will be removed which is older than a customer specific number of days. However, no need to be afraid: The real BW data is not deleted - master data and transactional data residing in the InfoProviders is NOT affected! Housekeeping only shrinks the request history behind them!
Also, good to know: During the housekeeping process, the objects are not locked, and they are available for loading/reporting without restrictions. Still, it is clear it is better to run the cleanup not during business hours.

As you see in the figure below, the housekeeping process removes the detail data from the original tables and moves it to housekeeping tables in a compressed form. If that is not enough (normally however this is not the case), you can even move all to an external archive (RSSM details here, for RSPM see SAP notes 2867347 and 2980697).

HOW to analyze the status quo?
RSSM: Tr. RSREQREDUCE provides the full list of InfoProviders with critical no of requests.
The same information is provided by table RSMDATASTATE_EXT.

RSPM: In SAP BW/4HANA, leverage the Cockpit apps for Request Management in ADSOs, InfoObjects or Open Hub Destinations and sort the column of 'Successful Requests' properly.

In SAP BW on HANA it is a little more challenging as these apps are missing. You can either use the new CDS-based technical content (aka "statistical analysis"). In Tr. RSRT following sources are available which both provide a key figure "No of successful requests" per ADSO:
2CRVCREQUEST/!2CRVCREQUEST or 2CRVCREQUEST/2CRVCREQQRY

The second BW on HANA option is to run following SQL statement (e.g. in Tr. DB02-SQL Editor) to get the ADSOs in most requests in descending order:
SELECT DATATARGET, COUNT(*) NUM_OF_REQUESTS
FROM RSPMREQUEST
GROUP BY DATATARGET
ORDER BY NUM_OF_REQUESTS DESC;

or as of SAP note 3487315 - How to find the datatarget that needs to be housekeeping:

SELECT DATATARGET, COUNT(PROCESS_TSN) AS Count_TSN
FROM RSPMXREF GROUP
BY DATATARGET ORDER BY Count_TSN DESC, DATATARGET DESC;

HOW to run BW request housekeeping?
RSSM Request Housekeeping can be triggered and scheduled from Tr. RSREQREDUCE based on the two reports RSSM_REDUCE_REQUESTLIST and RSSM_AUTODEL_REQU_MASTER_TEXT. Thorough documentation here.
For RSPM, SAP provides report RSPM_HOUSEKEEPING which can be scheduled via Process Chains (in BW 7.5 as ABAP variant, in BW/4HANA there is a new dedicated variant). Thorough documentation here.

WHICH Requests can be removed?
You can clean up a request if it is not available as physical entity. This means, it is not stored in an InfoProvider table anymore (e.g., in an ODSO/ADSO inbound queue to change log table).

1. To start with, all deleted requests can be removed from all InfoProviders.
2. In general, all requests can be removed from following InfoProviders: ODSO/ADSO (type direct update), IOBJ and DEST
3. Requests can also be removed if they meet following prerequisites:
   • ODSO/ADSO (type standard), CUBE/ADSO (type data mart)
     DTP loading requests can be cleaned from the inbound queue if they are activated / compressed already.
   • ODSO/ADSO (type standard):
     Change log requests can be cleaned up if they have been deleted from Change log. This is only possible if the delta has been provisioned to all connected data targets.
   • ADSO (type staging):
     Requests need to be deleted first before RSPM cleanup has an effect.

Finally, HOW to set the whole process up properly?

1. Define your individual housekeeping strategy. This means you have to make a decision how many days of request history should be kept in your BW landscape.
2. For RSPM: Depending on your SP-level, check whether some recommended SAP corrections need to be installed (see SAP note 3137171 and 3012005).
3. Test housekeeping on a few objects (e.g. 1 ODSO/ADSO, 1 IOBJ, 1 DEST).
4. Run housekeeping once for the whole DEV-system (e.g. with > 1000 days).
5. For RSPM: Transport SAP notes (2) to the next system.
6. Run housekeeping in tiers until you reach the defined limit of of days (1), depending on the age of the system, for example 1st run > 1500 days, 2nd one > 1000 days, then >800 days… In a large system each run could take a few hours.
7. When the defined limit (1) is reached: Schedule housekeeping, e.g. for RSPM in a weekly process chain like the following one:

Useful sources:

• RSSM Housekeeping https://help.sap.com/docs/SUPPORT_CONTENT/bwdabc/3564975514.html
  
• RSPM Housekeeping
  - SAP note 3137171 - Housekeeping for Request Administration tables (RSPM* tables)
  - SAP note 3012005 - RSPM Housekeeping: Collection of Notes
  - Help https://help.sap.com/docs/SUPPORT_CONTENT/bwdabc/3361383396.html 
  - BW/4HANA release notes