Troubleshooting SAP DWC performance issues with HANA Plan Visualizer
PROBLEM SCENARIO & LANDSCAPE
Our landscape involves creating reports (Stories) on SAP Analytics Cloud that uses a live connection to SAP Data Warehouse Cloud, where our reporting data models are located.
As with any analytic scenario involving complex modelling and/or large volumes of data, sometimes there is a need to monitor the performance of the dashboard. Dashboard performance will depend on the following factors:
- Back end data model performance (DWC data model in this case)
- Network performance
- Front end performance: time taken to render the widgets, usually impacted by the total number of widgets
Note: It is recommended to limit the number of widgets on each story page to ‘6’ as this is the cap of parallel requests most browsers (ex: Chrome) are capable of. Meaning, additional widgets will be ‘stalled’ and will load only after the initial 6 widgets load.
You can read more about this behaviour in the following link (Multiple TCP Connections).
Unfortunately, neither SAC nor DWC comes with an out-of-the-box performance analysis tool that can accurately gauge where bottlenecks occur that cause slow response-times on the reporting end.
The scope of this blog is to analyse the data source response time of the DWC data models.
Listed below is a high-level overview of the steps we undertake:
- Capturing the JSON Request from SAC to DWC by running the Trace
- Generating the PlanViz Output file
- Analyzing the HANA Visualized Plan
SAP DWC uses SAP HANA Cloud DB for data storage and execution. Tools used for HANA performance analysis can also be used for analysing DWC performance.
The section below outlines the step-by-step process on how we can get to the HANA PlanViz analysis.
PRE-REQUISITES FOR FURTHER ANALYSIS
- Google Chrome browser with access to Developer Tools to capture the JSON Request
2. Any text and source code editor to properly format the JSON request (ex: Notepad++)
a. Also install the JSON Viewer Plugin on Notepad++
3. A user with access to create a Database Analysis User on the DWC tenant side, requiring the DW Administrator role. This enables the reading of all space data, SAP HANA monitoring views, traces, reproduce issues and use explain plan. (Please check the following SAP Documentation for more information on this).
4. Visual Studio Code to analyse the PlanViz output
a. The ‘SQL analyzer tool for SAP HANA’ extension for Visual Studio Code.
Read more at Troubleshooting SAP DWC performance issues with HANA Plan Visualizer
