Flow’s Date Lookup cube – Back to the Future!

Introduction

Firstly, sorry there hasn’t been a blog post for a while. Sometimes work commitments don’t play nicely with good intentions.

I thought I’d spend a bit of time talking about the advantages of using the SYS Date Lookup cube bundled with the Flow Server Manager. This cube has been invaluable to me during past implementations and is worth considering as a starting point for any project.

Time Travel for TM1 Rules?

Sometimes your rules and TI processes need to time travel. There are many scenarios where this is necessary. but I’m sure if you’ve got a few projects under your belt, you won’t need me to provide an example.

TM1 provides some basic calendar and time functions to deal with calculations that need to factor in the fourth dimension of our physical reality. However, they fall well short of those offered in Excel and other programming environments, and often you just can’t find an efficient way to do what you want.

How many times have you used the expression:

SUBST(
    'JanFebMarAprMayJunJulAugSepOctNovDec',
    (
        MONTH(
            DATE(['Date'] - 21916, 1)
        ) - 1
    ) * 3 + 1,
    3
);

?

Never? Oh, ok, maybe it’s just me! Anyhow, experience and logic will show you that it’s almost always better to look up such a value in a cube or attribute than it is to write a convoluted date/string combo like this.

The SYS Date Lookup cube won’t save you for the above example (you’d need a dimension with month numbers and a month name attribute for that), but is instead designed specifically to help with looking backward and forward in time.

Developers need to do this very often when creating rules that accumulate values over time, such as depreciation calculations, and, although you can use rules that look backward one index in the dimension, this approach is tricky when you need to look to a previous year (especially if Year is a separate dimension), and falls apart completely if someone changes the element order in the period dimension.

The Flow “SYS Date Lookup” cube

A seasoned developer with spend a fair bit of time considering their period dimension structure at the beginning of the project. With this, I think most of us probably also consider a date lookup cube, even if we don’t design it explicitly.

When starting on a project a few years back, I decided to create the ultimate date lookup cube, one which I could re-use for new projects. What I came up with formed the basis of the cube you find in the Flow Server Manager.

The first thing to note is that the cube is calendar based. If you use a fiscal year dimension, such as “Plan Year 1″, “Plan Year 2″, like we do in Propel Planning, you need to convert that to the correct calendar year.

Once you have a calendar year and month, you can use the SYS Date Lookup cube to look forward or backward any number of months up to 20 years from the current date.

Overkill? Probably, but at least you’ll never have to build one from scratch again!

How to get it

Simply fire up the Flow Server Manager (or if you don’t have it, get it here).

  1. Click the “Create New Data Folder” button.
  2. A dialog will appear asking you what options you would like to enable for the new TM1 data folder. Make sure you have “Date Lookup” checkbox checked.
  3. Type a server name and select the folder you would like to create the data folder in, then click the “Create” button.
This will create a new TM1 data folder with the SYS Date Lookup cube already included. If you want to include it in an existing model, you can take the files from this data folder and copy them manually into the target data folder.

How to use it

To look up a date using the SYS Date Lookup cube, you need to provide 5 parameters.

  1. Source Year
  2. Source Month
  3. Number of Years
  4. Number of Months
  5. Measure (forward or backward year or month)
For example, if you wanted to look 12 years and six months into the past, you’d use the following formulae:
[‘Year in the Past’] = N: DB(‘SYS Date Lookup’, !Year, !Month, ’12’, ‘6’, ‘Backward Year’);
[‘Month in the Past’] = N: DB(‘SYS Date Lookup’, !Year, !Month, ’12’, ‘6’, ‘Backward Month’);
This may look a little complex at first, but it is far simpler and more efficient than trying to write a rule to calculate it on the fly.

Conclusion

Using a date lookup cube is often necessary for efficient rule and TI development.

It’s definitely worth thinking about this aspect early in the project, and if you have matching year and month dimension, why not give yourself a little shortcut by using the Flow Date Lookup cube?

It’ll get you right into the future and back again safely.

1 Trackback