Using Table Functions in SAP HANA for custom calendars

It is a common case that every company have different projects and some of them needs custom calendar implementation for their project. A default approach would be to create a persistent table and populate it with the required custom calendar data. But, this will need continuous maintenance/Updates as the years pass through.  Also, it might need some type of procedure or data procurement methods to populate the data.

This one is particularly interesting as this approach eradicates the need for continuous maintenance or update of calendar tables. neither will it be based on a persistent table. I will move on in explaining the approach using an example.

Problem statement: Recently, I came across a problem where we had to filter data based on the number of technical weeks. This needed implementation of a custom calendar as the technical week definition is a little complex and cannot make use of standard formulas or factory calendars.

Technical week definition: Every Monday of week and also every 1st day of the month.

Approach: While there are several ways to solution this, I found the use of Table Function to be very easy and practical. Especially, as we know that the user is not going back for more than 2 years in terms of technical weeks. Also, we have a standard set of rules for technical week Calendar.

Table Function:

In the above screenshot of table function, you can observe couple of points.

◉ System Time dimension table – we use this as base for our calendar data.

◉ Row Number function – This is included for easier filtering based on user need. if user says we have to go back 6 technical weeks, I can simply select row number 6.

◉ Filter – first component: ‘110*7’ actually regulated how far back are we going and generating this records. in this case it is 110 weeks or close to 2 years.

◉ Filter – Second component: This actually determines the logic of calendar. in our case, we have to consider every Monday (day of week – 00) and every first day of month (day – 01) as a technical week.

 

Output: The output of the Table Function is shown below and you can see from Line 13 and 14 on how the Monday and subsequent 1st of January are considered as different technical weeks. Though the Gap is not 7 days as normally we have for a week. Also, the Row numbers tell us which week it is (in the backward order). Depending on the need, the row number can be reversed as well.

Usage: This table function is then used in the graphical calculation view and parameters are passes to filter out the required record out of this calendar. In our case, we Joined the table function with a Parameter table where user inputs are recorded and if the user select 14 as the number of technical weeks (that he/she what o go back to), the corresponding day (Jan1,2021 as shown in above screenshot) will be selected and used as the lower limit in date selection.

Observations:

1. The table function only consumes ~6MB during runtime. so, the Table Function do not cause any performance issues.

2. Persistent storage and update of a custom calendar table can be eliminated