qdmm wrote: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.