Performance Tuning – Dashboard on HANA

[former_member229916]

In this blog I will share some tips and tricks on how to optimize the performance of a dashboard created in Design Studio directly connecting to HANA as data source. Although there is a lot of information available, but I thought it would be worthwhile to compile a few of such tips/tricks and show it with proof that these do really help in optimizing the performance and meeting the ever-growing business need of bringing down the response times of dashboard. Please keep in mind that an individual tip may not improve the performance significantly but knowing all these and applying them in right context can result in 200 – 300 % faster performance. Again, not all of these may be applicable in all scenarios but being aware will generally help.

Overview – How a data source loads and fetches data.

Loading of a data source means just reading and validating the metadata and not fetching the data. Loading happens by default if “Load in Script” is not set to “True”

Data fetch happens at a subsequent step if required. A data fetch may not happen at all if the data source in question is not used in screen object that is active. For example, if you have two tabs using two different data sources and you open the dashboard and never go the second tab then the corresponding data source will not be refreshed at all.  Also, certain navigation in dashboard may trigger a data refresh.

 

TIPS –

Even though these are inter related I have tried to separate them out for easy reading.

1. Load Data Sources in Parallel 

If there are multiple data source always use following settings to force parallel loading.

• At Data-source Level
  
  
  
  • Use Grouping in Data Sources
  
  
  • Set "Load in Script" to "True"
  
  
  
  


• At Application Level
  
  
  
  • Use APPLICATION.loadDataSources in the “On Startup” event or a subsequent event.
  
  
  • Set "Merge Prompts to "false" at application level. This is very important and if true no palatalization will occur.
  
  
  
  

Data sources in one group load in series but different groups load in parallel when triggered by script. One of the groups can be "default group" itself. These settings not only help loading the data sources in parallel but also helps while refreshing the data by changing filters. The refresh also runs in parallel.

 

With all Data Sources in “Default Group" and “Load in Script” is set to “False”   

             

 

With one data source in group “default group” and another one in G1. Both of them “Load in Script” set to “false” and use script in the “On Startup” event of dashboard. Also "Merge Prompts set to "false" at application level.

 

2.  Configure Value Help in Hana for dimensions used to build drop down lists

If you are using a Drop-Down linked to a column from Hana Model or using script getmember or any process that triggers getmember method, then make sure that the value help column in Hana Model for that column is defined properly. If Value help is not defined or not defined correctly using a master data table (or an attribute view) then the whole Hana Model is materialized before the value help is shown. Using a master data table makes sure that the Characteristic Member access is much faster. If using a column from an attribute view, used in a Calculation view then value help needs to be defined on attribute view. 

This setting also helps when filtering data using set filter or data binding. The "getmember" method runs fast if the "validate" is set to true. See tip 4.

 

Without Value Help Assignment

With Value help assignments 

 

3. Use separate data source for each drop box or filter element. 

If the dashboard has multiple Drop-Down boxes then it would be worthwhile using a separate data source for each and put those data sources in separate group. This will make sure that even the getmember runs in parallel for all these drop downs.

 With same data source for 2 Drop-Down box

 

With different data source in different group for each Drop-Down box

 

4.  Use Filter cautiously

When filtering a data source using a dimension either by using Set filter or by data binding the filter value is validated against the dimension values by default and "GET_MEMBER" method is called.  You can change this behavior and set it to no validation. You do not need validation if the Drop-Down values are coming from database itself. This will have save significant time when Member access takes long time. You can set the "Validate" to false either in script command set filter or in binding properties of the element "Validate" is an optional parameter of set filter or data binding and is by default true. You should use this setting even if you have implemented tip number 2.

 

                 

or Or Script DS_1.setFilter("GENDER", DROPDOWN_1.getSelectedValue(),false);

 

With Validate = Default "true"

                  

 

With Validate = "false"

 

 

5. Use Background processing to keep ready for next screen

User does not see the spinning logo when background process is running. It is an excellent feature that can be used to do processes that are needed for screens other than current active screen and will be a seamless navigation to user when they navigate to a separate tab or screen. I recommend this is for experienced developers as it requires good scripting skill and knowledge of various events.

Here are some things that you can do in back ground processing. Note that background processing has to be specially called in script. Also, you can control what happens in background by using variables. Set variables in "On Select" or "Startup" event , use it in background and then clear it.

• While initial load of application only initialize data sources that are required for first or default landing screen. All other data sources can be initialized in background while user is on first screen.


• Set filter for data sources not used in current active screen element. Even though set filter does not necessarily trigger a data refresh (if data source is not bound to an active screen element) it still triggers the GET_MEMBER (if not switched off using Tip 3). So, for data sources that are not used on active screen it is best to use set filter in background event


• Refresh the data sources on other tabs while user is at one tab. The user will not even notice when they switch from one tab/ screen to another and the spinning logo (for data refresh) will not appear as concerned data source will already have refreshed in background. Normally a data source is not refreshed by set filter unless it is used in active screen element. To force a refresh for data sources bound to non active screen elements please follow these steps
  
  
  
  • Create a dummy text element for each data source and bind it to one cell of data source
  
  
  • Put the dummy text behind any screen element such as panel so it is not visible to user
  
  
  • Trigger a set filter command for the concerned data source in background.
  
  
  
  

Note - 

As you can see that small changes make big differences. It is always a good idea to run the dashboard with profiling on (&PROFILING=X added to document link URL) and then expand the statistics to see where the issue is. Some of these tips are documented in SAP documentation but some are not and it all depends on what features you are implementing inside a dashboard. Hope you enjoyed the article.