How to define and manage the time range

Center of Excellence

1. Abstract

In Board, the definition of the time range becomes very important because it guides the data that will be loaded and made visible to the end user.

When setting the time interval, there are some considerations to bear in mind to avoid running into unpleasant situations during the development phase, where the system shows poor performance.

2. Context

When defining the time interval, there are several time settings (such as From Year and To Year, Day, Week, Quarter or Fiscal Year) that can be defined and activated, and developers must decide according to the business requirements with full awareness of the impact they generate on the application.

3. Content

Defining the time interval implies awareness of the following elements:

• Time width, i.e., number of periods (years) within the From Year and To Year;
• Time Dimension options (Day, Week, Quarter and Fiscal Year)

3.1 Time width

Defining the time interval of a database correctly is very important because it contributes to defining the final usability, which is what the user perceives.

The best practice is to restrict and manage only those years that contribute to determining and explaining the customer's business model.

This means avoiding including observations on the past for years too far away from the current period (usually at most two to three years in the past).

Likewise, in future periods, it is advisable to restrict the scope for a limited period (a couple of years).

Board is not a data repository and being an in-RAM technology, the wider the time gap to be considered, the greater the RAM consumption required to manage the data in memory.

If the customer nevertheless claims the need to see a lot more data about the past, it is highly recommended that an additional database be created to accommodate that information.

3.1.1 Changing the time range

During the application’s life cycle, there comes a time when it is necessary to narrow down or move the time range closer to the actual period of activity.

For instance, a rather common request under the GDPR regulation requires that all data older than a certain time threshold to be cleaned, or simply time has passed and the application needs an additional future year and fewer years in the past to keep functioning as usual.

Adding additional years in the future (“To Year”) does not require any extra activity other than a maintenance window and a restart of the service.

It is however more complex to increase the “From Year” value, which means reducing the size of the time range. To manage the time shift correctly, the following steps must be followed in order:

1. Extract all cubes.
2. Setting the sort of any Custom time entity to None
3. Extract the time tree and Custom time entities
4. Clear all cubes.
5. Increase the “From Year” value.
6. Reload all cubes.
7. Reload Custom time entities to recreate the members
8. Reload the time tree to regenerate the relationships with the custom entities
9. Restore the Sorting setups
10. Restore any fixed selection or ReferTo on a time entity according to expectations

Some notes on the steps above to explain better why they are suggested:

• Clearing and Reloading cubes: Moving the “From Year” value clears all the Info-Cubes, therefore is necessary the extract cubes step first. A warning message reminds you the data model is cleared while you are performing the change. Board clears the Info-Cubes because the time dimensions (Day, Week, ..) and the time functions (PP, PY, YCV,..) are based on the “From Year” setting and therefore have to adapt to the new time range. As result, the application restricts itself to the set years and all historical records prior to the “From Year” value are discarded. This means that all the Info-Cubes are lighter since the amount of stored information has been restricted. Moreover, we suggest performing an additional “Clear all cubes” action which will take the chance to optimize all the cubes structures.
• Extract and Reload custom time entities and relationships: when the time range is changed BOARD will recreate the entire time tree setup; this action will clear any “Custom time entity” member and therefore it is important to extract them and recreate them as part of the maintenance activity;
• Restore selections: since we are moving the time range we are shifting the internal IDx of the members; this means that the previous IDx will now refer to a new element in the entity (i.e. the starting Year is 2022 now instead of 2021, so the IDx 1 is assigned to 2022); this could be according to expectations or not depending on case by case; for this reason, we recommend that, as a final step of this activity, any fixed selection or refer to on native/custom time entity is checked and if necessary repointed ;

3.1.2 Archive historical data

The best practice is to avoid storing all the historical years in the main data model but archives them in a dedicated data model only for historical reporting purposes. The suggested approach foresees a synchronization process to extract and load only the relevant cubes and structures needed for reports and dashboards from the main data model to the historical one.

3.2 Time dimension options

When configuring the time range of the database, you may come across the use of certain time options (Day, Week, Quarter, Fiscal Year).

For simplicity's sake, let us approach the discussion by considering the day option, but the same reasoning applies to the other available time options.

Having the Day option enabled, means that the Info-Cubes can host the transactional data at the day level.

The activation of the day makes the time range more complex to manage. Since the day is activated the time range includes an additional level, therefore managing a custom time entity can become more cumbersome. If we configure for example a custom time entity connected to Month and a change occurs, especially if managed by the BOARD users, then normalization of the time tree is necessary to generate the indirect relationships.

Similarly, the more time options we activate (Day, Week, Quarter, Fiscal Year) the more complex the management of the time tree becomes.

It is therefore best practice to always evaluate carefully whether it is necessary to have any extra time dimension in the database and not advised to enable additional ones if not needed.

Also consider that, if needed, these extra time dimensions can always be activated after the creation of the data model.

Anastasia Pesyakova

Great article, thanks for the detailed notes. An additional step to consider when changing a time range (whether From Year or To Year) is to update any admin/utility cubes with time entities in dimension after the cubes are reloaded. For example, we often use utility cubes flagging first period of each fiscal year and the last period of fiscal year for balances carry forward calculation. When adding years to the database, we need to flag the periods related to these newly added years so that the calculation runs correctly for them.