Dynamic Tyring

SAP HANA Dynamic tiering 

SAP HANA is a unique technology, where all the data is stored in Memory(RAM) and process their for extreme performance.

As the data grows, the SAP HANA Hardware must grow also. 

"⧬" SO to overcome the above hardware issue, we have a solution called Dynamic tyring

👼idea:

Does all the Enterprise data,required high performance of HANA in memory.

Cold data: old data & History data for Compliance & History trend analysis.
Warm data: The data in this tier still active, but occasionally updated.
                       It is part of HANA DB,but maintained with transactional consistency.

1. Hot data - HANA - in memory.
2.warm data - HANA Dynamic tier, HANA Extention Nodes.
3.cold data - External DB's, SAP IQ,Hadoop.

• HANA Dynamic tier is a disk back column store.based on SAP IQ technology with hana database.
• Dynamic tiering is installed on its own server,but  it is not a pure HANA node.
• Query info passed to HANA Dynamic tiering and execute from their & can execute 'Sequel & calculate views' on HANA Dynamic tiering.
• How ever to run more Queries like predictive, text analysis on dynamic tier, that data need to come to hana DB first, before it comes can be processed.

😊Dynamic tiering is tightly integrated with HANA in memory, same Admin tools,HANA studio & cockpit for Administration of Dynamic tiering.

• Backup of HANA DB, leads to both HANA in memory & Dynamic tier.
• system replication can be done in both tiers.

Practical Steps

Step 1: Create Extended Storage: (in system DB)

-----------------------------------------------------------------------------------------------------------------------

❶ Right -click the system to select "open SQL console"

❷Execute the script to create extended storage.

    dbspace for dynamic tiering

"CREATE EXTENDED STORAGE AT ' <YOUR_MACHINE_NAME>'  SIZE 1000MB''

------------------------------------------------------------------------------------------------------------------------

💪Dynamic tiering itself is scales upto 100 Tera bytes, addition to in memory storage to HANA.

🙏  Dbspace object is introduced

💬Dbspace - logical name for a container of files.

💭stores dynamic tiering table data & objects.

• In in memory storage, the Space management will happened automatically.
• where as in "Dbspace", manually management of DB space is required.

--------------------------------------------------------------------------------------------------------------------------

Step 2: create user for Dynamic tiering(TPCH): 

Default user: signed in as ''system'' user will all roles & privileges assigned.

roles:
AFLPM_Creator_ERASER_EXECUTE
content_Admin

privillages
1. EXTENDED STORAGE ADMIN
2. IMPORT
3.CATALOG READ

click on Execute.

• Right click on ''system DB" -->choose ''Add system with different user'' -->Enter user id & password of  "TPCH"

--------------------------------------------------------------------------------------------------------------------------

Step 3 Creating & using Extended tables

• Extended Tables are not existing part in  Dynamic tiering,multi - store tables are most existing.
• database defined their structure using catalog tables,In that DB catalog.

Syntax for Creating table: (both in m/m & Extended)

→in memory table:

--------------------------------------------------------------------------------------------------------------------------

CREATE column TABLE t1 (a INT, b INT);

--------------------------------------------------------------------------------------------------------------------------

  Here t1 is table name

→

in Extended STORAGE TABLE
--------------------------------------------------------------------------------------------------------------------------
CREATE COLUMN TABLE t1_ext (a INT , b INT) USING EXTENDED STORAGE;
--------------------------------------------------------------------------------------------------------------------------

here t1_ext is table name

Note: Both HANA in memory & Dynamic tiering DB Space, both supports only column storage.

*import from CSV file:

copy data file into the directory on SAP HANA dynamic tiering host.
--------------------------------------------------------------------------------------------------------------------------
Execute IMPORT FROM CSV FILE 'bigfile.csv' INTO t1 statement in sql console
--------------------------------------------------------------------------------------------------------------------------

*Row insert
--------------------------------------------------------------------------------------------------------------------------
->Execute INSERT INTO t1 (COL1 , COL2...) VALUES (VAL1,VAL2...) statement in SQL console.

--------------------------------------------------------------------------------------------------------------------------

Creating & using Extended Tables: