TUAM Reporting Performance

White paper


Abstract

Information about TUAM Reporting Performance

Content

TUAM Reporting Performance



White Paper

TUAM includes a robust web reporting component that performs extremely well when the system is tuned properly. Some TUAM customers have experienced variability in performance that is caused by a number of factors. Surprisingly, the size of your organization, your operating system platform, the DBMS, the Report Writer and even the volume of data does not necessarily determine the performance of the TUAM Web Reporting system.

The purpose of this document is to discuss the causes of reporting performance issues and various methods for resolving these issues.

The key factors determining performance include (in order of importance):

1) Customer expectations/requirements
2) Level of identifier summarization on Detail
3) Hardware
4) Level of date summarization
5) Level of account code on Summary
6) Individual report, stored procedure and index tuning
7) Database maintenance
8) Number of months of data retained in the database


1) Customer expectations/requirements


More than anything, the end user running reports must understand what is being requested and have realistic expectations of the performance.

Because TUAM is a web based reporting application, the perception is oftentimes that all reports should be created in time frames as short as 2 to 5 seconds. Unlike e-commerce or news sites found on the internet that typically have a very fast response time, TUAM is trying to process and aggregate very large volumes of data and then return it to the client’s browser. TUAM is basically a data warehouse, so expectations have to be set at a realistic level.

A careful examination of reporting requirements is sometimes required. A Detail report that runs in 15 minutes may be processing one month of Detail data that could contain 2 million rows. Through extensive additional indexing, performance tuning, aggregation and hardware upgrades, the report could complete in 10 minutes, not a significant improvement. Unfortunately, many users will still consider this still unacceptable. This is when using the Published Reports or Batch Email Distribution capability might be necessary. This is really the best avenue to creating a report in the sub-second range that many customers might expect.

Recommendations

1) Set expectations for performance. IBM can assist you in this process after all other avenues to increase performance have been exhausted.
2) After all tuning of a report and its corresponding stored procedure, if the performance is still unacceptable, TUAM’s Batch Email Report Distribution feature should be considered. This tool will allow the user to generate one, or many, reports during off hours and then distribute the report via email or distribute a link to the report via email. There are many advantages to this methodology. The main advantages are the off-loading of processing to off hours and the ability to have reports delivered directly to a user’s mailbox. A secondary advantage is the potential tightened security, since each end user would only receive their report and could be restricted from using the website entirely.
3) TUAM’s Published Reports feature enables users or administrators to create a report and then save it for future viewing by the report creator or other users in the organization. This powerful feature eliminates the time consumed by running the same report with the same parameters multiple times.

2) Level of identifier summarization on Detail


This factor, more than any other, can determine how well an individual report will perform. An identifier is something like server name, user name, jobname, process name, etc. All data in TUAM is aggregated to the level of these identifiers.

For example, take this raw, sample data:

Start Date Start Time End Date End Time Server Name User Name Process Name CPU Time Reads
1/1/08 12:01:01 1/1/08 12:01:04 Server1 User1 Process1 .25 12
1/1/08 12:01:02 1/1/08 12:01:05 Server1 User1 Process2 .5 22
1/1/08 12:01:03 1/1/08 12:01:06 Server1 User2 Process3 .5 10

In the above case, the identifiers are Start Date, Start Time, End Date, End Time, Server Name, User Name and Process Name. Aggregation can be set using any or all of these identifiers. Normally, users would not aggregate based on time, so that is almost always eliminated.

If aggregation is done on Date, Server Name, User Name and Process Name, there would be no aggregation of the records and three records would be stored in the database.

If aggregation is done on Date, Server Name and User Name, the first two records would be combined, creating two output records, one with .75 CPU seconds and one with .5 CPU seconds.

If aggregation is done on the Date and Server Name identifiers only, all three records would be combined into one record in the database with a usage of 1.25 CPU seconds.

You can see that if this is done on millions of records, the decision on the number of identifiers will dramatically change the number of records stored in the database.

Recommendations

Evaluate which identifiers are needed for reporting. Be aware, though, that if you decide to not retain an identifier and aggregate it out, it can not be added back later unless the raw data is retained. Fortunately, over time this decision can be refined. If, at a later date, one or more of the identifiers are deemed unnecessary, they can be removed from the database.

