Technical References

1-58
Cisco Prime Service Catalog 10.0 Reporting Guide
OL-31037-01
Chapter 1 Advanced Reporting Data Mart
Data Mart Administration
DM_FDR_SERVICETABLE_n
A set of tables captures data for each service which has been designated as reportable. The tables contain
all fields in all dictionaries used in the service. The number of these tables is configurable as part of the
application installation, as well as the number of columns of each data type (character, numeric, or
datetime). Each table is numbered sequentially, starting with 1.
Each table has the name DM_FDR_SERVICETABLE (or alternate pattern, as designated via the
installation procedure), followed by a numeric suffix, _n. Each instance of this table represents a
reportable service.
The DM_FDR_SERVICETABLEs appear in the reporting tools as a set of dimensions within the
ServiceData folder. The name of each dimension is the name of the corresponding service. The attributes
of the dimension are the fields which comprise all dictionaries in the service. Fields are added to this
table in the order in which their dictionary occurs in the service. Since the number of fields that may be
accommodated in each table is limited (specified via the installation procedure, but physically limited
by database constraints), the service table may not be complete—some fields, indeed some dictionaries,
may be truncated. Therefore, DM_FDR_SERVICETABLEs should be used with care, especially if
dictionaries with large numbers of fields are designated as reported, or if a great number of dictionaries
are used in the same service.
For a service configured with no reportable grid dictionaries, each request for the service (that is,
requisition entry) is captured by the ETL process and inserted into the corresponding
DM_FDR_SERVICETABLE as one row of data. However, for a service configured with one or more
reportable grid dictionaries, the ETL process inserts multiple rows of data into the
DM_FDR_SERVICETABLE table. The number of rows inserted corresponds to the greatest number of
rows in any of the reportable grid dictionaries.
For example, consider a service with a reportable nongrid dictionary (Employee) and two reportable grid
dictionaries (Contact, Address). Assume a request for this service has three rows of data in Contact, two
rows of data in Address, and some data in the Employee dictionary. The form data captured in the service
table for this service would look the following:
Column Name Data Type Description
ServiceID Integer Service ID
RequisitionID Integer Requisition ID
RequisitionEntryID Integer Requisition Entry ID
Field1 through Fieldn Varchar(200) Varchar fields to hold service data
Fieldn+1 through n+m Numeric Numeric fields to hold service data
Fieldn+m+1 through.. Datetime Datetime fields to hold service data
Requisition
EntryID
Employee.
FirstName
Employee.
LastName
Contact.
Type Contact.Details Address.Line Address.City
Address.
State
Address.
Country
NNN John Smith Cell 650-123-4567 3333 Third St. San Mateo CA USA
NNN (NULL) (NULL) Work 408-765-4321 1111 First St. San Jose CA USA
NNN (NULL) (NULL) Email [e-mail] (NULL) (NULL) (NULL) (NULL)