Very slow CellPutN in the same cube

[Wim Gielis]

Hello all

I have an issue in a TI process, probably about locking. I use TM1 10.2 but I originally encountered the problem in Cognos Express V10.1.

A TI process has its data source as a view on cube A (scripted in the Prolog tab). Nothing special. Dimension subsets are created through MDX or statically, makes no difference. The view contains 1 measure.
In the Data tab, I populate a second measure, with a ratio, calculated as:

the first measure (Value as the variable name)
divided by Value on more consolidated levels in the dimensions.

Specifically, Value at the level of the article number (leaf level) is divided by Value at the Article group level (parent of article number).
Also, in another dimension, the denominator contains a "total" consolidation instead of the leaf-level element from the data source view.

Hence, pseudo-code:

CellPutN( "Value for an article number, aging element, measure" \ CellGetN(artigcle group, total aging, ..., ..., measure),
cube A, ..., ..., ..., (all the same elements in the view source, OTHER measure).

There is no rule on cube A, and there is no relation whatsoever between the 2 measures elements (both n level).

For 150,000 cells in the cube view, this goes extremely slow (5 minutes).
If I ONLY change the code so the CellPutN is done in a different cube - with the same dimensions - it completes in about 5 seconds.

Any ideas? Does it ring a bell?
The real business problem/calculation is more difficult but it comes down to calculate the ratio above. And transfer it to a different measure in the SAME cube.
Transfer to a different cube, then it's lightning fast.

Thanks !

Wim

PS: I can only reply back in 24 hours time, so don't panic if I cannot provide feedback.

[tomok]

Are you creating/modifying either the subsets or view used as the source in the same TI process where the CellPutN function is being used? Separate the view/subset work into a separate process (create the view in process 1 and use that view as the source in process 2) and see if that changes the performance.

[Wim Gielis]

Thanks Tomok, I will try.

Just to stress that doing CellPutN to a different cube, ceteris paribus, is lightning fast.

Tomorrow evening I can report back on the outcome.

[Wim Gielis]

Hi Tomok,

This one, I could already test. Ik makes no difference: very fast in a different cube, extremely slow in the same cube.

[Wim Gielis]

Here's the real code:

Prolog: create a view with 1 measure as the source, zero out the target measure (both level n)

Metadata: no code

Data:

Code: Select all

# Determining the article group from the article number
vArticleGroup = Elpar( 'SV_ArticleNo', SV_ArticleNo, 1);

vFraction = Value \ CellGetN(vCube, YearMonth, BusinessArea, 'Total Aging', vArticleGroup, 'SSV' );

##############################
# Calculate the Price Inflation
##############################

vPriceInflation =
    vFraction * CellGetN('LU_SV_COP1', YearMonth, BusinessArea, vArticleGroup, 'Price Inflation');

# VERY SLOW
CellPutN(vPriceInflation, vCube, YearMonth, BusinessArea, SV_Aging, SV_ArticleNo, 'Price Inflation');

# VERY FAST - StockValuation is a different cube but with the same dimensions
CellPutN(vPriceInflation, 'StockValuation', YearMonth, BusinessArea, SV_Aging, SV_ArticleNo, 'Price Inflation');

Epilog: clean up of temporary view and subsets


Writing to the same cube is very fast if I use, for example:

vFraction = Value;

(this is non-sense logic but the CellGetN is removed which is the culprit in cooperation with writing to the same cube)

[Duncan P]

Try using batch mode, which will save all your CellPutNs until the commit point and only invalidate the target cube once.

[lotsaram]

Hi Wim,

Whether there is some bug or difference between versions that would make this process redonkulously slow it does make sense that it is slower, even many times slower. A speed difference is what you should expect, it is just a matter of by how much.

The fact that the measures are unrelated isn't the issue, but by writing back to the cube that the process data source view is from any calculation cache that might exist for the cube will be dumped on each iteration so the calculation of the "fraction" could be causing quite a bit of wheel spinning, especially if the pair of Total Aging + SSV is quite consolidated. Writing to another cube will be much faster as the total you are dividing by to determine the fraction doesn't have to be recalculated from scratch on each iteration.

To get optimal speed you could either
- write the totals out to another cube beforehand and reference from there to calculate the fraction
- make the process 2 step, first write out the values you intend to load to a CSV then set the CSV as a datasource and load straight to the target

More work but I'm pretty sure that either of these options would be equivalent in speed and would optimize the performance.

Or seem as Duncan beat me to it you could use BatchUpdate (does it still work in 10.1, haven't used it in ages?), then the new data will only be "seen" on the commit and won't invalidate the caching of the totals.

[Duncan P]

does it still work in 10.1

I've used it in both 10.1.0 and 10.1.1 with success.

[Wim Gielis]

The BatchUpdate method seems to work brilliantly, thank you.
That was the easiest and fastest method to implement, so I tested that one first.
I'm sure the other 2 methods will work as well.

Thanks a lot! I never used / had to use BatchUpdate kind of functions.

Wim

[paulsimon]

Hi Wim

I can't say for sure if this is the same issue, but we found something similar in 10.1.1. Processes that had previously run in seconds would run forever eg > 4 hours. If we restarted the TM1 service then the process ran in seconds, but a second run would again take hours.

We reported this to our supplier, Infocat, who did an excellent bit of work with IBM in tracking it down. Apparently it was due to the cache being invalidated meaning that the source view was constantly re-calculating its values. IBM were going to produce a fix for this, so I would advise you to contact them to see if this is the same problem and whether or not a fix is now available. I have moved on from that client and I forget the details. In some cases, just populating a value, I think in the source cube, seemed to force the caches to be re-created and marked as valid again.

Regards

Paul Simon

[Duncan P]

If Wim's problem was fixed by using BatchUpdate then it is almost certainly not the cache mistrust problem that was discussed here.

[Wim Gielis]

Hello,µ

Thanks for replying.

I might have a look at this issue (and sending it to IBM) on Thursday.
For now, BatchUpdate wasn't successful on its own, it was still slow when writing to the same cube (*). I could only write the values to a "staging" cube (same dimensions) in the Data tab,
and then in the Epilog of the process, call a different process to transfer all data from the staging cube to the real cube again.
A lot of hassle in my opinion and something that is not needed (never saw this before in TM1).

If some wants to play around in the environment, just drop me a note and we'll see how to transfer the TM1 DB (only relevant objects).

(*) To reiterate the problem: I have a cube view, numeric values at level 0 are part of the view, only 1 measure in that sourceview;
the data is transferred to a second measure, where the value transferred is the value from the view divided by a more aggregated cell value for the same measure (2 times a parent is used in the numerator of the division).

[Wim Gielis]

Update: PMR 48320,800,624

[mvaspal]

Hi,

Wim,

Update: PMR 48320,800,624

is the PMR of your issue or the one that Paul described? Or are they the same issue?
We have the very exactly same issue in 10.1.1, FP1:

in 10.1.1. Processes that had previously run in seconds would run forever eg > 4 hours. If we restarted the TM1 service then the process ran in seconds, but a second run would again take hours.

PI was my first suspect but it seems nothing to do with it.

I load between two different cubes, not linked with any rules. Now if I comment out the CellPutN, the TI runs really fast, even with 2 nested while loops using elcomp and elcompn, otherwise for hrs. I'm wondering, how a CellPutN into Cube B can invalidate the cache in source Cube A if there are no dependencies between the 2 cubes.

For me, both workaround worked well:
- batch update (although not 100% well)
- export to txt then import in the 2nd TI

Does anyone has the PMR number for this issue? Or maybe someone can confirm FP2 includes the fix?

Thanks four your help,
Matyas

[amin]

Hi Wim,

I had a very similar issue while i was scripting a simple ratio calculation and it seems the issue is really with The CellGetN referencing a consolidated value as its argument. To resolve the issue, I had loaded the data in an additional dummy element( in your case that would be 'No Aging' while loading 'ssv' data) and use that no aging element instead of 'Total Aging' .

Hopefully, that should do the trick..