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:
Michael Jackson |
15488.0 |
7491407.5 |
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]