SAP HANA SPS 09 – SERIES DATA with My Exercises

[Former Member]

CONTENTS:

SERIES DATA TABLE CREATION and GENERATE TIMESTAMP DATA

SERIES DATA TABLE vs REGULAR TABLE – STORAGE and COMPRESSION

EXPLORING SERIES DATA BUILT-IN FUNCTIONS

PREREQUISITES:     

• Series Data Column Table "STOCKS_DATA_SERIES" has to be present in Schema “SERIES_DATA".

[CSV Files (STOCKS_DATA_SERIES.csv) is attached in this Post,

Using Flat File Import Create tables "STOCKS_DATA_SERIES"  in schema “SERIES_DATA" in your landscape.]

Exercise 1:  Create & Compare the Series table with Column table

Explanation	Examples / Screenshot
Step 1: Creating Regular column Table and Series Data Table.	Create column Table "SERIES_DATA".MyTab (key int, ts timestamp, value int); Create column Table "SERIES_DATA".MyTabSeries (key int, ts timestamp, value int) Series (series key(key) period for series(ts,null) equidistant increment by interval 60 second);
Step 2: Inserting Data to Regular column Table and Series Data Table using “SERIES_GENERATE_TIMESTAMP” function.	Insert into "SERIES_DATA"."MYTAB" select 1, GENERATED_PERIOD_START, 7 from SERIES_GENERATE_TIMESTAMP ('INTERVAL 60 SECOND', '2010-01-01', '2011-01-01', null, null, null); Insert into "SERIES_DATA"."MYTABSERIES" select 1, GENERATED_PERIOD_START, 7 from SERIES_GENERATE_TIMESTAMP ('INTERVAL 60 SECOND', '2010-01-01', '2011-01-01', null, null, null); No.of rows in both the table has to be 5,25,600.
Step 3: Execute Merge Delta Operations for both the tables.	merge delta of "SERIES_DATA"."MYTAB"; update "SERIES_DATA"."MYTAB" with parameters ('optimize_compression' = 'force'); merge delta of "SERIES_DATA"."MYTABSERIES"; update "SERIES_DATA"."MYTABSERIES" with parameters ('optimize_compression' = 'force');
Step 4: Verifying Series Table Storage and Comparing Memory size and Compression with Regular Column table.	Select Table_name, column_name, memory_size_in_total, sum(memory_size_in_total) over (partition by table_name) as tab_memory_size, uncompressed_size, sum(uncompressed_size) over (partition by table_name) as tab_uncompressed_size, compression_ratio_in_percentage as ratio, compression_type, "COUNT", distinct_count from m_cs_columns where table_name in ('MYTAB', 'MYTABSERIES')

Verify both normal column table and series Table.

Normal Column table --> TS Column memory size --> 5 MB

Series Table --> TS Column memory size --> 10 KB

Now You can understand How efficiently Series Table stores the data for  Time values.

Exercise 2:  Series Table Built-in and Analytic Functions:

Explanation	Examples / Screenshot
Step 1: Check the data Preview of Series Data Table "STOCKS_DATA_SERIES"	STOCKS_DATA_SERIES (SERIES TABLE): The table (“SERIES_DATA”.”STOCKS_DATA_SERIES”) is having Stock Market data with values (TICKER_ID, TICKER_DESCRIPTION, DATE, OPEN, HIGH, LOW, CLOSE, VOLUME, ADJ_CLOSE and DAILY_PERCENT_CHANGE) Since Year 1959 to 2015 (all the business days) for Deutscher Aktien Index (DAX). Total no.of rows in the Table 13895. SQL QUERY TO CHECK THE DATA:
Step 2: Exploring Series Data Built-in Functions: SERIES_DISAGGREGATE	SERIES_DISAGGREGATE (Built-in Function): Transforming an equidistant time series with a coarser delta to one with a finer delta can be performed using the SERIES_DISAGGREGATE function. SQL Query: We have data on daily basis. We are going to disaggregate data to hourly basis from Daily. select * from ( SELECT s.DATE, s.close * g.FRACTION_OF_SOURCE_PERIOD AS "By Hour Close" FROM "SERIES_DATA"."STOCKS_DATA_SERIES" AS s LEFT JOIN SERIES_DISAGGREGATE_TIMESTAMP ('INTERVAL 1 DAY', 'INTERVAL 1 HOUR', '2015-01-19', '2015-01-20') g ON s.DATE = g.SOURCE_PERIOD_START ) where DATE = '2015-01-19';

Step 3: Exploring Series Data Built-in Functions: SERIES_ROUND

SERIES_ROUND (Built-in Function): Horizontal aggregation transforms an equidistant series with a narrower interval to a new series with a coarser interval. Horizontal Aggregation functionality performed using the SERIES_ROUND function. SQL Query: We have data on daily basis. We are going to Aggregate data to monthly basis from Daily. Select rounded.TICKER_ID, Month, Avg(CLOSE) as Monthly_avg from ( select t.TICKER_ID, SERIES_ROUND(DATE, 'INTERVAL 1 MONTH', ROUND_DOWN) As Month, CLOSE from "SERIES_DATA"."STOCKS_DATA_SERIES" As t ) As rounded Group By rounded.TICKER_ID, Month

Summary:

You have completed the exercise!

You are now able to:

1)  Create the Time Series Table.

2)  Understand the Storage of Series Data Table.

3)  Usage of Series Data Built-in Functions.