TM1 Web: Pasting values into Websheets

[harrytm1]

Hi,

I encountered the following problems in TM1 Web. Hope you can shed some light on them:

Issue #1: Pasting values into TM1 Websheet takes a long time
I created a template and put it in TM1 Web. I intend to have a similarly formatted Excel file that users can put in data using Excels. These users do not have access to TM1 via Excel client. They can only access through TM1 Web.

After they have input data into the Excel file, they can then login to TM1 Web and copy-paste the region of cells into the corresponding Websheet. This Websheet contains DBSW formula that will save the data.

However, I realised that it takes a long time (e.g. 20 minutes for a few thousand cells) for the pasted values to be captured into Internet Explorer. Anyone knows how to overcome this problem?

Issue #2: TM1 Web's DBSW formula not working well
I further notiiced two issues in TM1 Web that do not occur in Excel Client.

First, my DBSW formula is something like this:
=DBSW(SUM(Z32, -Z83), svr&cube1, ....)
The value to be sent is a sum of positive and negative values. This works fine in Excel. However, when done in TM1 Web, the formula actually ignores the negative values completely.

Second issue is this. The DBSW formula is =DBSW(A83, svr&cube1, ....), i.e. a normal cell reference to the source data. When the source data that is PASTED from an Excel file is a NEGATIVE value, the DBSW formula again ignores the negative value and does not send it.

Please let me know if you have experienced this before, and any solutions to them. Thanks!

[lotsaram]

Hi Harry

I'd say issue 2 sounds like a definite bug. Behaviour of TM1 formulas in TM1 Web vs. Excel Perspectives should be the same. This would most likely be a development oversight as they would have assumed that the primary (or perhaps sole ..) data entry via websheets would be entering into DBRW cells as opposed to DBSW so your scenario was probably not tested. Possibly a simple fix for your issue: do the SUM in a separate cell and use a simple cell reference as the first argument for the DBSW. But still a bug all the same.

Issue 1 probably comes down to fit for purpose. The TM1 web interface simply wasn't designed for large slabs of simultaneous data entry like pasting an array of several thousand cells. You can't expect performance to be the same as in Excel because you don't have the same calculation engine. The answer to your performance issue lies in design, is such a large array of data to change at once really necessary? Can data input be broken down into smaller more sensible chunks? Would a different UI (eg Citrix or Terminal Server with Excel) be better?

[harrytm1]

Thanks, lotsaram.

Regarding Issue #1, I'm afraid Citrix is out due to costs. We are contemplating getting the users who are based in UK to access using Excel Client instead of TM1 Web. However, the server is located in Asia. We are not confident of the performance over such distance as the connection is through WAN.

From your reply, does it mean that even if I use DBRW instead of DBSW in the Websheet, it will still take just as long?

On Issue #2, it turns out that the root of the problem is due to TM1 Web not recognising the pasted value as numbers and treat is as strings. in the source Excel file, the negative numbers are in (123,456) format. So if I copy and paste these values into a Websheet, it will appear as (123,456). The DBSW will ignore the data as it is treated as strings.

To overcome this, users will have to copy and paste-value into a blank Excel sheet first before transferring them into the Websheet.

[lotsaram]

Citrix might be out due to cost but MS terminal service achieves the same result give or take 1% at a per unit license cost of around $100 (which is insignificant compared the the unit cost per TM1 license these days) ...

To answer your last question pasting on the web into an array of DBRW will definitely be faster than pasting to an array of cells which are then used as inputs to DBSW and probably quite significantly so as you will at least be halving the size of the websheet! However performance when pasting an array of several thousand cells on the web is still not going to be stellar.