In the previous blog post, I introduced the topic of fiscal calendars in SAP Datasphere. The blog post also explained one way to generate such calendar, namely by leveraging an SAP source system fiscal calendar using an existing built-in procedure in SAP Datasphere.

In this blog post I will introduce another way to generate a fiscal calendar. Instead of relying on an SAP ABAP source system as we did in the previous blog post, this time the calendar is generated entirely in SAP Datasphere (standalone!), within a SQLScript view. You can easily re-use the code in this blog post for SAP HANA Cloud as well.

The code that I will share in this blog post does not feature the exact same options as the one from the previous blog post. It provides two options:

1. Week-based fiscal calendar generation. This option is often used in retail to be able to make analyses where period comparisons include the same name number of weeks, and allow for KPI’s that include week on week, and week vs. last years week comparisons.
2. End-date based generation. This option supports fiscal calendars of which the fiscal periods deviate from regular calendar months, and (optionally) when the last day of a period is not the last day of a regular calendar month. For example, the last day could always be the 15th of the month.

The fiscal calendar generator introduced here might well cover your scenario, but it does not cover all possible fiscal calendars options. In the case it does not match your scenario, at least you got a bunch of code and a method to create another type of fiscal calendar.

Step 1: Create a SQLScript view in the Data Builder

Ok, a separate heading for this step might have been a bit too much. I assume you know how to create a SQL view and change the language to SQLScript. Do this directly when creating the view and not after copying the code. The idea is that this view will become the fiscal calendar, after adding some code.

Step 2: Copy the code

Simply copy all the below code into the editor. I would also advise you to run through the code to get an idea of what it does. You will have to make adjustments in section 2 of the code where the variable assignment is done, but you might want to first run a test if everything runs fine and only afterwards make changes. Don’t deploy yet – you will run into an error.

	/** 1. Variable definition **/
	declare lv_fiscal_type nvarchar (100);
	declare start_date date;
	declare start_f_year integer;
	declare lv_max_periods integer;
	declare arr_weeks_per_period integer array;
	declare arr_period_end_days integer array;
	declare lt_f_calendar table (DATE date, F_WEEK integer, F_PERIOD integer, F_YEAR integer);
	declare i_period integer;
	declare i_period_end_days integer;
	declare i_week integer;
	declare i_day integer;
	declare lv_date date;
	declare lv_f_week integer;
	declare lv_f_period integer;
	declare lv_f_year integer;

    /** 2. Adjust below variables to generate the calendar */
	lv_fiscal_type = 'week based'; /* 'date based' | 'week based' */
	start_date = to_date('20200116','yyyymmdd'); /* first date of the first fiscal year */
	start_f_year = 2021; /* first fiscal year, this might differ from the first date of the first fiscal year */
	lv_max_periods = 12; /* number of periods in a fiscal year */
	arr_weeks_per_period = array(
	    4,5,4,4,5,4,4,5,4,5,4,4,
	    4,5,4,4,5,4,4,5,4,5,4,5,
	    4,5,4,4,5,4,4,5,4,5,4,4); /* each array element represents a fiscal period, with the number representing the number of weeks within that period */
	    
	arr_period_end_days = array(
	    15,15,15,15,15,15,15,15,15,15,15,15,
	    15,15,15,15,15,15,15,15,15,15,15,15,
	    15,15,15,15,15,15,15,15,15,15,15,15); /* each array element represents the last day date of a fiscal period */

    /** 3. Below the calendar is generated, based on the variables set in above section, the code below does not need to be changed **/
	lv_date = :start_date;
	lv_f_year = :start_f_year;
	lv_f_week = 1;
	lv_f_period = 1;

    /* week based generation */
    if :lv_fiscal_type = 'week based' then
    	for i_period in 1 .. cardinality(:arr_weeks_per_period) do
    		if :lv_f_period = :lv_max_periods+1 then
    		    /** move to first period of next fiscal year **/
    			lv_f_period = 1;
    			lv_f_year = lv_f_year + 1;
    			lv_f_week = 1;
    		end if;
    		for i_week in 1 .. :arr_weeks_per_period[:i_period] do
    			for i_day in 1 .. 7 do
    				insert into :lt_f_calendar values (lv_date, lv_f_week, lv_f_period, lv_f_year);
    				lv_date = add_days(:lv_date,1);
    			end for;
    			lv_f_week = :lv_f_week + 1;
    		end for;
    		lv_f_period = :lv_f_period + 1;
    	end for;
    /* end date based generation */
    else
    	for i_period_end_days in 1 .. cardinality(:arr_period_end_days) do
    		while 1=1 do
    		    /** week is not generated, therefore entry for week is NULL **/
    			insert into :lt_f_calendar values (lv_date, NULL, lv_f_period, lv_f_year);
    			lv_date = add_days(:lv_date,1);
    			if dayofmonth(:lv_date) = :arr_period_end_days[:i_period_end_days]+1 then 
    			    /** move to next fiscal period **/
    				if :lv_f_period = :lv_max_periods then
    				    /** move to first period of next fiscal year **/
		    			lv_f_period = 1;
		    			lv_f_year = lv_f_year + 1;
		    		else
		    			lv_f_period = :lv_f_period + 1;
    				end if;
    				BREAK; 
    			end if;
    		end while;
    	end for;
    end if;
	
	return select DATE, F_WEEK, F_PERIOD, F_YEAR from :lt_f_calendar;

Step 3: Define the columns

After adding the code, if you try to deploy, or click the Validate SQL button, you will be presented with an error and a warning message. The error incorrectly states that there is a syntax error, so don’t even look into that. The issue is that the editor cannot figure out what the output definition of the SQLScript is, which is correctly indicated through the warning message. Therefore, you’ll have to define the columns yourself.

Below is the definition you should add yourself, after clicking the Edit columns button. The Business Name is optional and up for your creativity.

Figure 1: Required column definition

Step 4: Preview your data

You’ll have to deploy the view before you can preview the data, but once you’ve done this, the output should look something like this.

Figure 2: Data preview of the fiscal calendar

There you go, there’s your fiscal calendar. Now, this would be the moment you make changes to the variable assignments in section 2 of the code, if you hadn’t already done that. You will need to re-deploy for every code change to take effect.

Step 5: Persist the view

So your fiscal calendar is now ready to be used for inclusion in any other view. However, one optional but highly recommended step is to persist the fiscal calendar view. Generating the output takes a little processing power, about 1-2 seconds in my tests. You don’t want to add that to any of your total query time, and this is also not necessary because the output of the code is static anyway. Therefore, make sure you persist the view so that the code generation is only done once.

Figure 3: Persist the view

Conclusion

With the above steps you have generated yourselves a brand new and shiny fiscal calendar. All in all, these steps take less time than the SAP ABAP source system based approach as explained in the previous blog post, as you don’t need to source the metadata tables from your source system. Also, it adds the week-based calendar option. However, it does not fully replace it, so have a good look at what your requirements are. In the case that neither option fulfils your requirement, hopefully the above method and code will help you.

Now that we have the fiscal calendar ready, it’s time for the actual analytics part, namely the logic to calculate KPI’s based on fiscal year and periods. That is saved for the next blog post in these series