Skip to Content
Product Information
Author's profile photo Abby Barry

Store More with SPS04

About the authors

Abby Barry is a Platform and Technologies Solution Engineer with experience in database, data management, and analytics solutions. She has been at SAP for 3 years in this role.

Doug Hoover is a Platform and Technologies Principal Solution Engineer with decades of experience in database, real-time data movement, enterprise data warehouse, advanced analytics, and big data.


This is our first post in our new blog series about the new innovations in the most recent SAP HANA release. Interested in a topic? Let us know!

Product: SAP HANA 2.0 SPS04

Feature: Native Storage Extension (NSE)

 

With SAP HANA’s latest service pack update (SPS04) comes new innovations. In this blog post, I will be explaining Native Storage Extension (NSE), a new native option for SAP HANA customers for warm data tiering. With NSE, you will be able to reduce TCO by gaining storage capacity by storing data in pageable columnar tables. An sample of the SQL code used to administer data storage and a technical deep-dive video are also included below.

Reduce TCO by gaining storage capacity

Reduce your TCO by significantly expanding your data capacity without requiring an additional SAP HANA License.

Think of your mission critical sales data from the past few days. Process managers need this data available in real-time to make critical operational decisions. Once the business process has been completed, you still need access but not with the same response time required by operational business needs. Now with NSE, this older data can be stored in a lower cost tier while still managed as part of the SAP HANA database.

Store data in pageable columnar tables

SAP HANA stores data at rest in columnar tables in-memory. NSE extends this in-memory storage to support pageable columnar tables while maintaining efficient and fast accesses. This mechanism provides a significantly lower Total Cost of Ownership by expanding the capacity of the SAP HANA database. Although slower than SAP HANA columnar in-memory tables, NSE is still much faster and extremely more efficient than standard row-based databases. Therefore, a huge benefit is that NSE allows SAP HANA to store huge amounts of data very affordably, supporting both vertical and horizontal partitioning of columnar tables across memory and disk.

Administer data storage with SQL

/*To create a page loadable table*/
CREATE COLUMN TABLE T (C1 INT, C2 VARCHAR(10)) PAGE LOADABLE;
 
/*To create a page loadable partition*/
CREATE COLUMN TABLE TP (C1 INT) PARTITION BY RANGE (C1)
((PARTITION 0 <= VALUES < 1000 PAGE LOADABLE, 
PARTITION 1000 <= VALUES < 2000 COLUMN LOADABLE, 
PARTITION OTHERS COLUMN LOADABLE));
 
/*To create a page loadable column*/
CREATE COLUMN TABLE TC (C1 INT, C2 VARCHAR(10) PAGE LOADABLE);

 

Technical overview

Check out this video to see a technical overview of NSE

To start using SAP HANA today

We are excited to roll out this native SAP HANA warm data tiering solution that boasts full SAP HANA functionality at a lower TCO for customers. Drop a question below if you want to learn more. Want to start using SAP HANA today? Check out SAP HANA Service homepage on the SAP Cloud Platform site and contact your SAP Account Executive!

