How to resolve "Error [sapbwbex-3000000]: Cannot filter on output columns in BI query..." error in TIBCO Data Virtualization?

How to resolve "Error [sapbwbex-3000000]: Cannot filter on output columns in BI query..." error in TIBCO Data Virtualization?

book

Article ID: KB0073142

calendar_today

Updated On:

Products Versions
TIBCO Data Virtualization 8.3.x

Description

The SAPBW adapter in TIBCO Data Virtualization cannot filter on output columns in the BI query. Instead, it filters on columns starting with an underscore (_).
==========
Error [sapbwbex-3000000]: Cannot filter on output columns in BI query. Filter on columns starting with an underscore (_) instead.
===========
This article explains the error message received and also explains the nature of the SAPBWBEx query. This also talks about a plausible workaround to get rid of this error.

Environment

All supported environments

Resolution

Note: SAP BW queries are different from relational tables. They are programs that is been called to return data & for these programs to filter the data before it has been received, they need to be written explicitly to do so. Typically, SAP query authors do that by creating variables, which are exposed as "underscore" columns. These are columns that can be filtered on and the filter gets pushed to the SAP side. However, TIBCO Data Virtualization adapters cannot selectively push some parameters and not others by design and resulting in all of the WHERE clauses being sent to the adapter. If these adapters receive filters on columns that can't be pushed down (such as regular columns that do not correspond to variables in the SAP query), it may fail with errors.

Workaround:

The workaround depends on the nature of the BW query. If the BW query contains variables and they're either mandatory or if they are used to push filters, then the below steps can be followed: 

  1. Create a parameterized view to encapsulate the SAP view, where variables are passed down (if required).

  2. Write a regular view on top of it, pass through the variable values to the parameterized view.

  3. The parameterized view acts like a wall that prevents other filters from being pushed to the adapter.

  4. The enclosing view can be filtered now and the filters will be applied by TIBCO Data Virtualization itself instead of being pushed down. 

Below is an example of the above workaround for reference:

(1) Create a view 'SAP View' that gets the data from the SAP tables. Put any filters in the WHERE clause.

SELECT 
    "columns1",
    "columne2",
FROM 
    /shared/Test/bex_filt_issue_2
WHERE
    <condition>
 
(2) Create a Parameterized Query 'PQT' on top of the view 'SAP View'

 PROCEDURE PQT(
     OUT result CURSOR (
         "column1" VARCHAR(70), 
         "column2" VARCHAR(70), 
     )
 )
 BEGIN
     OPEN result FOR 
         SELECT 
             *
         FROM 
             /shared/Test/bex_filt_issue_2;
 END
 
(3) Create a 'Test_View' View on top of 'PQT'

SELECT 
     *
FROM 
 /shared/Test/bex_filt_issue_2/PQT/PQT() PQT
WHERE
     PQT."column1" = '1000/620000' 

Note: The names, 'SAP View' and 'Test_View' used for views and 'PQT' used for a parameterized query are used for examples and are random notation.

Issue/Introduction

This article explains about the nature of SAP BW queries and the meaning of "Filter on columns starting with an underscore (_) instead". This article also suggests plausible workaround to get rid of this error.