TM1 Forum

Hi guys.

Here again.

We are now into fine-tuning our Excel reports. These Excels are DBRW-driven. We use TM1 10.2.
I never knew that using the VIEW function in Perspectives would give the results faster during data calculation compared when there's none. If it has none, it says in this link -- http://www-01.ibm.com/support/docview.w ... wg21454290 -- that DBRW may be behaving more like DBRs (each traveling in separate packets).

http://pic.dhe.ibm.com/infocenter/ctm1/ ... _view.html

The link above says that the VIEW function creates an optimized view specified by its arguments.
E.g. =VIEW("INSTANCE:CUBE";$B$2;$B$3;$B$4;$B$5;$B$6;$B$7;$B$8;"!";"!")

The two "!" argument values indicate that the elements in these two dimensions are free-styled (can be more than one elements and can be from any cells in the spreadsheet file).
In our case, for example, we use two different cells in replacement for cell $B$2 (from the sample VIEW above) and either of these two cells are actually being looked-up based on conditions. So only one is used at a time.

Then I think if I will use these two cells in the VIEW function, the $B$2 will be replaced by "!".
=VIEW("INSTANCE:CUBE";"!";$B$3;$B$4;$B$5;$B$6;$B$7;$B$8;"!";"!")

My question is, does VIEW function really return results faster as long as it is used (whether or not all arguments are set to "!") ?

I hope I made sense.

Sorry. Haven't been able to contribute much on others' questions.

Art

bunchukokoy wrote:Then I think if I will use these two cells in the VIEW function, the $B$2 will be replaced by "!".
=VIEW("INSTANCE:CUBE";"!";$B$3;$B$4;$B$5;$B$6;$B$7;$B$8;"!";"!")

That's the right thing to do.

bunchukokoy wrote:My question is, does VIEW function really return results faster as long as it is used

From here:

IBM wrote:Using the Stargate view for rules, in most cases, results in a significant improvement in performance. It is more efficient to retrieve a calculated value from memory than to request and retrieve a calculation from the server.

bunchukokoy wrote:(whether or not all arguments are set to "!") ?

You need to pay attention to your server-side VMM and VMT arguments as well.

bunchukokoy wrote:My question is, does VIEW function really return results faster as long as it is used (whether or not all arguments are set to "!") ?

There is no definitive answer. Generally yes it is better to have a correct VIEW formula and this will ensure the values are cached as a stargate view (provided VMM and VMT is set up correctly) an in turn provide much better response time following the 1st calculation. But it is not always better. if the VIEW contains too many ! arguments for row and column dimensions (especially on large dimensions) then the overhead involved in building and saving the view can take longer than the sum of the individual single-cell queries. If calculation performance is slow then it is worthwhile experimenting (for example if only a few element selections are appropriate for a given dimension then one view formula with ! vs. separate view formulas for each possibility). In my books any sheet that takes longer than 5 sec is "slow".

I have seen examples where adding a view formula has reduced calculation from 1 minute to 2 sec but I have also seen examples where removing a view formula to a simple hardcoded server:cube improved performance from 15 sec to 5 sec. The second possibility is less common but it can happen.

Here's an interesting quote from the official guide:

IBM Cognos TM1: The official guide wrote: If a cell is calculated by a rule, Cognos TM1 knows if the calculation is performed in the context of a Stargate view. If the rule-calculated cell is calculated by a consolidated cell and the context is a Stargate view, the rule calculation will create a new Stargate view. The new Stargate is created with the same row and column dimension as in the origin Stargate. So the new Stargate is caching not only a single value but many values that are likely requested by the rule, too.

So it is going to make a big difference if rules are involved in large cubes that are referenced by VIEW formula especially, as Lotsa mentions, the VIEW formula has a "!" against a large, or massive, dimension.

There are other configuration options apart from VMM and VMT to consider on the server side:

• AllRuleCalcStargateOptimization - helpful when cubes are purely rule calculated including C: level intersections
• CalculationThresholdForStorage - tinker with this when the stargate (VIEW) has complex rules
• DisableWorksheetView - perhaps for consideration when using small VIEWs against massive dimensions
• UseStargateForRules - weird rule-driven cube that might benefit from this configuration (unlikely)
• CognosMDX.CellCacheEnable - from 10.1 - helps certain Cognos integration scenarios
• CognosMDX.SimpleCellsUseOPTSDK - from 10.1 - helps certain Cognos integration scenarios

You've really got to analyse what's best in relation to the number/ type of cubes in the server before getting stuck into this sort of configuration. Specific configuration is probably only optimal for scenarios where the server is running small, but critical, and intensive applications. For large, generalised TM1 applications, a configuration that helps one cube may hinder another so there's going to be a lot of trade-off.

Thank you rmackenzie and lotsaram! I really appreciate it.

Thanks for providing very detailed options guys. Always the complete answers.

The Excel reports don't really have many "!" arguments. I just wanted to know, to what extent it will still work.

I'll go ahead use this function. Now I'm enlightened.