TM1 Web performance, Stargate View for report

[vovanenok]

Hi all

Can someone help me to improve my TM1 webseet performance.

I have a cube with no rules/calcs, no hierarchies, no dynamic subsets, absolutely static cube, only elements I need in the report (everything to maximize the performance):
Entity (930)
Version (2)
Period (50)
Account (500)
Measure (1)

I can open it in Architect pretty fast (5-7seconds) even putting all the dimensions to rows/cols, but in TM1 Web everything is very slow. The report is a pretty simple active form and displays the data for selected entity by accounts and for 6 periods. It takes about 12-15 seconds to run.

If I try to slice the report to Excel it takes around 35-40 seconds and the file size is 0.9mb.

If I try to slice it to Excel with 7 entities selected (title dimension) and 1 Period ( so 7 sheets in total) it takes around 2.5-3 mins and the file size is 6.1 mb .

If I try to slice the report with 14 entities, it takes around 5-5.5 mins and the file is around 12.2 mb.

So it looks like for slicing to Excel it needs 20-25 seconds for each entity. But when I look at tm1top while slicing, it shows that my session is almost always idle, so I suppose that could be an issue with TM1 Web itself.

Additional info:
TM1 10.1.1

Cube info:
Total Memory Used: 276,825,088
Number of Populated Numeric Cells: 6,045,784
VMT=1
VMM=1048576

My questions:
- do you have any similar performance issue with TM1 Web (when TM1 itself works perfectly fine)?
- what would be the suggestions to look at in my situation?
- another question: why my process with ViewConstruct doesn't create a stargate view for that cube? I noticed that the star gate view is getting created only when I open manually that cube in architect and putting dims to rows/cols

[declanr]

There are a few parts to your question.

Part 1, the actual websheet:

Make sure to hide all extra columns to the right of your active form.
Make sure to only have 1 tab in your workbook.
If there are hidden excel sheets (especially {AR} reference sheets, delete them.)
Don't make the TM1RptView defined cube area any bigger than needs be.
Make sure all of your cells reference the TM1RptView cell if you can.

How are the TM1RptRow formulas defined? ( Subsets? MDX Formulas? Lists of elements? Dreaded {AR} hidden sheets?)


Part 2, exporting to excel:

Exporting from TM1Web isn't really down to TM1 as such, hence you not seeing anything in TM1 Top. It's all about the speed it takes to create the excel sheet or PDF document and then send it to your PC. You won't be able to do a great deal about improving that speed other than the things listed in part 1 such as making the workbook as small as possible.


Part 3, stargate views:

Is the view you are defining in your viewconstruct function exactly the same as that you are viewing in the cube? (keeping in mind skip consolidations settings etc.)
How do you know that no view is being cached? Is it just that it still takes a while to open after that or have you turned on the debugging option for stargates?

If you haven't turned on stargate debugging that should be your first step before doing anything else.

I would say look at adjusting VMM and VMT settings but the fact you can get it to cache by opening a view suggests they are probably ok anyway.

[vovanenok]

Hi declanr

Thanks for your reply. I just started working for that client and I don't know their environment very well.

Part 1, the actual websheet:
Make sure to hide all extra columns to the right of your active form.
Make sure to only have 1 tab in your workbook.
If there are hidden excel sheets (especially {AR} reference sheets, delete them.)
Don't make the TM1RptView defined cube area any bigger than needs be.
Make sure all of your cells reference the TM1RptView cell if you can.

How are the TM1RptRow formulas defined? ( Subsets? MDX Formulas? Lists of elements? Dreaded {AR} hidden sheets?)

That was done initially. TM1RptRow uses all elements

Part 2, exporting to excel:

Exporting from TM1Web isn't really down to TM1 as such, hence you not seeing anything in TM1 Top. It's all about the speed it takes to create the excel sheet or PDF document and then send it to your PC. You won't be able to do a great deal about improving that speed other than the things listed in part 1 such as making the workbook as small as possible.

