In the following we discuss Open ODS views using source type Transformations. The transformation is based on a BW DataSource that supports direct access(this is possible for ODP and SAP HANA source systems). In particular we take a closer look at the transfer of query filters to the DataSource which is often crucial regarding performance and memory consumption(see also KBA 2913234). The situation is similar to a 'classic' virtual InfoCube based on a DTP for direct access, see VC: DTP based on DataSource where you can find, among others, a flow chart displaying the most important processing layers of queries based on such virtual InfoProviders. This might also help to understand better the situation we have for Open ODS Views of the type discussed in this page. Check out the examples below where you can also find a detailed technical guidance on how to analyze such queries.
- Queries defined on a Open ODS View based on a DataSource(where data is retrieved during query runtime) are not intended to transfer mass data. The number of records to be transferred from the source system should be less than about 10000 rows(see note 2949923). Keep also in mind that there is no package wise reading for 'direct access' and the complete result set requested needs to be processed at once. The DataSource should not contain more that 100 fields since internal tables needed during query execution would then allocate much memory when the data set gets big. See note 2949923.
- If query restrictions/filters cannot be transferred to the DataSource, much more data as necessary has to be requested and processed in the transformation(in order to guarantee correct query results). Excess data records are filtered out in the Analytic Engine after the transformation.
- The following (typical)situations may mean that selection conditions for a characteristic cannot be forwarded to the source:
- The corresponding field of the DataSource does not support filters. Hence, check which fields of the Datasource can handle filters and define the DataSource accordingly(Field 'Selection', more precisely, field SELDIRECT of table RSDSSEGFD, see Example I below).
- The transformation consists of an expert routine or it contains a start or end routine. In such a case an 'inverse routine' is necessary(if possible), see note 1486659.
- The characteristic is filled by a rule of type 'Formula', 'Routine' or 'Reading Master Data'. See note 967798.
- In case the query uses hierarchy node restrictions, the OLAP Engine needs to 'resolve' the nodes into all corresponding leaves since such kind of filters cannot be handled by the DataSource. Note that if there are many leaves(e.g. more than 20000), the handling of these selections might lead to a performance issue. In contrast to a Virtual Cube(see VC: DTP based on DataSource), the parameter BW_EQSID_CONVERSION_LIMIT(see note 1814452) cannot be used to define a limit.
- In case filters regarding navigation attributes are used, the system needs to convert this filter into the corresponding set of values of the basis characteristic(since the DataSource doesn't 'know' the concept of navigation attributes). There is a limit of 5000 values which at most can be handed over to the DataSource.
- 2198480 FAQ: BW Open ODS View - Query Execution
- 1486659 Inverse Routines in Transformation for Virtual Providers , SPO,....
- 967798 Selection conditions are ignored by the VirtualProvider
- 1814452 Large SID restriction on virtual BW InfoCube
- 2913234 Open ODS View based on Transformation/DataSource: BW Query Performance/Memory Issues
- 2949923 Consulting: Memory issues during execution of a Query based on Open ODS View based on a BW DataSource/Transformation
- Example I: Only one field of DataSource supports selections.
- Example II: Transformation has a start routine.
- Example III: Example II with additional inverse start routine
- Example IV: DataSource is extracting via SAP HANA SDA.