SAP Analytics Cloud has provided a new hack to improve the loading performance for Live BW models using enhanced techniques by reducing round-trips to the backend, and memory by combining backend calls for multiple charts within a story as long as they share the same dimensions, along with a few other prerequisites.
In SAP Analytics Cloud, when it comes to performance, Live BW Connection may take up a little extra time to fetch the data from the server when compared to Import Connection. In this blog, we will see how this new Enhanced technique / Query Merging helps in performance improvements of a Live BW connection.
Query Merging
In addition to Query Batching, SAP Analytics Cloud has now introduced a feature called “Query Merging” where the data requests of multiple widgets will be merged together as a single request. The requests of the different widgets would be merged if they hold the same set of visualization that falls under the prerequisites given by SAP Analytics Cloud.
The number of requests to the backend server could be calculated based on the number of user sessions and the number of requests sent by the Story. Having different requests may increase the overhead on both the SAP Analytics Cloud layer as well as the BW layer as the application creates concurrent requests for the same session which results in performance impact. Consider a simple scenario as outlined below where the story has 10 widgets and 10 user sessions:
| Number of Widgets in the Story (that can be merged)(n) | Number of User session(m) | Total request | |
| With Query Merging(1*m) | Without Query Merging(n*m) | ||
| 10 | 10 | 10 | 100 |
Steps to enable Query merging
- (Edit Story) Query Settings. Select Enable merging requests.
- Visualize merging request displays coloured chart and the merged group ID, which helps to identify the charts that are merged as a single request. This detail will be displayed only in Edit mode.
In general, charts need to have the same query and the same variables as a basis in order to combine the queries.
Scenario 1: Queries contain the same dimensions with the same hierarchies and drill
Below is a scenario, where 8 charts are mapped with different measures across the same dimensions.
All the charts are coloured same and have the same merged group ID indicating that they are all merged as a Single request. In addition to this, you can also check the number request change under the Network tab in Google Chrome Developer tool.
With Query Merging( 1 Request for 8 Charts)
Without Query Merging( 8 Request for 8 Charts)
Scenario 2: Queries are part of the same receiver group if they use linked analysis
Consider, the story has a Page Filter based on dimension, the charts that are linked to the filter will be grouped together and the charts that are not linked will be considered as a separate request,
Here, the 7 charts have the common set of filters in the page and will be considered as a single group. The chart which is not linked to that page filter will be considered as a different group. Hence, totally there will be 2 requests.
.
Apart from the above-mentioned scenarios, below are a few other scenarios in which the queries will be merged together,
- Queries use the same sorting and ranking.
- Queries do not have an active threshold with threshold filters.
- The widget does not have a local filter on a dimension that is also used in a restriction in SAP BW.
- There is no widget filter on a secondary structure query.
- The widget filter is not defined as a complex tuple or range filter.
- Presentation types are the same
To learn more about SAP Analytics Cloud visit our series of blogs here. Click here to evaluate if SAP Analytics Cloud is right for you.
