Tm1 Perspective 10.1

[Analytics123]

Hi,

We are using tm1 perspective 10.1 version . We have a cube which has around 12000 customers data grouped by hierarchy .

When we slice the data by lowest n level customers , it takes less than a minute to slice all data and on the sliced sheet if we do a dbra then it takes less than a minute to recalculate and save .

But when the user is 50 miles away from the actual server and trying to perform the same operation, while recaluctaing tm1 perspective sheet goes to not responding state with 12000 customers and saving the file crashes the excel .

We tried with few like 10 customers and it worked fine .

COuld the slowness be only due to distance accessed from the server and network used, since its faster for me .

Can you tell me the steps to trouble shoot the issue .

Thanks,

[tomok]

Please use the search feature on this forum and if you can't figure that out just Google "tm1 perspectives network issues". No need to re-create the wheel here.

[jim wood]

I think this is the post that Tomok mean't for you to look at:

http://www.tm1forum.com/viewtopic.php?t=78

Funnily enough it doesn't come up first if you use his suggested statement but it does come up 3rd,

Jim.

[lotsaram]

This is kind of a TM1 101 question. I was beaten to the trigger but seem as I went to all the typing effort here you go to add another 5c to the old thread.

The old TM1 APIs (which is what Perspectives for Excel uses) have a notoriously "chatty" protocol sending lots of message packets back and forth between client and server. This means that latency rather than bandwidth is a real performance killer. It's pretty easy to get a handle on the network latency, just send a ping request from your "50 miles away" user to the server and see what it is.

Although Perspectives is far from WAN optimized there are a few things that can be done to make it much worse! Which is what I think may be happening here.
1/ use of the standard automatic calculation mode rather than manual calculation mode
2/ over-use of single-cell query formulas (DBR, DBS, DBSS, SUBNM, DBRA)

When we slice the data by lowest n level customers , it takes less than a minute to slice all data and on the sliced sheet if we do a dbra then it takes less than a minute to recalculate and save.

But when the user is 50 miles away from the actual server and trying to perform the same operation, while recaluctaing tm1 perspective sheet goes to not responding state with 12000 customers and saving the file crashes the excel.

Key point here being "DBRA". If you have a DBRA formula this is non WAN-optimized meaning each DBRA is a unique query to the TM1 server requiring a unique round-trip to request and receive back the value. Each single cell query will be processed in serial. Meaning if you have 12,000 such queries and a latency of 100 ms then you are looking at a MINIMUM overall end to end time of 120 seconds. If your latency is more or you have more cells then you do the math but your wait time will be longer.

From what you say I would question whether "the file crashes Excel" versus it just takes so long that the user assumes Excel has crashed as Excel is unresponsive.

Unlike the single-cell query formulas DBRW are handled as a batch. If you change the DBRA reference to DBRW and chanfe the dimension reference to }ElementAttributes_dimension then your 12,000 round trips become one round trip (or a small handful of trips depending on packet size) and the end to end query time could be thousands of times faster. (Just make sure the users have read access directly to the }ElementAttributes cube in addition to read access to the dimension).

The reason calculation mode is important is that when manual calculation model is selected the TM1 Perspectives add-in takes over management of the calculation event in Excel and is able to do things like batching the DBRW requests. However if Excel is in automatic calculation then Excel manages the calculation and the DBRW is managed the same as a DBR (that is cell-by-cell) as from Excel's perspective all TM1 references are volatile sincve the cell value is not intrinsic to Excel but is external. This is why manual calculation mode is so important for good performance when using Perspectives.

Also be aware that if element security is applied to the customer dimension then this will make access for non-admin users slower as TM1 needs to filter and subsets to elements accessible to the user.

Also. Pulling data for 12,000 rows doesn't sound like a "report" to me. It sounds like a "data dump" and not an efficient use of a reporting tool.

PS it doesn't change the answer this question but you say users are using 10.1 Perspectives but your profile says version 9.5.

[Analytics123]

Thanks all for your suggestions . I changed the DBRA formula to DBRW to pick value from Element attribute cube and now the calculation is much faster in WAN .