In general, it is not best practice to allow users to run reports in the same environment in which a transactional system such as Service Catalog is operating. The resource requirements for running reports, ad-hoc queries, and other in-depth analyses are vastly different from resource requirements for running a transactional system that responds acceptably to online users. Therefore, data that forms the basis for reports and in-depth analyses is typically extracted from the transactional system and loaded into an environment dedicated and optimized for reporting.

The Service Catalog dedicated reporting environment consists of two packages, whose contents and usage are explained in detail in the rest of this chapter.

The Standard Reports Package supports the prebuilt reports and KPIs. A variety of output options provide information on task-, service-, and requisition-based measures and trends. In addition, reports on the structure of the Service Catalog data, including persons, organizations, service teams, and service groups, are available. All data used in the prebuilt reports is also available in the Custom Reports package. Over time this package is merged with the Custom Reports package.

The Custom Reports Data Package provides a dimensional model which allows ad-hoc reports on task, service and requisition-related data. In addition to the measures and attributes available in the Standard Reports package, data entered by users into the customized service forms configured at each site is available. This “form data reporting” (FDR) provides visibility into all attributes of all dictionaries and services which the service designers have designated as “Reportable”.

The database which supports the Standard Reports Package contains both detail tables and summary tables.

The detail tables provide a denormalized view of the database. Each table provides all the data that appears on a corresponding report. This means that running each report is optimized, so no report needs to access related data in lookup tables. It also means, however, that these tables cannot be combined in a report with other tables, since there are no relationships between the tables: each table is a complete, denormalized view of one type of fact about the OLTP system, to the specified level of detail. Further, no drill-up or drill-down, to different levels of detail, is possible.

The summary tables contain aggregated or summarized data. Presummarizing data eliminates processing delays that would otherwise occur if summary reports needed to aggregate data in real-time, in response to a report request. As for the detail tables, each summary table should be used only for its dedicated reports or KPIs—no summary tables can be joined to other tables to support ad-hoc reporting requirements.

The prebuilt reports that are included in the Standard Reports Package are created using the Report Studio tool and included in the Reporting module. The default report display format is set to HTML; but this delivery format, as well as other runtime parameters, can be modified when the reports are run. If the reporting solution includes Report Designer, users are able to view the definitions of the prebuilt reports and, if desired, modify the definition or create new reports to better meet corporate requirements.

The service Key Performance Indicators are generated using JFreechart API’s (JFreechart is not part of the Cognos product suite, but is open source software). The charts are generated on demand, by reading from the summary data tables created for each KPI.

The Service Catalog Data Mart is based on the Custom Reports Data Model package. This package gives users access to a data mart which includes service, task, requisition and effort-related information, such as the task performer or the duration of a task. The custom reports package differs from the standard package in important ways:

• The custom reports package does not include any prebuilt report or KPIs. It is meant strictly for ad-hoc reports and queries.
• The custom reports package allows access to form-based data, data derived from the dictionaries and attributes which are displayed and entered on user-configured service forms.
• The custom reports package is organized as a dimensional model, a flexible data model with the relationships among the different types of data, which encourages building ad-hoc reports and queries. This model is described in detail in Creating Custom Reports.

Tasks can be assigned to individuals or assigned to queues from which individuals can draw work. The data that is available in the Standard Reports Package and prebuilt reports (specifically the fields PERFORMERID, PERFORMERFIRSTNAME, PERFORMERLASTNAME, PERFORMEROUID and PERFORMEROUNAME in DM_SERVICETASKDETAIL and DM_AUTHORIZATIONTASKDETAIL) can refer to a person under one of three conditions:

• The task has been assigned directly to that individual, and no other person has ever worked on it.
• The task has been assigned to a queue, and the performer is the only person who has ever worked on it.
• The task has been assigned to a queue, and the performer is the last person who worked on it, but there may have been others. In this case, the performance of the person listed in the report is not their own, but is also affected by all the people who previously touched the task. So it is not a fair measure of the performance of the individual named.

The first condition can be distinguished from the data in the Standard Reports Package. However, the second and third cannot. This means that reports that attempt to measure the performance of individuals will only fairly represent their performance if service teams have very clear and consistent rules about not sharing responsibility for tasks. For this reason, a disclaimer appears on all prebuilt reports that measure the performance of individuals.

Prebuilt reports about the duration of tasks performed during service delivery use the PERFORMERACTUALDURATION measure. This measure takes the performer’s working calendar into account, so that weekends and other nonworking hours are not counted against their time working on the task. Conversely, the CUSTOMERDURATIONOFSERVICE measure makes the calculation taking the customer’s calendar into account, making it an inaccurate measure of the performer’s work.

There are two ways to assess whether the delivery team is performing their tasks well. Both are valid, but they have different meanings and uses:

• Is the task late, relative to the promise (Due Date) made to the customer? To make this assessment, reports include the TASKONTIMEFLAG, TASKDUEDATE and TASKCOMPLETEDDATE measures. This is good for determining whether a queue or service team is meeting its promises to customers in terms of absolute dates. It is not, however, a good measure of the performance of individuals. Assume that performing a service requires three tasks, done by Persons A, B and C. If you are measuring the performance of Person C, their tasks could be late because Person A or B was late. So this measure is used only to assess the customer service of whole teams or queues.
• Has the task been performed within the standard time we plan for this kind of task? To make this assessment, standard reports use the TASKSTDCOMPLIANCEFLAG, TASKPROJECTEDDURATION and PERFORMERACTUALDURATION measures. This allows you to compare the actual duration spent on performing the task with the standard planned duration for this kind of task. This measure is more appropriate for assessing the performance of individual team members, because focusing on the duration isolates the task performance from any upstream effects: it is entirely possible for a task to be completed late according to its due dates, but with a duration that is equal to or less than the standard duration. That indicates a person who is performing their own work well, but falling behind due to upstream effects.

A solid, customer-focused measurement regime requires BOTH metrics. If you base everything on task duration, you are in effect saying “Who cares what we promised to the customer, as long as we are meeting our internal standards?” On the other hand, focusing only on the dates provides an inaccurate picture of team members’ performance, which does not allow you to make the effective resourcing decisions you need to improve your performance for customers.