tm1s.cfg parameter: DisableWorksheetView. How does it work?

[harrytm1]

DisableWorksheetView

This parameter is optional.

Please contact customer support to determine if this parameter is applicable to your TM1 system.

DisableWorksheetView disables any VIEW functions contained in slice worksheets. Any slice worksheets containing a VIEW function remain functional, but the function does not generate a Stargate view.

Generally, you should disable the worksheet VIEW functions when you work with extremely large row or column dimensions in slice worksheets. The VIEW function generates a Stargate view that contains all row and column dimension elements, and not just those elements contained in the current row and column subsets. With the Stargate view, you might experience decreased performance when, for example, a row dimension contains 9,000 elements but only 20 elements are actually used in the row subset.

The above is extracted from Op Guide. I also refer to the section on VIEW Excel function in Reference Guide.

From the description, it seems that this parameter is set to TRUE if one wants to improve on the performance. What actually happens in a VIEW function i.e. creating a stargate view? Does it create a view that encompasses ALL elements in the dimensions (instead of just the required elements defined in the slice worksheet) and hence takes up a lot of memory? If this is true, this may also mean that other users who view a small chunk of the view will enjoy faster response, unless there is a change in underlying data that requires a recalculation.

I really don't understand how Stargateview and DisableWorksheetView work in terms of memory usage, recalculation or performance improvement.

Any comment is welcome! Thanks!

[Alan Kirk]

DisableWorksheetView

This parameter is optional.

Please contact customer support to determine if this parameter is applicable to your TM1 system.

DisableWorksheetView disables any VIEW functions contained in slice worksheets. Any slice worksheets containing a VIEW function remain functional, but the function does not generate a Stargate view.

Generally, you should disable the worksheet VIEW functions when you work with extremely large row or column dimensions in slice worksheets. The VIEW function generates a Stargate view that contains all row and column dimension elements, and not just those elements contained in the current row and column subsets. With the Stargate view, you might experience decreased performance when, for example, a row dimension contains 9,000 elements but only 20 elements are actually used in the row subset.

The above is extracted from Op Guide. I also refer to the section on VIEW Excel function in Reference Guide.

From the description, it seems that this parameter is set to TRUE if one wants to improve on the performance. What actually happens in a VIEW function i.e. creating a stargate view? Does it create a view that encompasses ALL elements in the dimensions (instead of just the required elements defined in the slice worksheet) and hence takes up a lot of memory? If this is true, this may also mean that other users who view a small chunk of the view will enjoy faster response, unless there is a change in underlying data that requires a recalculation.

I really don't understand how Stargateview and DisableWorksheetView work in terms of memory usage, recalculation or performance improvement.

Any comment is welcome! Thanks!

Well that was lovely; I just typed out a reply to have it vanish under me.

OK, in a nutshell what I said was:

Check out page 86 of the 9.5 Operations guide (my emphasis):

About Stargate Views
A Stargate view is a calculated and stored subsection of a TM1® cube that TM1 creates when you browse a cube with the Cube Viewer or In-Spreadsheet Browser. The purpose of a Stargate view is to allow quicker access to the cube data. A Stargate view is different from a TM1 view object. The Stargate view contains only the data for a defined section of a cube, and does not contain the formatting information and browser settings that are in a view object.

A Stargate view that TM1 creates when you browse a cube in the Cube Viewer or In-Spreadsheet Browser contains only the data defined by the current title elements and row and column subsets. TM1 stores a Stargate view when you access a view that takes longer to retrieve than the threshold defined by the VMT property in the }CubeProperties control cube. (If a VMT value is not explicitly defined, a Stargate view is generated when a view takes longer than five seconds. This is the default threshold when VMT is not specified in the }CubeProperties control cube.) A Stargate view persists in memory only as long as the browser view from which it originates remains unchanged. When you recalculate the browser view, TM1 creates a new Stargate view based on the recalculated view and replaces the existing Stargate view in memory. When you close the browser view, TM1 removes the Stargate view from memory.

