Month: November 2012

Back to the Future, Part II

Posted on by Harv

Introduction

Most financial models are based on the fiscal year, rather than aligning with the calendar year.

In many countries, these two calendars align, but in some it does not. In Australia, for instance (my home country), we have a Jul – Jun financial year, and both New Zealand and Japan have their own, too.

Propel Planning, Flow’s budgeting and planning application, always supported the idea of changing the starting month of the fiscal year, but we made some recent improvements which led to a better date lookup cube.

I thought I’d spend some time discussing it and provide a download link for those that are interested.

What was wrong with the old one?

Admittedly, there was not much wrong with the original date lookup cube, and it could still be used for most purposes.

However, there was often a necessity to map from Fiscal Year to Calendar Year and back again to do simple lookups, and we decided we it was worth finding a more efficient way to do this since it’s so core to the Propel Planning system.

To be honest, the former date lookup cube was something I built years ago, for us in consulting where the fiscal year period would be known well in advance of starting the job. And since I started working abroad, I don’t think I ever had a single implementation where the fiscal year was not aligned with the calendar year, and never really had to consider it.

However, Propel Planning is a dynamic, generic budgeting and planning system that can be sold world-wide, so it was time to face the music!

The Approach

A fresh look

I took a different approach this time, in several ways.

Instead of creating all the data in Excel and importing it into a TM1 cube, I created all the data in TM1 rules. The logic behind this was that we can easily export the rule-based calculations, delete the rules, then import the data as static numbers, so this technique just gives us an option to keep a rule-based lookup if we ever want to.

In the previous version of Propel, changing the start month was quite a significant effort. We had a Turbo Integrator function to do all the hard work, of course, but it had quite a lot to do – updating attributes, shifting data around in the date lookup, reordering elements. We even had a hidden cube that would provide data to the final date lookup when the TI process was run.

We realized this time round the designing a smarter, more flexible date lookup cube, along with the Month and Year Period dimension would make this configuration much simpler, and render most of the tomfoolery in the TI process unnecessary.

Here is the new data structure:

Generic Month & Year Period Elements

The most important part of designing this cube was avoiding the need to rename elements in the Month and Year Period dimensions. Instead of using “Jan”, “Feb”, “Mar” for the Month element names, we instead used “M01”, “M02”, “M03”, etc. Likewise, the Year Period dimension contains the elements “Y01”, “Y02”, “Y03”, etc.

This gives us freedom to interpret each month element as a particular named month, based on the Fiscal Year. So, in Australia, we would simply set the “Display Name” attribute on “M01” to “Jul”, and similarly for subsequent months.

Now we can always assume the element name “M01” is the first month in the fiscal year.

But it’s not quite that easy. We still need to make sure our date lookup cube calculates the correct calendar month for each Fiscal month.

Fiscal Month Offset

In order to do this, we use a simple attribute in the Month dimension called “Fiscal Month Offset”. This value is referenced by the date lookup cube when calculating the calendar month. The numeric value represents the number of months to “shift” the calendar month from the fiscal month.

For instance, if our fiscal year starts in July, we need to put a 6 in this value. The date lookup rules will then equate “M01” to “M07”, which is calendar month Jul.

This way, we can still look up calendar dates if need be.

Calendar vs Fiscal Date Measures

Hence, the date lookup cube supports two sets of measures, one for lookups that occur within the fiscal year, and another for lookups to calendar years. The simplest lookup is now to the fiscal year, with no requirement to translate to calendar month and back again to do a simple lookup.

Other Improvements

To reduce the number of measures, we also removed the “Backward” and “Forward” measures. This is now done using the “Offset Month” and “Offset Year” dimensions, which now include negative numbers.

This is more articulate, and simplifies rules which rely on numeric calculations to determine the number of months to search forward or backwards in time.

Example Usage

 

To lookup 3 months in the past from a fiscal-based cube:

# Lookup the year

