Data Aging on SAP HANA

Why is the uniqueness check for cold data switched off in SAP HANA per default?

For ABAP applications using the Data Aging Framework, the default is set to “no uniqueness check” by SAP HANA.

Unique constraints for primary keys or unique indexes are normally checked across all partitions in case the partitioning is done over the artificial temperature that is not part of the primary key column of a table. This means that the cold partitions have to be accessed whenever data is inserted or updated.

To prevent a negative impact on performance, a database application can switch off the uniqueness check for cold data by setting an option in the partition specification.

With this relaxation, it is up to the application to either prevent duplicates (e.g. via GUIDs or number ranges) or to cope with duplicates.

If uniqueness checks for cold data are switched off, SAP HANA only ensures unique values in the hot partition. Conflicts between records in hot and cold, between two cold partitions, and between records in the same cold partition, are not prevented on database level in that case.

When to create a new partition for the cold data?

We create a new partition range when data to be aged is not covered by an existing partition range in historical area. A new partition range can also be created when the maximum capacity threshold for an existing partition is reached soon.

Should Aging partitions be on first level or on second level?

Data Aging Partitioning can be done on first level without any second level or used as a second-level below a hash- or range-partition. If there is any risk that the remaining amount of records in “current” partition after Data Aging can approach the two billion records limit in the future, choose a two-level approach with aging being the second level.

Take future volume growth into consideration as well as residence times for Data Aging.

Is it possible to add a different partitioning on top of Data Aging?

It is possible to add a partitioning on top of Data Aging/after having introduced Data Aging. However, the preferred way is to start the other way round, in order to not move around massive amounts of aged data again.

Start with Hash (or Range) on first level, then add the time selection partitioning using the quick-partitioning possibility. Think upfront about the right partition size, as the second level partitioning will split the data further.

How to create partitions for tables participating in Data Aging?

The SAP HANA database offers a special time selection partitioning scheme, also called aging. Time selection or aging allows SAP Business Suite application data to be horizontally partitioned into different temperatures like hot and cold. The partitioning of tables that participate in Data Aging is administered on the ABAP layer (Transaction DAGPTM).

Partitioning needs to be done in each system individually as partitions are not transportable. Alternately, Partition Ranges can be maintained in customizing under SAP Netweaver > Application Server > Basis Services > Data Aging > Maintain Partition Ranges. Partition Objects must be defined to combine several tables that should be partitioned together according to the same schema (Transaction DAGPTC). Usually SAP applications suggest partitioning objects that can be applied.

All participating tables belonging to the data aging objects and enhancements must be assigned to a partitioning object. A table is assigned to exactly one partitioning object and cannot occur in more than one partitioning object. If the assignment of a table to a partitioning object does not fulfill customers’ requirements, they can create customer-specific partitioning groups without making modifications. Partitioning Groups override the assignment of tables to partitioning objects if a partitioning object as well as a partitioning group contains the same table.

The partitioning gets active only after the next Data Aging Run.

What does quick partitioning mean?

Initially all Tables participating in Data Aging have an additional column _DATAAGING, which is the basis for the time selection partitioning. The default value of this column is ‘00000000’.  When the time selection (=RANGE) partitioning is performed on this column, all records will remain in the hot partition – no data has to be moved. The cold partitions (=new time RANGEs) will be added empty one by one – again no data has to be moved during partitioning itself.
Only during the Data Aging Run column _DATAAGING is filled and the aged data will be moved from the hot partition to the cold partition(s).

How and when will the data be moved from the hot partition to the cold partition(s)?

The application logic determines when current data turns historical by using its knowledge about the object’s life cycle. The application logic validates the conditions at the object level from a business point of view, based on the status, execution of existence checks, and verification of cross-object dependencies.
The data will be moved during a Data Aging Run.