It's interesting that the information about which elements are in the rows and columns appears to contradict the information given in the parameter section, but it's not the first time we've seen that happen. However we often have long dimensions (weeks for columns, accounts for rows) and haven't had a problem in this regard. I used to be a View() function skeptic (and they DID seem to have issues in early versions) until I added some to an existing report and got a 50% performance boost out of them.

The config parameter simply prevents new slices from coming down with View() functions in the cell which contains the server name. I wouldn't unless I was having problems which I suspected may be caused by this.

[harrytm1]

The config parameter simply prevents new slices from coming down with View() functions in the cell which contains the server name. I wouldn't unless I was having problems which I suspected may be caused by this.

Thanks, Alan.

I have included DisableWorksheetView=T in the tm1s.cfg. When I slice from a Cube Viewer, the View function does appear in the Excel sheet. hence, the parameter does not stop the slice from using the View function. Does this mean that the parameter will stop creating any Stargate view?

Base on the description on Stargate view, this would also imply that setting DisableWorksheetView=T will result in a LONGER view generating time since there is no stargate view for other users to "recycle" quickly, assuming the underlying data does not change.

In your post (see quote), are you saying that you wouldn't set DisableWorksheetView=T unless you are having problem, just so that you can enjoy the benefits of stargate view?

Sorry for being naggy, as I want to find ways to improve user experience. So far, I have been using DisableWorksheetView=T.

[lotsaram]

Harry - if you are looking for ways to improve user query performance then possibly the last thing you would want to do is to disable stargate views in Excel reports. Disabling the construction of a stargate view from a VIEW() formula will result in the value of each cell being re-evaluated on each refresh rather than returning cached values.

The VIEW() function is there for a reason, because it boosts performance. It would be only a very rare instance when you would get better performance on subsequent recalcs without a stargate view, and if that were the case you can deal with that particular instance by removing the formula in that specific sheet.

[Alan Kirk]

The config parameter simply prevents new slices from coming down with View() functions in the cell which contains the server name. I wouldn't unless I was having problems which I suspected may be caused by this.

I have included DisableWorksheetView=T in the tm1s.cfg. When I slice from a Cube Viewer, the View function does appear in the Excel sheet. hence, the parameter does not stop the slice from using the View function. Does this mean that the parameter will stop creating any Stargate view?

Yes, there was a certain amount of ambiguity to the documentation which comes as a huge shock to me. I thought that it would stop generating the View functions but indeed you're right, it doesn't, it simply cripples the functionality of View functions.

DisableWorksheetView disables any VIEW functions contained in slice worksheets. Any slice worksheets containing a VIEW function remain functional, but the function does not generate a Stargate view.

"Remains functional" in Cognos-speak apparently means "will still return the name of the server, but will not do anything else. That is to say, it's functional aside from the fact that it isn't".

Base on the description on Stargate view, this would also imply that setting DisableWorksheetView=T will result in a LONGER view generating time since there is no stargate view for other users to "recycle" quickly, assuming the underlying data does not change.

That's correct; I'm not sure why you would have expected otherwise. Pre-cached data will always be faster than data that has to be re-evaluated.

In your post (see quote), are you saying that you wouldn't set DisableWorksheetView=T unless you are having problem, just so that you can enjoy the benefits of stargate view?

"Just so that (I) can enjoy the benefit" of faster performance? Well... yeah. In a nutshell.

OK, let me be clear.

- "I wouldn't" means that "I would not add that parameter to the config file", because I see no reason to do that;
- If I did, I wouldn't set it to T, because I see no reason to do that;
- The sole, only and exclusive exception to the above would be if I was having performance issues which I had reasonable grounds for believing were caused by the View() function. Of which, I have had none;
- In any other circumstance the parameter would be kept just beyond one (1) barge pole's distance from my .cfg file, or to put it another way;
- What Lotsaram said.