Rule based calculation for net cash

[shinymcshires]

Fellow listers:

I'm a rules rookie writer. I just read Chapter 3 of the TM1 Rules Guide (Version 9.0). I'm using Excel 2003, TM1 is version 9.0 SP3.

I'm working on a project to help streamline the senior accountant's monthly cash reconciliation (he currently hand enters all of the cash transactions into a worksheet).

Cash cube: Contains the bulk of the transaction data with the following dims:

Cash_JE_Date
Cash_System
Fund
Program
Account
Project
Cash_Batch_No
Cash_Calendar_Month
Cash_Calendar_Year
Cash_Batch_Type
Cash_Data

What I'd like to do is bring in credit card data from two other cubes that have the following dims:

Cash_JE_Date
Cash_Batch_No
Cash_Calendar_Month
Cash_Calendar_Year

Based on the procedure of our accountant, he derives the "Net Cash" by (Gross Cash-Credit Card Charges) for one particular Fund. Given that, I came up with the following rule for the Cash cube:

['1101','101100','Net Cash']=N:['1101','101100','Net']-DB('CR CC3',!Cash_Batch_No,!Cash_Calendar_Month,!Cash_Calendar_Year,!Cash_JE_Date)-DB('UB CC2',!Cash_JE_Date,!Cash_Calendar_Month,!Cash_Calendar_Year,!Cash_Batch_No);

The rules editor allows me to save, but when I try to calculate while in the Cash cube viewer, I get the "Building Cube View" window for a few minutes, and then the following error message:

noerror.JPG (6.7 KiB) Viewed 5350 times

I'm sure I've written the rule wrong, but could use some guidance from some rules gurus. Thank you in advance for your time and thoughts.

If there is more information I can provide, please let me know. Thank you!

[shinymcshires]

I've modified the rule a bit, and at least now when the cube calculates, it doesn't hang, but it's not calculating the "Net Cash" correctly:

SKIPCHECK;

['1101','101100','Net Cash']=N:['1101','101100','Net']-DB('CR CC3',!Cash_Batch_No,!Cash_Calendar_Month,!Cash_Calendar_Year,!Cash_JE_Date)-DB('UB CC2',!Cash_JE_Date,!Cash_Calendar_Month,!Cash_Calendar_Year,!Cash_Batch_No);

FEEDERS;

['1101','101100','Net']=>['1101','101100','Net Cash'];





I think I need to add some feeders from the other cubes, but haven't quite figured that out yet.

[paulsimon]

Richard

Based on the procedure of our accountant, he derives the "Net Cash" by (Gross Cash-Credit Card Charges) for one particular Fund. Given that, I came up with the following rule for the Cash cube:

['1101','101100','Net Cash']=N:['1101','101100','Net']-DB('CR CC3',!Cash_Batch_No,!Cash_Calendar_Month,!Cash_Calendar_Year,!Cash_JE_Date)-DB('UB CC2',!Cash_JE_Date,!Cash_Calendar_Month,!Cash_Calendar_Year,!Cash_Batch_No);

I can't see anything obviously wrong with this. You don't need to repeat the '1101','101100' on the right hand side of the rule, but that is a minor point. Do you have a SKIPCHECK statement at the top of the rules? Do you have feeders, if so then please post those too. Lack of skipcheck or overfeeding would lead to the long calc times you are experiencing. If you don't have a skipcheck at the top of your rules you will need to give feeders, in fact this rule may need 3 feeders, one from net and one from the 2 cubes.

I haven't seen the cube view error that you are getting. I suggest that you fix the rule and then see if it re-occurs. We did get a similar error on 9.1.3, and we still get it occasionally on 9.1.4. That is more like View Array out of date.

As a design principle I would recommend keeping the dimensions in the same order in all your cubes. You can always change the physical order using re-order dimensions for performance.

It is probably heresy to say this on a TM1 Forum, but I would consider doing this in SQL, and then just take in the final results in to TM1. I tend to view TM1 as being more about management information, but this appears to be more about transaction processing.

I am also concerned about the result that you will get.

Looking at the dimensionality of your cubes:

Cash_JE_Date
Cash_System
Fund
Program
Account
Project
Cash_Batch_No
Cash_Calendar_Month
Cash_Calendar_Year
Cash_Batch_Type
Cash_Data

vs

Cash_JE_Date
Cash_Batch_No
Cash_Calendar_Month
Cash_Calendar_Year

