In a tutorial video from the SAP HANA Academy robert.waywell provides an overview on the configuration and usage of dynamic tiering. Dynmaic tiering adds multi-temperate data management technology directly into the SAP HANA platform. Dynamic tiering is a new feature that was released with SAP HANA SPS09. Check out Robert's video below.
Dynamic tiering is installed, configured and managed as part of the SAP HANA environment. This includes integration with SAP HANA cockpit and SAP HANA Studio. Robert’s demonstration will show how dynamic tiering can be used to manage data for Hot and Warm sales orders and sales order items.
(0:50 – 2:20) Creating and Examining the Hot and Warm Tables
The syntax used to create extended tables is nearly identical to the syntax used to creating in-memory SAP HANA tables. The only difference is the addition of the USING EXTEND STORAGE clause (highlighted below) to the table definition.
In Robert’s example the Hot tables are being created in the in-memory data store of SAP HANA and the Warm tables are being created in the extended storage.
Once the tables have been created they are visible in the Tables folder of the Catalog folder in SAP HANA Studio. The only difference between the display of the Hot tables created in-memory and the Warm tables created in dynamic tiering is the addition of the term EXTENDED next to the Warm tables.
Data can be imported, inserted, deleted or updated directly in the dynamic tiering tables. The demo starts by importing data directly into the in-memory Hot tables.
After importing the data, Robert uses the data preview function to verify that the data has been loaded. Data preview also confirms that no data has been loaded into the Warm tables.
(2:20 – 4:40) Creating the Migrate_Aged_Orders Stored Procedure
The sales order data table contains orders from both 2013 and 2014. Since this business is constantly growing and most of the reporting is done on orders taken within the past 12 months, Robert wants to prioritize the in-memory data space for use of only 2014 orders. Thus Robert wants to move the previous year’s orders to the Warm store in dynamic tiering.
Complicating the movement of sales orders from Hot to Warm storage is the fact that each sales order has corresponding line items that must be moved along with it. To ensure that sales orders and their corresponding line items are moved together a stored procedure called Migrate_Aged_Orders is created.
First this stored procedure will set a variable that is one year prior to the current date. This variable will then 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 of the orders to be migrated from Hot to Warm storage, Robert then insert the sales orders and sales order items into the Warm table. Both cases will use a select statement against the corresponding Hot table.
Next Robert deletes the corresponding records from the Hot tables for both the sales orders and sales orders items. All of this will occur in the same transaction.
(4:40 – 5:30) Calling the Procedure and Verifying the Data Movement
Robert then calls the recently created Migrate_Aged_Orders stored procedure to move the 2013 sales orders and their corresponding items from the in-memory Hot store to the dynamic tiering Warm store.
After the stored procedure has quickly run Robert verifies that the data has migrated by using the data preview feature on the in-memory Hot sales orders table to confirm that it only contains data for 2014. Also now the Warm dynamic tiering table contains the orders from 2013.
(5:30 – 6:45) Creating Views for Join Reporting
This scenario of moving the aging sales orders from the Hot store to the Warm store saved spaced in the in-memory store for the current and most frequently accessed data. However, there will still be instances where we will want to report on all of the data combined.
To facilitate joint reporting across both the Hot and Warm store we can create views that will provide a union of both the in-memory Hot tables and the dynamic tiering Warm tables.
Using the views created for SalesOrders_All and SalesOrderItems_All we can now execute queries across both the Hot and Warm store without a concern if a record is only in one of the tables.
Using the SalesOrders_All view Robert verifies that the sales orders table contains orders from both 2013 and 2014 in the same result set.
(6:45 – 8:20) Query with a Time Frame and the Visualization Plan
In a more complex example Robert enters a query on the SalesOrderItems_All view to filter for all of the data that is part of SalesOrders_All between six months and 18 months ago. Check out this time frame query below.
To see the plan for how SAP HANA is processing this query Robert highlights the query, right clicks and selects visualize plan. After switching to the PlanVis perspective you will see the estimated query plan based on current statistics within the database.
From within the SAP HANA PlanVis perspective your can execute a query plan to observe the actual results in the actual query plan used. The results display an overview that includes compilation and execution times and the number of tables used.
Also a detailed executed plan is displayed. Comparing the executed plan to the estimated plan is helpful in determining where additional indexes may be useful.
Check out the SAP HANA Academy's series of over 75 videos on What's New with SAP HANA SPS09 as well as the playlist for Dynamic Tiering.
SAP HANA Academy over 800 free tutorial videos on using SAP HANA.
- SAP Managed Tags:
