Technical References

1-62
Cisco Prime Service Catalog 10.0 Reporting Guide
OL-31037-01
Chapter 1 Advanced Reporting Data Mart
Data Mart Administration
The Service Catalog ETL processes for all packages use Cognos Data Manager Runtime components
to generate executables that are deployed into the reporting server as part of the application installation
procedure. These scripts use Cognos SQL to read the data from the OLTP source, allowing for greater
degree of portability of the catalog between heterogeneous database environments. Oracle or SQL Server
specific code is abstracted to views that are created in the OLTP source. The Data Manager scripts also
include User Defined Functions (UDF) to handle the transformation of specially formatted strings stored
within the Service Catalog database (which support the internationalization of the software). The UDF
also cleanses html tags from the data, if they have been included in dictionary captions or field labels. .
A custom program is required to extract service form field-level data from the Service Catalog
requisition record (such data is stored in a proprietary and compressed format, to optimize OLTP
performance) into a standard relational format. This program runs on the Service Catalog application
server.
Another custom program is required to create and maintain the Custom Reports Data Project. This script
uses the Cognos Framework Manager SDK to dynamically create the Custom Reports Project, based
on the services and dictionaries each customer site has chosen as Reportable. This dynamic structure and
content is added to standard data mart facts and dimensions to produce the data mart available in the
Custom Reports Project.
The generated executables should be collated in a job stream for batch execution. The exact structure of
the job stream will vary depending on which Reporting components are installed and configured:
prebuilt reports and KPIs; and the custom reports data mart.
We recommend starting a reporting installation with a once daily refresh of the data mart, typically
scheduled during slow times for transactional processing. However, The refresh of the data mart may be
scheduled concurrently with online usage of Service Catalog or multiple times per day. Performance for
online transactional users would be affected only insofar as the database server load is affected. Some
reporting users may report a blip in performance as indexes are rebuilt; however, the effects are generally
transient.
Custom Reports Package
All tables that support the data mart available for Advanced Reporting are incrementally refreshed in
every ETL cycle. Therefore, in principle, the data mart remains online during the ETL cycle. However,
because of the increased database activity in the database and because indexes on the tables are
temporarily unavailable, performance may be adversely affected.
Process Flow for the Custom Reports Package
The process flow used to produce the Custom Reports Package uses the components described above.
The most substantive difference is the use of additional Cognos components and custom Cisco-provided
code to handle the inclusion of dynamically defined form data (in the form of reportable services and
dictionaries) in the data mart.
Data is loaded into the custom reports package by means of both a Cognos (Data Manager) ETL script,
and a custom Java program, as shown in the diagram below.