Archiving on the Fly with the SAP HANA Academy
Swapping from hot to warm now there’s a phrase you can have fun with. Today I am reviewing Robert’s introductory video on a new feature for SPS09 – Dynamic Tiering.
Back in the day I performed a similar process on MS Access. I had the joy of training IT teachers and teaching post-16 students how to make queries that archived data but more of that later.
Dynamic Tiering adds multi-temperate data management technology directly into the SAP HANA platform.
It is installed, configured and managed as part of the SAP HANA environment including integration with HANA cockpit and SAP HANA Studio. Please check out Rosalind’s brilliant Installation Overview.
This demonstration will show how Dynamic Tiering can be used to managed data for hot and warm sales orders and sales orders items.
The syntax for creating tables is virtually identical to syntax for creating in-memory HANA tables. The only difference is the addition of a using extending storage clause to the table definition. In this example the hot tables are being created in the in-memory data store of HANA and the warm tables are being created in the extended storage (Dynamic Tiering).
Once the tables have been created they are visible in the catalog along with any other tables in the database.
Data can be imported, inserted, updated or deleted directly in the Dynamic Tiering tables.
This demonstration shows you how to import data into the in-memory hot tables. However, before doing that it is pointed out the some of the data in the hot table refers to the previous year. To optimise space on the in-memory, hot tables it is best to move these entries into the extended storage, warm tables in Dynamic Tiering.
Complicating matters is the fact that the sales orders line items need moving with the sales order record. You can see this for the sales order below.
| Header 1 | Header 2 |
|---|---|
 |  |
To ensure that these entries are moved together from hot to warm storage you can create a stored procedure called migrate aged orders. This stored procedure will set off a variable that is one year prior to the current date. This variable will be referenced in each of the subsequent insert and delete statements to ensure that all records are being moved on a consistent date value. Having set the cut off date the orders to be migrated from hot to warm storage you will then insert the sales orders into the warm table and the sales orders items into the warm tables. In both cases using a select statement against the corresponding hot table, once this is done, within the same procedure and thus within the same transaction delete the corresponding records from the hot tables for both the sales orders items and the sales orders.
Now that you have your stored procedure created you can call it from the in-memory hot store to the Dynamic Tiering warm store.
Robert verifies that the data has been moved and reiterates why this process is important in terms of data performance i.e. preserving space in the in-memory data store for current, most frequently accessed data.
The video moves onto to demonstrate how the newly separated data by date can be joined together in one view. During the demonstration, how to use SQL code to make a query plan in SAP HANA is outlined based on current statistics within the database. The query plan is shown being executed along with the usefulness of comparing the executed plan with the estimated plan for determining where additional indexes may be useful.
Going back to my day. We used append and delete queries with a SQL statement to look for orders on the orders tables and associated order lines on the order line tables for data more than a year old. A macro attached to the autoexec macro ran both queries in sequence every time the database was started. It was at this time I came to understand the phrase banging your head against a brick wall. Teachers could not deliver the lesson easily as students would invariably make mistakes that needed debugging. My lovingly produced user guides tested on 13 year old students were occasionally followed incorrectly and I spent inordinate amount of time debugging systems and being called out of lesson to do so. Happy times. This is why we are in work.
- SAP Managed Tags:
