Yin Solutions: How to achieve complicated business logic with TM1 by using a “temporary cube”

Context

Comparing with most of OLAP engine, TM1 has two very special functionalities. The one is rule engine, with which you can build business logic besides the simple aggregation. The other is the write back functionality; you can input data into cube. In my opinion, I would like to consider TM1 as traditional relationship database. TM1 provides you the basic engine; you can use it to realize many business functions by using your imagination. This document is intended to provide a sample to demonstrate TM1 usage beyond financial planning, budgeting and forecasting.

Sample

Please see sample below

Data in input value will be evenly distributed into Month based on month range. The start month is specified, while the end month is the month of that row.

When a user inputs 3 with start month Jan, then 1 will be distributed from January to March.
When a user inputs 3 with start month Apr, then 1 will be distributed from April to June.
When a user inputs 4 with start month Jul, then 2 will be distributed from July to August.
When a user inputs 12 with start month Jan, then 1 will be distributed from January to December.

(This is a simplified sample for payment distribution in practical business application)

All these distribution is happened on the fly when user adds a value. You can image that this distribution can be displayed as chart. It can provide a user a very intuitive interface.

Analysis

This is not a simple problem, as you need to aggregate all distribution for each input value together. It is almost impossible to use rule to get all these working with in a same cube. This is where a “temporary cube” is born. TM1 is very good at handling sparse data. We can simply create a another cube behind the scene. Data can be populated as below