Situation & Challenge

 There may be a time when an organisation changes its source of record for a business service, such as service desk system. Doing so can cause a significant change to a data set, whereby the structure of the data, including column headings, is completely different from a certain month on-going into the future.

To solve a recent real-world example of this whereby an Apptio user wanted to import new (and more detailed) service desk data, a solution was sought to maintain an historic allocation based on the old data, whilst also driving a new allocation to a new object from a certain time period onwards, based on the new and improved data set. The challenge was also to deactivate the ‘old’ allocation at the same time the new one was introduced.

(Note that there was no requirement to maintain reports based on data in the old destination object, because new reports were made based on the new destination object)

Solution

One solution to this challenge is to create a ‘Version’ of the allocation, from one date period onwards, but this comes with the requirement to manage the version with any future config updates, and would typically be more applicable when the allocation was going to the same destination object.

The alternative method proposed here is a simplistic yet very effective way of keeping both the old and the new allocations intact and active in their relevant time periods. The basic concept is to filter the data out of the old destination object, based on a column of data which acts as a ‘date-based switch’.  The switch column can be used to filter out the old data set out from a specific point in time, therefore deactivating the allocation.

Steps

This can be done by creating three new columns in the Old Data Set, primarily using the ‘CurrentDate’ and ‘Days’ functions within TBM Studio. The example below is based on data switching over from May 2021 to June 2021.

 
1. Current Date

 The CurrentDate function returns reference to the current time period, based on the date format selected.

For example:        Current Date =CurrentDate("dd-MMM-yyyy")              returns                   01-May-2021

 
2. Days

The Days function can be used to derive a numeric reference, based on the return of CurrentDate, which can then form the basis of the synthetic switch column. As noted in the above link, this value is the number of days since 1^st January 1970.

For example (when in time period May 2021):             Days = Days(Current Date)                returns                   18748

The next step is to create the column which will act as the ‘switch’. In the example below this column is name ‘Allocation Rule’. The date from which the data needs to be filtered out should be defined as a number, based on number of days since 1^st January 1970 as per the Days function.

For example, to filter out data after May 2021, the column will require a formula as blow:

Allocation Rule = If (Days < 18749, "Allocate", "Don't Allocate")

The naming convention of this column is, of course, completely up to the user. This example is terminology that made sense for this Apptio user.

Add a ‘To’ filter in the allocation to the old destination object.

High Level Concept Diagram

For further information on how to use this function please see these links:

https://help.apptio.com/en-us/studio/formulas-and-functions/functions/currentdate.htm

https://help.apptio.com/en-us/studio/formulas-and-functions/functions/days.htm