Using semantic grouping in a DTP based on a QueryProvider is different from the known concept Semantic groups in DTP for other sources. Data packages returned by the OLAP processor/analytic engine are not sorted as known for data returned by other databases. See QPROV Packaging.
However, to control that only data for certain characteristics values are in one package or to trigger a packaging for dealing with memory/performance issues, the semantic grouping has been adjusted to work for Query Providers.
The following notes have to be implemented in your system to have the behaviour described here:
- 2224882 Use of semantic group for extraction of data from QueryProvider
- 2165017 DTP: Use of semantic groups during extraction from QueryProviders
Restriction (see SAP note 2165017)
In general, QueryProviders cannot return sorted data, and therefore cannot serve a semantic group. The data is returned in packages, but not grouped, depending on the query. With this correction, the first field of the semantic group is used to split the extraction into several disjunct queries, which guarantees that all data for the values of the first field are returned in a package. Therefore it is recommended to flag on only one field for Semantic Grouping.
Not all characteristics are suitable for such a semantic partitioning, check here for unsupported characteristics.
Hence, in order to load the data in packages, you need to select one proper characteristic as semantic group. However, this doesn't yet guarantee that the query is split into sub-queries(which e.g. could avoid a memory dump). Whether this can be done depends on the complexity of the query. So, basically we have the following two cases(provided semantic grouping is used):
- Query allows packaging(pack level 3): the field of the semantic group is used to split the extraction into several disjunct sub-queries. The result sets of the sub-queries is stored in an internal table. Once the number of records in this table has exceeded the package size(defined in the DTP), the package is loaded into the target provider.
- Query does not allow packaging(pack level 5): in this case all the data is fetched in one go from the QueryProvider and stored in an internal table. Afterwards the data is retrieved in packages from this table and loaded into the target provider. The packages again only contain records according the definition of the semantic grouping.
Examples were pack level 3 or 5 is chosen by the system, respectively:
- Pack Level 3
- Query with a key figure with currency or unit conversion
- Query with constant selection
- Query with commutative(see note 1903468) formulas like KF*C, KF/C and (KF1 + KF2)
- Query with a key figure with exception aggregation(only one reference char. and e.g. not AVG)
- Pack Level 5
- Query with formula with boolean operators
- Query with a key figure with exception aggregation Average
- Query with formula exception aggregation(FAGGR)
- Query with formulas like KF1/KF2, KF1*KF2 and C(constant)
This pack level displayed in the DTP refers to the entire query and will be checked again during DTP/QPROV runtime. For QueryProviders only 3 and 5 are allowed.
The following examples will discuss this in more detail.
- Example I: Pack Level 3 compared with Pack Level 5
- Example II: Details to Pack Level
- Example III: Semantic Grouping is used and the Package Size is Varied.
- Example VI: QPROV: Example where Query contains Non-Cumulative Key Figures