Query Delegation to SAP BW¶

The MDX API of SAP BW has certain limitations that limit the conditions that Virtual DataPort can push down to SAP BW. These restrictions do not apply to other multidimensional databases such as Oracle Essbase, Mondrian or Microsoft SQL Server AS. As we will explain, when the “Include leaf levels of hierarchies only” option is enabled, these restrictions do not apply to SAP BW either so we recommend enabling this option.

This section explains the restrictions of the MDX API and how they are removed when enabling the “Include leaf levels…” option.

To illustrate the problem, let us suppose that a certain query has the condition srep_level03 = 'Michael Jackson' over the base view created in the section Creating a Multidimensional Base Views Over a Multidimensional Data Source, with the option “Include leaf levels…” disabled. If the condition is pushed down to SAP BW, the result set returned by the SAP BW MDX API will only include the data of the matching members corresponding to the specified level (in this case, srep_level03):

These result returned by SAP only contains these data:

Nevertheless, to create complete rows of the base view, Virtual DataPort also needs the data of the other levels of the matching members (in this case, srep_level01 and srep_level02). To obtain this information, the Server would need to perform an additional request to SAP BW for each matching member.

Since that could be very costly, Virtual DataPort only pushes down a condition to SAP BW when the condition of the query includes equality conditions for all the ancestor levels of the lower level used in the query. For instance, the following query would be entirely pushed-down to SAP BW:

SELECT *
FROM sapbw_view
WHERE srep_level01 = 'Midwest' and srep_level02 = 'Dallas' and
srep_level03 = 'Michael Jackson'

In this example, the condition includes the values of the ancestor levels of “Michael Jackson”. Therefore, Virtual DataPort can use them to complete the result set returned by the MDX query executed in SAP BW.

Note that Virtual DataPort does not require the condition to specify values for all the levels of the hierarchy. The restriction is that if a certain level appears in the condition, then all its ancestors should also appear. For example, the condition of the following query can be pushed down to SAP BW because, although the condition does not include all the levels of the hierarchy, it includes a condition for the ancestors of the level srep_level02.

SELECT *
FROM sapbw_view
WHERE srep_level01 = 'Midwest' and srep_level02 = 'Dallas'

Yet another example of a query that is entirely pushed down to SAP BW:

SELECT *
FROM sapbw_view
where srep_level01 = 'Midwest'

Query Delegation with the “Include Leaf Levels of Hierarchies Only” Option Enabled¶

When the “Include leaf levels…” option is enabled, the restrictions mentioned in the previous section disappear and Virtual DataPort can push down to SAP BW more complex conditions.

Any boolean condition over any field using the operators =, <>, <, <=, >, >= and like will be pushed down to SAP BW.

The restrictions discussed in the previous section disappear since Virtual DataPort does not need to complete the rows returned by SAP BW with data of the ancestor levels.

To illustrate the additional push down capabilities, we will create a new base view with the “Include leaf levels…” option enabled. For this example, we will also select the name attribute of the dimension (see the following figure):

Creating a SAP BW (BAPI) base view with “Include Member Keys” enabled¶

As the “Include leaf levels…” check box is selected, the schema of this view only has one field for the leaf level of the selected hierarchy, one field for the selected attribute and one field for each measure.

Virtual DataPort can now push down more operators (the ones previously indicated) to SAP BW. In addition, the operators can be delegated when applied to measures and attributes.

Virtual DataPort can entirely delegate queries like the following to SAP BW:

SELECT *
FROM sapbw_leafs_view
WHERE srep_level03 like ' S' AND sales_quantity < 500 AND name_attr <> 'Lisa Simpson'

The MDX delegated for this query is the following:

SELECT NON EMPTY Filter (
  Filter (
    [IO_SREP IO_SREP_HIER].[LEVEL02].Members,
    instr ( [IO_SREP IO_SREP_HIER].CurrentMember.Properties ( "Name" ), " S" ) > 0 And [IO_SREP].[1IO_SREP] <> "Lisa Simpson"
  ),
  [Measures].[IO_QUAN] < 500.0
) Dimension Properties [IO_SREP].[1IO_SREP] ON ROWS,
{ [Measures].[IO_QUAN], [Measures].[IO_REV] } ON COLUMNS
FROM [$IC_DEMOBC]