The SAP BW Formula Builder Engine is available for use in Transformations and Process Chains. While it offers its functionality to both equally, it does not reflect the true needs of each. The available formulas have been built up over the years based upon practical usage, with new ones occasionally added. This implies that the list of available features should always be considered … incomplete.
When we take a step back and look at features involving Dates, it becomes clear that Transformations and Process Chains do not always have the same time of day alignment requirement. The usual comparison of dates is centered on the concept of day time; where that day is from midnight to midnight.
The WEEKDAY() function will return a ‘Day of the Week’ number aligned from midnight to midnight where Monday = ‘1’ through to Sunday = ‘7’. It is fundamentally based upon a date only, no time.
“so where are all the formulas with an
alignment of midday to midday?”
The ability to conditionally execute based upon an alignment centered around midnight, from midday to midday, is very useful. If only there was a WEEKNIGHT() function available. It would fundamentally be based upon a date and time. A WEEKNIGHT() function would return a ‘night of the week’ number aligned from midday to midday.
- ‘1’ is Monday Night (12pm Monday to 12pm Tuesday);
- ‘2’ is Tuesday Night (12pm Tuesday to 12pm Wednesday);
- ‘3’ is Wednesday Night (12pm Wednesday to 12pm Thursday);
- ‘4’ is Thursday Night (12pm Thursday to 12pm Friday);
- ‘5’ is Friday Night (12pm Friday to 12pm Saturday);
- ‘6’ is Saturday Night (12pm Saturday to 12pm Sunday);
- ‘7’ is Sunday Night (12pm Sunday to 12pm Monday).
WEEKNIGHT Function in Process Chain
The design and execution of a daily Process Chain can include a Decision Process Variant to execute a branch only on a particular day. Quite often the nightly load window will utilise the WEEKDAY() function to ensure that branch only executes on that specific day.
For Example: WEEKDAY() = ‘3’ for Wednesday.
At first glance this seems fine, so why waste time tweaking it to implement WEEKNIGHT() = ‘3’ instead? Lets take a step back and look at a few of the core features of a process chain:
- Starts at a point in time or after an event;
- Executes the Process Variants in order;
- Can never guarantee a sub-chain starts/finishes on time;
- Most use the ‘continue only after success’ links;
- At the mercy of how long a BW Support person takes to fix failures;
- Has little control over the process slot priortisation;
- Has no control over the available hardware.
When a process chain uses the WEEKDAY() function it places an additional time limitation onto each execution. If the process chain does not make it down to execute the Decision Process Variant by midnight then an entire branch of the process chain will not be executed, when it should have. It is that simple.
Unfortunately this is not in alignment with the spirit of any Nightly Process Chain execution. The business requirement was very clear:
“execute these jobs every Sunday night; but then
we technically added a ‘before midnight’ limitation”
As at BW v7-3 there is still no WEEKNIGHT() function available to simplify the implementation of any business requirement implemented within a nightly load window that needs to be aligned to ‘Tonight’.
WEEKNIGHT Function is Possible
Fortunately, we can add new custom functions to the SAP BW Formula Builder. Check out the ‘WEEKNIGHT in Formula Builder’ article for the ABAP code required to make this happen.
While the solution is initially targeted at an alignment of ‘Tonight is Midday to Midday’ there is nothing stopping the ABAP developer from tweaking the WEEKNIGHT() function to use a different time of day alignment that matches an Enterprises specific needs.
It is very easy to adapt the solution to use an Enterprise definition of ‘Tonight is 3pm to 3pm’.
* What is the 'Nightly Load Window' time alignment within a day? "MANUAL MAINTENANCE: Assign the Nightly Load Window time. "l_time = c_time_9am. "l_time = c_time_midday. l_time = c_time_3pm. "l_time = c_time_6pm.Take Action Now
This small enhancement can be easily implemented by any seasoned BW ABAP Developer. Please allocate some time and implement the WEEKNIGHT() function in your BW system, this week. If you are having trouble convincing your managers manger to prioritise this activity to be built sooner rather than never; then don’t talk Tech to them, talk Business.
The real world cash flow implications of ‘Compounded Support Debt from Technical Debt’ article outlines why leveraging the BW Support Teams time later is a False Economy and costs the Enterprise a lot more real cash in the long run.
What other functionality would you like to see added to the SAP BW Formula Builder Engine?