Contents


IBM Business Analytics Proven Practices

Tips and Techniques on working with IBM Cognos TM1 Web

Product(s): IBM Cognos TM1 10.2.2; Area of Interest: Financial Management

Comments

Content series:

This content is part # of # in the series: IBM Business Analytics Proven Practices

Stay tuned for additional content in this series.

This content is part of the series:IBM Business Analytics Proven Practices

Stay tuned for additional content in this series.

Purpose of document

This document will provide tips and techniques for the IBM Cognos TM1 Model Builder with tips on how to build models using IBM Cognos TM1 Web.

Applicability

IBM Cognos TM1 10.2.2

Exclusions and exceptions

There are no known exceptions or exclusions at the time this document was created.

Assumptions

This document covers advanced IBM Cognos TM1 concepts and uses TM1-specific terminology. The reader should have an understanding of IBM Cognos TM1 cubes, dimensions, rules, and terminology before proceeding.

Tips and techniques on working with IBM Cognos TM1 Web

IBM Cognos TM1 Web Overview

IBM Cognos TM1 Web extends the analytical power of IBM Cognos TM1 Web by offering a number of tasks in a web browser. Some of these tasks are,

  • Analyze cube data
  • View and edit data in formatted Excel reports
  • Drill, pivot, select, and filter data
  • Build charts from cube data
  • Perform some of the IBM Cognos TM1 server administration tasks

IBM Cognos TM1 Web architecture

IBM Cognos TM1 Web uses a multi-tiered architecture that enables users to access and interact with Cognos TM1 data using any supported web browser. The IBM Cognos TM1 Web multi-tiered architecture includes,

  • Tier 1 – A TM1 Web client running in a supported web browser (Internet Explorer, Google Chrome or Mozilla Firefox)
  • Tier 2 – The TM1 Web application running under Apache Tomcat
  • Tier 3 - Data running on the TM1 Admin Server and the TM1 Server
Figure 1 – The multi-tier TM1 Web architecture
Figure 1 – The multi-tier TM1 Web architecture
Figure 1 – The multi-tier TM1 Web architecture

IBM Cognos TM1 Web runs on a Java Application Server such as Apache Tomcat. By default, the IBM Cognos TM1 installation configures IBM Cognos TM1 Web to use the Tomcat server that is provided with the IBM Cognos TM1 installation. For additional information, refer to theTM1 Installation and Configuration Guide.

IBM Cognos TM1 supported software environments

IBM Cognos TM1 Model Builders should be familiar with the supported software environments. This information can be found at http://www.ibm.com/support/docview.wss?uid=swg27038140.

IBM Cognos TM1 Web and Internet browsers

IBM Cognos TM1 uses Internet Explorer, Mozilla Firefox and Google Chrome as part of their web-based interface. It is suggested that IBM Cognos TM1 Model Builders consider the following when selecting a web-based interface to use with IBM Cognos TM1 Web.

  • Mozilla Firefox and Google Chrome handle both memory management and the rendering of objects within IBM TM1 Web as compared to Microsoft Internet Explorer.
  • IBM Cognos TM1 Model Builders will find better performance with IBM Cognos TM1 Web using newer versions of Microsoft Internet Explorer due improvements made to functionality of Microsoft Internet Explorer.
  • IBM Cognos TM1 Model Builders that are using Microsoft Internet Explorer will find 64-bit Internet Explorer as their best option.
  • For the list of supported browsers and versions refer to the IBM Support site at http://publib.boulder.ibm.com/infocenter/prodguid/v1r0/clarity-reports/report/html/prereqsForProduct?deliverableId=1375106724669

IBM Cognos TM1 Web security configuration

IBM Cognos TM1 Model Builders may want to refer to the developerWorks article at http://www.ibm.com/developerworks/library/ba-pp-security-fpm-page678/index.html for additional information on how to configure security for IBM Cognos TM1.

Improving Performance and Memory Management for IBM Cognos TM1 Web

Increasing memory management for IBM Cognos TM1 Web

One of the options to help improve performance with IBM Cognos TM1 Web is to change the application server default memory. Figure 2 shows the IBM Cognos TM1 Application Server maximum memory set to 2000 MB in IBM TM1 Cognos Configuration. IBM Cognos TM1 Model Builders will need to review this memory setting to ensure it is set at the appropriate level. Refer to the IBM Technote at http://www.ibm.com/support/docview.wss?uid=swg21669247.

Figure 2 – TM1 Application Server properties in IBM Cognos Configuration
Figure 2 – TM1 Application Server properties in IBM Cognos Configuration
Figure 2 – TM1 Application Server properties in IBM Cognos Configuration

