Slow performance from DBRW/CellGetN vs Cube Viewer

[fleaster]

Hi all,
So here's the situation: I am extracting a consolidated view from a cube, however I am finding that extracting values via a DBRW worksheet function or CellGetN TI function is extremely slow - whereas bringing up the numbers in the Cube Viewer is normal speed.

This problem only started happening recently (prior to the last week the cube was fine) - so am wondering whether it could be because of some caches being invalidated etc... If anyone has any ideas what I should look for, please let me know

Thanks!

Matt

[RJ!]

Are you also noticing a difference between ISB & then ISB sliced?

[whitej_d]

There is an issue with cache on some versions of 9.5.2 and 10.1. I believe they are fixed in 9.5.2 FP3 and in v 10.2 onwards. If you are not on FP3 for 9.5.2 then applying this fix pack should solve your issue.

The issue occurs when you change some data and do a SaveData. This causes TM1 to not trust its own cache and therefore always recalculates from scratch. Invalidating the cache with another data change resets and causes normal behaviour to resume, but due to the many branches to the the dependency tree it can be quite hard to work out which data to change reset. Restarting the server is often easier.

[RJ!]

We're on 10.2.2 with the latest FP on the server, the client package is still 10.2.2 without the current FP.

[fleaster]

There is an issue with cache on some versions of 9.5.2 and 10.1. I believe they are fixed in 9.5.2 FP3 and in v 10.2 onwards. If you are not on FP3 for 9.5.2 then applying this fix pack should solve your issue.

The issue occurs when you change some data and do a SaveData. This causes TM1 to not trust its own cache and therefore always recalculates from scratch. Invalidating the cache with another data change resets and causes normal behaviour to resume, but due to the many branches to the the dependency tree it can be quite hard to work out which data to change reset. Restarting the server is often easier.

we did end up bouncing the server several times, but no joy

..am wondering if we need to run a process to setup a stargate view everytime we populate the cube......

[fleaster]

Ok, for future reference...

...I discovered my current issue was caused by a few elements that were tagged as "ConsolidateChildren" ...a couple of which had 4000+ children in the rollup

Am still curious as to why the performance is fast in Cube Viewer, but extremely slow for CellGetN and DBRW... in any case, will probably need to rethink how the structures are organized.

[tomok]

Ok, for future reference...

...I discovered my current issue was caused by a few elements that were tagged as "ConsolidateChildren" ...a couple of which had 4000+ children in the rollup

Am still curious as to why the performance is fast in Cube Viewer, but extremely slow for CellGetN and DBRW... in any case, will probably need to rethink how the structures are organized.

You do realize that the cube viewer only retrieves the number of intersections at a time that can be shown on the screen? If you have a 10,000 row view it's sill only going to retrieve the number of rows that can be shown without scrolling then retrieve the others as you work your way down. Your TI process probably does all of them. You can't compare the time it takes to open a cube view versus the time to process a view in a TI. It's apples and oranges.

[fleaster]

You do realize that the cube viewer only retrieves the number of intersections at a time that can be shown on the screen? If you have a 10,000 row view it's sill only going to retrieve the number of rows that can be shown without scrolling then retrieve the others as you work your way down. Your TI process probably does all of them. You can't compare the time it takes to open a cube view versus the time to process a view in a TI. It's apples and oranges.

Yes, I understand this where a table of elements are concerned, but in this case I'm only talking about one cell/intersection, composed of consolidated elements (one of which has a ConsolidateChildren rule on it).

So in cube viewer it takes about 5 seconds to return a result for this one cell; if I use the exact same criteria in a DBRW formula or CellGetN* formula, it takes about 10 minutes.

* for TI, I have tested this in Prolog with a CellGetN and hardcoded criteria (so no source view used)

[lotsaram]

What else is in the Excel sheet appart from the single cell DBRW? If there is a VIEW formula in the sheet with any "!" arguments then you are actually instructing the server to calculate and cache all elements on that dimension. Therefore if you have a view formula in the sheet you're not doing a valid comparisson.

[declanr]

As others have eluded to retrieving a view should take no difference in time when accessed through different interfaces when conditions are the same (e.g. exactly the same view and same caches to start with.)
What will take slight differences is the time to construct a websheet or render values into the excel interface vs a cubeviewer i.e. perspectives, web, contributor, ISB, cubeviewers and anything else all construct a view on the server and then use a different interface to render it - it is the coding for those interfaces that will all obviously have to be different and would therefore be expected to take different times to show. The same counts for how it sends the request to construct the view to the server; this will be a different path for each interface.

If you really want to confirm that the view itself takes the same amount of time to construct then you should enable stargate debugging in the log file; you will then be able to see exactly what intersections (which elements) are included in the view and exactly how long the server takes to calculate it. If there is any level of material difference in the log then you have found a bug and a very surprising one. Just remember that a true test would need your starting point of caching to be identical (this however can also be monitored via stargate debugging.)

[RJ!]

I don't think we've ever done that kind of debugging before... Will give it a go.

Another topic regarding the Debugging for reference:
http://www.tm1forum.com/viewtopic.php?f=3&t=9969

And another from IBM which gives some instruction to enable on the server:
http://www-01.ibm.com/support/knowledge ... ption.html