To set up an Aging Run several tasks need to be fulfilled upfront:

  • Determining the data: The application-specific runtime class can be used to determine the data for which Data Aging is intended. The SAP application assigns these runtime classes to the relevant Data Aging object so that the runtime class can be called and processed in a Data Aging run.
  • Managing Partitions: To be able to move the data from the HOT partition of the database to the COLD partition(s) according to the specified partitioning objects and partitioning groups, all of the participating tables must be partitioned for Data Aging.  For each system, you need to define the partitions for corresponding tables of a Data Aging object (DAGPTM), this setting is not transportable. If the conditions are not fulfilled, the Data Aging run is not started. There should be at least one cold partition covering todays date and for multiple partitions on one table the intervals can have no gaps.
  • Activating Data Aging Objects: After the partitions have been defined, choose transaction Data Aging Objects (DAGOBJ) to activate the Data Aging object. The system runs through various checks on each table belonging to the Data Aging Object so that the Data Aging object can be used for the run.
  • (specific Settings for Data Aging Objects)
  • Managing Data Aging Groups: Define Data Aging Groups via transaction DAGOBJ -> Goto -> Edit Data Aging Groups and select all Data Aging Objects to be processed in one Group.

For scheduling Data Aging Runs go to transaction DAGRUN and select a Data Aging Group, Maximum Runtime and Start Date/Time to schedule the run. The same transaction can be used to monitor Data Aging Runs as the initial screen shows a list of runs with the details, such as, the Data Aging Groups, Start date/time, Duration, and Job name.
Further details on the Data Aging Run can be found in the SAP Help Portal Data Aging – Data Aging Procedure (Runtime).

How to restrict the memory consumption used by the cold data (paged attributes)?

As of SAP HANA SPS 09 the memory footprint of the Paged Attributes is enhanced. It is possible to configure the amount of memory used by page loadable columns.

  • The parameter for the lower limit is page_loadable_columns_min_size (page_loadable_columns_min_size_rel) which is set to 5% of the effective allocation limit of the indexserver per default.
  • The parameter for the upper limit is page_loadable_columns_limit (page_loadable_columns_limit_rel) which is set to 10% of the effective allocation limit of the indexserver per default.

The memory consumption used by page loadable columns can fluctuate between the two limits. If the upper limit is exceeded, all page loadable columns objects are unloaded until the lower limit is reached again. The default values are quite high. Nevertheless the lower limit should be set to > 0, otherwise performance issues might occur. Still, pages are reported in the memory statistics as part of “Used Memory”. The memory consumed by pages has to be measured separately. This has to be considered when reporting memory statistics.

Known Issues:
If an inverted index is defined on a page loadable column an gradual increase of the allocated paged memory might happen and even exceed the limits defined by the parameters above. (SAP Note 2497016).

How to monitor the memory consumption used by the cold data (paged attributes)?

On table level the monitoring capabilities allow to only show how much data is loaded into memory per partition/per column. As technically Data Aging in SAP HANA is using Paged Attributes for cold partitions only during the access of these data it needs to be loaded to Memory. There is a dedicated Cache area with configurable size, which allows improving the performance of consecutive accesses to same data.

  • The System Views M_MEMORY_OBJECT_DISPOSITIONS and M_MEMORY_OBJECTS are available to globally monitor the memory consumption of the paged attributes.
  • M_MEMORY_OBJECT_DISPOSITIONS shows how much memory is currently consumed by the paged attributes (PAGE_LOADABLE_COLUMNS_OBJECT_SIZE) and how many paged attributes are currently loaded into memory (PAGE_LOADABLE_COLUMNS_OBJECT_COUNT).
  • M_MEMORY_OBJECTS shows how many paged attributes (PAGE_LOADABLE_COLUMNS_LIMIT_SHRINK_COUNT) were unloaded from the memory since the last restart and their size (PAGE_LOADABLE_COLUMNS_LIMIT_SHRINK_SIZE) e.g. due to configured limit reached. This view shows mainly the turnaround of the cold data in memory.

If cold data is accessed, will the partition(s) be loaded to the in-memory part of SAP HANA?

Cold partitions make use of Paged Attributes. While ordinary columns are loaded entirely into memory upon first access, Paged Attributes are loaded page-wise. Ideally only the pages that hold the requested rows are being loaded.

In the context of data aging/cold partitions there are only a few (release dependend) constraints which columns cannot be paged:

  • Some data types (LOB, BLOB, CLOB, TEXT, GEOMETRY, POINT)
  • Some internal columns ($RowID$, $Udiv$, Shadow columns for text indexes)

Can the customer define own Data Aging Objects?

