Model setup

Post Reply
lynnsong986
Posts: 2
Joined: Wed Feb 28, 2018 2:20 pm
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2010

Model setup

Post by lynnsong986 » Sun Mar 18, 2018 6:31 pm

Hello I’m new to TM1 and trying to use TM1 to mimic my forecast model in Excel. In my excel model (forecast year 2018 to 2022) monthly revenue is calculated as number of units per building * admin fee per month per building however each building has a different billing start month, I.e. if a building has a billing start month of May 2018, then the first month (May 2018) revenue is only 1/3 of the full month, the 2nd month (June 2018) Revenue is 2/3 of the full month, and from the 3rd month (jul 2018) onwards revenue would be 100% of the full month. I’m not sure how to set this up in TM1, I’d like to use rules rather than hard code a factor table for each project and each month, any help/idea would be greatly appreciated. I’m actually quite confused about how to set up dimensions/cubes for this.

User avatar
paulsimon
MVP
Posts: 579
Joined: Sat Sep 03, 2011 11:10 pm
OLAP Product: TM1
Version: 10.1.1 and 10.2.2
Excel Version: 2013

Re: Model setup

Post by paulsimon » Sun Mar 18, 2018 11:08 pm

Hi

I couldn't tell what varies month by month so I have assumed that the number of units is the number of units in use and that this and the Building's Monthly Admin Fee per Unit that can vary over time.

Firstly, don't have separate year and month dimensions. Just have a single time dimension with months, eg 2018-01, 2018-02 etc. Add consolidations for Year, you can potentially consolidate different groups of months for Calendar Year, Financial Year, Tax Year, etc. Construct the dimension so that all base level months are listed first, before any consolidations. List the months in order. The old fashioned Dimension Worksheet approach can be the best way to do this for a beginner. You can use Excel Formula to generate the months and consolidations and auto fill down. There are other ways. I use an Excel VBA Macro sheet that I can parameterise for different start and end years, etc, others use TI and SQL.

I suggest you have a Building_Revenue cube with dimensions :

Effective_Mth (the months dimension described above)

Building (one element for each building with an All Buildings total consolidation)

The measures dimension Building_Revenue_Meas will have elements

Number of Units
Building Monthly Admin Fee per Unit
Monthly Revenue

(Obviously you might want to add other dimensions, such as Version, etc, but the above will do for the immediate problem)

I suggest that you create a Building_Info cube for visibility although you could just create attributes on the Building dimension.

This will have dimensions of

Building

and a measures dimension Building_Info_Meas with one String (Text) element

Billing Start Month

Then, the rule in the Building_Revenue cube will be

Code: Select all

SKIPCHECK ;

['Monthly Revenue'] = N:
  ['Number of Units']
  *
  ['Building Monthly Admin Fee per Unit']
  *
  IF( dimix( 'Effective_Mth' , !Effective_Mth ) 
      < 
     dimix( 'Effective_Mth' , DB( 'Building_Info' , !Building , 'Billing Start Month' ) )
     ,
     0
     ,
     IF( dimix( 'Effective_Mth' , !Effective_Mth ) 
          = 
         dimix( 'Effective_Mth' , DB( 'Building_Info' , !Building , 'Billing Start Month' ) )
         ,
         1/3
         ,
         IF( dimix( 'Effective_Mth' , !Effective_Mth ) 
              = 
             dimix( 'Effective_Mth' , DB( 'Building_Info' , !Building , 'Billing Start Month' ) ) + 1
             ,
             2/3
            ,
            1
         )
     )
  ) ;

FEEDERS ;

['Number of Units'] => ['Monthly Revenue'] ;
The first check on the Effective Mth being before the Billing Start Month is just to ensure that nothing will be charged even if someone accidentally puts in a Number of Units and Admin Fee before the Building is opened.

There might be the odd syntax error in the above as I haven't actually built this, but it should give you a starting point.

Regards

Paul Simon

lynnsong986
Posts: 2
Joined: Wed Feb 28, 2018 2:20 pm
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2010

Re: Model setup

Post by lynnsong986 » Mon Mar 19, 2018 8:23 pm

Thank you so much Paulsimon for replying. I’m sorry that my question is not clear enough but you understood everything correctly. Your reply is so helpful and I really appreciate all the details you put in there especially the written rules. I will go implement these in TM1 for sure!

Best regards,
Lynn

Post Reply