TM1 Cube Maitrix

[qdmm]

I am new for TM1 and I am struggling to do the following.

Project Date Cube
Dimensions:
Project
Measure:
Launch Date

Project Lease Rate Spread
Dimensions:
Project
Period (Month1, Month2, Month3....)
Measure:
Percentage

Project Lease Spread
Dimensions:
Project
Year
Month
Measure
Percentage

The end user will input the Project Date Cube and Project Lease Rate Spread manually. Once he has entered, we need to popluate the Project Lease Spread. I am attaching a excelsheet of what is expected.

[Alan Kirk]

I am new for TM1 and I am struggling to do the following.

Project Date Cube
Dimensions:
Project
Measure:
Launch Date

Project Lease Rate Spread
Dimensions:
Project
Period (Month1, Month2, Month3....)
Measure:
Percentage

Project Lease Spread
Dimensions:
Project
Year
Month
Measure
Percentage

The end user will input the Project Date Cube and Project Lease Rate Spread manually. Once he has entered, we need to popluate the Project Lease Spread. I am attaching a excelsheet of what is expected.

My first thought was that it would be simpler to have the user enter the numbers directly into the Project Lease Spread cube, which would essentially negate the need for the other two cubes. However there are arguments that I could think of for retaining input and calculation cubes; one would be read and write security (though that could be worked around using a single cube approach) and the other being data validation.

It's in fact data validation that I see as your greatest potential issue; specifically, the possibility that someone might not be able to count up to 100.

That being the case, having the Project Lease Spread populated from another input cube rather than directly may make sense, especially if you have TuboIntegrator. (I presume you do?)

However I think that using TWO input cubes makes things unnecessarily complicated. You could combine the Project Date and Project Lease Rate Spread cubes into a single cube, where the measures dimension has (say) an element for Launch Date and then elements M1, M2, M3 and so on up to the maximum number of periods in your leases.

Now, as to the launch date itself... does it HAVE to be in text format as per your example? You can certainly do it that way, but it would involve more coding if you're going to populate the Lease Spread cube by TI since you'd need to parse the string expression out to be able to determine the month dimension element that you need to start writing values to. And again, there are validation issues; what if someone entered typoed the month name?

An alternative (which would also avoid stuffing around with string elements) would be to have a numeric input element (or two if necessary; one for month and one for year) for the launch date. So if the lease was launched in May, the input would be 200805.

This leads to the question of whether you need to make the year and months separate elements in the Project Lease Spread cube as well. If you were to combine them into a single dimension with, say, names like Apr 2008, May 2008 and so on, and with ALIASES like 200804, 200805, etcetera, it would be amazingly easy to work out the periods that need to be populated. The TI would simply look at the StartDate element in the input cube, find the corresponding element in the Project Lease Spread cube, and start writing the values from that element onwards.

In other words, the TI sees that the StartDate value is 200805, so it finds that element in your Project Lease Spread cube. It writes the M1 value into 200805, alias May-08. It then looks at the next element in the Input Measures dimension (M2), and writes that value (20 in your example) into the 200806 element, and so on until it doesn't find any further input elements with data in them.

During this loop you should be keeping track of the total of the values that you've written (or you could even write a loop to check them first if you want to get really creative), and if they add up to more or less than 100% you would have a number of options; adjusting the amount in the final month to get it to 100% if it's under that amount, writing an error log to disk, rejecting the data and what have you.

I've attached a modified version of your original workbook for reference.

If you're new some of this may go over your head, but you can try taking a look through the TI manual and having a bash at coding such an interface; I'm sure you'll find plenty of help over here if you get stuck.

[Steve Rowe]

What you are trying to do is fairly involved.

You are going to need to put some attributes against your time dimension(s).
I'd change the way I was collecting the project date so that the Project Date cube is by Project, Month and Year and then a 1 is put at the intersection of the start date.
This will give you something to test against in the rules of the Project Lease Spread cube.
Your rule is going to be something like

['%AGE']=N:
#Check the project date flag and populate cube
If (DB(Project Date(!Year, !MOnth, !Project)=1, DB(Project Lease Rate,.....),
#check the prior period in _this_ cube and populate the flag
If (
#Reference to last months prior period (if last month is not =0)
#Special case for Dec
If (!Month@='Dec',
~DB (Project Lease Spread (....Attrs('Year', !Year,'PriorYear'), Attrs('Month', !Month, 'PriorMonth')....)=0,
#Make link to Project Lease Rate cube
DB(Project Lease Rate,.....),
#general case for months not=Dec
~DB (Project Lease Spread (....!Year, Attrs('Month', !Month, 'PriorMonth')....)=0),
#Make link to Project Lease Rate cube
DB(Project Lease Rate,.....),
#Else 0
0));

Feeders are a bit tricky too
In the Project Date cube
[]=>DB(Project LEase Spread, !Year, !MOnth, !Project, 'The top of any other dimensions');

In the Project Lease Spread cube
['%AGE']=>
#feeder to next month
DB (Project Lease Spread (....If(!Month@='Dec',Attrs('Year', !Year,'NextYear'), !Year), Attrs('Month', !Month, 'NextMonth')....);

So that's a rule based approach. I'm not sure that I would solve this using rules though. If the input environment was Excel you could easily build a spread sheet that allowed the users to input the rates directly into the Project Lease Spread cube.

1.User selects Launch Date
2.VBA or other excel formula recalcualtes row references to allow input of %age into the correct location directly into the Project Lease Spread

This has the advantage that the allocation rules you are going to write are not dependant on a slightly nasty piece of rule work. The system would perform a lot better if your allocationn drivers are not rule based, allocation rules can be slow as they tend to apply over a large sparse area of a cube.

I'd also try and store my percentages as decimal values so that I don't have to divide by 100 all the way through my allocation rules.

HTH give you an idea of where to go.

(Also I hope I'm helping an end-user and not a consultant moving over from the Cognos world.....)

Cheers,