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.
- 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)
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.
Ncum Key Figure 0D_FC_N_STK
We focus on plant 7001 and the products FC001 - FC005.
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
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
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.