By Sanjeev Datta, Senior Consultant for PerformanceG2
Some of the new features in TM1 9.5.1 and 9.5.2 are:
1> Undo/Redo – This feature helps users to undo and redo changes made to data cells in their TM1 cubes. A useful feature when dealing with complex data spreading techniques.
2> Pick List – TM1 now gives the ability to create pick lists per cell in cube views. Pick list contain a set of values that are menu driven and users select the values from a drop down list. These lists can be populated manually or by TM1 Rules.
3> Sandboxes – A functionality that brings out the true “what-if” scenario feature in TM1.
4> TM1 Contributor – A new interface that eases the transition for Cognos Enterprise Planning users to get familiar with TM1
5> Data Reservation – An alternate method of dynamically controlling write-access to cube views instead of dynamically adjusting TM1 security to achieve the same
6> Parallel Integration – Increasing concurrent read-write operations on cube objects
Of the new features, let’s take a look at using Sandboxes in TM1. Sandbox gives the data contributor the ability to create data layers on top of the base data, which is primarily used for “what-if” scenario testing. A much awaited functionality that saves development efforts, tightens the finance workflow process, brings trust & confidence in the submitted data and reduces strain on the TM1 server. Frequent direct write-backs to the TM1 cubes slow the overall performance whereas; sandboxes can be used to reduce the risks of a slow performing environment, when used correctly.
Here is how it works:
In a situation where modifications to cells in a cube were viewed by the entire user community, the sandbox plays a role in enabling users to modify cell values, work on budget processes and modify data without any other users being able to see the data changes until and unless the data changes were approved and intended for the entire user community – a commit action would send the modifications back to the TM1 server replacing the base data.
In this scenario – a data contributor is looking to create a sandbox off the base version to make data changes that calculate an impact by increasing the Quantity Sold for a certain product by region. As the numbers check out correct, the contributor then commits the changes to the base view.
The user creates a Sandbox by clicking on the drop down on the top right corner of the cube view window. Sandboxes can also be created in excel using the same methodology. Depending on the version of Microsoft Excel (2003, 2007, 2010) the Sandbox icon position changes but generally should be on the top left side of the Excel interface.
Click on Create Sandbox. This option will create a layer on top of the cube view data. This new data layer (or Sandbox) will be viewable by the creator and can be used for “what-if” scenario testing. The selected rectangle for the months of January, February and March for Indirect Stores are to be modified. Note the black font.

Appropriately name the Sandbox in the pop-up window.

Any changes made to the cells appear as a default blue font.

Click on Calculate to view the new scenario. The blue font cells denote changes made to the data within the Sandbox, keeping the base numbers constant and unchanged to the rest of the user community.

By selecting the drop down – one can switch between the base view and Sandbox view without affecting the cells or changing data on the base view.

Switching back and forth to the base view from any Sandbox, does not change the cell values in the base view, as shown below:

In situations where an existing Sandbox underdoes an approval process, a new Sandbox can be created from an existing Sandbox. Another use would be to ensure any new changes do not affect data modifications from another “what-if” version.

In the screenshot below, notice a new Sandbox with modified cell values, different from the base and Sandbox 1 view.

Let’s go back to the base view. Switching between any Sandbox and Base value does not change the data.

Deleting a sandbox can be accomplished by selecting “Delete Sandbox” from the drop down icon.

Select the view to delete and click “OK”

Upon confirmation and approval of the data changes by clicking on the “Commit” icon – changes the base view. The commit icon should be used when data changes need to be promoted to the entire user community, as shown.

These features are also available if you are using TM1’s excel interface. As you start creating Sandboxes, it must be noted that Sandboxes are not a copy of the base view but an overlay of the base data. Sandbox enables the data contributors to write back to the TM1 database in a personal workspace environment and commit the changes, when promoting versions to the user community.
From an Administration point of view – usage of additional RAM memory and hard disk space increases as Sandbox functionality is implemented. You can control the amount of memory used per Sandbox in the TM1s.cfg (TM1 configuration file located in the installation folder).
The parameter responsible for defining the sandbox size per user is: MaximumUserSandboxSize
The maximum size per user on a Sandbox is 100MB on a 32 bit environment and a 500MB on a 64 bit environment. To allow for maximum amount of memory allocation, set the value equal to “n”.
Enabling and disabling this feature is controlled within the TM1 configuration file under the parameter:
DisableSandboxing. By default this value is set to F (for False).
TM1 Sandboxes are stored in a sub-folder within the data directory of the TM1 server. It is always good practice to backup this subfolder, along with the TM1 data directory.
Contact the Blogger