Technology Blogs by Members
Explore a vibrant mix of technical expertise, industry insights, and tech buzz in member blogs covering SAP products, technology, and events. Get in the mix!
cancel
Showing results for 
Search instead for 
Did you mean: 
uladzislau_pralat
Contributor

    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

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

Labels in this area