Product Information
Smart Queries for SAP Analytics Cloud with BW Live Connection
One important aspect of the integration of SAP Analytics Cloud and SAP BW / BW4HANA is always the topic performance. With wave 19 we released a new feature especially for the BW Live connectivity. Officially the feature is called “Query Merging”, but I thought “Smart Query” sounds far better…
Lets assume a very simple case:
In our example, we have three charts displaying:
- Net Value by Product (Top 10)
- Costs by Product (Top 10)
- Net Value by Region (Top 10)
Without doing anything, this would result in three queries towards the SAP BW system.
Now with the new option of query merging, we can reduce the amount of queries.
First I open the Query Settings as part of my story…
.. and I then enable both options:
- Enable Query Merge
- Visualize Query Merge
.. and then i refresh my story.
All three charts are shown with a different color and there is a small comment in the bottom right corner for each chart, showing with with data request the data was retrieved. In my given example we still have three data requests – so we didn’t improve.
Why ?
So lets look at the pre-requisites:
-
Queries contain the same dimensions with the same hierarchies and drill.
-
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.
-
Queries are part of the same receiver group if they use linked analysis.
-
There is no widget filter on a secondary structure query.
-
The widget filter is not defined as complex tuple or range filter.
-
Presentation types are the same.
So in our example the second rule is why our three charts are not being merged, because we are using three different sets of ranking rules.
So lets remove the ranking from all our three charts…..
…. and we can see that the two charts that share the dimension are now part of the same data request, so instead of 3 requests we now are only using 2 requests.
When looking at this simple example the impact might not be so obvious, so lets put this into a perspective.
Lets assume we have a SAP Analytics Cloud Story with a total of 6 widgets and instead of running 6 data requests, we would be running 2 data requests with the Query Merging capability enabled.
Lets also assume that the SAP Analytics Cloud Story is being viewed by 100 people each morning.
Our old scenario:
100 Users x 6 requests = 600 requests
New scenario with Query Merging:
100 Users x 2 requests = 200 requests
I am sure your BW Administrator would appreciate this and your end users will as well …
Definitely, a smart query sounds cooler than query merging.
Query merging reminded me old methods like combining queries into workbook or APD 🙂
It's the step in very good direction, but you need to improve this feature to remove limitations related to sorting.
In the example you presented you significantly decreased functionality of the dashboard by removing sorting. Users will not appreciate this…
It's a great function as the performance increases when there are a lot of widgets used in one story.
Unfortunately we have the issue, that the scaling factor set in the SAC is reseted by using the query merge. So we have a numeric point with a scaling factor set to thousand and when we activate the query merge an reload the story the scaling factor is set back to one.
Is there any solution to this?
Kind regards
Britta
To be honest, I can't think of any scenario where this would be useful. To show the results of complex computations with big data, one would need way more control over the reusage of query results for multiple widgets than SAC provides. I have been submitting emails and enhancement requests for years, but the limitations are still too extreme to build a dashboard with SAC.