Skip to Content
Technical Articles

Using Table Functions in SAP HANA for custom calendars

Hello,

Hope every one is doing good.

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.

I am writing this Blog to explain my approach of Implementing custom calendars. 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

 

This article covered How we can use the Table Function in a real scenario for Custom calendars. Through the example stated above, we could see how well we can use the table function in our VDM and how it can be customized for your needs.

All the readers out there, once you go through the above details, Please let me know your view and any other suggestion you may have. Also, Feel free to ask any questions about this. just for FYI. you can refer to https://answers.sap.com/tags/73554900100700000996 for Q&A in the Respective Area.

 

Thanks,

Sri.

 

2 Comments
You must be Logged on to comment or reply to a post.
  • Thanks a lot for sharing your design, Sri!

    If I may, a few comments:

    - SELECT ... FROM DUMMY is superfluous and doesn't help query performance

    - the output column name (ROWNUM) is not very self-explanatory. Instead, it would be better to name it after the concept it represents. In this case, "TECHNICAL_WEEK_NUMBER" would seem appropriate

    - the output column for ROWNUM is defined as NVARCHAR, while the ROWNUM function returns an INT. This will force an implicit type conversion, which is bad for resource consumption and query performance. I guess, this is what is causing the high 6 MB memory requirement for this tiny query.

    -  often special calendars not only have a rule on how to compute them but also exceptions (e.g. vacations, public holidays, etc.), It may be easier to manage those by actually creating a custom calendar table that contains this information pre-computed and editable.
    This also would make the query optimisation easier as this would be a relatively small data set to be joined in a single step.

    Anyhow, your solution does work for you, so the most important criterium has already been met 🙂 the rest is "optimisation"...

     

    Cheers,

    Lars

    • Hi Lars,

       

      Thanks for the valuable suggestion.

      About the Row number being defined as Varchar, It was defined based on the further joins n VDM. But, I understand the implicit conversion. Will definitely check on this.

      As mentioned in the approach this solution is working as "We have a standard set of rules for technical week Calendar". as you indicated, In case of Holidays etc., we would root for a factory calendar.

      Your suggestion did provide good insights on optimization.

       

      Thanks,

      Sri.