To remove identifiers and increase aggregation:
1) For all collectors, a key option for removing identifiers and increasing aggregation is use of Integrator and its Aggregator Stage. Please refer to the “Usage and Accounting Manager” documentation. Integrator has the Aggregator stage which aggregates a file based on the identifiers and resources specified. The identifiers are used for aggregation and the resources are summed. Any resources and identifiers not specified are dropped from the record.
2) Mainframe – the best option for aggregating mainframe data is to make changes to the number of identifiers being retained using the TUAM Extract process on the mainframe. Refer to the Mainframe documentation on how to set that aggregation.
3) Unix – make changes to the Collector scripts. Refer to the “Data Collectors for UNIX and Linux User's Guide” for more information.

3) Hardware


TUAM’s minimum requirements of a 1 Ghz processor with 1 GB of memory on a single server will work, but may not perform at an acceptable level. Processor speed and memory are the two key factors in determining the TUAM application performance. The DBMS will consume as much of the system memory as possible. Therefore, it can be very advantageous to have a separate machine for the database. This allows the main TUAM machine to allocate its resources to the reporting engine and IIS while the second machine can be dedicated to the database.

Recommendations

1) Add an additional machine that contains the DBMS
2) Add additional memory to the machine hosting the DBMS
3) Add a larger processor or an additional processor to the machine hosting the reporting engine

4) Level of Date Summarization

Like Identifier Summarization, Date Summarization is highly configurable in TUAM. But, unlike Identifier Summarization, it is much less important in most cases to reducing data size. Most installations prefer to report usage on a daily basis. The exceptions to this are the “snapshot” type collectors like Disk Usage (WinDisk, Mainframe DCollect, Mainframe Tape and Unix filesystem) and Database Size (DBSpace, Oracle Tablespace, etc.)

Recommendations

Assess how frequently “snapshot” collector data should be collected. If the collection is for a small amount of Windows folders or database tablespaces, it probably won’t have a lot of daily data. If the collection is for every dataset and tape in a mainframe shop on a daily basis, there may be millions of records per day. Sometimes this data can be averaged or collected on a weekly or monthly basis instead.

5) Level of account code on Summary

Most TUAM Reports are run at the Summary level of the data. Like other aggregation points in TUAM, this data is also automatically aggregated by BILL using the Account Code, Start Date, Shift and Rate Code. No “raw” identifiers from the CSR data are included automatically in the aggregated records. The account is derived from the combination of identifiers and the account code conversion table. The user has complete control over the account code. Normally the account code is built using business type values like company, business unit, application, agency, etc. But it is possible to also include identifiers like jobname, server, or even a date. Adding identifier values like jobname, server name or date will normally dramatically increase the number of Summary records and consequently slow down Summary report performance.

Recommendations

Closely evaluate the account code for extraneous fields. A longer, more detailed account code normally means more CIMSSummary table records. The only reason to include identifier information in the Summary account code is if the resource usage needs to be costed at that level. Be aware that any identifier that is in the CSR record will automatically be included in the CIMSDetail table in the database, so it will always be accessible for reporting on reports that use the CIMSDetail table.

6) Individual report, stored procedure and index tuning

If most reports appear to be performing acceptably, but one report is not, the problem could lie in a few areas. With TUAM Standard reports, they are normally optimized fully to perform acceptably. But sometimes, because of data distribution issues, a report may perform worse at one installation versus another.

Recommendations:

