Customizing Cognos Analysis for Microsoft Excel

You can set options that apply to IBM® Cognos® Analysis for Microsoft Excel or specifically to your explorations.

Options for the application Setting options for IBM Cognos Analysis for Microsoft Excel apply to workbooks created in IBM Cognos Analysis for Microsoft Excel that may include explorations, analyses, and consumed reports from IBM Cognos data sources. The data structures are retrieved from the packages in the content store.
Options for explorations Set options for exploration sheets apply to worksheets that contain explorations. You can use these options to quickly change how you view performance indicators, such as revenue or production costs. OLAP exploration refers to the term slicing and dicing to describe the ease with which you can change context and view details.

Setting options for IBM Cognos Analysis for Microsoft Excel
You can change default settings to improve performance.
Set options for exploration sheets
In IBM Cognos Analysis for Microsoft Excel, each exploration that you create is assigned to a single worksheet. You can select a different server and package for each exploration. The packages that you use to create the reports are based on models that are created in the modeling tool, Framework Manager.
Using application settings to optimize your system performance
You can optimize system performance by using applications settings.
System optimization with large data sets
For list reports based on IBM Cognos Business Intelligence packages, IBM Cognos Analysis for Microsoft Excel can retrieve large data sets in CSV format. This enables IBM Cognos Analysis for Microsoft Excel to retrieve these data sets much faster than the standard fully formatted XML results. The CSV request format removes data formatting from the model.
Controlling data compression
Automatic data compression is one way that IBM Cognos Analysis for Microsoft Excel decreases processing time for large data transfers from the BI server. Data compression should be turned on under normal circumstances. Although compression is turned on by default, it can be turned off by setting the UseGzipCompression property to false in the CommManagerSettings.xml file, which, by default, is located in the Office Connection directory, such as C:\Documents and Settings\user name\Local Settings\Application Data\Cognos\Office Connection or C:\Users\user name\AppData\Local\Cognos\Office Connection.
Chunking
Chunking enables IBM Cognos Analysis for Microsoft Excel to query for large IBM Cognos BI data sets in chunks or subsets of rows. We recommend that chunking be turned on and that the row setting be set initially to 2000. If server out of memory errors still occur, set the chunk size smaller.
Setting limits on expanding items
The Expand member limit setting in the Options dialog box controls how much data to retrieve from the source tree. It is complementary to other limits, such as chunk size and GZip compression.
Working with dimensionally modeled relational and OLAP data
For IBM Cognos Analysis for Microsoft Excel, data items are organized hierarchically. Dimensional data sources include OLAP data sources and dimensionally modeled relational (DMR) data sources. The source tree provides a hierarchical-based view of the data.
Prompted metadata in packages used for Cognos Analysis for Microsoft Excel reports
Prompts help you find the information you need. It is a way for you to define the scope and limit large amounts of data. You can change the behavior of prompts in IBM Cognos Analysis for Microsoft Excel by modifying the definition of dimensions or query subjects in the model or other data source.
Understanding the differences between crosstab, list, and cell-based analysis
Using IBM Cognos Analysis for Microsoft Excel, you can work in three different ways: crosstab exploration, list reports, or cell-based analysis. When you create a crosstab, you use drop zones to add objects from the source tree to the rows, columns, measures, and context area of an IBM Cognos Office workbook. When you create a list, you use a single drop zone, columns, to create a list of items. You can add additional columns to populate the list with additional information. In a list, each column shows all the values for a data item in the database. When you use cell-based analysis, you drag and drop objects directly onto the cells of a workbook. You can move between the methods. Each of these methods has its own benefits and limitations.
Recommendations for when to use crosstab, list, or cell-based analysis
This section outlines some of the recommended practices for working with data using IBM Cognos Analysis for Microsoft Excel.

Parent topic: Cognos Analysis for Microsoft Excel