Best practices

Minimize the size of the workbook

The most significant impact on workbook performance is the size of the workbook. Since all data defined in the dataset is cached in the workbook, efficiently defining datasets is the most effective way to manage workbook performance. To maximize performance, you should only include information that is necessary to accomplish the purpose of the workbook whether it is used for data entry or reporting. Use this formula to estimate the impact the dataset definition will have on the size of the workbook: 
Number of Cells in Dataset = (Number of members in Dimension 1) * (number of members in Dimension 2) *…(number of members in Dimension n)

For example, if a dataset includes 4 dimensions and each dimension includes 3 members, multiply 3*3*3*3 for a total of 81 cells for that dataset.

We strongly recommend designing workbooks with a target size of 150,000 cells or fewer. As the workbook size increases to 500,000 or more cells, performance does not meet the expectations of most users.


Tips for minimizing workbook size:

  • Do not include more dimension members, such as units, products, or projects, just in case you might need them. If it becomes necessary to add additional members, redefine the dataset definition and refresh the workbook.
  • Utilize techniques to dynamically select members such as unit. The sample workbook, \ComshareWeb\CBRWorkbook\Templates\Samples\CBRSelect.xls, shows how to use the API to dynamically select units and refresh the dataset for reporting purposes.
  • Do not automatically select all periods for an entire year (12 months, 4 quarters, 2 Semi-Annual periods, and 1 Total Year) if fewer would suffice. For example, four quarters and the total year can be used for establishing targets through top down spreading and would reduce the dataset by approximately one-fourth.
  • Select only the schedule lines you need rather than an entire schedule.
  • Define multiple smaller and more focused datasets rather than one large dataset if the workbook is serving multiple purposes. Use the formula above to verify that the total of all datasets is smaller than a single large dataset.


Tips to improve performance:

  • Minimize the size of each dataset. It takes significantly longer to refresh values from a large dataset than from a small one. 
  • Minimize the size of all datasets combined. The total size of all datasets impacts the time to retrieve data from the server and to internally cache the data within the workbook when data is refreshed.
  • Minimize hierarchical dimensions in a database. Typical query times can be longer with some databases than others.
  • Employ standard Excel techniques to maximize performance. CPM workbooks are affected by the same performance issues related to size and calculation as generic Excel workbooks. Minimizing Excel macros, Excel formulas and CPM formulas can minimize Excel calculation times.
  • Use faster hardware and more memory on the client desktop

    The bulk of the retrieval time for most large datasets is spent on the client updating the internal cache and refreshing the Excel formulas. A faster CPU performs this work in less time. Increasing memory can also improve performance.

  • While designing plans, set Excel’s calculation mode to manual

    This minimizes recalculation of the cell formulas until you are done, at which point you can reset calculation mode to automatic.

  • Use Save As rather than Save

    As the workbook size increases, so does the risk of Excel encountering errors saving the workbook. If Excel errs during a save, you will lose your workbook. However, if Excel errs during a save as, you can safeguard the original workbook without the changes.