Query Stripping in Web Intelligence 4.2 SP3

Let us understand what is query stripping in Web Intelligence 4.2 SP3 and how to enable it at the universe, query and document level in three simple steps.

What is Query Stripping?

Query stripping is a query performance improvement technique. It was initially only available for universes based on OLAP data sources.

However, from SAP BusinessObjects BI 4.0 onwards, it is available for relational data sources as well as universes based on relational data sources.

The query stripping feature strips out all the objects from the report level query that are not used in the report and has significant improvements when it comes to reporting performance.

It improves the report performance by sending the stripped query to the report which can be viewed in the view script function at report query level.

There are chances that we may include measures and dimensions on the report query panel which are not directly or indirectly used in the Web Intelligence report.

The query stripping feature helps to strip out or remove all the objects that aren’t used on the BOBJ report. Hence, every time a measure or a dimension is added or removed from the query, the SQL script will be modified.

This process, therefore, helps to reduce the report refresh time and the response time to load the tables, charts, data and other visualizations on the WebI report.

This feature is enabled by default at universe level if it is based on OLAP data sources. However, if the universe is based on relational data sources, then it needs to be explicitly checked.

Query stripping is deactivated by default for relational data sources and thereby, the reports based on them. So, it’s important to explicitly check the options to enable query stripping.

It is not every time that there will be a huge performance impact after applying this feature. It depends on what objects you’ve already added to the report and which ones are stripped off.

While designing the WebI report, the report designers usually tend to add all the dimensions and measures on the report because of the fact that you never know which object you need for the report elements.

So, query stripping functionality identifies which objects are used in the report and which aren’t and it is intelligent enough to remove such measures and dimensions from the report.

Enable Query Stripping in Web Intelligence 4.2 SP3

The query stripping feature in SAP BusinessObjects needs to be enabled at three different places which we shall demonstrate now.

Note: This tutorial for query stripping in Web Intelligence is based on SAP BusinessObjects Web Intelligence 4.2 SP3 and SAP BusinessObjects Information Design Tool 4.1.

Universe Level

This is the first location where the query stripping option must be enabled. Normally, a universe is developed using Information Design Tool (IDT) which could be based on relational or OLAP data sources.

It is, therefore, important to enable query stripping in Information Design Tool for it to reflect on the WebI report.

Select the universe and click on the query options tab as shown in the image below. Check the allow query stripping option and that is all you need to do at the universe level.

However, there are a few things to note. When you enable the query stripping in IDT, you need to have a look at the following options and decide whether you should uncheck it or not.

1. Limit size of result set to n rows
2. Limit execution time to n minutes

Report Query Level

If you have enabled the query stripping option in IDT universe, you should get this option checked by default. However, just in case it is unchecked, let us see how to enable query stripping in WebI at the query level.

You have two options here for navigating to query panel.

1. Open WebI report in design mode and click edit data provider under file menu.
2. Open WebI report in design mode, go to data access tab and click edit data sources.

In the query panel, go to query properties and click on it. You will get to see the following window and check enable query stripping option below.

However, again, you need to make sure that the following properties are proper otherwise you may get partially refreshed data when you refresh the reports.

These properties should match with what you selected at the universe level.

1. Max rows retrieved
2. Max retrieval time

Document Level

To enable query stripping in WebI, you need to enable it at the document level as well. Click Apply and close and return to the report panel. Click on the Properties tab as shown in the image below.

Now, as you can see in the image below, check enable query stripping option in the document summary window.

These were the three places where you have to enable query stripping option for the report to improve in terms of performance and refresh time.

Let us know if you have any information about query stripping in Web Intelligence in the comment section below. Find more about Business Intelligence and Analytics on BI LaunchPad.