DB('SYS Fiscal Date Lookup',

	!GBL Year, !GBL Year Period, !GBL Month,

	'0','-3',

	'Target Fiscal Year');

# Lookup the month

DB('SYS Fiscal Date Lookup',

	!GBL Year, !GBL Year Period, !GBL Month,

	'0','-3',

	'Target Fiscal Month');

Conclusion

With the new cube structure, it is much easier to lookup months and years in the past and future in fiscal-based cubes. Concordantly, it is no more difficult to use the cube in a calendar-based design.

Now where we’re going, we… er… don’t need roads… Sorry, I know it doesn’t really fit, I just wanted to say it.

Returning values from Turbo Integrator processes

Posted on by Harv

Introduction

The two cornerstone features of any generic programming platform are encapsulation and reuse. These related ideas are simply the ability to write a piece of code once and then use it many times.

Being able to call processes within other processes is a useful technique for performing a set repeated task, but what if you would like the two processes to interact and communicate?

“Hey”, I hear you say, “TI has that covered, doesn’t it?” Well, sort of!
Yes, the ability to call a process from within another can allow processes to act like functions or procedures, and yes, they do accept parameters, so the calling process can modify the behavior of child process.
But what about return values? What if you want to build a utility function that performs some processing, and returns a value to the calling process?
This post will discuss various ways to tackle this very problem.

Techniques

Global temporary cube

Since cubes are globally available to all TI processes, it’s a natural first choice in storing global variables that can act as return values.

You would probably take the following steps:

  • The calling process would call a initialization function, that would receive a parameter to tell it what particular “global variable” measure to create.
  • The calling process would then call the child process, passing a parameter containing the name of the “global variable” measure.
  • The child process would do it’s processing, and write it’s result to the parameter cube using the appropriate CellPut function.
  • The calling process would read this value and continue.
Immediately, you can probably see issues with this approach. For one, it’s pretty messy, and creates annoying control cubes, and elements with temporary names. Sure, you can hide these with security, but it still clutters up the view for administrators.
Secondly, it’s quite inefficient. You could potentially have predetermined elements for your return values, but then you have to worry about concurrent processing and if a user mistakenly interferes with values in the cube using Perspectives or Excel. The speed of writing to and reading from a cube is probably not optimal for such a core requirement.

User-defined Global Variables

TI supports the idea of user-defined global variables. These are the same as local variables, but are available to all the functions being called within the call stack of the parent process or chore group. This is quite useful for the purposes of return values, and avoids the mess of using cubes.

Here’s an example:

StringGlobalVariable('sReturnValue');
ExecuteProcess('GetReply', 'pStatement', 'Hello');
sReply = sReturnValue;

In the child process, you could do the following:

StringGlobalVariable('sReturnValue');

IF(pStatement @= 'Hello');
    sReturnValue = 'Goodbye';
ENDIF;

ProcessBreak;

The end result of this would be your variable, ‘sReturnValue’ would contain the string ‘Goodbye’.

The limitation of this approach is mostly dictated by the maximum length of string values in Turbo Integrator. In the current version, TM1 10.1, this is 65536 characters, which is ample for many applications. Just be wary of using this technique in earlier versions of TM1, because prior to 9.4 and the Unicode upgrade, the maximum length of a string was 256 characters.

Update: The upper limit of a string passed to ASCIIOUTPUT is 64K. However, a string variable appears to be able to have a larger size, depending on the amount of available RAM. On my laptop with 4GB RAM installed, I was able to create a string variable of around 512K. Anything larger and my entire system would crash!

You’re also well-advised to grab the value of the global variable immediately after the ExecuteProcess call and assign it to a local variable. This is simply so you don’t get confused if making multiple calls to other processes which may use the same global variable name as others called in your parent process.

Conclusion

TI Libraries like Bedrock are great for processes that perform stand-alone operations, but do not support the idea of reusable processing functions that return a value to their caller.

These two a simple techniques can be extremely powerful for creating generic, reusable processes. Start using it in your development, and you’ll begin to see what I mean!

Happy modelling!