Back to the Future, Part II

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.

An Item-based Approach to TM1 Model Design

Introduction

James and I have been working together for some time now, and we have evolved toward what I would class as a non-typical design approach in TM1. For the sake of simplicity, I refer to this as an “item-based approach”.

It is very prominent in our implementation of Propel Planning, and underpins a lot of the power of that particular product.

I thought it worthy of some discussion, as it has many advantages, and a few gotchas, that are worth considering in any implementation you might be involved with.

The Approach

The item-based design approach has the goal of allowing data input in a flat, tabular format without giving up the browsability and analytic capability of highly dimensional cubes. It also separates input from reporting in a very elegant fashion.

You begin with an input cube, which should have only the basic dimensions, plus a measures dimension to represent the columns of the input table, and item dimension to represent an arbitrary number of rows.

The measures dimension will include many string elements which map to other dimension elements. Thanks to the picklist feature in TM1 9.5+, these lists can even be restricted to ensure invalid entry does not occur.

A separate reporting cube is then created that maps the string elements to actual dimensions, for reporting and analysis, usually via rules. This cube has no data entry and populates itself entirely from data in the input cube. You could also use TI to populate such a cube without too much trouble, for implementations that have higher data volumes.

I call it item-based, because this approach naturally requires an item dimension, with arbitrary names. Most of the time we just call the elements “Item 1”, “Item 2” etc, up to a maximum amount. Because this maximum is imposed, it is important to keep the efficiency of the model from being affected by the number of elements in the item dimension. More about that below.

Advantages

There are many advantages to such an approach.

Data-entry simplicity

New users of TM1 are often uninitiated, and, dare I say it, sometimes under-prepared by standard TM1 training courses, to understand the full advantages of a multi-dimensional database model. It doesn’t matter what you do, some users have spent way too much time in Excel or Access and simply think in terms of tables.

And why should they bother? Many of these users are simply data contributors, and do not have any interest in performing extensive analysis on their data.

The flat input approach allows such users to contribute their data in a way that makes sense to them.

It also allows them to adjust manual inputs and correct errors without cutting the data from one intersection and pasting it in another, an operation which can be error prone and, let’s face it, slightly buggy in the TM1 Perspectives cube viewer, and difficult in the TM1 Contributor front-end.

Maintainability & Agility

TM1 implementations are naturally agile and flexible. Developer with an IT background like myself might fight against it, and try to impose strict, inflexible Business Requirements and a rigid change request process to protect against scope creep, but that really undermines one of TM1’s key advantages in the market place: agility.

Imagine a retail sales model, which has Region, Sales Rep and Distributor as data points of interest. Sales reps and other users contribute data from the field using their laptops.

In a typical TM1 design, you’d create a cube with Region, Distributor and Product as dimensions. The input form would ask the user to select elements from each of those 3 dimensions and would write the sales/inventory data to the intersection of the elements chosen.

All is good, and the managers and finance staff can browse the cube and get the insight they need.

However, imagine, after months of data has been collected, someone in head office decides they would like to also track the data by Customer Type. The data already exists in the point of sale system, as each customer is tracked by credit cards and loyalty cards they use when making a purchase.

With your typical design, you don’t have a lot of choice, but to redesign from scratch and create a new cube with the additional dimension. You might choose to keep the existing cube for backward compatibility, in which case you’d have two sources of the same data, which could lead to synchronization issues since the original data is manually contributed from Sales Reps in the field.

It’s your basic nightmare, and if you were halfway through the implementation, you’d probably tell your customer that it’s a change in scope and that it would have to be left to phase 2.

With an item-based approach, you don’t have these issues. You can take the new data from the POS systems, import the Customer Type field via TI (while creating the new Customer Type dimension on the fly), then update your reporting cube and rules.

Yes, you still have to do some basic redesign, but there is no requirement for a complex and error-prone data migration.

Contributor & Insight-friendly

TM1 Contributor (or “Applications” as it’s now known) and Cognos Insight, are great front end tools for data contribution. They are a little weak, however, when it comes to customizing views to be friendly for the end-user. A highly dimensional input cube forces the view designer to choose between unworkably large grids or many laborious title element selectors which make cutting and pasting data difficult.

A flat, item-based input cube is much simpler to work with, supports multi-level cut and paste, and presents itself in a more logical fashion for quick data input. String values can be typed in as well as selected from a list, then copied down as necessary.

Downsides and Gotchas

Performance

If you’re not careful, this design approach can tempt you into inefficient rules and over-feeding. Performance can suffer with large data volumes.

However, with better design and a clean rule-based approach this can be avoided. Over-feeding is not necessary and rules can be structured logically and efficiently.

As always, TM1 has it’s quirks, but once you understand the gotchas associated with this design approach, they are easy to avoid or work around.

I’m planning several follow-up articles that will go through these issues in detail, and how to make sure they don’t have you pulling your hair out.

Complexity

Rules in this design approach can appear more complex and be harder for another developer to understand. I have first hand experience of handing over such a design to very capable developers and having them screw up their noses and replace my cubes with a more standard TM1 design.

I believe this is partially a cultural issue, as TM1 is taught in a particular way, and that has become accepted as “correct”. Once a developer adjusts to this kind of thinking, it’s actually very difficult to go back!

Obviously well-formatted rules and code comments can go a long way to alleviating this issue also.

Limitations

There is a natural limitation imposed by the item-based approach, and that is the number of elements in the item dimension forms a maximum number of “slots” for data input.

To avoid the situation where a user does not have enough “slots” to input their data, a developer might be tempted to include a large number of elements in their item dimension, and, if the rules and feeders are designed poorly, this could lead to poor performance.

However, a well designed cube won’t need a lot of input slots, as the average person is not able to navigate, or even usefully perceive, thousands of rows of data!

In our retail sales example above, there may be many sales items entered, and at first glance, it may appear to require a form with thousands of visible items shown. With a bit of thought, it usually possible to group input tasks meaningfully so that only a useful number of items need to be shown for the current input task. For instance, the Sales Rep could enter only the data for the particular store they are visiting, as they most likely wouldn’t be entering data for several stores simultaneously.

And, either way, a more dimensional approach does not mitigate this problem either!

Conclusion

With a bit of planning and thought, an item-based approach to TM1 development offers many advantages and rewards.

My follow up articles will be based on a simplified example, which is attached to this post for you to download and examine. The example is built and tested in TM1 v10, with the ForceReevaluationOfFeedersForFedCellsOnDataChange set to “T”.

Feel free to leave comment, criticisms, or unfettered praise in the comments section below!

And yes, that is the name of the property!

StringFeederSample.zip (2.98 mb)