BI4.1 introduces query stripping options for Web Intelligence reports for relational UNX universes. Previously, query stripping was only available for OLAP data sources such as Bex queries. This is a worthy addition as it will allow for many queries that were previously built to run much more efficiently. Often times queries are built by users who may initially bring in more detail than is necessary but may not always go back and remove that detail when the report is finalized.
Have no fear, with just the click of a few buttons, Web Intelligence will go back and rework the SQL for you to get you a more efficient query based on the level of detail included in the actual report. We will go over the steps to activate query stripping and show you how it actually works within your document.
If it is not already checked, go ahead and check the ‘Allow Query Stripping’ on the Query Options tab in the Business Layer of your universe and then export it to the repository.
Create a new Web intelligence Report using the universe that has the Query Stripping enabled. For purposes of this post, we will use the trusted e-Fashion Universe.
We will keep the query simple and only pull in Store Name, City, and Sales Revenue.
Click on the query properties button and enable query stripping for that specific query. (Note: this will need to be done for each query in a multi-query report)
Before running the query, let’s check the SQL to see how it looks before any stripping takes place. All objects in the query should appear in the SQL.
Next step is to run the query
Initially, all of the objects by default will appear on the report.
To see query stripping in action, remove one of the objects from the report block. In our example, Store Name has been deleted leaving only City and Sales Revenue.
Now, let’s refresh the report and see what happens in the SQL.
The SQL no longer shows the ‘Store Name’ in the SELECT or in the GROUP BY clause. However, it remains in the query panel and shows up in the available objects on the left hand side.
(Note: any object stripped out of the query will appear in bold)
Once Store Name is dragged back into the report, the field will appear as #REFRESH until the query is refreshed again.
Once the query is refreshed, the original results will appear and the SQL will revert back to the original settings.
Query stripping is a welcome addition to Web Intelligence reporting in BI4.1. If you have some legacy reports that seem to take ‘forever’ to run and have been upgraded to the 4.1 environment, they would make great candidates for query stripping. It’s very likely they contain objects no longer necessary for the report to run successfully.