Using Decision Table to Route Calculation View to Hot and/or Cold Storage – Part 1
In order to manage size of costly HANA storage concept of Hot and Cold storage is used. Recent data is stored in Hot Storage (HANA) and historical data is moved to Cold Storage (for example,IQ). Calculation View implements source pruning by accessing only storage(s) where requested data is located (performance improvement). Routing is usually implemeted by deriving source of data from HANA table.
I improved Routing using Decision Table instead of HANA table. Advantages of using Decision Table are:
- Accomodation of complex Routing conditions (for example, date range instead of a year);
- More simple Calculation View design (single Routing Input Parameter and shorter Source Filter Expression);
- Separation of Routing Rules from Calculation View for simplier testing and reusability;
- Use of standard interface for Decision Table maintenance rather then HANA table update.
In first part of the blog I will explain how to:
- Create Remote Source and Virtual Table
- Create Routing Decision Table
- Create Wrapper Stored Procedure
Those are prerequistes for Calculation View creation which is explained in second part of the blog.
Creation of Remote Source and Virtual Table
Create IQ Remote Source that for simplicity points to the same HANA system
Create SO.Header Virtual Table
Creation of Routing Decision Table
Define DT_SOURCE Decision Table
As you see, DATE_FROM and DATE_TO are Conditions and SOURCE is an Action. This means that DATE_FROM and DATE_TO values determine SOURCE value. DATE_TO is marked as Horizontal Condition. SOURCE parameter default value is NONE (if Source was not found for date range specified)
In my example current 2014 data is stored in HANA (Hot Storage) and historical 2012-2013 data is stored in IQ (Cold Storage). That is why Routing Rules defined in Decision Table as follows
Just a better view of the same data exported to Excel
Let’s test our Routing Rules
(Optional) You might be curious to see Stored Procedure that implements Decision Table
CREATE PROCEDURE “_SYS_BIC”.“workshop.exercises/DT_SOURCE“
(IN DATE_FROM DATE,IN DATE_TO DATE,
OUT returned_tt “_SYS_BIC”.“workshop.exercises/DT_SOURCE/TT”)
LANGUAGE SQLSCRIPT
READS SQL DATA WITH RESULT VIEW “_SYS_BIC”.“workshop.exercises/DT_SOURCE/RV” AS
BEGIN
returned_tt = SELECT :DATE_TO AS “DATE_TO”, :DATE_FROM AS “DATE_FROM”,
CASE
WHEN :DATE_TO Between ‘2012-01-01’ and ‘2013-12-31’ THEN
CASE
WHEN :DATE_FROM < ‘2012-01-01’ THEN ‘IQ’
WHEN :DATE_FROM Between ‘2012-01-01’ and ‘2013-12-31’ THEN ‘IQ’
WHEN :DATE_FROM Between ‘2014-01-01’ and ‘2014-12-31’ THEN ‘NONE’
ELSE ‘NONE’
END
WHEN :DATE_TO > ‘2014-01-01’ THEN
CASE
WHEN :DATE_FROM < ‘2012-01-01’ THEN ‘HANA,IQ’
WHEN :DATE_FROM Between ‘2012-01-01’ and ‘2013-12-31’ THEN ‘HANA,IQ’
WHEN :DATE_FROM Between ‘2014-01-01’ and ‘2014-12-31’ THEN ‘HANA’
ELSE ‘NONE’
END
ELSE ‘NONE’
END
AS “SOURCE” FROM “SYS”.“DUMMY”;
END;
Creation of Wrapper Stored Procedure
Decision Table can not be used directly in Calculated View to populate Parameter. That is why Wrapper Stored Procedure is need. Another reason is that multiple source values need to be fixed. This what Decision Table provides: ‘HANA,IQ’ whereas Calculated View needs ‘HANA’,’IQ’
CREATE PROCEDURE “WORKSHOP”.“workshop.exercises::Source“
(IN IV_DATE_FROM VARCHAR(10), IV_DATE_TO VARCHAR(10),
OUT OV_SOURCE VARCHAR(10))
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER
READS SQL DATA AS
BEGIN
CALL “_SYS_BIC”.“workshop.exercises/DT_SOURCE”(DATE_FROM => :IV_DATE_FROM,
DATE_TO => :IV_DATE_TO,
RETURNED_TT => :DATA);
SELECT SOURCE INTO OV_SOURCE
FROM :DATA
WHERE DATE_FROM = :IV_DATE_FROM AND DATE_TO = :IV_DATE_TO;
SELECT REPLACE (OV_SOURCE,’,’, ”’,”’) INTO OV_SOURCE
FROM DUMMY;
END;
Let’s test our Wrapper Stored Procedure
In second part of my blog I will explain how to model Calculation View that uses Decision Table for Routing to Hot and/or Cold Storage