Saturday, November 30, 2013

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

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.

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.

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

While user may not notice this cube behind the scene, this cube can be populated from front cube, and then send data back to front

Cube front
Cube front has two dimension Month 1 and Measure 1


Cube Back
Cube Back has Three dimension Month 1, Month 2 and Measure 1


No comments:

Post a Comment