Large Memery Consumed in TM1Web with mulit excel-sheets

[solverxyz]

Hi all,

I have a Excel file which is sized ~3.5MB and inclued with 35 sheets( a lot of DBRW, of course).

When I Open It with TM1 Prespective, only 133 MB memery was consumed in Ctrix Server.
However, while opening the same file in TM1Web, it was 1441 MB consumed in TM1Web server !!

I know that if you open a excel file in TM1 Web, there is a TM1ExcelService running to convert the excel form into the web form for Browser such like IE.
Much time to open a file is understood and acceptable, but the large memery consumed ???

Considering with the same HardWare( RAM = 32 GB ) used for the Server:
With TM1Web, only 20 user can login and open one excel at the same time.
With Ctrix, 22x user can login and open the same excel at the same time.



I want to use TM1Web to replace Ctrix ( because of money... ), but the memery consuming is a serious issue.
Could anyone tell me how to solve this issue ?


--
TM1: 9.5.2 FP3
Excel : 2010 SP1

[declanr]

Solverxyz,

Yes TM1Web is an excellent solution to replace the use of excel workbooks over a citrix server BUT think about what you are doing when you open something in TM1Web; it's not the same as just opening a workbook in a new environment... it has to convert every bit of every spreadsheet into an entirely different format that can be viewed through your browser.

To go down the route of using TM1Web the bottom line is that you will need to change your process (aka your mega workbooks) to accommodate this, search the forum for best practices surrounding websheets but I will start by pointing out that it is best to keep them to just that webSHEETS... the second you get into multipage workbooks it can become taxing.

Keep an eye on the w3wp.exe that will run on the server whilst someone is using tm1web, this is where you will see your memory skyrocket if the sheets aren't designed with tm1web in mind.

HTH

[lotsaram]

Hi solverxyz,

Please do a search of this forum and you will find many threads discussing limitations of TM1 web and how to use it effectively.
(Hint: workbooks with 35 tabs is not an example of how to use TM1 Web effectively.)

[PlanningDev]

Just to play devils advocate. What about the use of 64 bit web servers with larger amounts of ram? I know there are some performance issues with lots of sheets but wondering what others experiences have been like.

[solverxyz]

It seems that TM1WEB is ony suitable for simple and read-only report.

As a input interface, however, the most of end-users always want to process like what they do in Excel.
For example, they want no change in their working paper ( excel, mulit-sheet ) , but just all the data could be stored into TM1 server.
In a additional , they want a instant snapshot for printing ( based on print area they have set in Excel ! ).
I don't think TM1Web could do these.....it's not flexable.

More important, it's not acceptable for them if they have to change their habits to use single websheet in TM1WEB( alomst 100 cubes, how many websheet they have to open ???? )
,especially after Pre-sale said "TM1 is like excel when you input" to End-User( They will expect all excel behavor are remained ).

Thanks to you, all guys.

[Wim Gielis]

solverxyz

TM1 Web is not my most favourite tool within the IBM Cognos TM1 suite, but what you write above often makes no sense at all.

- Read-only report? Did you investigate the possibilities of the DBRW formula?
- TM1 Web is not single sheet only. Upload a multisheet Excel file as a websheet and observe what you do with it in TM1 Web.
- It can contain information from several cubes at once, so why would they open up many websheets?
- If users want to have the same experience as in Excel, well, give them TM1 Perspectives. TM1 Web is just NOT Excel in a web browser.

And so on. But I agree that TM1 Web could be improved (IBM seems to be doing that, wait for version TM1 10.2).

[rmackenzie]

Just to play devils advocate. What about the use of 64 bit web servers with larger amounts of ram? I know there are some performance issues with lots of sheets but wondering what others experiences have been like.

In larger, more mature implementations, it is usually a dead-end to look at the existing report collateral and simply try to upload to TM1 web and continue to use the reports as they stand. Issues around massive multi-worksheet workbooks, with VBA, with 'working paper' sheets and links to other workbooks etc just don't transfer.

The advice is to analyse the required functionality in existing workbooks and rebuild including this functionality and modify for the strengths of TM1 Web and avoid the weaknesses. With all but the most simplest of read/ write workbooks, some sort of re-design is usually inevitable.

Pre-sale said "TM1 is like excel when you input" to End-User( They will expect all excel behavor are remained )

This isn't a bad generalisation - TM1 Web acts very much like Excel from an input point of view. But as Wim says, it isn't Excel on the web.

[solverxyz]

solverxyz

TM1 Web is not my most favourite tool within the IBM Cognos TM1 suite, but what you write above often makes no sense at all.

- Read-only report? Did you investigate the possibilities of the DBRW formula?
- TM1 Web is not single sheet only. Upload a multisheet Excel file as a websheet and observe what you do with it in TM1 Web.
- It can contain information from several cubes at once, so why would they open up many websheets?
- If users want to have the same experience as in Excel, well, give them TM1 Perspectives. TM1 Web is just NOT Excel in a web browser.

And so on. But I agree that TM1 Web could be improved (IBM seems to be doing that, wait for version TM1 10.2).

Thanks, Wim,

Let me clearfy why I said so.

- Read-only report? Did you investigate the possibilities of the DBRW formula?
when auser want a "report", it's usually complex and ready for printing. That's means a lot of sheet's are needed. because end-user are lazy, it's hard to force them do the open-and-print loop. ><

- TM1 Web is not single sheet only. Upload a multisheet Excel file as a websheet and observe what you do with it in TM1 Web.
Yes, 10 cube per sheets , 100 cubes means 10 sheets, means 10 workbook( it's for One package, what if there are 10 package ? ). End-User donts want to do that. ><

- It can contain information from several cubes at once, so why would they open up many websheets ?
same as i metioned above.. ><

- If users want to have the same experience as in Excel, well, give them TM1 Perspectives. TM1 Web is just NOT Excel in a web browser.
TM1 Perspective has WLAN issue without Citrix ( incrediable slow in WLAN, VPN )... and that's why i am suverying TM1WEB.

what's I said were just my case, not the general situration. thank you.