1) If it is a Summary based report (costs are included), be sure that other Summary reports appear to perform acceptably. If all Summary reports are performing poorly, look at other categories for the performance issues.
2) If it is a Detail based report (costs are not included), be sure that other Detail reports appear to perform acceptably. If all Detail reports are performing poorly, look at other categories for the performance issues.
3) If it is only this report that is performing poorly, test running with different date ranges. Does this make a perceptible difference in performance? If a smaller date range greatly increases the performance, it is likely too much data is being selected and the performance is what is to be expected.
4) Is this a custom report/stored procedure? If so, please contact IBM for assistance in optimizing the report/stored procedure. It’s possible that certain indexes are being used improperly or that the stored procedure could be tuned to work more optimally.
5) Is the data being selected a large part of the underlying Summary or Detail table? Mainframe CICS will oftentimes be a very large part of the Detail data. A Rate Group report running against CICS data versus a Rate Group report against Unix Process data may take much longer because there is a much larger set of data being returned to the reporting engine. Test the report with different parameters to better assess its performance bottleneck.
6) Is a main report performing acceptably, but the drill down is very slow? This is likely to be a problem with an index. Be sure to go to the Database Objects Manager in the TUAM Console and verify that all objects are built and exists in the database. If it does not, click the Exists checkbox and then the Create Selected Objects button.

7) Database maintenance

DBMS products like MS SQL Server, Oracle and DB2 can work quite well for a time without significant maintenance being performed on the database. But, over time, there will be a performance degradation if standard database maintenance procedures are not performed regularly.

Ideally the organization will have a DBA function that can perform these functions.

The following are tasks that need to be undertaken in the database:
1) Regular backup
2) Index rebuilding
3) Check database (DBCC CHECKDB)
4) Checks the allocation of data and index pages for each table (DBCC NEWALLOC)
5) Checks for consistency in and between system tables (DBCC CHECKCATALOG)
6) Rebuild of indexes
7) Update of statistics on each table
8) Database shrink (DBCC SHRINKDATABASE)

Recommendations

Ideally, the DBA will perform the functions above on a regular (at least weekly) basis. Many shops, though, do not have an available DBA function. For this reason, on SQL Server a script is provided that performs all of the above operations. This batch file is installed as ..\bin\SampleSQLMaint.bat. The same functions that are in this script are also available from MS SQL Server Enterprise Manager. Executing this process on a regular weekly basis will normally have your database running at an optimum performance level. There are also 3rd party SQL Server performance tuning products that can accomplish these same functions. Shops using DB2 or Oracle should consult with their DBA about automating these same tasks.

8) Number of months of data retained in the database

Removing older Summary and Detail data from the database, while useful, is usually not as important for improving report performance. Most TUAM reports are run with date selection criteria that will restrict the data reported using End Date. This selection is done early in the query process using indexes, so the performance gain from removing older CIMSSummary and CIMSDetail data is not as great as other measures detailed here.

The larger benefit from removing older data is a reduction in hard disk space consumption. This is still a critical requirement at many IT shops, so even without the significant performance gains, older data should be removed.

Recommendations

TUAM provides both a Windows Graphical method for removing older data and a batch method using either JobRunner or scripts.

Within the TUAM Console, the Load Tracking form (see the TUAM Documentation for more information) allows for permanent deletion of Summary/Detail data.

For batch removal, TUAM includes a DBPurge step that can be launched from JobRunner. This step removes data based on a date range or a date keyword.

Conclusion

Following the measures listed above will normally result in a TUAM implementation that performs at an acceptable level. Years of tuning of the TUAM application, coupled with user education, has yielded a product that can grow over time and still maintain an acceptable performance level.

These steps, while well documented, are sometimes not easily implemented without assistance. so please consider additional Services where necessary.


Original publication date

2007/11/16

Rate this page:

(0 users)Average rating

Add comments

Document information


More support for:

SmartCloud Cost Management

Software version:

4.3, 5.1, 6.1, 7.1

Operating system(s):

AIX, AIX 64bit, HP Itanium, HP-UX, Linux, Linux Red Hat - pSeries, Linux Red Hat - xSeries, Linux Red Hat - zSeries, Linux SUSE - pSeries, Linux SUSE - xSeries, Linux SUSE - zSeries, Linux TurboLinux - pSeries, Linux TurboLinux - xSeries, Linux TurboLinux - zSeries, Linux iSeries, Linux pSeries, Linux xSeries, Linux zSeries, Linux/System p, Linux/x86, MVS, MVS/ESA, OS/390, Solaris, Windows, Windows 2003 server, Windows 64bit, z/OS

Software edition:

All Editions

Reference #:

7011197

Modified date:

2009-11-21

Translate my page

Machine Translation

Content navigation