# Intensive calculations (e.g. Depreciation)

This site uses cookies. By continuing to browse this site, you are agreeing to our Cookie Policy.

• # Intensive calculations (e.g. Depreciation)

I have been building a cube handling a rather simple depreciation calculation. Such depreciation is obviously rather intensive, butthe time Palo seems to be taking to recalculate on these rules seems crazy at the moment!

The cube is 6 dimensions (N-elements: 6x8x14x3x8x5), so tiny. The depreciation rule takes prior month book value and multiplies by a certain percentage. It only does this for 2 scenarios, for 2 years, for 12 months, so not many calculations at all, but it literally runs into several minutes to recalculate! Due to IF conditions I can't use markers, but the cube isn't that large, so doubt that it would have an impact. Any ideas? Has anyone had similar issues? Alternatively, would I rather need to do this via ETL? Bit worrying though, as the cube is still tiny, with data in only a few cells (6 or 7!).

Any help would be very much welcome!
• Hi,

it's just an idea, but it could be worth a try: if you know in advance the combinations that are to be excluded from the calculation, you could write some rules like:

['ToBeExcluded1','ToBeExcluded2', 'ToBeExcluded3',...] = STET()

They will be put above the others so the following ones (the rules actually performing time consuming calculations) won't be activated for such combinations.

Hope this helps,

regards,

RQ
• Hi RQ. Thanks for that. I have many STET's at the top of the rules, so many of these are already excluded. It's just real strange how the rule seems to be making Palo so extremely slow.

Basically, the rule is as follows:

['Depreciation'] = N:IF(['No Year','Annual','No Entity','Depreciation Type'] == "SL",MIN(PALO.DATA("MyDB","Assets",!'Version',!'Year',CONCATENATE(!'Month'," LTD"),!'Entity',!'Asset',"Cost") * ['No Year','Annual','No Entity','Depreciation Rate'] / 12 / 100,PALO.DATA("MyDB","Assets",!'Version',!'Year',CONCATENATE(PALO.ENAME("MyDB","Month",PALO.EINDEX("MyDB","Month",!'Month') - 1)," LTD"),!'Entity',!'Asset',"Net Carrying Value")),CONTINUE;

Before this is the same rule, just for the first month as this can't look at prior month LTD values.

Any ideas? There's no way I can use it based on current performance.
• Hi,

I can't clearly see the structure of your cube, but maybe you can split the rule into two self excluding to see if there's any performance gain (I'm just guessing, it's difficult to write them without the possibility to parse against the cubes structure):

['Depreciation', 'No Year','Annual','No Entity','SL'] = N: MIN(PALO.DATA("MyDB","Assets",!'Version',!'Year',CONCATENATE(!'Month',
" LTD"),!'Entity',!'Asset',"Cost") * ['No Year','Annual','No Entity','Depreciation Rate'] / 12 / 100
['Depreciation'] = N: PALO.DATA("MyDB","Assets",!'Version',!'Year',CONCATENATE(PALO.ENAME("My
DB","Month",PALO.EINDEX ( "MyDB","Month",!'Month') - 1)," LTD"),!'Entity',!'Asset',"Net Carrying Value")

If this does not solve, I can't see other ways other than to modify data structures so you have physical data instead of calculated ones, and put appropriate markers.

Best regards,

RQ
• Thanks for your reply, RQ. The 'SL' is however not an element, only a value. I could also store this as attribute, but don't think it will change the performance much. The issue appears to be that this is a rolling calculation, i.e. that a calculation can be based on values that have previously been calculated, etc. It is thus intensive, but I would not expect these issues with performance,.e.g on another MOLAP product I can run same with no issues, and on 6000 items. Here I am trying to do on 5 items!

There must be some way around if Palo is being implemented by large corporations, but I am struggling to find where. At the moment Excel is significantly faster to arrive at this result than I can get Palo to be - which is real strange, and makes it hard to put Palo forward as potential solution.

Re Markers, unfortunately I cannot use these where I have conditions in the Palo.Data formulae.

Another option - if I break up this calculation by inserting more elements that contain the results of portions of the above calculation, and then lead on to the next element that contains the final result, would this make any difference? I can't imagine so, but would be interesting to hear how others handle the above issue! Must be possible somehow...
• Mm,

yes you are right, my idea is not fully applicable in this case...
Looking at a similar implementation I have, I had the same performance issue when I started to use complex rolling calculations, the only solution I found was to write some vba code to store the phisical value (in my case the problem was due to the fact that to calculate a value for a period, the entire time series needed to be recalculated on the fly), instead of calculating it.
Second-class solution, I have to say, but the only working for me.

Hope one of the 'black belts' around here can provide a hint... I'm very interested too.

Best regards,

RQ
• Surely someone must have been doing calculations of some degree in Palo, and not be doing all this via VBA or ETL? Black Belts - any ideas?

Quite critical to any decision to actually use Palo, I would think - at the moment we can't consider it an option really...
• snoozy,

did you evaluate your rules stepwise and with hard-coded values in the first run? This approach might show you where the bottleneck lies. Then decide if a minor change in modeling is an alternative.
You are using several functions in your rules in combination. One of these combinations PALO might not like.

Modelling concepts derived from other MOLAP tools are not always transferable 1 to 1. Which one did you use until today?

Your example still interests me. I will try to simulate it at the weekend.

Regards,

M.
• Maybe I misunderstand your approach, just one remark to the use of CONTINUE. I would expect it inside your IF() statement.

[] = N:IF(x == "SL", MIN(arg1, arg2), CONTINUE);
• Yes, sorry, the last ()) was missing in the copy and paste of the rule onto the forum. Had tried this rule quite differently ( not using any consolidations, but doing all calc's via the rules), but this brings different issues. Will investigate further this weekend and poste something as soon I have more... It's as though the new rule works for 3 months, but then just returns #Value...
• Kratzer/Others

I have spent more time looking at this, to try and get to grips with Palo and the way it tries to handle rules.

The only rules I now have are the following (they don't make sense from accounting perspective, but just simplified them to get to the core of the issue):

['2008','Depreciation - Opening','Jul'] = N: STET();

['Depreciation - Opening','Jul'] = N: PALO.DATA("MyDB","Assets",!'Version',PALO.ENAME("MyDB","Year",PALO.EINDEX ("MyDB","Year",!'Year') - 1),"Jun",!'Entity',!'Asset',"Depreciation - Opening") + PALO.DATA("MyDB","Assets",!'Version',PALO.ENAME("MyDB","Year",PALO.EINDEX ("MyDB","Year",!'Year') - 1),"Jun",!'Entity',!'Asset',"Depreciation");

['Depreciation - Opening'] = N: PALO.DATA("MyDB","Assets",!'Version',!'Year',PALO.ENAME("MyDB","Month",PALO.EINDEX ("MyDB","Month",!'Month') - 1),!'Entity',!'Asset',"Depreciation - Opening") + PALO.DATA("MyDB","Assets",!'Version',!'Year',PALO.ENAME("MyDB","Month",PALO.EINDEX ("MyDB","Month",!'Month') - 1),!'Entity',!'Asset',"Depreciation");

['Depreciation'] = N: ['Depreciation - Opening'];

So basically, all this should do is STET the first month (Jul, 2008 ), roll forward Value "Depreciation - opening", and then make Value "Depreciation" the same. I have deliberately avoided ALL consolidations as the rules don't like these at all in this case, but I still just get #Value for Values "Depreciation" and "Depreciation - opening".

As I say, the above rules don't make much sense from financial perspective, but surely they should work in PALO? Can anyone see anything, because this is driving me insane... I could have built an entire TM1 budgetting model in the time I've been trying to find the issue with this simple rule!:)

Might be worth putting together some basic technical details on rules in PALO - i.e. in what order rules and consolidations etc are executed. The manual really doesn't give much info on it.

Really appreciate ANY help! Thanks in advance!

The post was edited 2 times, last by snoozy ().

• We could help you better if you post an attachment with your highly confidential data and an Excel-file with some text and values (hard!) that describes what you are up to and expect and the Palo-view that does not work.
• Axi

Confidential?

Please see attached. Note that the latest rules above are cut-down version, so not original intent, but they still not working. Appreciate you having a look at this!
• snoozy - the zip-file contains only one Excel-file

no MyDB with data and rules...
• Hi Axi

Sorry - didn't realise you wanted full data directory. Herewith a copy that shows the issue. Not the final thing I want to do, but don't see why the errors? Will be great if you can assist. Cubes and Rules should be included in data directory - let know if there is anything missing - haven't previously done a backup of Palo data directory!

Vielen Dank fuer deine Geduld!!
• Hi,

the issue is caused by the fact that the rules are trying to calculate Palo.Eindex for not existing elements "called" by, e.g. !'Year' - 1.
You have to introduce some test to avoid this, please see the attached db with reviewed rules (I just put a couple of quick tests, you will have to adjust the concept to your needs): now the palo.data formulas return values, not #VALUE

Hope this helps,
regards,

RQ
• realquo,

the last time I wrote such rules - Palo bombed. At least we are now blessed with #value.
I wrote I'd like an error message (at least in the log) in my bug report in November 2008.
• THanks so much Realquo - much appreciate. I thought the initial STET for June and 2008 would resolve the issue, as the rule would only apply to the first item? If I start up the data directory that you attached (where it tests for index of month >32), that rule is never applied as there are no base elements with index >32.

Could you just clarify what the 32 refers to? I presume we can just STET Annual to achieve the same?

Thanks again
• Hi,

@axi: yes, I also found that sometimes an incorrect rule makes palo exploding (in a case it was necessary to restart the machine because of some unknown reason, the service was not able to restart after crashing)... Quite poor for such a powerful tool as palo is.

@snoozy
something like STET() for Jul - 2008 would work in case the Time dimension was a hiearchy like Year -> Month, with July 2008 as first element of the dimension. But here stet() July, 2008 works only for July AND 2008, while it does not work for July AND 2009. I suppose this is the reason because the initial stet was not enough.

I put 32 because it is the lowest index on the Time dimension; you are right it is not a base element, but without this condition the rule returns #value: maybe N: makes the rule APPLIED to base elements only, but internally CALCULATED for all (base & consolidated)?

I tried to stet 'Annual' but it avoids #values only for some formulas, pls see the attached screen:
- in screen03, ['Depreciation', 'Opening '] rule tests for time index > 32 and no stet() is put for 'Annual'
- in screen04, the test for the Time index was removed and ['Annual'] = STET() was added (on top of the list).

Hope this helps,

regards,

RQ

The post was edited 1 time, last by realquo ().