[SAP HANA Academy] Utilize Modeled Persistence Objects in the SAP HANA Data Lifecycle Manager

In a series of three tutorial videos the SAP HANA Academy's Tahir Hussain Babar (Bob) details how to use Modeled Persistence Objects in the SAP HANA Data Lifecycle Manager. In this series Bob is using DLM SPS03 on top of SAP HANA SPS 11. This series is part of the SAP HANA Academy's comprehensive playlist covering What's New in SAP HANA SPS 11.

Overview of the Exploration Module

In the first video of the series Bob provides an introduction to the SAP HANA Data Lifecycle Manger's Exploration Module by showcasing how to use it for analyzing a system's data.

Bob first launches SAP HANA Studio. Bob is running a SAP HANA SPS 11 system that already has Dynamic Tiering installed. Bob has create a schema called BOBSDATA that contains the sales data he will be using throughout the series. Also, Bob has a system user that already has the necessary rights (shown below) to execute all of these tasks.

BOBSDATA contains two tables of sales orders. Bob runs a Select statement on both of the tables in a new SQL console to show that the data contained in his schema is organized in a Header table and a Footer table.

The link between the two data sets is the ID column. The SALESORDERS_HOT table contains information on the individual transaction and the SALESORDERITEMS_HOT table contains information on the products and quantity of each order. Bob next runs the below SQL statement to group the data by year.

The data is now divided by 2014 and 2015.

In a web browser Bob logs into the DLM as his system user. The exploration function can be used to establish which tables are appropriate candidates for data relocation. Essentially it summarizes the amount of data (space) that each of your tables or other objects are occupying within your SAP HANA system.

This is a very useful utility when viewed from a two step approach. First, you can preform graph-based exploration on different levels. With a sun burst chart (show above) you can drill down into information on various levels. The levels are either host, schema or table. For example, we can use this to identify the 10 largest tables within our system or identify the 10 largest tables within our schema on a given host.

Second, you can use the form-based exploration to view how the data in a selected SAP HANA table is spread out according to a selected column filter. With this insight you can then derive meaningful business rules for relocating the data.

Currently Bob has a relatively small, single node system. By drilling down further, Bob can see how much data is being used by each host and each port. As Bob only has one host and one port he clicks on the eye icon next to each to deselect them. Now Bob sees how much data each of his schemas are taking up. His _SYS_REPO is taking a hefty 72% of the total share. 

Hypothetically, if BOBSDATA was taking up too large of a share of his schema he may want to archive it. By drilling down into that schema Bob can view the total memory share of each table in the schema. Bob further modifies the sunburst chart so he can view how much each individual table in his BOBSDATA schema is taking up within the entire system. 

How to Build Table Groups with Modeled Persistence Objects

Bob examines how to build tables groups using Modeled Persistence Objects in the SAP HANA Data Lifecycle Manager in the series' second video. One way to relocate Bob's header and footer SALESORDERS tables would be to build a pair of lifecycle profiles. However, using Modeled Persistence Objects is a much better method as the two tables will be linked together in a table group.

In the Data Lifecycle Manager Bob opens the MANAGE MODELED PERSISTENCE OBJECTS tab and clicks on the plus button to create a new join. Bob names his join SHA_LP_MPO. At the bottom of the screen Bob searches for BOBSDATA in the text box adjacent to Fully Qualified Table Names and selects his two SALESORDER tables from the resultant drop down menu. 

ID is the Common Key Column Name shared by the two tables, so Bob selects that. Next Bob clicks Save and then clicks Activate.

With the new object activated Bob opens the MANAGE LIFECYCLE PROFILES tab and builds a new profile by clicking on the plus button. Bob names his new DLM Profile SHA_LP_MPO. For Source Persistence Bob select the SAP HANA Table Group option and uses his recently created Managed Persistence Object for the Table Group Name. Bob opts to use a Manual Trigger. Bob keeps the Destination Attributes shown below. 

In the Rule Editor Bob leaves the Rule Editor Type as Table Group SQL-Based Rule Editor and then scrolls to the bottom. The only Column available for Bob is the ID column.

Back in SAP HANA Studio Bob must find the highest ID listed for each of his years. Bob executes the below SQL statement to output the maximum ID for each year.

So Bob wants to discriminate by the maximum IDs for 2014 and 2015. Back in the MANAGE LIFECYCLE PROFILES tab Bob selects the ID column and then writes less than < 2014 Maximum ID. Note Bob removes the number's commas. So all of the IDs from 2014 will be moved across. Then Bob clicks Validate Syntax. 

About Half of the records will be affected and relocated. Next click on Save and after click on Activate. Then click on the Simulate button and choose Data Relocation Count to see how many items from both of the SALESORDERS tables will be moved from Hot to Cold. 

Next, click Run to trigger the relocation manually. To confirm that the relocation is working, open the LOGS tab, select your Lifecycle profile and then choose to show the logs for the current run ID. Once the relocation is completed, in the MANAGE LIFECYCLE PROFIlES tab, you can see how many rows were moved from your SAP HANA system to SAP HANA Dynamic Tiering. 

The Difference Between Pruning Views and G Views 

In the third and final video of the series Bob examines the different objects that were created when he built the lifecycle profile.

In the MANAGED LIFECYCLE PROFILES tab with your recently created MPO profile selected, scroll to the bottom of the page and choose the Destination Persistence to view the objects you have created. Due to the fact that multiple objects were relocated (the header and the footer table) we now have four views. A pruning node view, a union view and the root view for the SALESORDERS and SALESORDERITEMS tables. 

Opening Generated Objects will show the single stored procedure that will execute that rule.

Back in SAP HANA Studio Bob runs the SQL syntax shown below to see how many of his records have been moved to Dynamic Tiering. 

Most of the records from 2014 have been moved to Dynamic Tiering. Next Bob opens the view folder in his SAP_HANA_DLM_GNR schema. The view folder contains normal, aka SQL Union views, called G views. These G views are highlighted below. The other type of views are called P views and those are listed in the Column Views folder. 

The G views perform a regular SQL union on the table which resides in SAP HANA Dynamic Tiering and SAP HANA. Bob shows the content of the G view that combines the orders.

Bob copies the syntax highlighted above and pastes it into a new SQL console. Bob changes TOP 1000 to COUNT(*) in the syntax and then executes the statement. This returns a count on the combination of the two data sets. Next Bob runs the below statement that will provide a count of his SALESORDERS table from his BOBSDATA Schema.

The count for the view stored in Dynamic Tiering and SAP HANA is much large than the count of the SALESORDERS tables. Bob confirms the data's source by opening the create statement for his DLM_GVIEW_BOBSDATA_SALESORDERS_HOT views and highlights the pair of sources. 

The problem with the G views is that no matter what select statement you run, it will access data from both stores. So even if you only want data from 2015 it will use both sources.

The Pruning View has in-built intelligence so it will only access the relevant store based on the SQL query run against it. When reporting you should use a P view rather than a G view as it will use the system resources more efficiently. 

SAP HANA Academy - Over 1,300 free tutorials videos on SAP HANA, SAP Analytics and the SAP HANA Cloud Platform.

Source: scn.sap.com