Performance Tuning and Logging in TM1
Monitoring Server Performance using Control Cubes
built in per
formance monitoring tools called as system cubes/
ontrol Cubes. These four cubes
hold performance statistics for clients, cubes and servers. Once these cubes are selected to capture
they track changes on a minute
This cube tracks message count, a
verage message size, total elapsed time and other measures:
This cube tracks the memory used for each cube on the server:
This cube tracks the connected clients, active threads and memory used for the server.
TM1 allocates memory as it needs it from
the operating system (OS). However, when it frees memory, it
does not return it to the OS but puts it in a garbage list, which it then re
uses as needed. Accordingly, the
operating system performance monitor will not give you an accurate measure of the cu
of memory of the TM1 Server. From below, the sum of the “memory used” and “memory in garbage”
corresponds to the memory consumption reported by the OS performance monitor.
This cube tracks the number of cell updates, cell retrievals, view calculations and view retrievals for each
client and cube on the server.
TM1 Top is a utility that allows dynamic monitoring of threads running in an instance of the TM1 server.
alone utility runs within a console (command) win
dow on a windows machine. It is designed
to make a minimal demands on the TM1 server and the supporting network and system. The TM1 Top
utility does not use any cube or dimension resource and it does not interact or use the data or meta data
hence no lock
s. This utility reports on the state of the server for monitoring TM1 utilization and acts as a
great built in performance monitoring tools
Windows Performance Monitoring and “PerfMon”
The Windows Performance Monitoring tools is nothing but the task man
ager on a Windows System.
There are 2 ways of monitoring the performance of the TM1 server: the Task Manager Process Tab and
in TM1 Performance monitoring tool. This console tool provides a display
of TM1 performance counters.
Performance Enhancement to TM1 Environment, The Server
There are multiple ways of enhancing the performance of TM1 using tools and techni
Performance in TM1 can be increased within 3 areas: the server, the
The various techniques available for performance tuning TM1 under the server category are: Stargate
Views (SGV), using
the control cube “}CubeProperties” and tuning RAM.
Stargate Views (SGV)
: Stargate Views (SGV) are calculated and stored subsections of TM1 cubes that are
created when you browse a cube with the cube viewer or create a slice using the slice button in the
viewer. SGV’s contain only the data for a defined section of a cube and do not contain the formatting
and/or browser setting information. This allows the views to be smaller than an entire cube, hence,
requires much less server storage memory allowing
it to be queried and manipulated much more
efficiently because the dimensions occupying the title position of a view are limited to the single elements.
SGV’s not only allow quicker access to cube data, they also persist in memory only as long as the brow
view from which it originates remains unchanged. The TM1 engine creates and purges these views
dynamically when necessary. In an instance when there are multiple views available, the oldest view is
dropped from the temporary memory to allow room for th
e newer view. SGV’s could be best described
cubes” created in memory, based on title, row and column dimensions of a specific user view.
SGV’s can also be setup by Turbo Integrator (TI) functions like the “View Construct”. This function is
ly in TI processes. It constructs, pre
calculates and stores the result in ofa SGV memory of a TM1
server. This function is useful for pre
calculating and storing large views so they can be quickly accessed
after a data load or update.
Where “ViewName” is a publicly share view
SGV’s with Excel Slices
: When working with slices, a “view” function is created in Excel worksheets. The
syntax for these “views” contains cube name and its elements (rows, dimensions, ti
tles). The SGV created
in this environment helps in performance tuning
but developers must be made aware that deleting or
incorrectly modifying the “view” function may have profoundly negative effects on the worksheet
recalculation times, server memory c
onsumption and server stability if the cube you are referencing by the
DB formula, is very large.
The above changes are manually added to the tm1s.cfg (TM1 configuration file). These changes can
significantly improve performance as they are related to the
use of stargate views.
he above screen shot represents the 2 manual changes made to the “tm1s.cgf” configuration file.
1. DisableWorkSheetViews set to TRUE
This addition disables any “view” functions contained in slice worksheets. Although, any worksheet
containing a “view” fun
ction may still be functional but the function does not generate a stargate view
2. UseStargateForRules set to TRUE
When set to TRUE, this manual addition to the configuration file allows TM1 to retrieve, by default, a
calculated value from a SGV s
tored in memory. This can significantly improve performance since it is
more efficient to retrieve a calculated value from memory than to request and retrieve a calculation from
Using Control Cubes
: There are various control cubes in a TM1 en
vironment. Their uses range from
Security, Administration, Performance Monitoring, Object Attribute and Property.
falls under the property category of TM1 system cubes and can be viewed in the Server
>Display Control Object
This control cube stores property values of all cubes and can be used to enhance the performance of
existing cubes in an environment. Some of the definitions from this control cube determine how the cube
is loaded on a TM1 server, what the measure and ti
me dimensions are and if cube logging is enabled. In
case you migrate cubes from one server to the other using replications
some of the properties define the
source cube, replication status and sync information between views and rules.
View Maximum Memo
ry (VMM) and View Minimum Time (VMT) are 2 properties which can help you
performance tune your TM1 cubes.
VMM: This is the amount of memory the TM1 Server will allocate for all Stargate views for a given cube.
The values are entered in kilobytes and the default value (if not specified otherwise)
is 64KB. The more
memory made available for SGV’s, the better the performance will be. However, one must keep in mind
sufficient memory exists for the TM1 server to load all cubes.
Example: Consider 100MB allocated to a cube in memory. While the user open
s views during an active
session, the first Stargate view requires 60MB, while the 2nd SGV requires another 30 MB. This leaves
10MB or 10% remaining for any other views. Now, a thirdy view (SGV) requiring 70MB needs to be
utilized. How does TM1 manage this
? An intelligent memory management algorithm frees up another
60% of memory that was originally being used by the 1st SGV, hence allocating a total 70MB for the 3rd
SGV. This is an example of how VMM is used to establish the amount of memory that will be u
dealing with SGVs.
VMT: This cube property defines the time threshold in seconds beyond which the algorithm that stores
’s is triggered. If the time requires calculating a cube view surpasses the specified threshold,
TM1 attempts to store the SGV. If there is not enough memory available to store the SGV, TM1 purges
the oldest SGV that is not currently in use and continues t
o purge views in this manner until sufficient
memory is made available.
Tuning RAM in a 32
: The following describes how to tune RAM so that 3GB is available to
the TM1 server. NOTE: This procedure would require you to modify the “boot.ini
” Windows system file
and can have adverse affects if not performed correctly!
1. Verify that your operating system (OS) is one of the following that supports RAM tuning:
a. Microsoft Windows 2000 Advanced Server
b. Microsoft Windows 2000 Datacenter Server
c. Microsoft Windows Server 2003, Enterprise Edition
d. Microsoft Windows Server 2003, Datacenter Edition
2. Open C:/boot.ini in a text editor
3. Add the “/3GB” switch to the end of the last line of boot.ini
4. Reboot the physical server in which the TM
1 server runs
Below are the Database Technique
s you could use to improve performance:
: This is a manual addition that needs to be made to the configuration file
(TM1s.cgf). When this value is st to TRUE, it causes a write to the TM1 database to wait until all prior
sts are executed. Any writing requests to the database only begin when no incoming read
requests are detected.
: This prevents other users from reading or writing to the server while a TI process is executing.
This is done within Turbo Integrat
or (TI) so as not to corrupt memory or cause a server crash when
updating server objects at the same time that users are trying to read from the server.
3. Batch Updates
: A best practice, batch updates allow you to improve the performance of input
applications by holding changes to cube data and saving those changes to cubes in a single batch. This
would hold all edits to cubes residing on a selected server in a temporary storage structure until the batch
update is saved. After the batch is sent
, all edits are committed to the target server and the temp storage
structure is destroyed. This process minimizes the impact on users who need to access the server when
the TI process is running. Typically performance by a TM1 Administrator or a process c
reator and can be
scheduled to run as a chore in TM1. All edits that are held in batch updates are not written to the servers
log file until you save the batch updates. NOTE: If you lose connection to your TM1 server, or if the
server shuts down abruptly,
all changes/edits would be lost.
Check back next week for part 3 of Performance Enhancements to the TM1 Environment blog series
where I will be discussing spreadsheets.
Performance Enhancement to TM1 Environment, Spreadsheet Techniques
Following are techniques used on spreadsheets to enhance performance on TM1 servers:
: This is a worksheet function valid only in worksheets. It retrieves a value from a specified TM1
cube and can be used to write values to the database when
all the arguments are element values.
Syntax: DBR(cube, e1, e2,[...en])
: This is a worksheet function valid only in worksheets. This function is similar to the DBR
function but is used to reduce network traffic and hence is extremely useful on Wide
When implemented, DBRW function forces TM1 to use “bundles” rather than individual read/writes
from/to the database.
Syntax: DBRW(cube, e1, e2,[...en])
: This is a worksheet function valid only in worksheets. It returns the name of a
child of a
consolidated element in a specified dimension. It is similar to the DBR function in that it retrieves a value
from a specified TM1 cube but it also results in a round trip between the server and Excel.
Syntax: ELCOMP(dimension, element, index)
Dimension Ordering and Cube Optimizer
: Dimension ordering can have significant impact on memory
consumption as well as recalculation times in a TM1 server environment and its best to sort dimensions in
2 categories: Sparse & Dense Dimensions before creat
A sparse dimension would be Products & Regions, for example, where not every products is sold in every
region. On the other hand, a Dense Dimension would be a month/time where you will always have a
Budget amount in every month of the year.
a general recommendation, the ordering of dimensions should be: smallest sparse to largest sparse
followed by smallest dense to largest dense.
In practice, design the cubes with dimensions in “natural business” order and then use the Dimension
as necessary. Re
ordering does not break the “DB” references.
As a best practice, use the time and measures dimension at the end always.
: The Cube Optimizer feature in TM1 Server Explorer lets you optimize the created cubes
to consume les
s memory and improve performance. Over time as Business needs and Dimensional
priority change, cube optimization is useful especially as re
calculation in TM1 is RAM
based i.e. “on
The internal re
ordering of cube dimensions in TM1 is valuable f
or tuning sparse cubes, large dimensions
or simply large cubes. As a note, dimensional ordering and cube optimization require twice the amount of
the cube size. For example, if a cube size is 50MB, 100MB must be available in memory to perform cube
Other TM1 Techniques
Calculations are def
ined implicitly by dimension hierarchies and are an order of magnitude faster than
Rules and should be used whenever possible.
Rules on the other hand, allow defining any cell as an arbitrary calculation of any other cells in any cube.
For best practice,
place calculations you expect to change often in rules, even though the “math” could be
done faster with consolidations.
feeding by using Conditional Feeders when using TM1 Rules.
Performance Monitoring with TM1, Message Logging
multiple ways of monitoring the performance of TM1 using built
in tools. In my 4
blog series, I will be discussing some of these tools including: Message Logging, Control Cubes, TM1 Top
Utility and Performance Counters.
For part 1 of my serie
s this week, I will be discussing Message Logging:
There are 4 internal logging files which act as “flight recorders” for the TM1 Server and can help in
tracking changes made to the server. These 4 internal logging files are:
: The TM1 environment is a client
server environment. As the TM1 server starts up, they
register themselves with the TM1 Admin Server. The TM1 clients contact the Admin Server to find the
location of all available TM1 Servers. SSL security is establish
ed between TM1 Admin Server, TM1 Client
Server and TM1 Server. The log data can then be used to track the various Server startup times and the
connection with the Admin Server.
: These logs acts as a “User Audit Log” where all changes made
to the data transactions are
recorded along with a time stamp. Developers can use this log file to identify any changes made to the
data by Users, Client Machines, Cube Name, Element Type and cell number. The level of detail allows to
store the before and
after numbers as well.
Server Message Log
: This log file maintains details performed by the server such as executed processes,
chores, loaded cubes and dimensions, and synchronized replications.
: The Audit Log records any changes to TM1 objects. L
ogin activities and modifications to
dimensions, views and subsets, are all captured in this log file.