Skip to content

Web Intelligence Query Stripping

Find the query objects that Web Intelligence documents retrieve but never display, and turn on query stripping in bulk so refreshes fetch less data.

Goal

Users report that a Web Intelligence document "takes forever" to open. Often its data providers retrieve objects that no table, chart, variable or rule in the report uses. Typical places where unused objects hide:

  • data provider objects from a universe (UNV, UNX) or a SAP HANA source
  • report variables that no block uses
  • alerters and deeply nested variables
  • charts and their formulas
  • formulas used only in visibility rules (section hiding, conditional formatting)

With query stripping on, BusinessObjects removes unused objects from the generated query at refresh time. MetaMiner shows you which objects are unused and switches the setting for many documents at once.

Who

Report developers and BI administrators.

Before you begin

  • MetaMiner collects Web Intelligence detail for the environment. See Document Processing.
  • Query stripping must be supported for your data sources by your BusinessObjects version.

TODO: confirm the minimum BusinessObjects version for relational query stripping

The earlier page stated BI 4.2 SP3. Confirm against SAP documentation and the Supported BusinessObjects Versions page.

Steps

Find unused objects in a document

The video shows where in a Web Intelligence document (query panel, variable library) unused objects are found:

  1. In the MMC Meta Query tab, open IK-007 WebI Unused Objects from the Script Catalog, or paste the query below.
  2. Set the document name and the CMSID of your environment, then run it.
  3. Rows marked NOT USED are query objects that the report does not use.
Used and unused query objects of one document
SELECT wro.foldername || '.' || wro.name AS class_object
     , CASE WHEN used.object_dsobjid IS NULL THEN 'NOT USED' END AS is_used
     , wro.identifier
     , wdp.dataproviderid
     , wdp.universename
     , wd.cmsid
     , wd.id
     , wd.name AS doc_name
     , wro.iscondition
FROM webi1400_documents wd
JOIN webi1400_dataproviders wdp ON wdp.webi1400document_id = wd.id
JOIN webi1400_querynodes wqn ON wqn.webidataprovider_id = wdp.id
JOIN webi1400_resultobjects wro ON wro.webi1400query_id = wqn.id
JOIN documents d ON d.objectid = wd.objectid AND d.cmsid = wd.cmsid
LEFT JOIN webi1400_used_objects used
       ON used.webi1400dataprovider_id = wdp.id AND used.object_dsobjid = wro.identifier
WHERE wd.isdeleted = false
  AND wd.isinstance = false
  AND wd.name = '<document name>'
  AND wd.cmsid = '<cmsid>'
ORDER BY class_object;

Remove the LEFT JOIN and the is_used column to list the full inventory of query objects instead.

Tables used

The query reads the webi1400_* tables, filled by BI 4.x Web Intelligence processing. Environments that use REST-based Web Intelligence processing store the equivalent detail in webi1440_* tables. See MetaMiner Database Schema.

Turn on query stripping

  1. In the CMS Explorer, select the Web Intelligence documents (filter by folder or use the result of your query).
  2. Right-click and choose Modify > Enable Strip query.
  3. For UNX universes, select them and choose Modify > Enable Strip query in the same way.

Image of the CMS Explorer right-click Modify submenu with Enable Strip query highlighted here

../images/use-cases/webi-enable-strip-query.png

Check the effect

  1. Refresh a changed document and compare the refresh time with MM-W20 Query Time and Rows.
  2. To prove the output is unchanged, run a one-sided Report Validation report set before and after the change.

Expected result

The selected documents and universes have query stripping turned on, refreshes retrieve only the objects the reports use, and you have before/after evidence of refresh time and output.

Variations

  • Undo: select the same objects and choose Modify > Disable Strip query.
  • Remove unused objects from the universe itself: see Unused Universe Objects.

Next steps