Registration

Dear SAP Community Member,
In order to fully benefit from what the SAP Community has to offer, please register at:
http://scn.sap.com
Thank you,
The SAP Community team.
Skip to end of metadata
Go to start of metadata

Introduction

It is possible to use the feature  'Query as InfoProvider'(QPROV) for queries with with non-cumulative key figures and then load the query result via a DTP into an InfoProvider. This InfoProvider(e.g. ADSO) does not contain non-cumulative(ncum) key figures, so, the ncum key figure of the QPROV is mapped to a 'normal' cumulative key figure. You need to be aware of the fact that the data in the target cannot be aggregated over time since the corresponding key figure isn't a ncum key figure. A query would just calculate the sum which is obviously incorrect for stock values. Hence, a proper query design is necessary. If the target ADSO only contains e.g. all stock values for e certain date(snapshot, see example below), the issue regarding aggregation over time can already be avoided on provider level.

Examples
  • Snapshot Example: Query retrieves the data for a certain/fixed date and only contains one ncum key figure, the target of the DTP is a ADSO. Since the DTP has no semantic group defined, the data is transferred in one package
  • Example where DTP uses Semantic Grouping: data is loaded in packages(see also Semantic Grouping in DTP)

Snapshot Example

Our sample query is based on an ADSO with non-cumulative key figures(type Inventory). We activate the setting 'Query as InfoProvider' in RSRT and use the QueryProvider in a DTP in order to load a 'snapshot' of the stock values at a fixed day into another ADSO.

Query

Ncum Key Figure 0D_FC_N_STK

Query Result

We focus on plant 7001 and the products FC001 - FC005.

Checking QueryProvider 

When the flag 'Query is used as Infoprovider' is active(see screenshot above), it is possible the check the content of this special kind of provider by using transaction LISTCUBE. The technical name of such a provider is @Q'QUERYNAME':

So, Listcube displays the same values as the Query.

In general, it is also possible to use the so called adhoc query to check the content. However, in case the Queryprovider is based on a ncum query, the processing of ncum key figures is not supported(they are removed during runtime). Hence, since our query only contains one ncum key figure, no data is displayed:

DTP and Transformation

The package size 10000 is defined in the DTP. However, as explained in the wiki page Packaging, this is ignored when there is no semantic group defined(see Semantic Grouping):

Checking Data in Target ADSO

Data is as expected.

Example where DTP uses Semantic Grouping

We check now another query based on the same provider and where the result set contains about 1.6 million records

Query Result

DTP uses Semantic Grouping

The package size is 20.000. The meaning of the pack level is explained in Packaging and Semantic Grouping. As mentioned above, when Semantic Grouping is used the system also takes into account the package size defined for the DTP. In our example we have 82 packages with about 20000 records each:

For each package a separate query is executed and each sub-query contains a set of about 12 plants as a filter restriction.

If the query only has 0plant in the drilldown, we can see that there are 1000 different plants.

  • No labels