Skip to Content

    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:

  1. Accomodation of complex Routing conditions (for example, date range instead of a year);
  2. More simple Calculation View design (single Routing Input Parameter and shorter Source Filter Expression);
  3. Separation of Routing Rules from Calculation View for simplier testing and reusability;
  4. Use of standard interface for Decision Table maintenance rather then HANA table update.

In first part of the blog I will explain how to:

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

Remote Source Creation 1 of 4.jpg

Remote Source Creation 2 of 4.jpg

Create SO.Header Virtual Table

Remote Source Creation 3 of 4.jpg

Remote Source Creation 4 of 4.jpg

Creation of Routing Decision Table

Define DT_SOURCE Decision Table

Decision Table 1 of 10.jpg

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)

Decision Table 1 of 10.jpg

Decision Table 2 of 10.jpg

Decision Table 3 of 10.jpg

Decision Table 4 of 10.jpg

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

Decision Table 5 of 10.jpg

Just a better view of the same data exported to Excel

Decision Table 6 of 10.jpg

Let’s test our Routing Rules

Decision Table 7 of 10.jpg

Decision Table 8 of 10.jpg

Decision Table 9 of 10.jpg

Decision Table 10 of 10.jpg

(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


Wrapper Storage Procedure 1 of 4.jpg

Wrapper Storage Procedure 2 of 4.jpg

Wrapper Storage Procedure 3 of 4.jpg

Wrapper Storage Procedure 4 of 4.jpg


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

To report this post you need to login first.

Be the first to leave a comment

You must be Logged on to comment or reply to a post.

Leave a Reply