Monitoring Server Performance using Control Cubes



Performance Tuning and Logging in TM1

Monitoring Server Performance using Control Cubes

TM1 has built in performance monitoring tools called as system cubes/Control Cubes. These four cubes hold performance statistics for clients, cubes and servers. Once these cubes are selected to capture statistical information- they track changes on a minute-by-minute basis.

 

}StatsByClient:

This cube tracks message count, average message size, total elapsed time and other measures:

[pic]

 

}StatsByCube:

This cube tracks the memory used for each cube on the server:

[pic]

 

}StatsForServer:

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 current consumption 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.

[pic]

 

}StatsByCubeByClient:

This cube tracks the number of cell updates, cell retrievals, view calculations and view retrievals for each client and cube on the server.

[pic]

TM1 Top

TM1 Top is a utility that allows dynamic monitoring of threads running in an instance of the TM1 server. This stand-alone utility runs within a console (command) window 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 locks. 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 manager on a Windows System. There are 2 ways of monitoring the performance of the TM1 server: the Task Manager Process Tab and the PerfMon — another built-in TM1 Performance monitoring tool. This console tool provides a display of TM1 performance counters.

 

[pic][pic]

[pic]

 

Performance Enhancement to TM1 Environment, The Server

 

There are multiple ways of enhancing the performance of TM1 using tools and techniques. TM1 environment Performance in TM1 can be increased within 3 areas: the server, the database and spreadsheets.

 

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.

[pic]

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.

[pic]

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.

[pic][pic]

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

[pic]

 

Below are the Database Techniques you could use to improve performance:

 

1. ReadersBypassWriters: 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 read requests are executed. Any writing requests to the database only begin when no incoming read requests are detected.

 

2. Locking: This prevents other users from reading or writing to the server while a TI process is executing. This is done within Turbo Integrator (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-intensive 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 creator 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-

Spreadsheet Techniques

Following are techniques used on spreadsheets to enhance performance on TM1 servers:

 

DBR: 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])

 

DBRW: 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 Area Networks. When implemented, DBRW function forces TM1 to use “bundles” rather than individual read/writes from/to the database.

Syntax: DBRW(cube, e1, e2,[...en])

 

ELCOMP: 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 creating cubes.

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.

As 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 Optimizer as necessary. Re-ordering does not break the “DB” references.

As a best practice, use the time and measures dimension at the end always.

 

Cube Optimizer: The Cube Optimizer feature in TM1 Server Explorer lets you optimize the created cubes to consume less 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-fly”.

The internal re-ordering of cube dimensions in TM1 is valuable for 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 optimization.

[pic]

 

Other TM1 Techniques

Calculations are defined 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.

Reduce over-feeding by using Conditional Feeders when using TM1 Rules.

Performance Monitoring with TM1, Message Logging

 

There are multiple ways of monitoring the performance of TM1 using built-in tools. In my 4-part, weekly 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 series this week, I will be discussing Message Logging:

 

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:

Admin Server Log: 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 established 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.

Transaction Logs: 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.

Audi Log: The Audit Log records any changes to TM1 objects. Login activities and modifications to dimensions, views and subsets, are all captured in this log file.

 

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download