Mainly the SAP application suggests and delivers Data Aging Objects and Partition Objects. However,  there is a list of Data Aging objects and enhancements provided by the individual SAP application in the transaction Data Aging Objects (DAGOBJ). Customers can implement their own Aging-Objects only for custom tables. The Data Aging Framework provides APIs to implement-Aging Objects. More detailed information can be found in the Data Aging Development Guide.

What Data Aging Objects are currently available?

In SAP Business Suite on HANA only Basis Documents are available for Data Aging like e.g. Application Log, IDocs, and Workflow.

In SAP S/4HANA Basis and Application Documents are available for Data Aging like e.g. FI Document, Material Document, and Billing Document.

A complete list of all available objects can be found in the SAP Help Portal Data Aging – Available Data Aging Objects and under ABAP transaction DAGOBJ.

On which Technologies is Data Aging based in SAP HANA?

Data Aging in SAP HANA uses:

  • Time selection partitioning: The column, _DATAAGING, is used to split tables into one hotand some cold partitions
  • No uniqueness checks: The uniqueness checks on the cold data is switched off (the application will enforce uniqueness) to improve performance
  • Page-loadable columns/Paged attributes: The memory management of an attribute is based on pages so that partitions can partially be loaded into memory (used to store data in the cold partitions)

How does Data Aging in SAP S/4HANA function technically?

The data aging mechanism for ABAP applications is based on a new data aging framework provided by SAP NetWeaver ABAP. ABAP developers use this framework to specify the data aging objects that are aged as one unit, to identify the involved tables, and to implement the logic for determining the data temperature. The data temperature is set via an additional data temperature column ‘_DATAAGING’ (type DATA_TEMPERATURE with ABAP date format “YYYYMMDD”), which is added to all participating tables.

The data temperature can be used to horizontally partition the application data with time selection partitioning (a.k.a. “aging”) on the column ‘_DATAAGING’ for optimizing resource consumption and performance. Only one partition contains the hot data (represented by the value “00000000”) and the other partition(s) contain cold data with different data temperature ranges.

By default, only hot data is accessed. As the hot data is located in a separate partition, SAP HANA should only load that partition into memory during normal operation. If required, ABAP developers can set the data temperature context to switch between accessing only hot data, all data, and all data above a specified data temperature.

The SAP HANA-specific database shared library (DBSL) in the ABAP server adds a corresponding clause to the SQL statements that are sent to SAP HANA. By adding the clause WITH RANGE RESTRICTION (‘CURRENT’) to a SQL statement, SAP HANA restricts the operation to the hot data partition only.

Instead of ‘CURRENT’ also a concrete value can be specified. This restricts the operation to all partitions with data temperatures above the specified value. The clause WITH RANGE RESTRICTION (‘20100101’), for example, tells SAP HANA to search the hot partition and all cold partitions that contain values greater or equal than ‘20100101’. Range restriction can be applied to SELECT, UPDATE, UPSERT, DELETE statements and to procedure calls.

All other clients that want to access these Data Aging tables with proper filtering, the same generic syntax extension may be used.

The application knows which business objects are closed and may hence be moved to cold partitions. Therefore the application actively sets values in this column to a date to indicate that the object is closed and the row shall be moved to the Cold partition(s) during an Aging Run. Since the table is partitioned by the temperature column, the rows are automatically moved then to a cold partition. The move influences the visibility of the data and its accessibility. Several configuration steps/prerequisites need to be administered to be able to execute a Data Aging Run (-> “How and when will the data be moved from the hot partition to the cold partition(s)?”).

What is cold data?

Historical data is data that is not used for day-to day-business transactions. By default, historical data is not visible to ABAP applications. It is no longer updated from a business point of view. The application logic determines when current data turns historical by using its knowledge about the object’s lifecycle. The application logic validates the conditions at object level from a business point of view, based on the status, executing existence checks, and verifying cross object dependencies. Historical data is stored in historical area.

Examples of historical data:

  • Cleared FI items posted two years prior to the current fiscal year
  • Material documents one period older than the current closed period
  • Processed IDocs, and application logs after X number of days.

What is hot data?

Current data is the data relevant to the operations of application objects, needed in the day-to day-business transactions.

The application logic determines when current data turns historical by using its knowledge about the object’s life cycle. The application logic validates the conditions at the object level from a business point of view, based on the status, execution of existence checks, and verification of cross-object dependencies. Current data is stored in the current area.

