Several Cognos and
Service Catalog components are required to refresh the database contents of the
Custom Reports Package, which provides the business view of the data mart.The
data mart must be loaded with data from the transactional systems on a regular
basis in an Extract-Transform-Load (ETL) process. That is, data is extracted
from the transactional system; transformed into a format that is optimized for
reporting (rather than for online transactions); and then loaded into the data
The ETL process is
incremental. Only the data that has been changed or created since the last time
the data mart was refreshed are processed in the next ETL cycle. Users can
continue to access the data mart and run reports while the refresh process is
running. However, the response time for some reports may be adversely affected.
Running the ETL process has no or very limited impact on the response times in
the transactional database.
The refresh process
is typically scheduled to run automatically at regular intervals. We recommend
that the data mart be refreshed every 24 hours, ideally at a period of limited
The Service Catalog
ETL processes for all packages use Cognos
Runtime components to generate executables that
are deployed into the reporting server as part of the application installation
procedure. These scripts use
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.
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.
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
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.