You are fixing on the elements of 3 dimensions in your rules but that still leaves 4 other dimensions, where it appears that you will be repeating the numbers that you bring in from the credit card cubes. That will give incorrect results and could also lead to the explosion in size giving slow calculation and possible memory problems.

A better approach might be to get your data to a higher level cube that has the same dimensionality as your credit card data, which perhaps a measures dimension to make calculations clearer. You could get the data there by rules or a TI export and load. The TI will need to be additive. The Rules would need to read in data from total level consolidations in eg Cash_System, Project, etc.

Regards

Paul Simon

[shinymcshires]

Paul-

Thank you for your suggestions. I will give this another shot tomorrow morning. Best wishes!

[Steve Rowe]

Hi Shiny,

Thanks for taking the time to read the "Request for Assistance Guidelines".

You will need to feed this calculation from all three cubes. The internal feeder you have will only drive the calculation when Net is populated so if the calculation that needs to happen is Net Cash = 0 -100 -200, then the -300 result of this will not consolidate correctly. Even if it's not supposed to be possible to have the expense values without the cash position it would be better to have the extra feeders as then the feeders are then logically correct and you are not relying on the data coming in the correct place to got the right result out.

Note that adding these feeders will increase the load time of your server and the save time of the rule sheets, if you are working with large volumes of transactional data then this could be a large increase.

If I had this problem, I'd be looking to try and get these values next to each other in a measures dimension of a single cube when I load the data. Once you have this then you can do the subtraction in a dimension consolidation without any need for rules or feeders. This will save you some server load time but more importantly the calculation should be many times faster.

As Paul says there seems to be difference in the dimensionality that is not quite covered in your rules so I'm not sure if the above would work, but it is certainly what I would be aiming for.

One further thought, just take care when comparing the result of the system to the accountants result. If you're sure that your logic is working then you need to make sure that you understand how the accountant calculates his result. Often you will find that what the described is a simplistic view and they actually exclude all of "transaction type B" or they do what they said and then think of another number and add it on at the end...

Oh and who are you calling a lister??? Well maybe by late Friday evening I suppose

Cheers,

[shinymcshires]

Steve-

My apologies for the mislabel I subscribe to the trombone listserv (yes, I wrote trombone) and accidentally addressed everybody as listers instead of forum members. Thank you for taking the time to write a thoughtful reply. You offer very good insight into my project. I'm going to take your advice to make the process and calculations more efficient. I will do my best to try to implement your suggestions.

And yes, I play trombone.

[shinymcshires]

Ok- I'm trying to think this through before I start. If I am to put all of the values next to each other via a measures dimension, here's my plan of action:

1. Edit the 3 TI processes to add a measures dimension.

2. Create a view extract of each of the cubes.

3. Manually create a "consolidation" cube and define the consoldiation weight of the measures dimension to derive Net Cash.

I'm going to get the ball rolling now. I'll update with results.

[shinymcshires]

Isn't it funny how things never go as planned?

After some thought, I decided to make an audible from the plan. After editing the TI processes for the three data sets to add the measures dimension (and removing some unnecessary dimensions), I created a cube manually with the following elements:

createcube.JPG (46.77 KiB) Viewed 5284 times

I also edited the measure dimension to add the 'Net Cash' element and change the weight of the 'CRCC' and 'UBCC' to -1:

cashmeasuredim.JPG (41.05 KiB) Viewed 5284 times

When I view the newly created cube, I'm not getting any data intersections.

nodata.JPG (65.81 KiB) Viewed 5284 times

I thought about the view extract for each of the three cubes and decided it was not necessary (and how would I create a TI process that would be able to use three different cube views as the data source?) I decided that the only way was to create the cube manually. I must have missed a step somewhere, but I'm not sure what I'm missing.

Once again, my humble thanks for your help.

[shinymcshires]

I can't believe it. I think I actually got it. Based on my discussions with the accountant, I know that he attributes the credit cards to only one fund (element in the Fund dimension), one account (cash account in the Account dim), ...etc. I added the dims to the other cubes so that they would be of equal dimensionality. Then I changed the TI processes so that they would update the same cube, just to different elements in the measure dimension. The only part I need to add is a TI process to zero out the data each time the TI processes are run(as I have it set to aggregate values) so there is no duplication. Thank you all for your input. I appreciate it.