Derivation in HANA

[Former Member]

Hi

I am having an issue with a Characteristic Relationship with derivation that I created. I have implemented the interface IF_RSPLS_CR_EXIT_HDB in a class I derived from CL_RSPLS_CR_EXIT_BASE Characteristic Relationships: Exit .

I added the following code to the GET_SQLSCRIPT_INFO method:

clear: e_parameter_name,

        e_procedure_name_check,

        e_procedure_name_derive,

        e_procedure_name_create.

      e_procedure_name_derive = 'Z_CR_SA_001_SCRIPT'.

     e_parameter_name = ''.

The script exists in SQL. When running a planning sequence, the code above is called, however, continuing to debug I get to the following:

S_GET_SQL_SCRIPT_CHAR_REL (LCL_SQL_SCRIPT_SERVICE)

case i_script_category.       

i_script_category's value is CHK for Check not Derive, so the procedure name is never correctly assigned. is there anything I need to do in my class/characteristic relationship setup to change this?

L.

[gregor_dieckmann · Accepted solution]

Hi,

I recommend to implement SQL Script via AMDP in your exit class implementation. So you have an AMDP method SQL_SCR_DERIVE in your exit class 'ZCL_MY_CR_EXIT', so the method SQL_SCRIPT_INFO has to return

e_procedure_name_derive = 'ZCL_MY_CR_EXIT=>SQL_SCR_DERIVE'.

Observe, that it depends on the context when the system calls 'DERIVE', 'CHECK' and 'CREATE':

- DERIVE is called when at least one target field is not in the aggregation level and all source fields are in the aggregation level; this call happens when the system writes data into the delta or after-image buffer. In addition DERIVE might be called in new lines (input-ready query)

- CHECK is called when all fields of the relation are known; this happens usually in input-ready queries (depending on the query view) and in planning functions

- CREATE is called in some planning function implementations (e.g. COPY) and in input-ready queries (F4 help and 'access mode for result values' based on characteristic relationships)

Don't forget to have ABAP implementations as well since e.g. CHECK is called e.g. for cells in the result set of a query; this is implemented in ABAP and thus CHECK also has to be implemented in ABAP.

Regards,

Gregor

[Former Member · Accepted solution]

Hi Gregor,

So if I have an input ready query and I adjust an existing record, that will revert to the DERIVE in ABAP? For example, I have an aggregation level that I edit records on an aggregated level that will be top-down distributed later on, editing those records will call DERIVE in ABAP and not SQL?

[gregor_dieckmann · Accepted solution]

Hi Lambertus,

whether DERIVE or other methods of a characteristic relationship are executed in ABAP or directly on HANA depends on the 'layer'. One idea of SQL Script or more general HANA is to bring the algorithm to the data and not the data to the algorithm. This makes sense in general if a significant amount of data has to be processed.

On the other hand running a planning function and/or an input-ready query involves some software layers:

1 read data from DB and aggregate/calculate: this is usually done using some kind of flat records format; even here there are some logical layers, e.g. InfoCube, MultiProvider, Aggregation Level

2 BW-IP data buffers for user session data, delta and after-image buffer and the plan buffer (the OLAP cache of a flat technical query)

3 Planning function execution: flat records using the characteristics and key figures of an aggregation level

4 Input-ready query: this includes many logical views and calculation layers depending on the query definition (aggregation, calculation, list geometry, hierarchy usage); the output format of a query is a result set, this is a grid like format, not flat records. The 'objects' here are 'cells', and the cells are not just flat records, e.g. restricted key figures, formulas and may have any kind of aggregation even with respect to the aggregation level itself.

The layers 1 - 3 are a good choice for HANA execution; layer 4 usually is not a good choice for HANA execution since the result set might be big, represents complex objects and the algorithm to handle e.g. all kinds of consistency checks exist already in ABAP. In other words, data and the algorithm to process the data are already on the same layer. To reimplement this e.g. via SQL Script and to copy the result set to HANA to be able to access all the complex cells via SQL Script and to copy the result of the checks back is just overhead.

Back to your question:

Characteristic relationships play a role in layers 2 - 4 and we execute the corresponding methods in HANA or ABAP when it makes sense:

- HANA in layers 2 - 3 and ABAP in layer 4

As an example DERIVE is executed in HANA via SQL Script (if such a method is implemented) when data are written into the delta or after-image buffer. But DERIVE may also be called in new lines in an input-ready query, but then in ABAP.

There is also a HANA implementation of disaggregation in the query since here really mass data may be processed: the visible result set may be small but the data to be read for disaggregation may be some order of magnitudes bigger. The result of the disaggregation is send the the delta or after-image buffer in HANA and also DERIVATION in on HANA.

In general, we always have an ABAP implementation as a fallback. Cf. also note 1956085.

Regards,

Gregor

[Former Member · Accepted solution]

Great answer, thanks.