Troubleshooting SAP DWC Performance Issues with the HANA Plan Visualizer
This blog was also submitted at Applexus Blogs as part of our employee engagement towards ‘Knowledge Center’ initiatives.
Huge thanks to Ankur Goyal for his valuable inputs.
Problem Scenario & Landscape:
Our landscape involves creating reports (Stories) on SAP Analytics Cloud that uses a Live connection based 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 performance: time used to render the widgets and the 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’ while the initial 6 widgets load and will only load after that.
You can read more about this behavior 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 also can 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:
(1) 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++)
- 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
- The ‘SQL analyzer tool for SAP HANA’ extension for Visual Studio Code.
Generating the HANA Visualized Plan:
Listed below we will go over, step-by-step, the process involved in generating the HANA Visualized Plan to thoroughly examine areas where the performance impact lies.
Capturing the JSON Request from within SAC by running the trace
(1) Open up the Developer Tools panel from within the Google Chrome browser as shown in Figure 2. Select the ‘Network’ tab from within it.
(2) Navigate to the SAP Analytics Cloud story that you want to analyse and select it.
(3) As each widget in your story loads, it will correspond to a single ‘GetResponse’ line recorded within the Network trace. Type in ‘GetResponse’ in the filter pane to filter only the necessary items you want to inspect.
(4) Now, we have a clear representation of the number of widgets loaded, and also the Time it took for each one to load. While most of our widgets have loaded within ~2.5s one widget is taking longer. Let us click on the ‘GetResponse’ record to perform further analysis.
(5) Next, under the Headers section go to the Request Payload area and click on ‘View Source’
(6) Here we find our initial JSON trace. Make sure to select ‘Show More’ to fully display all the text.
(7) Select the entire text within Request Payload and copy it. Note that to have properly selected it, ensure that a white fade envelops the entire text field.
(8) Paste the text within Notepad++ & from under Plugins, select the JSON Viewer plugin (as shown in Figure 3) and click on Format JSON.
(9) Set this formatted text aside for the moment as we now focus on creating the Database Analysis User.
Creating the Database Analysis User within Data Warehouse Cloud
(1) Please refer to Figure 4 on how to access the Database Analysis User. Select ‘Create’. The ‘Create Database Analysis User’ prompt lets you set the User Name Suffix. Make sure to capture the Database Analysis User Name as this is required later.
(2) IMPORTANT – Make sure to save all the following details in a Notepad. These details are necessary to be able to generate the PlanViz file and cannot be accessed once this below window is closed.
(3) Once the DBA User is created, select it from within the User list and click on ‘Open Database Explorer’, as shown in the Figure below.
(4) This opens up the SAP BTP login screen so as to be able to access the underlying HANA Database, please enter your DWC credentials here.
(5) Optional – There might be an additional security check before you can access the SAP HANA Database Explorer. If so, please sign in with your S-User ID credentials.
(6) Finally, we can access the SAP HANA Database Explorer panel. Here we can sign in to the SAP HANA Database using the Database User ID and Password we captured during initial creation of the Database User, as shown in Figure 14.
Note: It is not recommended to enable the ‘Save Password’ option here
(7) Once successfully signed in, we can see the Database User we have created along with the Catalog & Database Diagnostic Files which we will use for further analysis
Generating the PlanViz output file
(1) Upon logging in, right click on the Database and from the context menu select ‘Open SQL Console’
(2) In the console, first paste the following code. Ensure proper formatting so as to leave some blank rows between the first and second rows of text.
CALL SYS.EXECUTE_MDS (‘Analytics’, ”, ”, ”, ”, REQUEST=>’
[Your code here]
(3) As shown in the figure below, paste the formatted JSON from the Notepad++ application into the SQL console, ensuring that the text is pasted in between the present rows of text.
(4) Once the entire text is pasted, let us execute the script.
(5) If the JSON request has been captured and executed properly so far, the Result panel will appear as shown below without tossing any errors.
(6) We can proceed onto the next step of generating the PlanViz file by selecting Analyze > Generate SQL Analyzer Plan File
(7) Give a name to the generated PlanViz file and Save. Initially this saves this file within the HANA Database Explorer in the location mentioned below.
(8) This file can then be downloaded directly onto your local system. From within the ‘Plan Saved’ pop-up box, select the ‘Download’ option.
Analyzing the HANA Visualized Plan:
Now that we have the PlanViz file saved to our local system, we can start analyzing this further to gain insights on the slow response time produced by our SAC widget. To do this, let us perform the following steps:
(1) Open Microsoft Visual Studio Code. From under File>Open File… select the PlanViz file we’ve saved to the local system in the previous section.
(2) The file is now ready for analysis within Visual Studio Code.
(3) Using this tool we can break down individual components of the processes (ex: Join executions, Calculation times) that occur within the underlying HANA database before a response is sent to the front-end Browser. We have included the below screenshots as a reference to show how the drill down works at a high-level.
(4) Further analysis can be continued on a case-by-case basis. This will help you gain insights on in which specific part of the query execution process the bottleneck lies and thus how to resolve it.
To derive deeper insights from the PlanViz file we would like to direct you to one of the multiple resources online that delve deeper into this topic.