Examples of current data:

  • Open FI items, items cleared only a few months ago
  • Undelivered purchase orders, sales documents of sales cycle that is still in progress
  • Documents for an ongoing project
  • IDocs that need to be processed.

 

How to include Data Aging statistics into the Early Watch Alert (EWA)?

A chapter on data aging was designed, which displays the activation status and the ratio between current (hot) and historical (cold) data.

This ratio will give an indication in which extent data aging is implemented.

The prerequisite is having software component ST-PI with Support Package 4 or higher. For earlier support packages it is required to implement SAP Note 2237911.

What needs to be considered during sizing?

The potential on how much the SAP HANA memory footprint can be reduced by the usage of Data Aging is estimated by the sizing report available in SAP Note 1872170 – Business Suite on HANA and S/4HANA sizing report.

Are there any prerequisites for a table to participate in Data Aging in SAP S/4HANA?

A table can participate in Data Aging in case it is part of one or many Data Aging Objects (Transaction DAGOBJ) and it is part in one Partition Object (Transaction DAGPTC).

Additionally it has to have the additional column _DATAAGING, which is the basis for the time selection partitioning.

This column is automatically added once the table is assigned for Data Aging.

Are there any prerequisites to use Data Aging in SAP S/4HANA?

Data Aging in only available in SAP Business Suite on HANA and S/4HANA Applications (starting with SAP NetWeaver 7.4 SP05). To apply Data Aging in SAP HANA there are only certain prerequisites on ABAP side:

  • The SAP application must provide Data Aging Objects
  • The Profile Parameter abap/data_aging is set to ‘on’
  • The Data Aging business function (DAAG_DATA_AGING) is switched on
  • Relevant authorizations for Data Aging activities are provided

SAP HANA is already ready to handle Data Aging without any further configuration. Further prerequisites are summarized in the SAP Help Portal Data Aging – Prerequisites for Data Aging.

What is the difference between Archiving and Data Aging in SAP S/4HANA?

In SAP HANA, Data Aging is different to Archiving in the sense that cold data is still kept within the SAP HANA Database and remains accessible via SQL in the very same table as the hot data (yet in another partition). Whereas archived data – strictly read-only – is written to an archive file and deleted from the database and needs additional access paths (address information or archive indexes) to be read. Aging targets the main memory footprint reduction whereas archiving is the basis for ILM, covering the full life cycle up to the destruction of information.

Data Aging offers you the option of moving operationally less relevant data within a database so as to gain more working memory. You use the relevant SAP applications, particularly data aging objects to move data from the current area to the historical area. The move influences the visibility when data is accessed. This also means that you can perform queries of large amounts of data in current area in a shorter time. To be able to apply Data Aging to your data, you need to fulfill certain requirements regarding the database and the application.

Data Archiving is used to remove data from the database and store it outside in a consistent and secure manner. The archived data is stored in a file system and from there can be moved to other, more cost-efficient and long-term storage system.

What are the benefits of Data Aging in SAP HANA within SAP S/4HANA?

The goal of aging is to both reduce the main memory footprint and speed up database queries by only keeping operationally relevant (hot) data in main memory. In contrast to this, cold data is placed primarily on (less expensive but usually slower) secondary storage and accessible via SQL on request.

What is Data Aging in SAP S/4HANA?

Data Aging is a Suite-tailored data management concept for reducing the SAP HANA memory footprint, based on a Data Aging Framework provided by SAP NetWeaver ABAP.

Data Aging is available for SAP Business Suite on HANA and SAP S/4HANA applications and offers the option of moving large amounts of data within SAP HANA in order to gain more working memory.

Data Aging differentiates between operationally relevant data (Hot/Current), and data that is no longer accessed during normal operation (Cold/Historical). The data temperature can be used to horizontally partition the tables (taking part in Data Aging) for optimizing resource consumption and performance – moving data between the different partitions (i.e. from hot to cold partitions).

Hot data is stored within SAP HANA main memory. Besides cold data stays primarily stored on disk, but remains accessible via SQL on request.

Privacy & Cookie Notice  | © Copyright 2018 Proceed Group | Web design by Union 10 Design