Skip to content

Web Intelligence Query Stripping & Data Provider Optimization

Web Intelligence reports, when correctly tuned both from the datasource (e.g. Universe, HANA, OLAP) and presentation layer (report tabs, variables, charts) perspectives, can offer end-users with a dashboard-caliber experience through rich, quick and interactive interfaces with both textual and visual data.

However, this is often not the case - and instead, we hear "Webi takes forever!" or "... the report just never comes up!" from our wonderful business users! 👩‍💼

Some areas where we find objects that are never utilized in a report:

  • Dataprovider objects from a Universe (UNV, UNX) or other (HANA) source
  • Report-level variables never used in a report
  • Alerts, deeply nested variables
  • Charts
  • Special formulas used for visibility rules (section hiding, layout/appearance custom rules)

Minimum required BusinessObjects version

You must be aware that this feature is only compatible with SAP BI4.2 SP3 - which is another excellent reason to upgrade!

Identifying unused objects in your Webi reports

The video below shows where within a Web Intelligence report (query panel, variable library) we might look for unused objects.

Webi Used vs. Unused Objects Listing SQLs

In the snippet below, we can compare the total inventory of universe objects in a report.

SELECT DISTINCT 
wro.foldername||'.'||wro.name class_object
,wro.identifier
,wdp.dataproviderid
,wdp.universename
,wd.cmsid
,wd.name doc_name
,wro.iscondition 
FROM webi1400_documents wd
    INNER JOIN webi1400_dataproviders wdp ON wdp.webi1400document_id = wd.id
    INNER JOIN webi1400_querynodes wqn ON wqn.webidataprovider_id = wdp.id
    INNER JOIN webi1400_resultobjects wro ON wro.webi1400query_id = wqn.id
    INNER JOIN documents d on d.objectid = wd.objectid and d.cmsid=wd.cmsid
WHERE wd.isdeleted = false AND wd.isinstance = false
and wd.name = 'Some Complex and Slow Webi Report'
and wd.cmsid = 'HANA001.CORP.INFOLYTIK.IK'
order by class_object asc

The next snippet provides both USED and UNUSED objects in our Webi report.

SELECT   
wro.foldername||'.'||wro.name class_object
,case when used.object_dsobjid is null then 'NOT USED' else null end is_used
,wro.identifier
,wdp.dataproviderid
,wdp.universename
,wd.cmsid
,wd.id
,wd.name doc_name
,wro.iscondition 
FROM webi1400_documents wd
    INNER JOIN webi1400_dataproviders wdp ON wdp.webi1400document_id = wd.id
    INNER JOIN webi1400_querynodes wqn ON wqn.webidataprovider_id = wdp.id
    INNER JOIN webi1400_resultobjects wro ON wro.webi1400query_id = wqn.id
    INNER JOIN documents d on d.objectid = wd.objectid and d.cmsid=wd.cmsid
    LEFT OUTER 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 = 'Some Complex and Slow Webi Report'
and wd.cmsid = 'HANA001.CORP.INFOLYTIK.IK'
order by class_object asc