Dynamically manage calendar settings

Center of Excellence

1. Abstract

In many cases, we will need certain mappings in our Calendar to define specific time frames for reporting and planning purposes. Some of those may be custom time dimensions and others might be created by mapping the standard calendar to specific periods.

All those mappings have to be kept up-to-date. That can be done manually by an administrator but it would be more efficient if it would be updated automatically.

2. Context

Keeping calendar settings up-to-date in a way that requires the least manual interaction is a best practice for any Board application. In this article, we will be taking a dive into the most applicable methods of doing so.

We need to keep in mind that most of our desired mappings can be done either by adding appropriate custom time entities and relationships as well as using dedicated mapping cubes. But, custom time entities will impact the complete database, the time range is one of the most important components of a Board database and any adjustment should be treated with care, whereas mappings provided by dedicated cubes will only affect the data and relations maintained therein. And, in contrast to custom time entities which need to be n:1 related to their respective parents, you could handle more than two axes in a mapping cube (i.e. open periods for different scenarios in Forecast and Budget). Moreover, there are only 4 custom time entities available, it should therefore be carefully considered whether introducing custom time entities to the model is really necessary to achieve your aim.

3. Content

As a best practice recommendation custom time entities are only necessary when you want to make use of Board’s integrated time functions (e.g. previous year, period-1, …). On the other hand, custom time entities will require only one tree to be managed as opposed to a considerable number of cubes and/or relationships.

We will be discussing different use cases as examples for the different approaches in order to provide some “real life” illustrations.

3.1 Custom Time Dimension

Scenario: In a reporting application arises the need of comparing sales on a day-to-day basis (e.g. comparing weekend sales of Friday/Saturday to last week’s Friday/Saturday). So, we would need a generic weekday as parent of the standard day. We are not making use of the mask-function here for two reasons—simplicity of the example and providing the option to show the code of the weekday in a layout.

Solution: In a first step, the members for the desired dimension have to be populated according to Table 1 and then we need to connect them directly and indirectly to the other time entities according to the defined relationships. This connection will have to be kept up-to-date.

We need to connect each day (and of course—indirectly—the other members of the tree) of the database to a dedicated member of the weekday entity. The steps to be followed will always be the same:

1. Extract the tree “Day”
2. Reload the tree, populating the relationships via DataReader

For this purpose, we’ll create a procedure “CTD – Custom Time Dimensions”

The Datareader’s ETL uses the built-in function “Weekday()” delivering a number for each day of a week (in the standard this will be 7=Saturday, 1=Sunday, …).

Formula:

F4 = IF(E4=7,"Sat", IF(E4=6,"Fri", IF(E4=5,"Thu", IF(E4=4,"Wed", IF(E4=3,"Tue", IF(E4=2,"Mon", IF(E4=1,"Sat")))))))

The DataReader will normalize the tree, assuring that direct and indirect relationships are correctly set. The ETL will always deliver the correct results—therefore the procedure should be run each day in the nightly load to keep all relationships up-to-date.

! Note: Whenever you implement additional custom time dimensions (or delete some you don’t need anymore) the Datareader has to be adapted to reflect the most current situation.

This approach can be used with any custom time dimension that is part of the Day tree. In some cases it’s as easy as described above, in other cases (where you cannot derive the intended code of the dimension programmatically) you might need an intermediary step to calculate the correct code. Look at the 4-4-5 calendar for example.

3.2 Current Year

Usually, the built-in functionality for time dimensions allowing you to dynamically select by “current date” should suffice for a great deal of requirements. There might, however, be situations where it is necessary to select more than a directly related group of time dimensions. For example, selecting the current year in a forecasting scenario will not select months in the complete forecasting period covering part of the current and following year. Additionally, the forecast months for input plan values will start at a new period every new month.

We will start with the simple example and get to the more complex scenario later on.

3.2.1 Flagging just the current year

Scenario: A reporting application needs its screens to be set to the current year as current reporting period. This could, of course, be covered by the select based on “Current date”—It is just the starting point of this example.

Solution: In order to select the current reporting period we can use the “select based on cube” option in the select window (or respective procedure step). The appropriate cube would be an integer cube with just the dimension “Year”, having “1” for the current year.

This cube, as should be the case with all administrative cubes, will be available for input on an administrative screen. But it should be kept up-to-date dynamically, too.

3.2.2 Flagging the current year in a procedure with Dataflow

The example cube can be populated and kept up-to-date with a small procedure—either with or without user’s interaction (whenever necessary for administrative purposes).

Since we’re flowing a constant into the flagging cube, we need to extend the calculation on “Year”.

3.2.3 Flagging the current year in a procedure with Data Reader

It is possible to achieve the same result with a Data Reader that offer some possibilities in its ETL functionality going beyond algorithms in dataflow actions.

