How to improve performance with 'Last Snapshot (xxx)' filters on 360Eyes reports

How to improve performance with 'Last Snapshot (xxx)' filters on 360Eyes reports

Symptom

How to improve performance on 'Last Snapshot (xxx)' filters in 360eyes reports?

Environment


  • 360Eyes
  • SAP BusinessObjects Enterprise XI 3.1
  • SAP BusinessObjects Business Intelligence 4.0
  • SAP BusinessObjects Business Intelligence 4.1
  • SAP BusinessObjects Business Intelligence 4.2
  • SAP BusinessObjects Business Intelligence 4.3


Cause

The queries generated by the filters Last Snapshot (xxx) add sub queries that can greatly increase the refresh time of the reports.

Here is an example of a report using Last Snapshot (mono CMS)









Resolution

The general idea for this work around is to separate the filter results from the main query. Then use the results as a filter for the main query

First we need to create a new query that we can name SNAPSHOT_ID.
Using the Snapshot Id object and the previous filter Last Snapshot (mono CMS) or (multi CMS).

See the example below:


Run this query, then we can go back in the main query and replace the Last Snapshot filter by a new filter on Snapshot Id.
This filter should be set to Equal to the Result from another query (select Snapshot Id from SNAPSHOT_ID query).

See the example below:



Keywords
WEBI, SQL, filter

Product

Product or Product version

  • 360Eyes

Languages

  • English 

    • Related Articles

    • How to expand Dataprovider SQL Query column in 360Eyes database

      Symptom In your 360Eyes Webi and Crystal job logs you are seeing data being truncated in EYE_DATAPROVIDER.DATAPROVIDER_SQL_QUERY Environment 360Eyes SAP BusinessObjects Enterprise XI 3.1 SAP BusinessObjects Business Intelligence 4.0 SAP ...

    • Error : "Can't instanciate Webi Factory"

      Symptom When running WEBI jobs, the following error may be encountered: 2014-11-14 15:05:00 +0100 INFO Creating hash for CUID-ShortName/path universe link: done 2014-11-14 15:05:00 +0100 INFO Getting url 2014-11-14 15:05:00 +0100 INFO Found url ...

    • How to install 360Eyes on SQL Server on Azure

      SymptomInstalling 360Eyes using SQL Server on Azure. Environment 360Eyes SAP BusinessObjects Enterprise XI 3.1 SAP BusinessObjects Business Intelligence 4.0 SAP BusinessObjects Business Intelligence 4.1 SAP BusinessObjects Business Intelligence 4.2 ...

    • How to fix the 'WEAK link' issue

      Symptom How to relink a document to a universe when encountering a "weak link" issue? Environment 360Eyes SAP BusinessObjects Enterprise XI 3.1 SAP BusinessObjects Business Intelligence 4.0 SAP BusinessObjects Business Intelligence 4.1 SAP ...

    • How to configure SQL Server with Windows Authentication

      Symptom When trying to configure a SQL Server database using Windows Authentication as part of the 360eyes database configuration, the following error is returned: "The driver is not configured for integrated authentication". Environment 360Eyes ...