This chapter contains the following
Reporting in Service Design
Since each customer
site will obviously have a different set of dictionaries and services, there
can be no hard-and-fast rules as to how to decide which of these to make
reportable. However, the following guidelines may help you decide which objects
to include in the data mart. For more information, see
Configuring the DataMart.
Dictionaries with dates or numbers in them might be good candidates for inclusion in the data mart.
Dictionaries consisting wholly or partly or very large text fields, designed to hold descriptions or explanations, are not good candidates for inclusion; such fields would be truncated at the maximum character size specified when the data mart is built.
Dictionaries deemed critical to the business need to be included. This would typically include the Customer and Initiator dictionaries since they include information that is not in the corresponding person-based dimensions or is more current than the information that was in Organization Designer.
Including a service essentially provides you a shortcut to reporting on all the dictionaries in a service without having to know identify those dictionaries individually (that is, as separate dimensions/query subjects.) This is especially useful for users particularly interested in one service only, or who are infrequent users of the query tools and need a quick-and-dirty way to report on items of interest.
Making services reportable has the following drawbacks, which may mitigate against having dimensions derived from services in your data mart:
If the service contains two fields with the same name (from different dictionaries) they appear as Dictionary1_FieldName and Dictionary2_FieldName in the query subject based on the service. Fields which are not ambiguously named simply have the field name.
Some services contain so many dictionaries, with so many fields, that the service-dimension configuration must be greatly adjusted from the default number of fields allowed. Typically, this means increasing the number of character, date, or numeric fields to be the highest number required for any one service to be made reportable. SQLServer documentation warns that this may adversely affect performance, because it would entail “row chaining”.
Making Service Items Reportable
Any dictionary or service may be designated as reportable.
dictionary appears as a query subject in the DictionaryData folder of the
Service Catalog data mart. Dictionary fields are listed in the dictionary
dimension in the order in which they are specified in the dictionary. Any
fields exceeding the limits on the number of each type of field (character,
date, number) are excluded from the data mart. The names of the data mart query
items will match the field names specified when the dictionary is defined.
reportable provides the maximum flexibility for reporting on dictionaries used
in multiple services. You can freely write reports containing data from the
dictionaries, the desired fact table and any relevant dimensions.
guidelines when naming reportable dictionaries and their component fields:
- Standardize dictionary names
and field names, since these names are now exposed to more people.
- Develop and adhere to
site-wide standards for capitalization and style.
- Field labels for
dictionaries used in multiple forms need to be consistent. In fact, the field
label should closely resemble the field name. The field name is exposed in the
dictionary dimension. The field label would be exposed in the service
dimension, if used.
guidelines when constructing dictionaries to be reportable:
- If a dictionary is reportable, all
component fields appear in the data mart, including fields hidden in various
services. If a hidden field needs to be kept hidden from users in all contexts,
place it in a separate dictionary that is not reportable.
- Be sure to configure the reserved
dictionaries (Customer and Initiator) to contain only fields that are used in
your services. Any fields included in these dictionaries will appear in the
- A field name
should match its contents. For example, if a field is called "date", it should
be a Date data type, with only valid dates or date-times allowed as the field
values. Failure to do so could result in a confusing user interface. For
example, the presence of any nonvalid date value in the field means that the
Cognos reporting and query tools prevent users from applying date calculations.
- Similarly, fields
which contain numbers should be stored as a Number data type, and an
appropriate size and decimal precision applied. This ensures that numeric
calculations can be applied and may eliminate the need to format the field in
the reporting or query tool to ensure a user friendly and consistent display.
Once a dictionary has
been made reportable, it will initially appear as noneditable in Service
Designer. You can change the Reportable value to No in order to edit the
dictionary definition. This behavior is a double-check, to ensure you are aware
of the consequences of changing the definition of a dictionary that has been
- Added fields are
added to the dictionary data in the data mart. Any service requests submitted
before the field was added will simply have no values for the field.
- Deleted fields
will remain in the corresponding query subject in the data mart. Field values
are blank for any requests submitted after the date the field was deleted.
- You can freely
change the length assigned to any field.
- You cannot change
the data type assigned to any field. This will cause the ETL process that loads
the data mart to fail. If this must absolutely be done, you will need to
recreate the form data component of the data mart and reload all data.
Procedures for doing so are available from the Cisco Technical Assistance
Making a service
reportable means that the service appears as a query subject in the ServiceData
folder. The service record consists of all reportable dictionaries in the
service. Any fields exceeding the limits on the number of each type of field
(character, date, number) are excluded from the data mart. The names of the
data mart query objects are derived from the field labels as specified when the
form for the service is designed. When a reportable service includes two fields
with identical labels, the ETL process adds the dictionary fields to the data
mart with query item names of Dictionary_Label_1, Dictionary_Label_2, and so
A service bundle can
be designated as reportable. Making a service bundle reportable means that
service bundle appears as a query subject in the ServiceData folder. The
service bundle record consists of all dictionaries which were associated to the
child services as well as the service bundle itself.
If the child services
which were attached to the service bundle are also reportable, the child
services record consists of the dictionaries which were associated with the
respective child services.
In addition to the
guidelines for reportable dictionaries, follow these guidelines when
constructing services to be reportable:
- Do not assign two fields in different
dictionaries in the same service the same label. Since labels are used to name
the corresponding query items in the data mart, the labels for fields used in
the same service should be unique.
Deployed Dictionaries and Services
The service design
methodology must also consider how best to accommodate changes to previously
deployed dictionaries and services. These considerations need to include both
the effects on the Service Catalog transactional system and effects on the data
- A dictionary is reportable and has
been used in a service that has been ordered, and
- The data mart (and its metadata) has
already been built and populated for those requests.
What are the effects
of changes to the dictionary on the configuration of the data mart? These
effects manifest in two ways:
- Changes to the
reporting metadata, that is, the dictionary and service-based dimensions, and
their component fields, available as query subjects and query objects in Ad-Hoc
Reports and Report Designer.
- Changes to the
data loaded into the data mart via the Extract-Transform-Load (ETL) process.
Adding a Field to a
The next time the
data mart load process is run, the job which builds the reporting model (the
metadata) adds the field as a query item to the query subject corresponding to
the dictionary. The ETL process will now include that field. The field value is
populated for requisitions submitted after the change to the dictionary was
made, and blank for all requisitions submitted before the date the change was
The only caveat with
this scenario is that the field cannot be added if the dictionary now exceeds
the maximum number of fields allotted for each data type in a dictionary or
service-based dimension. (The number of fields is specified when Advanced
Reporting is installed. System administrators may customize default values of
60 character fields, 10 date fields, and 10 numeric fields for each dictionary,
and 80 character fields, 20 date fields, and 20 numeric fields for each
service. Be sure to check with the system administrator if in doubt.)
In both cases, the
current software would add the field as the last query item in the query
subject representing the dictionary or service. This will probably not
correspond to where the field appears on a service form.
Adding a New
becomes a new query subject, and all its fields, query items, as expected. Data
is loaded into the dictionary as of the date it was made reportable.
A possible difficulty
comes in reporting on requests for an existing service over a time period that
spans the addition of the dictionary. If you include fields from the new
dictionary in the report, only requisitions that contain that dictionary are
included. You would be forced to use the service-based query subject to
generate a report including data from the new dictionary and any old
dictionaries that spanned the time period of the change. Conversely, if you had
added the fields to the existing dictionary, requisitions that predate the
change would also appear on the report, with blank values for the new fields.
Deleting a Field
from a Reportable Dictionary
The business view of
the data mart is unchanged—that is, the field will continue to appear as a
query item in the query subject corresponding to the dictionary. However, the
ETL process will no longer load data into that field.
The effect on the
data mart is the same as the effect of hiding the field: no further data values
are supplied in service requests, or appear in the data mart. However, it may
be more efficient to delete the field, both from the service design perspective
(the field no longer has to be hidden every time the dictionary is included in
a new service) and from the data mart perspective (the ETL process no longer
has to load data into the field). Of course, before a field is deleted from a
dictionary, thorough testing should be done to ensure that no side effects
exist; for example, you must verify that no ISF code refers to that field or no
Service Link agent parameters are bound to fields to be deleted.
Results are similar
to those seen when a field is deleted from a dictionary. The dictionary
continues to appear as a query subject. However, no additional rows are added
to the underlying dimension. An attempt to report on data from that dictionary
will, logically, include only requisitions ordered when the service definition
included the dictionary.
The number of
reportable dictionaries cannot exceed the maximum number specified as part of
the Advanced Reporting configuration. Though this number is entered when the
data mart is created, it can be increased by the System Administrator while the
data mart is in operation. However, since deleting a reportable dictionary does
not remove the dictionary from the data mart, this dictionary still counts as
one of the allowable dictionaries. Once requisitions using a dictionary have
been loaded to the data mart, there is no way to remove the dictionary from the
In general, a
dictionary name should not be changed once the dictionary has been designated
as reportable. The name of the corresponding query subject in the data mart
would be changed. However, any reports or queries saved using fields in the
dictionary would become invalid and would no longer run. (The report owner or a
user having write permission to a report in a public folder would need to edit
the Report Definitions of such reports in order to repair them.)
Changing Field Definitions
This section describes about the field definition parameters.
Changing a field name
is like changing a dictionary name—it is possible but not recommended once the
field has been included in a reportable dictionary or service. The name of the
corresponding query item in the data mart would be changed. However, any
reports or queries saved using the previous field name would become invalid.
The data type of the
field (for example, number, date, or character) should not be changed. When the
dimension corresponding to the dictionary is first created, all of the
dictionary fields are mapped to columns of the appropriate data type. This
mapping cannot be changed.
A possible approach
is to create a new field in the same dictionary of the appropriate type and
potentially delete the old field. Reports needing data from both pre- and
post-change would need to include both fields.
There are no
restrictions on changing the HTML representation (display type) for a field.
For example, a text field initially displayed as a set of check boxes could be
displayed as a drop-down list with no effects on the data mart.
Changing the length
of a numeric field is accommodated by the data mart. Any possible side effects
are minimal. For example, a format previously assigned to an item in a report
may have to be adjusted to accommodate the newly allowed field values.
Changing the length
of a text field has minimal effects. If the length were increased to exceed the
number of characters allotted to text fields in the data mart (by default,
200), truncation of the data in the field would occur. If a text field were
shortened, a possible side effect might be that users abbreviate or otherwise
shorten values previously entered in a different format. If a report or query
were designed with a group or section heading based on that field, rows might
not be grouped as expected.
When a dictionary is
added to an active form component, service designers can specify a caption for
each field in a dictionary in the active form component. These captions appear
as the field label on the service form. Service Designer does not enforce a
uniqueness constraint on the field captions, since this is allowed within
Service Catalog service forms. However, the same caption should not be used for
more than one field in the same dictionary if the service is to be reportable.
This would result in two query items with the same name, which is not
Deleting a Child Service from a Service Bundle
The business view of the data mart is unchanged—that is, the fields of child service will continue to appear as query items in the query subject corresponding to the Service Bundle. However, the ETL process will no longer load data into the fields which correspond to the deleted child service.