IBM Cognos TM1 Model Builders may also want to refer to the IBM Technote at http://www.ibm.com/support/docview.wss?uid=swg27041876 as it provides more details on setting the memory threshold for IBM Cognos TM1 Web.

Adjusting scrolling and buffering thresholds

Defining the Row and Column Threshhold for IBM Cognos TM1 Web

IBM Cognos TM1 Model Builders can make changes to the WebsheetRowThreshold and WebsheetColumnThreshold parameters as part of improving the data retrieval process from IBM Cognos TM1 Server to IBM Cognos TM1 Websheets. If the sheet fits roughly within the number of rows and columns specified with the new parameters, it will be fully loaded on start up. If a sheet is larger than the values set with the parameters, then IBM Cognos TM1 Web fetches additional data on demand as you scroll beyond the buffers of the previously loaded data.

It is recommended that the parameters for the row and column be set based on small to average sized Websheets that will be opened with IBM Cognos TM1 Web. IBM Cognos TM1 Web Model Builders will need to review these settings based on their own environment.

Defining the Row and Column Buffer Size for IBM Cognos TM1 Web

By default, the IBM Cognos TM1 Web displays pages of TM1 data with 20 columns and 100 rows, and includes the dimensions list in the row count. IBM Cognos Model Builders need to review the following as part designing IBM Cognos TM1 Websheets within IBM Cognos TM1 Web:

  • The lower the buffer size, the less data that needs to be transferred meaning better performance for larger Websheets.

IBM Cognos TM1 Modeller builders will need to change the following configuration settings in the tm1web_config.xml file. For additional information on these settings refer to the section titled Modifying Cognos TM1 Web Configuration Parameters in the chapter Administering IBM Cognos TM1 Web of the TM1 Operations Guide.

  • <add key=”RecalcOnActive” value=”true” />
  • <add key=WebsheetRowThreshold” value “150” />
  • <add key=”WebsheetRowBufferSize” value=”50” />
  • <add key=”WebsheetColumnBufferSize” value”20” />

For additional information on changing the threshold settings for IBM Cognos TM1, IBM Cognos TM1 Model Builders may want to refer to the IBM Technote at http://www.ibm.com/support/docview.wss?uid=swg27040580.

IBM Cognos TM1 Active Forms

Active Forms let IBM Cognos TM1 Model Builders view and update live IBM Cognos TM1 cube data directly in Microsoft Excel whenever end-users are connected to the IBM Cognos TM1 server on which the cube data resides. Active Forms retain the ability to expand and collapse row dimension consolidations in IBM Cognos TM1 views while allowing end-users to use native Excel features and functions to create complex reports.

It is important for IBM Cognos TM1 Model Builders to be familiar IBM Cognos TM1 Active Forms functions. Refer to the chapter titled Active Forms in the TM1 User Guide for more details.

Areas to focus on with Active Forms

When inserting data from other cubes, the following are recommended practices that should be considered by IBM Cognos TM1 Model Builders when working with Active Forms and IBM Cognos TM1 Web:

  • Do not link to TM1RptView.
  • Don’t use large static subsets.
  • Remove unhidden sheets that are not necessary.
  • If using Dynamic MDX, test in subset editor to get expression correct.
    • TM1SubsetBasis now supported on the web.
  • Review all DBR/DBRW dependencies. A DBRW should not refer to another DBRW.
  • Users cannot split TM1RptRow columns if there are multiple row dimensions in Perspectives. The TM1RptRow split will work with IBM Cognos TM1 Web.
  • Delete rows below master row before uploading as it will speed up and use less memory on initial data load into IBM Cognos TM1 Websheets.

IBM Cognos TM1 Websheets

IBM Cognos TM1 Websheets are highly interactive, zero-footprint HTML forms for IBM Cognos TM1 read/write activity that allows end-users to improve their ability to build financial planning solutions. IBM Cognos TM1 Websheets uses Microsoft Excel palette styling that will allow for the following functionality:

  • Free-form layout
  • Formatting
  • Formulas and functions
  • Local data
  • Charting

The IBM Cognos TM1 Model Builder should also be familiar with worksheet functions for IBM Cognos TM1, which can be referenced in the chapter titled Worksheet Functions in the TM1 Reference Guide. The following information will not go into details of the DBR and DBRW functions, but will stress recommended practices when using worksheet functions within IBM Cognos TM1 Active Forms and/or with IBM Cognos TM1 Websheets.

Commonly Encountered Issues with Websheets

