Dynamic tiering and Extended tables

What is dynamic tiering?

Let me begin by telling you what is dynamic tiering.

SAP HANA dynamic tiering is a native big data solution for SAP HANA. Dynamic tiering adds smart, disk-based extended storage to your SAP HANA database. Dynamic tiering enhances SAP HANA with large volume, warm data management capability. When you use SAP HANA to place hot data in SAP HANA in-memory tables, and warm data in extended tables, highest value data remains in memory, and cooler less-valuable data is saved to the extended store. The extended store can reduce the size of your in-memory database.

The dynamic tiering option in SAP HANA SPS 09 gives the ability to keep the data in either memory or on the disk in a columnar format. Data is not duplicated. Dynamic tiering option helps users to choose memory for hot data and disk for warm data, helping to strike the right price/performance balance. To do so, you define the table as “extended table” using an SQL CREATE statement. These tables are like any other SAP HANA tables except for the fact that they are created on the disk and not in memory.

Extended tables :

SAP HANA dynamic tiering adds the SAP HANA dynamic tiering service (esserver) to the SAP HANA system. We use this service to create the extended store and extended tables. Extended tables behave like all other SAP HANA tables, but their data resides in the disk-based extended store. The extended tables concept relates to warm data. Since warm data is not constantly accessed, this data does need to occupy space in the main memory of SAP HANA. Extended tables are tables managed by SAP HANA. Logically they are located in the SAP HANA database catalog and can be used as if they were persistent SAP HANA tables. These tables are physically located in a disk-based data storage however, which has been integrated into the SAP HANA system. The user sees the entire system as one single database.

Now enough theory lets get down to actually seeing how to convert in-memory data to extended tables.

We need the access to HANA studio for this purpose.

1. First we need to check that we have a table which is an in memory table. ( use HANA studio for this )

Right click on the table NATION_DT, which is now an in-memory table, and select Use Extended Storage

2. In the “Use Extended Storage” pop up window, keep all the options as default. Click the Movebutton to proceed.

3. Once the conversion is complete, a pop up window will appear verifying that the change has been made, click OK to close the window.

4. Verify the table has been correctly converted. You should see that the NATION_DT table has the “EXTENDED” attribute and is now an extended table.

So we have seen how to convert an in-memory table to an extended table.✌

PS: I took screens from SAP source because I am not allowed to take screens from my system.

Now lets see how to convert in memory table to extended table using SQL statement

Prerequisites :

1. The extended store exists.

2. If converting a HANA table to a delta-enabled extended store table, extended storage is delta-enabled.

3. Requires the EXTENDED STORAGE ADMIN system privilege.

SQL statements :

>> ALTER TABLE t3 NOT USING EXTENDED STORAGE; // without using delta

>> ALTER TABLE t4 USING EXTENDED STORAGE ENABLE DELTA; // using delta

Little information on Hot data and Warm data:

HOT data:

Data that is accessed very often, for example, for reporting or for processes in Data Warehouse Management. (Queries for Info Cubes, Data Store objects)

WARM data:

This data is no longer or rarely accessed. (Write-optimized Data Store objects of the corporate memory, or Persistent Staging Areas or write-optimized Data Store objects of the acquisition layer).

COLD data:

Data of a BW system that is no longer required, and that can be or was saved using Near-line Storage.

Advantages:

To maximize native query performance, query optimizations ship the query operations to either the hot store or extended storage. Query operations against extended storage data are pushed down to the dynamic tiering worker, minimizing the load on the SAP HANA host.

◉ It allows you to offload older, less frequently accessed data to an integrated disk tier.

◉ It lets you access the data in the disk tier with excellent performance.

◉ It lowers the total cost of ownership (TCO) of your SAP HANA system significantly.