Open ODS View based on DataSource where ODP Context is DataSources/Extractors
In the following we discuss Open ODS views using source type DataSource(BW). The BW DataSource supports direct access(this is possible for ODP and SAP HANA source systems) and the ODP Context is DataSources/Extractors. 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(could be also relevant when the query result is not as expected). The situation is a bit similar to an Open ODS View based on a Transformation and DataSource but without the complexity of the transformation(e.g. inverse routines). 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 afterwards.
- 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. In contrast to Open ODS View based on a Transformation and DataSource, the requirement that the field 'Selection' of the DataSource has to be set does not exist. However, it might happen that a DataSource just ignores filters of certain fields.
- 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.
- In case the DataSource has many more fields than requested by the query, assure that the optimization of note 2956656 is implemented.
- Consulting Notes
- 2198480 FAQ: BW Open ODS View - Query Execution
- 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
- 1814452 Large SID restriction on virtual BW InfoCube
- Important Current Coding Correction
- 2956656 750SP20: Memory allocation problems in Queries based on Open ODS View Based on DataSources
Example I: Query with hierarchy node filter and restriction regarding navigation attribute