Here is the most interesting thing I've just discovered. They have two TM1 web servers ( I was told they work as Active-Failover and on Friday I was looking at inactive one).
Today I opened two rdp, opened task manager in each one and run TM1 web report. I saw that TM1 server almost doesn't feel the request (minimum CPU usage), when TM1 web was working very hard (50-70% CPU)

I found that TM1 is installed on a physical machine with a good CPU / memory. TM1 web is VMware, just 8gb of RAM. I believe this is a cause of report performance.
So here I need to check what is the hardware VMware machines are running on and find some better environment for TM1 web. I would appreciate if someone could share or guide me to the hardware prerequisites for TM1 Web server (to work with a small-medium TM1 model).

Part 3, stargate views:

Is the view you are defining in your viewconstruct function exactly the same as that you are viewing in the cube? (keeping in mind skip consolidations settings etc.)
How do you know that no view is being cached? Is it just that it still takes a while to open after that or have you turned on the debugging option for stargates?

If you haven't turned on stargate debugging that should be your first step before doing anything else.

I would say look at adjusting VMM and VMT settings but the fact you can get it to cache by opening a view suggests they are probably ok anyway.
TM1 Web performance, Stargate View for report

As I mentioned before all the dims of the reporting cube are flat, and the cube doesn't have any rules. The report has a dropdown list show/suppress zeroes, so in viewconstruct I use the view with no suppression. Also that view uses static subsets with all elements.

Of course I enabled the debugging for a starview:
llog4j.logger.TM1.Cube.Stargate=DEBUG
log4j.logger.TM1.Cube.Stargate.Reference=DEBUG

It displays that the stargate view was created, but was destroyed after:
6704 [2] INFO 2014-06-30 09:51:42.057 TM1.Process Process ".aaa test" executed by user "myuser"
6704 [2] DEBUG 2014-06-30 09:51:42.728 TM1.Cube.Stargate Adding stargate: 0x0000000002320010 to catalog: 0x00000001B1DF6A58 of cube: 0x0000000006C6D010.
6704 [2] DEBUG 2014-06-30 09:51:42.728 TM1.Cube.Stargate Stargate 0x0000000002320010 referenced : RefCount=0->1
6704 [2] DEBUG 2014-06-30 09:51:42.728 TM1.Cube.Stargate dbstgCreate: dbstgCalculate (0x0000000002320010) in 0ms SGSize=606Kb workPoolSize=0Kb hPoolSize=97Kb.
6704 [2] DEBUG 2014-06-30 09:51:42.743 TM1.Cube.Stargate dbstgCreate: CalculateAxesConsolidations Stargate (0x0000000002320010) in 15ms
6704 [2] DEBUG 2014-06-30 09:51:42.743 TM1.Cube.Stargate New Stargate Created (0x0000000002320010) in 15ms SGSize=606Kb workPoolSize=0Kb hPoolSize=97Kb.
6704 [2] DEBUG 2014-06-30 09:51:42.743 TM1.Cube.Stargate Stargate 0x0000000002320010 released : RefCount=1->0
6704 [2] DEBUG 2014-06-30 09:51:42.743 TM1.Cube.Stargate Destroying Stargate 0x2320010
6704 [2] INFO 2014-06-30 09:51:42.821 TM1.Process Process ".aaa test": finished executing normally, elapsed time 0.76 seconds

Also I enable performance monitor and see the number of stored views and memory usage, and after running the process it still shows "empty".
But I found when I open some big view manually in a cube viewer, it creates some persistent stargate view.

I don't know why it happens. Also is it possible to know for which cube that view was created? (in my current process of course I know it, but when you have multiple stargate views this is getting difficult to track)

[PlanningDev]

