10.4 Using OLAP tools for customized reports

---

10.4 Using OLAP tools for customized reports

Online Analytical Processing (OLAP) is a technology used in creating decision support software that allows application users to quickly analyze information that has been summarized into multidimensional views and hierarchies. By summarizing predicted queries into multidimensional views prior to run time, OLAP tools can provide the benefit of increased performance over traditional database access tools. OLAP functionality is characterized by dynamic multi-dimensional analysis of consolidated enterprise data supporting end user analytical and navigational activities, including:

• Calculations and modeling applied across dimensions, through hierarchies, and/or across members

• Trend analysis over sequential time periods

• Slicing subsets for on-screen viewing

• Drill-down to deeper levels of consolidation

• Reach-through to underlying detail data

• Rotation to new dimensional comparisons in the viewing area

10.4.1 Crystal Reports overview

The OLAP tools used to demonstrate creation of OLAP reports in the following provides connectivity to virtually any enterprise data source, rich features for building business logic, comprehensive formatting and layout, and high-fidelity output for the Web or print.

In addition, Crystal Reports provides an extensible formula language for building complex reports requiring complex business logic. Built-in interactivity, personalization, parameters, drill-down, and indexing technologies enable custom content to be delivered to any user, based on security or on user-defined criteria. Finally, any report design can be outputted to a variety of formats, including PDF, Excel, Word, and our standard, published XML schema (XML can also be tailored for to match other standard schema).

The value of a standard tool extends beyond the widespread availability and general quality of the product. It includes all the value-add often associated with industry standards: large pools of skilled resources, large knowledge base, partnerships and integration with other enterprise software vendors, easy access to consulting and training, third-party books and documentation, and so on. Standard tools tend to travel with a whole caravan of support and services that help organizations succeed.

Crystal Reports is designed to produce accurate, high resolution output to both DHTML and PDF for Web viewing and printing. Output to RTF enables integration of structured content into Management Server Word documents. Built-in XML support and a standard Report XML schema deliver output for other devices and business processes and native Excel output enables further desktop analysis of report results.

For more information about Crystal Reports go to:

http://www.crystaldecisions.com/

10.4.2 Crystal Reports integration with TEDW

The following section provides information on how to customize and use Crystal Reports to generate OLAP reports based on the historical data in the TEDW database gathered by IBM Tivoli Monitoring for Transaction Performance.

Setting up integration

Follow the following steps to configure Crystal Reports:

1. Install Crystal Reports at your desktop.

2. Install DB2 client at your desktop if you have not installed it already.

3. Create ODBC data sources at your desktop to connect TWH_CDW database.

Crystal Reports and TMTP Version 5.2 sample reports

TWH_CDW database (ETL1) source data has been used here to create TMTP reports through Crystal Reports.

Steps to create a report

Follow the following steps to create a TMTP Version 5.2 report from Crystal Reports:

1. Select Programs → Crystal Reports → Using the Report Expert → OK → Choose an Expert → Standard.

2. Select Database → Open ODBC.

   Choose the data source that you have created to connect to TWH_CDW database and select the appropriate database ID and password.

3. Choose the COMP, MSMT, and MSMTTYP tables from the TWH_CDW database, as shown in Figure 10-31. Click Add and Next to create the links.

   
   Figure 10-31: Create links for report generation in Crystal Reports

4. Click Field and choose fields from the list shown in Figure 10-32.

   
   Figure 10-32: Choose fields for report generation

5. Click Group and choose the groups COMP.COMP_NM and MSMT.MSMT_STRT_DT.

6. Click Total and choose MSMT.MSMT_AVG_VA and summary type average.

7. Click Select and choose MSMTTYP.MSMTTYP_MN and COMP.COMP_NM to define filtering for your report, as demonstrated in Figure 10-33 on page 421.

   
   Figure 10-33: Crystal Reports filtering definition

• Provide a title for the report, for example, Telia Trade Stock Check Report, and click Finish.

Tip 

You can make a filter for the MSMTTYP_NM field and choose different values, such as Response time, Round trip time, Overall Time, and more to create different type of reports.

10.4.3 Sample Trade application reports

We have created the following sample reports using a very simple Crystal Reports report design:

• Average Simulated Response Time by date

• J2EE Response Time by date

• JDBC Response Time by date

• Average End-user Experience by date

You can download the Crystal Reports files containing the report specifications. Please refer to Appendix C, "Additional material" on page 473 for details on how to obtain a copy.

Average Simulated Response Time by date

The Average Simulated Response Time by date report in Figure 10-34 shows that the response times reported by the trade_2_stock-check_tivlab01 played back STI transaction are fairly consistent in the seven second range.

Figure 10-34: trade_2_stock-check_tivlab01 playback policy end-user experience

J2EE Response Time by date

The J2EE Response Time by date report in Figure 10-35 on page 423 shows that special attention needs to be devoted to tuning the J2EE environment to make the response times from the J2EE backed transactions monitored by the trade_j2ee_lis listening policy more consistent.

Figure 10-35: trade_j2ee_lis listening policy response time report

The JDBC Response Time by date shown in Figure 10-36 on page 424 shows that after a problematic start on 10/1, the tuning activities of the database has had the desired effect.

Figure 10-36: Response time JDBC process- Trade applications executeQuery()

JDBC Response Time by date

Average End-user Experience by date

The Average End-user Experience by date report shown in Figure 10-37 on page 425 reveals that there might be networking issues that are related to the end users running trade transactions on 10/6. This report does not detail the difference in the locations of the active user population between the two days, but it is obvious that troubleshooting and/or tuning is needed.

Figure 10-37: Response time for trade by trade_qos_lis listening policy

---