TROUBLESHOOTING SAP DWC PERFORMANCE ISSUES WITH HANA PLAN VISUALIZER
About Authors
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
Figure 2: Accessing Developer Tools - Any text and source code editor to properly format the JSON request (ex: Notepad++)
a. Also install the JSON Viewer Plugin on Notepad++
Figure 3: Source code editor with JSON Plugin - 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).
Figure 4: Create Database Analysis User - Visual Studio Code to analyse the PlanViz output
a. The ‘SQL analyzer tool for SAP HANA’ extension for Visual Studio Code.
Figure 5: SQL analyzer tool for SAP HANA
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 is impacted.
CAPTURING THE JSON REQUEST FROM WITHIN SAC BY RUNNING THE TRACE
- Open up the Developer Tools panel from within the Google Chrome browser as shown in Figure 2. Select the ‘Network’ tab from within it.
- Navigate to the SAP Analytics Cloud story that you want to analyse and select it.
Figure 6: Selecting a Story for Analysis - 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.
- 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.
Figure 7: Response times for Story widgets - Next, under the Headers section go to the Request Payload area and click on ‘View Source’
Figure 8: Request Payload - Here we find our initial JSON trace. Make sure to select ‘Show More’ to fully display all the text.
Figure 9: Request Payload - 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.
Figure 10: Capturing the JSON request - Paste the text within Notepad++ & from under Plugins, select the JSON Viewer plugin (as shown in Figure 3) and click on Format JSON.
Figure 11: Formatting the request - Set this formatted text aside for the moment as we now focus on creating the Database Analysis User.
Figure 12: Formatted JSON request
Creating the Database Analysis User within Data Warehouse Cloud
- 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.
Figure 13: Creating Database Analysis User - 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.
Figure 14: Database Analysis User Details - 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.
Figure 15: Open Database Explorer - 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.
Figure 16: Connecting with DWC user ID - 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.
Figure 17: S-User ID authentication - 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.
Figure 18: Signing in to the HANA Database Note: It is not recommended to enable the ‘Save Password’ option here
- 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
Figure 19: HANA Database Explorer
Generating the PlanViz output file
- Upon logging in, right click on the Database and from the context menu select ‘Open SQL Console’
Figure 20: Open SQL Console - 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=>'
', RESPONSE=>?); - 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.
Figure 21: Pasting the JSON request - Once the entire text is pasted, let us execute the script.
Figure 22: Executing the script - If the JSON request has been captured and executed properly so far, the result panel will appear as shown below without tossing any errors.
Figure 23: Result panel after execution - We can proceed onto the next step of generating the PlanViz file by selecting Analyze > Generate SQL Analyzer Plan File
Figure 24: Generate SQL Analyzer Plan File - Give a name to the generated PlanViz file and save. Initially this saves the file within the HANA Database Explorer in the location mentioned below.
Figure 25: Saving the PlanViz file - This file can then be downloaded directly onto your local system. From within the ‘Plan Saved’ pop-up box, select the ‘Download’ option.
Figure 26: Downloading PlanViz file locally
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:
- 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.
Figure 27: Uploading the PlanViz file - The file is now ready for analysis within Visual Studio Code.
Figure 28: Analyzing PlanViz within Visual Studio Code - 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.
- 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.