Assigned Tags

      4 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Daniel Dukes
      Daniel Dukes

      Would you like to discuss storing data using NSE with an expert? Join Abby and Doug this Friday afternoon for a 1 hour SAP HANA Expert Question and Answer session. Use the link below to register:

      https://sap-se.zoom.us/webinar/register/0f72653619acc763dc2040ba88984b7b

      After registering, you will receive a confirmation email containing information about joining the webinar.

      Author's profile photo Douglas Hoover
      Douglas Hoover

      Hi Feng Liu

      Here are some comparisons...

      Notice in-memory speed of 552ms vs NSE speed of 2503ms but second run was only 706ms, so depending on code and how often data is reused speed can vary from 1.2 to 5 times slower...

      In this example all of the tables where completely loaded into default HANA in-memory tables:

      SELECT R_NAME, SUM(O_TOTALPRICE)

      FROM CUSTOMER C, NATION N, REGION R, ORDERS_VP O

      WHERE C_CUSTKEY = O_CUSTKEY

      AND N_NATIONKEY = C_NATIONKEY

      AND N_REGIONKEY = R_REGIONKEY

      GROUP BY R_NAME;

      -- Since none of the page loadable columns are used...

      --Statement 'SELECT R_NAME, SUM(O_TOTALPRICE) FROM CUSTOMER C, NATION N, REGION R, ORDERS_VP O WHERE ...'

      --executed in 552 ms.

      In this example all of the tables where completely loaded into HANA NSE (PAGE LOADABLE)  on-disk tables:

      SELECT R_NAME, SUM(O_TOTALPRICE)

      FROM CUSTOMER C, NATION N, REGION R, ORDERS_HP O

      WHERE C_CUSTKEY = O_CUSTKEY

      AND N_NATIONKEY = C_NATIONKEY

      AND N_REGIONKEY = R_REGIONKEY

      GROUP BY R_NAME;

      --Statement 'SELECT R_NAME, SUM(O_TOTALPRICE) FROM CUSTOMER C, NATION N, REGION R, ORDERS_HP O WHERE ...'

      --executed in 2503 ms.

      --Statement 'SELECT R_NAME, SUM(O_TOTALPRICE) FROM CUSTOMER C, NATION N, REGION R, ORDERS_HP O WHERE ...'

      --executed in 706 ms.

      Now if you partition a table across both in-memory and on-disk then and you only hit the in-memory data with your query then you will get the in-memory speed 512ms

       

      SELECT R_NAME, SUM(O_TOTALPRICE)

      FROM CUSTOMER C, NATION N, REGION R, ORDERS_HP O

      WHERE C_CUSTKEY = O_CUSTKEY

      AND N_NATIONKEY = C_NATIONKEY

      AND N_REGIONKEY = R_REGIONKEY

      AND O_ORDERDATE >= '20080101'

      GROUP BY R_NAME;

      -- Since date newer than 2008 then only in-memory COLUMN LOADABLE Columns are used

      --Statement 'SELECT R_NAME, SUM(O_TOTALPRICE) FROM CUSTOMER C, NATION N, REGION R, ORDERS_HP O WHERE ...'

      --executed in 512 ms.

      CREATE COLUMN TABLE NSE.ORDERS_HP ("O_ORDERKEY" BIGINT NOT NULL ,

      "O_CUSTKEY" BIGINT  NOT NULL ,

      "O_ORDERSTATUS" CHAR(1) NOT NULL ,

      "O_TOTALPRICE" DECIMAL(12,2) NOT NULL ,

      "O_ORDERDATE" DATE  NOT NULL ,

      "O_ORDERPRIORITY" VARCHAR(15) NOT NULL ,

      "O_CLERK" VARCHAR(15) NOT NULL ,

      "O_SHIPPRIORITY" INTEGER  NOT NULL ,

      "O_COMMENT" VARCHAR(79) NOT NULL ,

      PRIMARY KEY ("O_ORDERKEY",O_ORDERDATE))

      PARTITION BY RANGE (O_ORDERDATE) ((

      PARTITION '20090101' <= VALUES < '20100101' COLUMN LOADABLE,

      PARTITION '20080101' <= VALUES < '20090101' COLUMN LOADABLE,

      PARTITION '20070101' <= VALUES < '20080101' PAGE LOADABLE,

      PARTITION '20060101' <= VALUES < '20070101' PAGE LOADABLE,

      PARTITION '20050101' <= VALUES < '20060101' PAGE LOADABLE,

      PARTITION '20040101' <= VALUES < '20050101' PAGE LOADABLE,

      PARTITION '20030101' <= VALUES < '20040101' PAGE LOADABLE,

      PARTITION '20020101' <= VALUES < '20030101' PAGE LOADABLE)) ;

       

      SAME result with vertical partitioning 512ms...

       

      SELECT R_NAME, SUM(O_TOTALPRICE)

      FROM CUSTOMER C, NATION N, REGION R, ORDERS_VP O

      WHERE C_CUSTKEY = O_CUSTKEY

      AND N_NATIONKEY = C_NATIONKEY

      AND N_REGIONKEY = R_REGIONKEY

      GROUP BY R_NAME;

      -- Since none of the page loadable columns are used...

      --Statement 'SELECT R_NAME, SUM(O_TOTALPRICE) FROM CUSTOMER C, NATION N, REGION R, ORDERS_VP O WHERE ...'

      --executed in 552 ms.

      create column table NSE.ORDERS_VP (

      O_ORDERKEY           BIGINT               not null,

      O_CUSTKEY            BIGINT               not null,

      O_ORDERSTATUS        CHAR                 not null,

      O_TOTALPRICE         DECIMAL(12,2)        not null,

      O_ORDERDATE          DATE                 not null,

      O_ORDERPRIORITY      VARCHAR(15)          not null,

      O_CLERK              VARCHAR(15)          not null PAGE LOADABLE,

      O_SHIPPRIORITY       INTEGER              not null PAGE LOADABLE,

      O_COMMENT            VARCHAR(79)          not null PAGE LOADABLE,

      primary key (O_ORDERKEY)

      );

      Author's profile photo Roland Kramer
      Roland Kramer

      Before using NSE and deactivate the "HANA Power", rather think about the usage of SAP IQ as large Datastore together with SAP HANA, as SAP IQ always uses columnar compression (on disk).

      See - SAP (Sybase) IQ – the hidden treasure …