Kontakt

Ansicht
Dokumentation

ABENHANA_DATA_AGING_DRULS - HANA DATA AGING DRULS

ABENHANA_DATA_AGING_DRULS - HANA DATA AGING DRULS

CL_GUI_FRONTEND_SERVICES - Frontend Services   General Data in Customer Master  
This documentation is copyright by SAP AG.
SAP E-Book

Dependency Rules for Optimization of Access to Outdated Data

Dependency rules are SAP HANA database objects that can represent dependencies between semantic columns and technical columns in a database table. The optimizer can then use these dependencies to create extended selection conditions when the table is read.

In database tables with active data aging using a temperature column, dependency rules can be used to optimize access to outdated data. In this case, a dependency rule joins the temperature column of a database table with regular date columns in the table.

Basics of Optimization

In tables with a temperature column, only the current data is located in the main memory of the SAP HANA database and outdated data is moved to other partitions. By default, the AS ABAP database interface accesses current data only. The relationship between the actual data and the partition limits is defined in data aging runs and is made transparent for application programming. This means that, when outdated data is to be accessed, the data in all partitions must first be loaded to the main memory of the SAP HANA database, which can have a negative effect on performance. Outdated data can be accessed as follows:

  • Disabling data aging in the database interface using the profile parameter abap/data_aging.

A dependency rule can be used to add a selection condition for the temperature column to an access of this type. This restricts the temperatures to those that are in the same rows as the data that is actually requested. This means that the SAP HANA database only has to load those partitions to its main memory that contain the required data.

It cannot be guaranteed, however, that the SAP HANA database optimizer actually creates the additional selection condition for the temperature column. This is why this condition must not modify the result set of the original query. This is technically possible, but it produces undefined behavior in cases where the SAP HANA database optimizer responds differently depending how a query is formulated.

The following sections demonstrate how dependency rules for optimizing access to outdated data can be defined.

Filter Dependency Rules for Data Aging

The temperature column has the following important properties for filter dependency rules:

  • The temperature column has the data type DATS.
  • For current data, the temperature column has the initial value 00000000.
  • In the case of outdated data, the temperature column contains a date value created in the data aging run.

This means that filter dependency rules can be defined as follows:

  • For access to outdated data, filter dependency rules can derive additional selection conditions. These conditions contain comparisons for the temperature column, restricting it to the same rows as the selection conditions for the associated date values.
  • If current data is required together with outdated data, filter dependency rules must be defined in such a way that the selection condition restricts the temperature column to the required date values without excluding the current data. In this case, the additional selection condition must contain a relational expression dbtab._dataaging = '00000000' joined using OR.

Note

The actual design of filter dependency rules for optimizing access to outdated data depends on the layout of the database tables and the rules applied in the data aging run.

Example

In the following CDS view, data aging is disabled for the database table DAAG_SFLIGHT using the annotation@DataAging.noAgingRestriction:true.

When this view is accessed using , both outdated and current data is read:

To avoid loading all partitions, a filter dependency rule can be defined as follows, assuming that the temperature column _DATAAGING for outdated data has the same value as the column FLDATE.

DEFINE FILTER DEPENDENCY RULE demo_daag_sflight_rule
  ON daag_sflight
    IF { daag_sflight.fldate >= $1 }
      THEN { daag_sflight._dataaging >= $1 OR
             daag_sflight._dataaging =  '00000000' }

The SAP HANA database optimizer then adds the following additional selection condition to the WHERE condition of the shown SELECT statement implicitly:

AND daag_sflight~_dataaging >= '20160101'
    OR  daag_sflight~_dataaging =  '00000000'

This does not affect the result set of the query.

Join Dependency Rules for Data Aging

If multiple database tables are joined together in a relational model, data aging must be respected for all these tables. Rows in different database tables that have the same date are usually joined using join expressions. These joins can be in , Native SQL, or in views.

Join dependency rules can be defined to optimize access to outdated data for database tables joined like this. As in filter dependency rules, the actual design of the data aging must be known as well as how the tables depend on each other.

Example

In the following CDS view, data aging is disabled for the database tables DAAG_SFLIGHT and DAAG_SBOOK using the annotation@DataAging.noAgingRestriction:true.

@AbapCatalog.sqlViewName: 'DEMOCDSDAAGSFLBK'
@DataAging.noAgingRestriction:true
DEFINE VIEW demo_cds_daag_sflight_sbook AS
  SELECT FROM daag_sflight
     INNER JOIN daag_sbook
       ON daag_sflight.carrid = daag_sbook.carrid AND
          daag_sflight.connid = daag_sbook.connid AND
          daag_sflight.fldate = daag_sbook.fldate
           { daag_sflight.carrid,
             daag_sflight.connid,
             daag_sflight.fldate,
             daag_sbook.bookid,
             daag_sbook.customid };

When this view is accessed using , both outdated and current data is read:

SELECT *
       FROM demo_cds_daag_sflight_sbook
       WHERE fldate >= '20160101'
       INTO TABLE @DATA(result).

To avoid loading all partitions, a join dependency rule can be defined as follows, in addition to the filter dependency rules in the previous example. Here, the assumption is made that the temperature column _DATAAGING for outdated data in both tables has the same value as the column FLDATE.

DEFINE JOIN DEPENDENCY RULE demo_daag_sflight_sbook_rule
  ON daag_sflight, daag_sbook
    IF { daag_sflight.mandt  = daag_sbook.mandt  AND
         daag_sflight.carrid = daag_sbook.carrid AND
         daag_sflight.connid = daag_sbook.connid AND
         daag_sflight.fldate = daag_sbook.fldate }
      THEN { daag_sflight._dataaging = daag_sbook._dataaging }

The SAP HANA database optimizer then adds the following additional selection condition to the ON condition of the join expression of the CDS view implicitly:

AND daag_sflight._dataaging = daag_sbook._dataaging

The following additional selection conditions from the filter condition are also added to the WHERE condition of the shown SELECT statement:

AND daag_sflight~_dataaging >= '20160101'
AND daag_sbook~_dataaging >= '20160101'
OR  daag_sflight~_dataaging = '00000000'
OR  daag_sbook~_dataaging =  '00000000'

This does not modify the result set of the query.






CL_GUI_FRONTEND_SERVICES - Frontend Services   Vendor Master (General Section)  
This documentation is copyright by SAP AG.

Length: 11017 Date: 20240423 Time: 121720     sap01-206 ( 130 ms )