To use a Datareader you would first extract the entity “Year” and then reload it into the cube applying an ETL to achieve the correct mapping.

The ETL uses the function year(today()) to determine the current year. This is then applied in a formula to derive the correct flagging.

The according steps in the procedure will then be as follows:

3.3 Complex example – open months

Scenario: Let’s consider a planning application with a rolling forecast.

In our example, forecasting shall always be possible for the current and 5 future months; each month, the period must be shifted 1 month into the future until next planning period. So, if the current month were July, it would look like this:

Solution: For each phase of the planning period the open months (those that still can be planned) shall be flagged with a “1” while the other months shall be set to 0.

To use an “Open months” cube for the application, we introduce a new entity, ‘Planning Period’, to distinguish different planning objectives.

To begin with, this entity will have the following members:

The cube ‘Open months’ is set as an integer cube with two dimensions ‘Month’ and ‘Planning Period’:

From the front-end in the administrative screen, this cube looks like here below:

The cube’s content must be kept dynamically updated: the months open for budget input move once a year and the months open for forecast move every month, as in Figure 10.

Assumption: the budget always covers the following year (CY+1)

Therefore, we can update the ‘Open Months’ cube for budget entry as follows:

1. Clear the cube
2. Select CY +1
3. Flow a “1” into the cube

The procedure steps according to that will be like this:

The selection of CY+1 deserves our special attention; that should be implemented as follows:

The Calculation of months to be open for forecasting is a bit more complex—although the period covers 6 months, the period’s start will have to shift on each month’s end.

We will need one additional cube holding the period’s duration in months. This cube is called “Period Duration” and dimensioned by Planning Period, only. It will come to use in a time offset.

The process will then be as described here:

1. Clear the cube
2. Flag the first open FC month (current month)
3. Flag month after the last FC month
4. Calculate the flags in-between

The selection of the first month to be flagged as open works like the selection of the budget year (see Figure 15).

At this stage we need to set the duration in the dedicated cube:

The latter is than used to calculate the ‘last+1’ forecasting month, i.e., to identify the seventh month onward (January 2024) from the current month (July 2023) and set it equal to 2 as value:

The ‘Period Duration’ cube is part of the layout because shifts ‘Open Months Cube’ value forward in time based on the number of periods specified in it (=6):

After this step the cube will be populated with ‘1’ for the first FC month and with ‘2’ for the ‘last+1’ forecasting month:

In the next step we use the “Last value” time function on the ‘Open Months’ cube which replicates the last non-zero value found in the time series (value 1 in July 2023) into subsequent zero-valued cells until a new non-zero value is encountered (value 2 in January 2024), then the function replicates the new value into subsequent zero-valued cells.

Since we are interested in having cells with value one, our expression is as follows:

After this step, our open months are correctly populated:

And keep in mind: since we are using references on the current date, both procedures can be implemented as part of the night run to properly keep the system always up to date.

3.4 Introducing closed months

We can use the same concept for determining the month prior to the current month to be open for forecasting too, if it has not been closed in our ERP system.

Then, the forecast values of the previous month must be overwritten by the actual data and must be excluded from the input.”

Scenario: The month preceding the current month must be open for entry, if it has not been closed. Closed months must be considered.

Solution: We will need another cube to reflect the months that have ended commercially – months that are closed.

The ‘Closed Months’ cube is an integer type and is only dimensioned for Month.

By setting the forecast duration to 7 instead of 6 months and selecting the previous month (instead of the current month), the calculation will be the same as before.

After the calculation, however, we must check whether the previous month is still open and set it as ‘closed’ if the ‘Closed Months’ cube indicate this.

The population of closed months is normally taken from the ERP system.

In our example we will flag the months manually:

For verification, it is necessary to examine the cube value of the previous month as follows.

With the selection active on the previous month, we can check whether it is closed as follows:

And consequently, set it ‘closed’ (i.e., delete it from the open months cube) if the condition above is true.

The updated procedure will therefore look as follows:

At this point, all Calendar Settings have been introduced,

• Current Year
• Custom time dimension
• Open Months

can be set dynamically using references to the current date and a simple export/import step with ETL for the custom time dimension.

The procedures can be included in the nightly loading and the system will always be up to date without the need for manual interaction.

The ‘Closed Months’ cube usually would be populated by a Data Reader that extracts the current status from the ERP system.

4. Conclusion

Custom Calendar enhancements common to most planning applications can quite easily be kept up to date in a dynamic way reducing probable faults in manual maintenance.

Nevertheless, those settings should be available for an administrator to adjust whenever there is need to do so.

Implementing the necessary steps takes a while but is definitely worth it.

Center of Excellence

Thanks @Helmut Heimann for the insights on this article!