Skip to main content

TROUBLESHOOTING SAP DWC PERFORMANCE ISSUES WITH HANA PLAN VISUALIZER

Published on 14 July 2021
SAP DWC

About Authors

Vibhav Nallan
Vibhav Nallan
Consultant

Vibhav Nallan is a Consultant for the Analytics Practice at Applexus. He has 7+ years of experience in the fields of Business Intelligence & Analytics. He has worked on various Analytical Accelerators to streamline reporting and ease problem-solving in Customer Retail.

Ankur Goyal
Ankur Goyal
SAP BW Analytics Architect

Ankur Goyal is an Analytics Architect for the Analytics Practice at Applexus. He has 15+ years of experience in the fields of Business Intelligence & Analytics. He has worked on various SAP BW/ HANA/BOBJ Implementations across various Retail, Manufacturing & Service Industries.

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.

SAP DWC
Figure 1: Reporting Landscape

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:

  1. Back end data model performance (DWC data model in this case)
  2. Network performance
  3. 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:

  1. Capturing the JSON Request from SAC to DWC by running the Trace
  2. Generating the PlanViz Output file
  3. 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

  1. Google Chrome browser with access to Developer Tools to capture the JSON Request
    Accessing Developer Tools
    Figure 2: Accessing Developer Tools
  2. Any text and source code editor to properly format the JSON request (ex: Notepad++)

    a. Also install the JSON Viewer Plugin on Notepad++

    SAP DWC
    Figure 3: Source code editor with JSON Plugin
  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).
    SAP DWC
    Figure 4: Create Database Analysis User
  4. Visual Studio Code to analyse the PlanViz output

    a. The ‘SQL analyzer tool for SAP HANA’ extension for Visual Studio Code.

    SAP DWC
    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

  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.
    SAP DWC
    Figure 6: Selecting a Story for Analysis
  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.
    SAP DWC
    Figure 7: Response times for Story widgets
  5. Next, under the Headers section go to the Request Payload area and click on ‘View Source’
    SAP DWC
    Figure 8: Request Payload
  6. Here we find our initial JSON trace. Make sure to select ‘Show More’ to fully display all the text.
    SAP DWC
    Figure 9: Request Payload
  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.
    SAP DWC
    Figure 10: Capturing the JSON request
  8. Paste the text within Notepad++ & from under Plugins, select the JSON Viewer plugin (as shown in Figure 3) and click on Format JSON.
    SAP DWC
    Figure 11: Formatting the request
  9. Set this formatted text aside for the moment as we now focus on creating the Database Analysis User.
    SAP DWC
    Figure 12: Formatted JSON request

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.
    SAP DWC
    Figure 13: Creating Database Analysis User
  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.
    SAP DWC
    Figure 14: Database Analysis User Details
  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.
    SAP DWC
    Figure 15: Open Database Explorer
  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.
    SAP DWC
    Figure 16: Connecting with DWC user ID

     

  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.
    SAP DWC
    Figure 17: S-User ID authentication

     

  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.
    SAP DWC
    Figure 18: Signing in to the HANA Database

    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
    SAP DWC
    Figure 19: HANA Database Explorer

Generating the PlanViz output file

  1. Upon logging in, right click on the Database and from the context menu select ‘Open SQL Console’
    SAP DWC
    Figure 20: 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=>'

     
    ', RESPONSE=>?);

  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.
    SAP DWC
    Figure 21: Pasting the JSON request
  4. Once the entire text is pasted, let us execute the script.
    SAP DWC
    Figure 22: Executing 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.
    SAP DWC
    Figure 23: Result panel after execution
  6. We can proceed onto the next step of generating the PlanViz file by selecting Analyze > Generate SQL Analyzer Plan File
    SAP DWC
    Figure 24: Generate SQL Analyzer Plan File
  7. 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.
    SAP DWC
    Figure 25: Saving the PlanViz file
  8. This file can then be downloaded directly onto your local system. From within the ‘Plan Saved’ pop-up box, select the ‘Download’ option.
    SAP DWC
    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:

  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.SAP DWC
    SAP DWC
    Figure 27: Uploading the PlanViz file
  2. The file is now ready for analysis within Visual Studio Code.
    SAP DWC
    Figure 28: Analyzing PlanViz 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.SAP DWCSAP DWC

     

  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.

 

Add new comment

Plain text

  • No HTML tags allowed.
  • Lines and paragraphs break automatically.
  • Web page addresses and email addresses turn into links automatically.

About

We are professional and reliable provider since we offer customers the most powerful and beautiful themes. Besides, we always catch the latest technology and adapt to follow world’s new trends to deliver the best themes to the market.

InSITE achieves the coveted "Spotlight Status" on SAP Store

Applexus InSITE Spotlight