Introduction
This blog post will outline how we can dynamically populate date in HANA Views that are designed to run for specific days only. We will demonstrate that instead of hardcoding the dates in the view we can populate them externally and facilitate the materialization (storing data into an ADSO/HANA Table) to happen with minimal manual intervention.
We’ll take an example of 2 views which have filters at their base projections to restrict data for 4 specific dates: Current Day, Current Day-1, Current Day-5, and Current Day-6.
Current Setup:
Dates are being derived as follows:
◉ Date1 = ADDDAYS(NOW(),0)
◉ Date2 = ADDDAYS(NOW(),-1)
◉ Date3 = ADDDAYS(NOW(),-5)
◉ Date4 = ADDDAYS(NOW(),-6)
Desired Setup:
◉ Date1 = ADDDAYS(Dynamic_Date,0)
◉ Date2 = ADDDAYS(Dynamic_Date,-1)
◉ Date3 = ADDDAYS(Dynamic_Date,-5)
◉ Date4 = ADDDAYS(Dynamic_Date,-6)
On top of these views is an ADSO in which a daily snapshot is to be stored.
Business ask is to store snapshots of at least 30 days in the past, which means changing the Date field 30 times in the view which will not be possible as it is the Production System.
Desired result is to dynamically populate the date without activating the views again and again.
Options We Tried
We tried the following solutions first to fulfil this requirement:
Using Table Function
◉ We maintained the desired Date in a separate Test Table in HANA.
◉ This can be any normal HANA Table.
◉ We then created a Table Function to lookup the Date from the above Table.
◉ Logic: Pick the Date from the Test Table; if Blank then take (Current Day – 1) as the default date.
◉ Code:
FUNCTION "<Package_Name>"."LocalPackage::tf_pass_date" ( )
RETURNS TABLE
(
"DATECAL" DATE
)
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER AS
BEGIN
/*****************************
Write your function logic
*****************************/
XYZ=
Select
CASE WHEN "DATE1" IS NULL OR "DATE1"='' THEN
CURRENT_DATE ELSE "DATE1" END AS "DATECAL"
from
"<Package_Name>"."<Path>::TEST_TABLE" where "CONTEXT"='AGG_DATE' AND "PARAMETER"='TO_DATE';
RETURN :XYZ;
END;
◉ Create a Wrapper View on top of this table.
◉ Create desired calculated columns to derive D-1, D-5, D-6
◉ Insert this view at the base projection and create an Inner Join with the existing Projections to restrict it to the Dates derived from the wrapper view.
◉ Result:
The view now restricts the data based on the Dates being looked up from the Test Table, via the Table Function.
◉ Why this didn’t work:
◉ Addition of multiple views at the base made the view heavy.
◉ Data Load into the ADSO which earlier used to take 2-3 minutes now started taking 20+ minutes.
◉ Even employing Inner Joins did not improve the performance.
Using Scalar Functions
◉ We maintained the desired Date in a separate Test Table in HANA, similar to what was done in the above step.
◉ Create a Scalar Function as follows:
◉ Logic: Pick the Date from the Test Table; if Blank then take (Current Day – 1) as the default date.
FUNCTION "<Package_Name>"."<Path>::test" ()
returns multi_count nvarchar(11)
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER as begin
declare cnt nvarchar(11);
declare cnt1 nvarchar(18);
Select "DATE1" into cnt from
"Package_Name"."<PATH>::TEST_TABLE"
where "CONTEXT"='AGG_DATE' AND "PARAMETER"='TO_DATE';
select TO_NVARCHAR(ADD_DAYS(CURRENT_DATE,-1)) into cnt1 from dummy;
if( cnt is null) or (cnt ='')
then
multi_count := :cnt1 ;
else
multi_count := :cnt ;
end if;
end;
◉ Create an Input Variable in the Calculation View to fetch the date from the Scalar Function.
◉ Input Variable won’t be Input Ready.
◉ Create a Calculated Column to convert the Input Parameter into Date format.
◉ Use this Calculated Column in the Filter Expression of the Projection to restrict the date coming from the Scalar Function.
◉ Snapshot_Date is a pre-existing Date field coming from the Projection.
◉ Result:
The view now restricts the data based on the Dates being looked up from the Test Table, via the Scalar Function.
◉ Why this didn’t work:
◉ Input Parameter failed to call the Scalar Function when the data preview of the View happens.
◉ When another Date Field (Like Snapshot Date) is pulled into the Data Preview, then only the Scalar Function gets called.
Final Solution
Using Input Parameters to lookup data from the table:
So far, we were trying to populate/maintain a Date externally and then lookup that date into the Calculation View(s). This Date then would’ve been used to populate the rest of the date: D-1, D-5, and D-6. This time we tried to maintain an Integer Value in the Test Table. Then use this value to determine how many days are to be subtracted from Current Date (NOW())
i.e.:
◉ Date1 = ADDDAYS(NOW(), INTEGER_FROM_TEST_TABLE)
◉ Date2 = ADDDAYS(NOW(), INTEGER_FROM_TEST_TABLE)
◉ Date3 = ADDDAYS(NOW(), INTEGER_FROM_TEST_TABLE)
◉ Date4 = ADDDAYS(NOW(), INTEGER_FROM_TEST_TABLE)
To achieve this solution, we tried the following approach:
◉ This time we maintained the desired Integer value in a separate Test Table in HANA.
◉ Create an Input Parameter to lookup this integer value from the Test Table.
◉ Create a Calculated Column to convert the value into a negative value.
◉ Note: Negative Value can directly be maintained in the Test Table as well; in that case this step can be skipped.
◉ Use this Calculated Column to Derive a common Date column:
◉ The “Date” calculated column can now be used to derive D-1, D-5 and D-6 dates.
◉ D-1 = adddays(“DATE”,-1)
◉ D-5 = adddays(“DATE”,-5)
◉ D-6 = adddays(“DATE”,-6)
◉ Result:
◉ The view now restricts the data based on the integer value being looked up from the Test Table, via the Input Parameter.
◉ Formal normal Production usage the default value will be set to 1; this can be easily modified using a SQL Statement to run for any other day.
◉ Performance also returned to the previous runtimes of 2-3 minutes.
No comments:
Post a Comment