Listed below are some commonly encountered issues that IBM Cognos TM1 Model Builders may experience when working with IBM Cognos TM1 Websheets.

  • Individual IBM Cognos TM1 Websheets that are too big in that they are including too many rows and columns with data can affect performance.
  • Individual IBM Cognos TM1 Websheets that contain too many formulas and/or formulas that are too complex can also affect performance.
  • Microsoft Excel functionality may not convert properly within the Websheets.
  • Chart conversions may not be displayed properly within the Websheets. End-users may be required to modify the charts within Websheets to ensure they are reflected properly within the Websheet.
  • Action Button activities causing contention.
  • Memory consumption when scaling user community.

Improving performance with TM1 Websheets

Recommended approaches for IBM Cognos TM1 Model Builders when improving performance with TM1 Websheets are as follows:

  • Reducing the formula complexity within the Websheets.
  • Reducing the size of both the rows and columns.
  • Avoiding using complex formatting.

Practices for TM1 Websheets models

Recommended practices for IBM Cognos TM1 Websheets models are the following:

  • Test query performance using IBM Cognos TM1 Cube Views.
  • Use a VIEW statement for each cube’s data.
  • Formulas that are not optimized for TM1 Web
    • DBRA formula (attribute retrieval) then use DBRW formula against }ElementAttributes cube.
    • DBS formula / DBSW formula should be used sparingly.
    • DBRW formulas in Excel Conditional Logic don’t batch for server retrieval. Use cell reference to DBRW with hidden column.
    • Avoid pick lists with more that 10,000 elements.
    • Reduce re-calculations after pick list or data validation selections by setting the workbook re-calculation mode to manual and UseBookRecalcSetting to True in web.config.
    • Don’t use SUBNM formulas in the body of a report as they can cause issues with retrieving of data from IBM Cognos TM1 Server.
    • Consider moving workbook logics into the TM1 Server Model.

Practices for large TM1 Websheets

Recommended practices for large IBM Cognos TM1 Websheets are as follows:

  • Use the Websheet paging option when the Websheet is greater than 1000 rows.
  • Consider breaking a large Websheet into a number of smaller Websheets that are connected by Action Buttons.

Practices on using static formatting with TM1 Websheets

Recommended practices on using static formatting with IBM Cognos TM1 Websheets are the following:

  • Each unique permutation of formatting in a sheet has a memory impact. Permutations of formatting occur when mixing and overlaying styles. Try to minimize number of colors, types of borders and formats.
  • Attempt to format ranges of cells instead of individual cells to reduce unintentional uniqueness.

Practices using conditional formatting with TM1 Websheets

Recommended practices when using conditional formatting with IBM Cognos TM1 Websheets are the following:

  • There are limits to the number of cells a conditional format can apply to. Be wary of using them excessively, particularly in an Active Form where the format is copied down across many rows.
  • Keep the logic in the conditional formatting as simple as possible. Avoid invoking TM1 database formulas that would not already be calculated.
  • Conditions evaluate in the context of each cell. If parts of the calculation are unrelated to position, even if only for a single axis, move them to a cell and reference that cell to eliminate redundant calculations.

Practices using Active Forms with TM1 Websheets

Recommended practices when using Active Forms with IBM Cognos TM1 Websheets are as follows:

  • Use Classic Slice if row set is fixed or blend Classic and Active Form sections.
  • Use named subsets because unnamed subsets in row dimension(s) will create a hidden sheet with element contents.
    • Use an MDX statement in the TM1RptRow formula. This is very powerful. The formula below illustrates the syntax for both TM1RptRow and TM1DrillDownMember:
      =TM1RPTROW($C$9,"bpm:Products","","","bob",,Q14) Cell Q14 contains  …
      {TM1DRILLDOWNMEMBER( {[Products].[Total]}, ALL, RECURSIVE )}
  • Best practices related to formatting apply and may take on even more importance due to the dynamic nature of the row count.

Publishing IBM Cognos TM1 Websheets

The following are recommended practices before publishing IBM Cognos TM1 Websheets that IBM Cognos TM1 Model Builders can use as part of the financial planning cycle:

  • Delete contiguous rows following TM1RptRow prior to publishing.
  • Check to see if the Websheet is picking up extraneous row or column data – you can tell if the vertical or horizontal thumb is relatively small. Delete these rows or columns.
  • Look for unnamed subsets in Active Form TM1RptRow and delete the subset from any hidden sheets.
  • Delete hidden sheets created to store pick list entries.
  • Workbooks stored on the TM1 Server will be faster than those stored on the file server.
  • Don’t overbuild the IBM Cognos TM1 Websheet Home Page.

Downloadable resources


Comments

Sign in or register to add and subscribe to comments.

static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Big data and analytics
ArticleID=980427
ArticleTitle=IBM Business Analytics Proven Practices: Tips and Techniques on working with IBM Cognos TM1 Web
publish-date=08122014