By Sanjeev Datta, Consultant for PerformanceG2
There are multiple ways of enhancing the performance of TM1 using tools and techniques. In my new, 3-part, weekly blog series, I will be discussing performance enhancements to the TM1 environment.
Performance in TM1 can be increased within 3 areas: the server, the database and spreadsheets. For part 1 of my series this week, I will be discussing the server.
Server Techniques
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 cube 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 browser 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 the newer view. SGV’s could be best described as “sub-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 valid only 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.
Example:
ViewConstruct(CubeName, ViewName);
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, titles). 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 consumption 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.
The 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” function may still be functional but the function does not generate a stargate view (SGV).
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 stored 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 the server.
Using Control Cubes: There are various control cubes in a TM1 environment. Their uses range from Security, Administration, Performance Monitoring, Object Attribute and Property.
}CubeProperties- falls under the property category of TM1 system cubes and can be viewed in the Server Explorer: View->Display Control Objects
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 time 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 Memory (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 opens 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 used when dealing with SGVs.
VMT: This cube property defines the time threshold in seconds beyond which the algorithm that stores TM1 SGV’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 to purge views in this manner until sufficient memory is made available.
Tuning RAM in a 32-bit Environment: 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 TM1 server runs
Check back next week for part 2 of Performance Enhancements to the TM1 Environment blog series where I will be discussing database techniques.
Contact the Blogger
