## 3.9 Troubleshooting BW on HANA
This section identifies some of the common problem areas for users of Business Warehouse on HANA and points to existing information sources which will help to resolve the issues.
## Documentation
All public documentation related to BW on HANA can be found in the SAP Help Portal on the SAP Business Warehouse product page. In the SAP Community Network (SCN) you can find a complete end-to-end
collection of detailed BW on HANA related information including presentations, blogs, discussions, how-to guides, release information, videos, roadmap and so on. An internal WIKI page is also available with additional information on BW on HANA troubleshooting:
- SAP Help Portal: SAP Business Warehouse, powered by SAP HANA.
- SAP Support Community: SAP Business Warehouse on SAP HANA and HANA SQL-based Data Warehousing.
- WIKI: BW on HANA Documentation and Troubleshooting
This chapter refers to the transaction codes which are used to access each BW function, such as RSRT for the Report Monitor and RSRV for Analysis and Repair of BW Objects. RSA1 is the Data Warehousing Workbench which is the central cockpit used for the administration of almost the entire BW system. A list of the most commonly-used transaction codes is given in a table at the end of this chapter: Frequently-used Transaction Codes.
## Table Redistribution
BW on HANA Table Redistribution ensures that the tables of a BW system are partitioned correctly according to the SAP recommendations and, in the case of a scale-out system, are defined on the correct database nodes. This is important for the optimal performance of the system.
Refer to the following for details of how to configure table redistribution:
- SAP Note 1908075 - BW on SAP HANA: Table placement and landscape redistribution.
- KBA: 2517621 - After landscape redistribution data does not appear evenly distributed between the slave nodes in terms of memory usage per a HANA node.
- Guided answer How to carry out preparations for a table redistribution for BW with a step-by-step description of how to configure the table redistribution.
If the table placement and table redistribution are done correctly there should be no requirement to make manual changes to the partitioning for BW tables. The expected partitioning for BW objects and associated tables is described here:
- KBA 2044468 -FAQ: SAP HANA Partitioning (question 17: How should tables be partitioned in BW environments?)
- SAP Note 2019973 -Handling Very Large Data Volumes in SAP BW on SAP HANA
## Two Billion Record Limit
##  Tip
It is not possible to partition BW master data SID tables. Irrespective of the underlying database, SID tables of master data InfoObjects already have the upper limit value of 2 billion records imposed by the datatype of the SIDs (INT4). A detailed explanation on this topic and what can be done to avoid this limitation is available in the SAP Note 1331403 -SIDs, Number Ranges and BW InfoObjects .
It is possible to proactively monitor the 2 billion table or partition limit on HANA using the SAP EarlyWatch Alert Workspace in the SAP ONE Support Launchpad. Using this application, you get an overview of which tables are approaching the limit including a prediction of when the limit will be reached, broken down into a mean,
best-case and worst-case scenario. By default, the forecast is based on all previous measurements. You can find details in the following blog post:
- The New 2 Billion Record Limit in the SAP EarlyWatch Alert Workspace .
## Checking Consistency with RSDU_TABLE_CONSISTENCY
For BW on HANA systems you can use the ABAP report RSDU_TABLE_CONSISTENCY to check that the table placement is correct and that the BW objects are located on the correct HANA nodes. A user document for the report is attached to the SAP Note 1937062 - Usage of RSDU_TABLE_CONSISTENCY.
A practical example of where this report can be useful is around DSO activation performance issues. If the DSO activation is in a distributed HANA environment and it takes longer than expected, you should check if the partitioning of DSO tables (Active Data, Activation Queue and Change Log tables) is consistent, that is, same partitioning and same hosts for the related partitions.
The RSDU_TABLE_CONSISTENCY report is only available in BW on HANA systems and not in the nextgeneration data warehouse product BW/4HANA where most of the checks made by the report are obsolete. Some checks made by the report that are still required will move to transaction RSRV - see the following subsection and also Analysis and Repair Environment in the NetWeaver section of the SAP Help Portal.
## Consistency Checks with RSRV Transaction
RSRV consistency checks (Analysis and Repair Environment) are part of the BW application. In the analysis and repair environment you can perform consistency checks on the data and metadata stored in a BW system. The main purpose of this is to test the foreign key relationships between the individual tables of the enhanced star schema of the BW system. These checks can be useful to solve problems in BW on HANA environments if you are getting unexpected data results. BW application inconsistencies can also cause errors when converting standard InfoCubes to SAP HANA-optimized InfoCubes, the RSRV checks can be used to find and resolve these inconsistencies. See SAP NetWeaver modeling documentation: Converting Standard InfoCubes to SAP HANA-Optimized InfoCubes.
## Delta Merge
The implementation and operation of a SAP BW on HANA system requires a solid understanding of the delta merge process in the database. Understanding this will allow administrators to optimize data loading and can in some cases help improve the performance of BW on HANA queries. Delta merge processing can consume a significant amount of system resources especially for large tables and therefore needs to be understood to manage any system powered by SAP HANA. For more information please see:
- For details of delta merge configuration for BW: Triggering a Delta Merge (SAP BW/4HANA).
- For information on troubleshooting delta merge: KBA 2057046 - FAQ: SAP HANA Delta Merges
## Query Execution Modes (RSRT)
For query execution BW always checks if optimized operations can be pushed down to the HANA database, the four available execution modes can be seen in RSRT from the query properties:
In some cases, especially where there is no support for push down of BW functionality to HANA Database or where you get an error or have a performance problem with HANA operation modes (TREXOPS=6 or TREXOPS=7 or TREXOPS=8) it can make sense to use operation mode 0 (No optimized operations in SAP HANA/no BWA) at least as a workaround to execute the query.
Not all BW queries will benefit from the HANA operation modes that push down BW functionality to the HANA Database. The benefit depends on the exact query definition and the data in the providers. Therefore, where the runtime of the query is critical it is necessary to test the impact of this feature on the performance of every single query and then choose the proper query operation mode.
For more information please see:
- For more details on the operation modes: NetWeaver Generic Tools and Services documentation: Operations in SAP HANA / BWA
- For more detail on the process logic, the SAP Community Wiki: Query Property: Operations in HANA BWA (TREXOPS) .
- For a list of the functionality that is currently pushed down to HANA, refer to the SAP Note 2063449 -Push down of BW OLAP functionalities to SAP HANA.
- For information on testing the benefits of pushdown: 2365030 - Bex Query slower with HANA pushdown.
## Performance Issues
There is often the expectation after a BW on HANA migration from another database that all BW queries should be much faster. Of course, if the query was slow already before database migration to SAP HANA due to some calculations happening on the BW application server and these calculations cannot be pushed down to the HANA database then HANA cannot help in these cases; a redesign of the BW query will be necessary.
The query run time component that we can expect to see significantly improved with SAP HANA is the database time, this is the Event ID 9000 (Data Manager Time) in the BW Query run time statistics. What we can possibly improve with HANA, apart from the database time, is the time spent in the Analytic Manager. The Analytic Manager in BW (previously known as the OLAP engine) is the brain of BW when it comes to BW query processing, so it is responsible for navigation, filtering and aggregation among other things in the query execution. For additional information here please refer to the following KBA:
- 2122535 -How to determine if a BW on HANA query performance problem is HANA database related .
Before starting performance troubleshooting on HANA we first need to identify if there is a general performance problem for the BW on HANA system or if the problem is just for one BW query or report. For information on the steps to verify this, troubleshoot the issue and collect the required logs and traces refer to the HANA WIKI:
- Troubleshooting HANA Performance issues .
## Plan Viz Trace Generated in RSRT
RSRT can be used to check Business Explorer (BEx) queries. The benefit of using RSRT to check a BW query is that it is independent of the client frontend, so you can eliminate the client frontend tool (Bex, BI frontend tool etc) from the problem analysis. If the issue can be reproduced in RSRT then the issue is not related to the client frontend.
Transaction RSRT provides several debug features that are helpful when analyzing a BW query. With BW on HANA, queries are usually executed on the HANA database using stored procedure TrexViaDbsl or TrexViaDbslWithParameter. The call of procedure TrexViaDbsl(withParameter) can be displayed in RSRT if the Debug flag Python Trace or Plan/Performance Trace is set on BW740 systems or Generate Plan Viz File on BW750 systems. You can copy this call and use it in SAP HANA Studio to execute Plan Viz to check the execution plan.
Prerequisites: at least BW 740 SP13 and HANA 1.0 SPS 11
Refer to the Wiki as follows:
- For basic information on transaction RSRT: Query Monitor .
- For the detailed steps on how to do this: BWonHana: PlanViz Trace generated in RSRT .
It is often the case that performance issues for individual BW queries can be resolved with hints. For details of hints refer to the KBA 2142945 - FAQ: SAP HANA Hints:
- Question 4. What important hints are related to SAP HANA?
- Question 3 How can hints be specified? (describes how the hints can be set on the BW application level).
## Composite Provider
One of the most used InfoProviders for BW on HANA reporting is the composite provider which can be created using the BW Modeling tools. These tools provide a new development environment for editing BW metadata objects. The development environment is based on the SAP HANA Studio. For more information refer to the following blog:
- SAP BW Modeling tools: Three things to know when using the BW modeling tools in Eclipse
A Composite Provider is an InfoProvider in which you can combine data from BW InfoProviders such as InfoObjects, DataStore Objects, SPOs and InfoCubes, or SAP HANA views such as Analytical or Calculation Views using join or union operations to make the data available for reporting.
To avoid performance issues with BW queries created on the composite provider, especially related to the use of joins or navigation attributes, it is important that the composite provider is modeled based on the information in the following SAP Notes:
- 2271658 - Design Considerations for Composite Provider
- 2103032 - Long runtimes in query on Composite Provider
## Advanced DSO
The end goal for the advanced DSO (ADSO) is to be the central persistency object in BW-on-HANA replacing the InfoCube, classic DSO, HybridProvider and PSA. While there are still some gaps to cover the complete functionality, we recommend considering the advanced DSO for all new projects as the central (and only) persistency object.
Additional information on the ADSO can be found in the following:
- SCN blog post The "advanced' DataStoreObject - renovating BW's persistency layer
- SAP Note 2070577 - (advanced) DataStore Object - availability in BW7.4 SP08, SP09 and SP10
To avoid data loading or reporting performance problems related to ADSO please implement the recommendations and corrections from the following SAP Notes:
- 2185212 - ADSO: Recommendations and restrictions regarding reporting
- 2684950 - 750SP14: Performance problems during access of DTP with source as ADSO
- 2374652 - Handling very large data volumes with advanced DataStore objects in SAP BW on SAP HANA and BW/4HANA
## Column Views
After you migrate a BW system to a BW on SAP HANA Database all InfoProviders and InfoObjects must have column views which are needed for reporting. In case a new BW InfoProvider or InfoObject is created, the associated column view is generated during activation on the SAP HANA database. If column views do not exist or are inconsistent, the associated BW queries based on the view may fail.
Problems with column views on SAP HANA can often be resolved by recreating the column view for the associated InfoProvider, this can be done using the report RSDDB_LOGINDEX_CREATE. For further information refer to the following SAP Notes:
- KBA 2106851 - Column Store Error 2048 executing RSDDB_LOGINDEX_CREATE on Multiprovider - on symptoms related to these issues and how to use the report to resolve them.
- SAP Note 1695112 - Activities in BW after migrating to the SAP HANA database for possible root causes for HANA view inconsistencies especially after the database migration to HANA.
You can use the report RSDDB_INDEX_CREATE_MASS to generate views for more than one InfoProvider. Further information can be found in the Wiki:
- BWonHANA: InfoProvider column views .
## Authorization issues related to BW on HANA Generated views
To ensure that you have the required permissions to access views and data related to BW on HANA please follow the configuration steps in the following:
- Help Portal: Authorizations for Generating SAP HANA Views.
- SAP BW ∕ 4HANA: Generating SAP HANA Views from the SAP BW ∕ 4HANA System
If after doing the above configuration you still get an authorization related error when accessing the BW generated view on HANA you can find the missing privilege(s) using the information in SAP Note: 1809199 -SAP HANA DB: Debugging user authorization errors.
## Cross-Database Access for BW Calculation views
Read-only queries between tenant databases in the same SAP HANA system are also possible for BW calculation views. This means that database objects such as tables and views can be local to one database but be read by users from other databases in the same system.
The configuration that is required on the HANA Database side is described in the SAP HANA Administration Guide under the section Cross-Database Access. For this to work with BW on HANA generated views an additional configuration step is required. This BW side configuration for the BW External HANA View is described in the following SAP note (see question 8 'External HANA View'):
- 2312583 -FAQ: BW and HANA MDC: Cross-Database-Access in HANA Multitenant Database Containers .
For the External HANA View, a dedicated setting is available to support the read access from a remote tenant database via HANA Cross-Database-Access (CDA) based on MDC. If the flag is set, then the request handling
is de-activated for the external SAP HANA views. This is required as the request handling is implemented in procedures because HANA CDA does not support embedded procedures in remote views.
Further information can be found in the context help of BW transaction RS2HANA_ADMIN General Settings Multi-tenant enabled Additional limitations for cross-database access are described in the following SAP note:
- 2196359 -Limitations for cross-database access in an SAP HANA MDC environment .
## Privilege Errors
A privilege error may occur for a user trying to do a data preview or access a view in the remote database:
```
[258]: insufficient privilege: Detailed info for this error can be found with guid '<some GUID>'
```
See Troubleshooting Authorization Problems [page 156] in this guide for how to use the GUID value to find out which privileges are missing.
Or an authorization error may occur:
User &lt;USER NAME>= is not authorized to use VIEW _SYS_BIC.&lt;package>=/&lt;view_name>= because of missing grantable privileges on underlying objects
The most likely root cause of these errors is that the user does not have SELECT privilege on the bottom column view WITH GRANT OPTION. For a particular user to be able to select from a view (independent of whether this is an activated column view modeled through the SAP HANA Modeler or a SQL view that was created manually), the view owner needs the SELECT privilege on all underlying objects WITH GRANT OPTION. In a cross-database setup, the remote user that is mapped to _SYS_REPO on the tenant database that created the top calculation view will usually not have this privilege. See also point 6 of the solution in the following SAP note:
- 2196359 -Limitations for cross-database access in an SAP HANA MDC environment .
Further background information on this privilege requirement can be found in the following:
- SAP HANA Security Guide: Cross-Database Authorization in Tenant Databases (in the section CrossDatabase Authorization in Tenant Databases)
- The 'Cause' section of KBA 2787578 Running a HANA view fails with error "User _SYS_REPO is not allowed to grant privilege SELECT for TABLE"
## Consumption of External BW HANA Views for BW InfoProviders in XSA WebIDE and Required Privileges
If you want to use BW external HANA views in your modeling in WebIDE then you need to assign certain privileges for this to work. If you don't do this then the creation of the native views and/or querying the native views in WebIDE will fail with an "insufficient privilege" error such as:
```
Error: (dberror)[258]:insufficient privilege: search table error: [2950] exception 306003: Authorization failed in Calculation Engine. User is not authorized to 'SELECT'.
```
The required privileges are:
- The HDI container owner (technical user with suffix "#OO") needs SELECT WITH GRANT OPTION on the relevant BW views.
- The application user needs analysis authorization (Analytic Privileges) on the relevant BW views.
See also SAP note 2907570 -Consumption of external HANA views for BW InfoProviders in XSA WebIDE database modules . Attached to this SAP note is a template ABAP report ZRS2HANA_CREATE_XSA_ROLES that you can use to assign the required privileges and a detailed document that has step-by-step information and examples on how to use the report.
## Data Loading, Sizing and Memory Usage
After a BW on HANA migration if you experience high memory usage or OOM dumps on HANA during data loading using Data Transfer Process or info package please check if the below SAP Notes are applicable in your case and applied in your system if relevant:
- 2230080 - Consulting: DTP: Out of memory situation during 'SAP HANA Execution' and the 'Request by Request' Extraction
- 2402503 - Increased memory requirement of BW objects in SAP HANA
- 2602477 - Considerations for packagewise extraction from HANA DataSource based on Calculation Views
If you experience high loads and unloads of column store tables as described in the KBA 2127458 - FAQ: SAP HANA Loads and Unloads the sizing of the system should be checked.
You can use the program /SDF/HANA_BW_SIZING to size the system either before the database is migrated to HANA to get an estimation of the required hardware and memory requirements, or after the migration when the database is running on HANA to check if the sizing of the BW on HANA system is correct. Further information on the usage of this program is available in the following SAP note:
- 2296290 - New Sizing Report for SAP BW/4HANA.
To get an overview of the current memory usage in the HANA system you can use the script HANA_Memory_Overview_1.00.90+.txt from the KBA 1969700 - SQL Statement Collection for SAP HANA, the script shows the size of the row and column store tables and also the size of the HANA memory HEAP allocators.
Background information, for example, to understand the purpose of the HEAP allocator and to understand what can be done to reduce the memory consumption for the HEAP allocator, can be found in the following SAP note:
- 1999997 -FAQ: SAP HANA Memory , (question 13: What can I do if a certain heap allocator is unusually large?)
For information on how to do housekeeping for large BW on HANA row store tables refer to the information in the following KBA:
- 2388483 -How-To: Data Management for Technical Tables .
## Wrong Data
As a first step to analyze wrong data or unexpected results, the issue should be checked from the BW application side using the steps given in the BW application KBAs:
- 2271335 - Wrong Data is Read from a HANA InfoProvider,
- 2399752 - Different Results with Operations Mode in BWA/HANA (TREXOPS) in a BW Query
- 1508237 - First Steps to Check Wrong Number in BW Query (Transaction RSFC).
Additionally, all known reasons why the HANA database could return wrong or unexpected results are explained in the following SAP HANA KBA:
- 2222121 - SAP HANA Wrong Result Sets
## Frequently-used Transaction Codes
The following table lists (in alphabetical order) the most often used transaction codes when working in a BW on HANA system:
| Tcode         | Description                                                                                                                                                                                                                                                                                                                                    |
|---------------|------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
| AL11          | SAP Directories, you can find trans log under DIR_TRANS, work process log under DIR_HOME, etc. It's useful when you don't have authorization to run st11                                                                                                                                                                                       |
| DB01          | Display blocked transactions, you can find the same information under DBACOCKPI- >Diagnostics->Locks->Blocked Transactions                                                                                                                                                                                                                     |
| DB02          | Display Consistency Check result, help you to find out missing objects in database, unknown objects in ABAP. Index checks etc. It will display the same interface as DBACOCKPIT/ST04->Diagnostics->Missing Tables and Indexes                                                                                                                  |
| DBCO          | Maintain secondary Database connections                                                                                                                                                                                                                                                                                                        |
| LISTCUBE      | List content of an Info Cube                                                                                                                                                                                                                                                                                                                   |
| OS01          | LAN Check by Ping, can check the connectivity between DBserver and Application server                                                                                                                                                                                                                                                          |
| RS2HANA_CHECK | Check all prerequisites for successful replication of BWauthorizations to SAP HANA, it can be used to check if the BWusers has authorization to run the HANAviews generated from the BWmodel                                                                                                                                                   |
| RS2HANA_ADMIN | It can be used as a general entry point where all BWobjects with enabled "External SAP HANAView" are visible.                                                                                                                                                                                                                                  |
| RSA1          | Data Warehousing Workbench (>= BI 7.0) / Administrator Workbench (< BI 7.0), The administrator workbench is the central cockpit used for the administration of almost the entire BWsystem. The RSA1 main screen can be divided into three general areas. The extreme left area, allows us to choose BWmodelling components like Infoproviders, |
InfoObjects, InfoSources and DataSources. Individual BW components represented by different icons:
| RSD1     | Characteristic maintenance, For issues like InfoObjects cannot be activated, you can use this tcode to activate the infoObjects again to reproduce the issue.   |
|----------|-----------------------------------------------------------------------------------------------------------------------------------------------------------------|
| RSDDB    | SAP HANA/BWAindex maintenance,                                                                                                                                  |
| RSLIMOBW | Create/modify Composite Provider                                                                                                                                |
| RSPC     | Process Chain Maintenance, can be used to find the process chain job logs                                                                                       |
| RSRQ     | Data Load Monitor for a Request                                                                                                                                 |
| RSRT     | Start of the report monitor, can be used to run Bex queries.                                                                                                    |
| RSRV     | Analysis and Repair of BWObjects, can be used to check BWObject consistency.                                                                                    |
| RSTT     | RS Trace Tool                                                                                                                                                   |
| RZ11     | List ABAP profile parameters                                                                                                                                    |
| SAINT    | SAP Add-on Installation Tool                                                                                                                                    |
| SPAM     | SAP Patch Manager                                                                                                                                               |
| SE01     | Transport Organizer Tool, for issues related to BWtransports, can be used to find the related transports and find the transport log                             |
| SE11     | ABAP Dictionary, display the ABAP table/view definition, can be used to find the primary index definition as well                                               |
| SE14     | ABAP Dictionary Database Utility, can be used to check if ABAP table exist on DBlevel, recreate table etc.                                                      |
| SE16    | Display content of an ABAP table/view                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 |
| SE38    | ABAP Editor, can be used to display/run ABAP programs. If only need to run the ABAP program, can use SA38 instead. Useful ABAP program: • RSDU_TABLE_CONSISTENCY • RSDDB_LOGINDEX_CREATE • RSDDB_MASS_INDEX_CREATE • RS_BW_POST_MIGRATION • RS2HANA_AUTH_RUN • RSPFPAR - Display profile parameter • ADBC_TEST_CONNECTION -- test if the DBconnection works • RSDU_EXEC_SQL - if SQL editor is not available through DBACOCKPIT • RSBDCOS0 -- runanOScommand • RSTRC000 - lock a SAP work process for yourself, and increase work process trace level |
| SLG1    | Analyze Application log. It is useful to get the logs and timestamps for errors on application side                                                                                                                                                                                                                                                                                                                                                                                                                                                   |
| SM04    | Display all the current ABAP user sessions                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |
| SM12    | Display and Delete Locks on ABAP level                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                |
| SM21    | Online System Log Analysis                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |
| SM37    | Overview of BWscheduled jobs, for a failed job, you can view the job log and from the job details you will be able to find which work process has been used to run the job.                                                                                                                                                                                                                                                                                                                                                                           |
| SM50    | Work Process Overview                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 |
| SM51    | List of SAP Systems, it's useful to switch to different application servers                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
| SM59    | RFC Destinations (Display/Maintain)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   |
| SM66    | Global work process Monitor, list all the active work processes on the system                                                                                                                                                                                                                                                                                                                                                                                                                                                                         |
| SNOTE   | Check if SAP Note being implemented on the system or not                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              |
| ST05    | Enable SQL trace, which will help you to tell which query cause the long running time.                                                                                                                                                                                                                                                                                                                                                                                                                                                                |
| ST06    | Operating System Monitor                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              |
| ST11    | Check work process log                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                |
| ST03    | Work load monitor                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
| ST04    | DBACOCKPIT-> DBoverview                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |
| ST12    | Single Transaction analysis, only available if the SAP ServiceTools has been installed(component ST-A/PI. SAP Note 69455). It's a combination of the standard ABAP(SE30) and SQL trace(ST05) see SAP Note 755977 for step-by-step instructions |
| ST22    | ABAP Runtime Error                                                                                                                                                                                                                             |
| SU01    | User maintenance, display/Modify BWusers                                                                                                                                                                                                       |