VMM and VMT settings control the stargate cache. VMT sets how long a view request should take before it caches the view. Default is 5 seconds I believe (This is TM1 Server create timing so taking 15 seconds over the web could mean a 1 second TM1 Server time and 14 seconds of network and webserver). VMM is the amount of memory the cube should have available for caching. The default is very low (However your cube is tiny in terms of TM1 capability.

I would start with the web server and any network issues. For TM1 Web depending on your version your time will be spent in IIS or in Apache. You should be looking at www3 and tomcat processes.

To expand on Declanr's point of extra rows or columns. Be careful that you don't have formatting or data outside of the area where you need it. It may be best to DELETE all columns to the right of your active form if they are not needed. I would also consider deleting all rows below your active form then resaving.

[vovanenok]

Thanks PlanningDev

I know about VMT/VMM. Unused rows/columns were removed. About network performance, what kind of tests would you advise to do?
As I mentioned in my previous post, I found the issue is in TM1 web, so another thing I'm going to try - better environment for TM1 Web.

[nick_leeson]

Try Wireshark http://www.wireshark.org/ or fiddler http://www.telerik.com/fiddler.

Cheers
Nick

[vovanenok]

Thanks Nick
Will try it

[vovanenok]

Guys

I would like to post here some my observations regarding ViewConstruct function and Stargate view creation. Previously I found my stargateview was destroyed right away after creation using ViewConstruct:

6596 [2] INFO 2014-07-07 11:26:45.564 TM1.Process Process ".test" executed by user "***"
6596 [2] DEBUG 2014-07-07 11:26:45.564 TM1.Cube.Stargate Adding stargate: 0x00000001B23A8010 to catalog: 0x00000001B2781758 of cube: 0x00000001B27F6010.
6596 [2] DEBUG 2014-07-07 11:26:45.564 TM1.Cube.Stargate Stargate 0x00000001B23A8010 referenced : RefCount=0->1
6596 [2] DEBUG 2014-07-07 11:26:45.564 TM1.Cube.Stargate dbstgCreate: dbstgCalculate (0x00000001B23A8010) in 0ms SGSize=112Kb workPoolSize=0Kb hPoolSize=97Kb.
6596 [2] DEBUG 2014-07-07 11:26:45.564 TM1.Cube.Stargate dbstgCreate: CalculateAxesConsolidations Stargate (0x00000001B23A8010) in 0ms
6596 [2] DEBUG 2014-07-07 11:26:45.564 TM1.Cube.Stargate New Stargate Created (0x00000001B23A8010) in 0ms SGSize=112Kb workPoolSize=0Kb hPoolSize=97Kb.
6596 [2] DEBUG 2014-07-07 11:26:45.564 TM1.Cube.Stargate Stargate 0x00000001B23A8010 released : RefCount=1->0
6596 [2] DEBUG 2014-07-07 11:26:45.564 TM1.Cube.Stargate Destroying Stargate 0xb23a8010
6596 [2] INFO 2014-07-07 11:26:45.564 TM1.Process Process ".test": finished executing normally, elapsed time 0.00 seconds

I feel like for ViewConstruct the same VMT / VMM criterions are applied.
In my TM1 web report I use SUBNM function for my entity dimension so users can view the report for the selected entity and they can shapshot/slice all the selected entities.
While creating a stargate view I put entity dimension to the title dimensions and I believe it caused a faster view creation and that's why it wasn't caches. Now I put almost all the dimensions to view rows / cols and finally I got my persistent stargate view.

That would interesting to hear any thoughts on this.

[tomok]

That would interesting to hear any thoughts on this.

Title dimensions, by definition, can only have one item selected in the view. Therefore only one entity is going to be cached. When your users go to export all the other entities they have to be calculated on the fly.

[vovanenok]

Hi tomok, good point. So in case with view title dimension when subset has > 1 element, only the first element is cached.

[tomok]

Hi tomok, good point. So in case with view title dimension when subset has > 1 element, only the first element is cached.

It would be cached with the selected element. If the view was created programatically and no one had modified it then yes, it would be cached with the fist element in the subset.