SAP HANA SPS 09 – SERIES DATA with My Exercises

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);

1. 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’)

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

1. 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