To understand the feature of Query Stripping, it is important to know about the microcube concept. The microcube is a kind of cache that behaves as a non-live data source. Every objects that are included in your query will be computed in the microcube, irrespective of whether they are directly or indirectly used in the final rendered report. So if you delete some objects in your rendered report, when refreshing the report, you will refresh the complete microcube. This can result in a performance impact as the data is retrieved by the query which will not be directly or indirectly used in the report that will be rendered or displayed to the User.
Because the microcube is more than a simple cache, its advantage is that you don’t neet to get an access to your live data source when adding some other reports as everything is already loaded in the microcube.
On the other hand, BEx is behaving in a different way: every time you add an object in your report, an MDX query is regenerated, hence an access to the data base is done.
Query Stripping is a new feature that is available on XI3.1 SP3 and BI4.0. This is a new paradigm because by using this feature, you will pay the price for what you really consume, and this is very similar to how BEx behaves. Query Stripping feature optimizes the query to fetch only DATA which will be directly or indirectly rendered or displayed to the User, thus decreasing the response time and faster display of the report for the user. This feature is only available for OLAP universes, and it will be greyed out when using any RDBMS universe.
How to activate the Query stripping feature
Query stripping provide two levels activation. First on the query level, enable the Query Stripping.
Second on the document level, enable the Query Stripping. With the feature enabled, user may see the stripped objects in BOLD font.
When adding stripped objects to report, users may see #REFRESH, as the query is updated, users need to refresh the report.
If the Query level property and the Document level property are both enabled then the Query will be stripped. Otherwise, the Query will not be stripped. When there are multiple queries in a document, with setting the property for each query, you can activate query stripping on some of the queries and deactive it on the others.
In the case you have a Web Intelligence report that mixes the 2 types of universe (OLAP universe and RDBMS universe), the option box will be available at the Document level but it will be available only at the Query level for the query based on the OLAP universe.
The Feature is deactivated by default for both the Document property and the Query property: this is to keep the compatibility of Web Intelligence documents from one patched environment with a non-patched environment.
Indeed, enabling the Query Stripping feature makes the Web Intelligence document incompatible with older versions of Web Intelligence clients. We need to recommend that the user have his Webi Rich Client clients at the same patch level as the server.
To facilitate troubleshooting, the Trace logs contain the information saying if the feature is activated or not.
The performance gain with query stripping depends on how many objects are stripped from the query. Users may usually drag and drop more than enough objects to result pane when designing the query, if many objects are not directly or indirectly used in the report, the query stripping can improve the performance significantly. Otherwise, if all the dragged objects are used in the report, the query stripping will make no change on the query.
The Query Stripping feature has two different use cases based on two user profiles:
- Power user – able to create and edit queries
When a user creates a report, by default the query stripping option box is disabled. When the property is enabled at the Query level, this property is not considered at the time of query generation until the document level property is enabled.
After creation of the report, the report creator can enable the Query Stripping option at the Document level. The report can be then distributed to other users.
So when the Power user creates a new Web Intelligence document based on an OLAP universe, he will have an option to set the Query Stripping property at the Query Level. By enabling the feature at the query level, he can let the option to the final user to strip the query. At the opposite by keeping the option unchecked, the Power user forces the Query not to be stripped.
- Report User – unable to edit the queries, only consumes existing reports
In current scenario, when the preoperty is activated at the Query level. The Report user will be able to enable the property at the document level (to get the query stripped) or to keep the query as it is (by keeping the option unchecked at the Document level).
- A WebI document with Query Stripping activated cannot be opened with a system without QS installed. But if you deactivate the feature, save the WID then you can then re-open it with a system without QS installed)
- This feature is only for OLAP sources.
References and Links
This new video explains how to enable Query Stripping and perform server-side tuning for Web Intelligence---these are especially useful techniques if you’re reporting on data in BW or BW-on-HANA.
YouTube Video: Tuning Web Intelligence - Query Stripping and Server Settings:
Tasks demonstrated in the video:
- Enabling Query Stripping (only applicable to reports on BW or other OLAP data sources)
- Tuning the Web Intelligence Processing Server (applicable to all data sources)
- Cloning the Web Intelligence Processing Server for better scalability and performance
- Setting and applying configuration templates in the Central Management Console (CMC)
If you have any questions or comments on the video, please post them here on SCN: