By Sanjeev Datta, Senior Consultant for PerformanceG2
 
 
I always find interest in situations where, as a full-service CPM organization specializing in BI, we make an instant difference to our clients by significantly improving their profitability and performance, while at the same time, helping them cut costs and go green.

A few weeks ago, one of our clients approached us with a situation where invoice reports that went our to their customers who bought products via a Web site, were eating up their budgeted office supplies allocations more than they had expected. In a way, this was good news as sales figures were up, however the sales numbers would look more attractive if internal costs were down too.

One solution that we proposed to our client to meet this challenge was to create invoice reports in IBM Cognos Report Studio and distribute them via an automated nightly email process rather than a daily chore of printing out invoices and analyzing and calculating shipments, which can be time consuming and cumbersome tasks, especially for invoices going to global clientele.

PerformanceG2 took upon the challenge of creating this highly customized and extremely detailed invoice report in Report Studio version 8.4. With the use of singletons, which are data items that can be placed anywhere on the pixel perfect Web based reporting tool, and with the use of blocks, tables and various conditional statements to customize the report to include business rules for specialized groups of customers (e.g., domestic versus international, currency conversions, changing address lines, etc.), we delivered to our client The Invoice Report.

Report delivery was decided to go out in PDF format as an email attachment. For this, we used a feature in Report Studio known as “bursting”. The burst feature is used to run a report and divide the output based on a burst key, in this case, unique invoice numbers, which then generate multiple outputs inclusive of security features for each of these invoices. Therefore, this one developed report was to generate thousands of invoices per month and email them out to their respective customers daily, eliminating human errors and postal delivery delays all while significantly reducing printing costs, paper and my favorite: terminating the extremely time consuming task of folding these paper invoices and carefully placing them into envelopes. In addition, in the event a scheduled job halts, or is not completed in the specified time, an alert is sent out to the concerned owners who can take appropriate actions to rectify any inconsistencies before they reach the far end.

In the end, the challenge was met, the project was a success, and the project is now in production to our client’s and their customers’ satisfaction. With the use of Cognos Event Studio, and some creative green thinking, we have handed ownership of this project to our client.

 
 
 
Contact the Blogger

 
By Sanjeev Datta, Senior Consultant for PerformanceG2
 
 
Business Intelligence (BI) is a technology that helps support business decision making. BI helps decision makers in making better and faster decisions – better as data layouts are business specific, customized and highly organized; and faster with the efficiencies that come in building this technology.

Business reporting is the backbone of BI where not only C-level executives, but also managers and analysts, keep track of and can share their goals and key performance indicators (KPI) in daily operations; bringing better insight into their data and the outcome of decisions giving a direction to their organization all leading to efficiencies in business processes.

IBM Cognos has a set of business reporting tools each of which come with features aimed at specific business users. Query studio is one such Web based ad-hoc reporting tool. Query studio gives users the ability to create basic calculations with simple formatting options that run in multiple output formats and can be saved as ad-hoc queries for future references.

Analysis studio is another Web based or “zero foot print” tool that requires no installations other than a simple URL link, and it works with On Line Analytical Processing (OLAP) and Dimensionally Modeled Relational (DMR) data sources. This tool is used for slicing and dicing, drilling up (to summary level) and down into details so users can swiftly and seamlessly arrive at the analysis they are looking for.

Report studio is considered the mother of all Web studios from Cognos BI arsenal. Report studio has the features of all the other reporting tools and then some. It is considered as a “pixel-perfect” tool which lets professional authors write simple to highly complex reports and distribute them to hundreds of users via various options. Apart from OLAP data sources and its MDX functions, report studio can also use relational data sources in creating reports and dashboards. The drag and drop feature helps in developing dashboards with great ease by dropping lists, pivot tables or cross tabs and various charts that usually enrich the real estate on the page and do away with number crunching on clustered report pages.

IBM Cognos report studio offers the ability to drill up and drill down from a summary to a detailed view of data and also allows drill throughs, when a user can jump from one report to another report related by common columns. Delivering reports in report studio can be done by attaching report outputs in emails or simply storing reports in a repository for shared or secured access. Outputs in PDF, HTML, Excel (various versions), CSV and XML can be accessed by users connected to the Web portal Cognos Connection that launches the various reporting studios. Another option is the ability to “burst” a report, which is a popular choice among many customers as it saves tremendous amounts of development time – as one report is run and its output is divided and delivered to the respective recipients – making certain, the appropriate security settings are incorporated to the lowest row level. An example of a ‘bursted’ report would be developing one sales report that gets pushed out to various sales reps, who would view only the accounts they have access to, thereby delivering unlimited report outputs based on distinct sales rep ID or the burst key.

Other reports that have been created using report studio are employee and product performance reports. For the financial sector there are income statements, balance sheets and actual versus budget reports with complex calculations that span over time periods. For the retail sector, there are track of stock reports. And for the defense or property management industry, the use of highly visual charts that make use of geographical BI by providing maps based on political boundaries or customized regions, have been created as well.

These powerful yet user-friendly business reporting tools create industry standard as well as customized solutions, and by providing the right information to the right people make investigating sweet spots and troubled areas of an organization possible and making BI a top priority for CIO’s across the globe.

 
 
 
Contact the Blogger

 
By Sanjeev Datta, Senior Consultant for PerformanceG2
 
Are you deciding to set up TM1? Below is a list of considerations when installing a TM1 environment – an initial insight into the set up and architecture to fill your technical needs. These stats can help determine cube size, server topology and other settings for an upgrade or new TM1 install.
 
User Definition:
Power Users (PU)- These are TM1 users who would load data, update models and objects and create complex reports and views. Compared to the total number of users and power users, 1:1 can be considered as a close approximation (concurrent user) ration.

Read/Write Users (RWU)- These are TM1 users who would update data in a model on a regular basis. They would create and view complex reports and views. Compared to the total number of users and RW users, 3:1 can be considered as a close approximation (concurrent user) ratio.

Read Only Users (ROU)- These are TM1 users who do not input data. Instead, they only read data or use pre-defined reports in Microsoft Excel or TM1 Web and require fast data access. Compared to the total number of users and RO users, 10:1 can be considered as a close approximation (concurrent user) ratio.
 
Estimating RAM:

Total concurrent users = 16
RAM required: 16 x 20 (MB per user) = 320MB
 
Estimating Data:
In TM1, each data item loaded occupies on average 14 bytes of RAM. Each calculated item will proportionally increase the data, and adding more CPU’s will benefit readers more than TM1 users or writers.
Rule of Thumb: Per CPU, 25 power users and 100 RO users

Data- 1,000,000 rows with 12 data items in each row = 160MB
Model with 5 years of data = 801MB

Calculations- Calculations forecast is 1 year of extra data = 160MB

Users- 10 users = 10 x 12MB = 120MB

Total Model Size- 801MB + 10MB + 120MB = 1.1GB
 
Report Server:
In a TM1 environment, use a Report Server for the following:

• If the RW users require fast access to static reports while data is being uploaded
• If writers lock server to the detriment of the readers
• If the RO user community outweighs the RW user community by a factor of 5 and there is constant RW activity on the main server
• If the RO users request reports simultaneously and if reporting performance is important, then use Reporting Server/Multiple Processes

 
TM1 Web Server:
In a TM1 environment, if the majority are Web users, then consider a separate TM1 Web server. TM1 Web servers should always run on separate physical servers if possible.
Rule of Thumb: Up to 250 concurrent users per CPU

 
 
 
Contact the Blogger

 
By Sanjeev Datta, Consultant for PerformanceG2
 
There are multiple ways of enhancing the performance of TM1 using tools and techniques. This week for my 3-part blog series, I will be discussing 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.

 
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.

 
 
Contact the Blogger

 
By Sanjeev Datta, Consultant for PerformanceG2
 
There are multiple ways of enhancing the performance of TM1 using tools and techniques. This week for my 3-part blog series, I will be discussing Database Techniques.

 
Database Techniques

TM1’s in-memory or “on-demand” architecture is made possible with the way it handles memory management. TM1 uses algorithms which efficiently compress data to allow large data sets to fit in relatively small amounts of RAM which result in reduced storage and improved performance. This sparse memory management scheme allows the server to hold very large cubes in much less space compared to the same amount of data stored in a relational database.

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.
 
 
Contact the Blogger

 
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

 
By Sanjeev Datta, Consultant for PerformanceG2
 
Last week, as part of my 4-part blog series,TM1 Top Utility. Today is the final chapter of my 4-part TM1 series and I will be reviewing performance counters.

Check back in next week where I will be starting another blog series on performance enhancements to TM1 environment.
 
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.
 

 
For more information about Cognos TM1 and Cognos TM1 training, email us at info@performanceg2.com
 
 
Contact the Blogger

 
By Sanjeev Datta, Consultant for PerformanceG2
 
Last week, as part of my 4-part blog series, I discussed monitoring server performance using control cubes. Today, I will be discussing TM1 Top utility.
 
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.

For more information about TM1 Top Utility, check out IBM’s TM1 information on-demand section, or email us at info@performanceg2.com
 
 
Contact the Blogger

 
By Sanjeev Datta, Consultant for PerformanceG2

Last week I started my 4-part blog series by discussing Message Logging in TM1. This week, I will review monitoring server performance using control cubes.
 
Monitoring Server Performance using Control Cubes
TM1 is built in performance monitoring tools exist as system cubes called 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:

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

 
}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.

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

 
Check back in next week for part 3 of Performance Monitoring with TM1, where I will be briefly go over TM1 Top Utility.

Contact the Blogger

 
By Sanjeev Datta, Consultant for PerformanceG2

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.
 
Check back next week for part 2 of Performance Monitoring with TM1 blog series where I will be discussing Monitoring Server Performance Using Control Cubes.
 
Contact the Blogger