The documentation set for this product strives to use bias-free language. For the purposes of this documentation set, bias-free is defined as language that does not imply discrimination based on age, disability, gender, racial identity, ethnic identity, sexual orientation, socioeconomic status, and intersectionality. Exceptions may be present in the documentation due to language that is hardcoded in the user interfaces of the product software, language used based on RFP documentation, or language that is used by a referenced third-party product. Learn more about how Cisco is using Inclusive Language.
This appendix contains the following topics:
This section describes the various schema designs for data mart.
The data mart schema was designed to be used with IBM Cognos Framework Manager and the business view used by the IBM Cognos reporting tools (Query Studio and Report Studio, represented as Ad-Hoc Reports and Report Designer in the Service Catalog Advanced Reporting module). As such, technical documentation in the Data Mart chapter outlines the contents and relationships of the query subjects, but does not cover the underlying data model, which is not exposed to reporting users or designers.
This chapter is meant for technical personnel who wish to investigate the physical data model underlying the business view exposed via the Cognos tools. In particular, it may be useful to map the names for the database objects in the diagrams that follow to corresponding names of query subjects. In the following diagrams:
The above diagrams are incomplete. All fact tables actually have one-to-many relationships to dictionary- and service-based dimension tables. For services that do not contain any grid dictionaries, the relationships are always one-to-one (see the DM_FDR_SERVICETABLE_n XREF for more information). These relationships are implemented via the column REQUISITIONENTRYID, which is present in all fact and dimension tables, but is not exposed in the business view's dimensions.
When you create reports using Query Studio or Report Designer with service and task fact tables joined, the task data may appear to be repetitive due to the one-to-many relationships among these tables. You may use the grouping feature in the reporting tool to collapse the identical information.
The dictionary- and service-based query subjects which appear in the data mart under the Dictionaries and Services folders correspond to tables in the physical database named DM_FDR_DICTIONARYTABLE_n and DM_FDR_SERVICETABLE_n, respectively, where
The mapping between the physical tables and the reportable objects is maintained in the tables DM_FDR_DICTIONARYMETADATA and DM_FDR_SERVICEMETADATA. These tables are populated when an object is designated as reportable, and used by the ETL processes to dynamically adjust the business view of the data mart to include the reportable objects.
If you wish to supplement the use of the IBM Cognos Business Intelligence tools, bypassing the business view offered by the Ad-Hoc Reports and Report Designer modules of Advanced Reporting, you can do so by interrogating those METADATA tables and constructing database VIEWs which match the dictionary- or service-based query subjects. A sample (SQLServer-specific) SQL statement for building a database view of the MemoryDetails dictionary is shown below. This is (obviously) just a starting point for such an effort.
SELECT distinct 'CREATE VIEW ' + dictionaryname + ' (' AS SQLColumn, 'A 0' AS DestinationColumnName FROM dm_fdr_dictionarymetadata WHERE dictionaryname = 'MemoryDetails' UNION SELECT ' ' + dictionaryattributename, 'A ' + DestinationColumnName FROM dm_fdr_dictionarymetadata WHERE dictionaryname = 'MemoryDetails' AND DestinationColumnName = 'FIELD1' UNION SELECT ', ' + dictionaryattributename, 'A ' + DestinationColumnName FROM dm_fdr_dictionarymetadata WHERE dictionaryname = 'MemoryDetails' AND DestinationColumnName <> 'FIELD1' UNION SELECT ', REQUISITIONENTRYID, REQUISITIONID, SERVICEID', 'A Y' UNION SELECT ') AS SELECT' , 'A Z' UNION SELECT ' ' + DestinationColumnname, 'B ' + DestinationColumnName FROM dm_fdr_dictionarymetadata WHERE dictionaryname = 'MemoryDetails' AND DestinationColumnName = 'FIELD1' UNION SELECT ', ' + DestinationColumnname, 'B ' + DestinationColumnName FROM dm_fdr_dictionarymetadata WHERE dictionaryname = 'MemoryDetails' AND DestinationColumnName <> 'FIELD1' UNION SELECT ', REQUISITIONENTRYID, REQUISITIONID, SERVICEID', 'B Y' UNION SELECT distinct 'FROM ' + DestinationTableName, 'B Z' FROM dm_fdr_dictionarymetadata WHERE dictionaryname = 'MemoryDetails' ORDER BY DestinationColumnName
Executing that SQL Statement yields a SQL Command like:
CREATE VIEW MemoryDetails ( CurrentMemorySize , MemoryType , MemorySizeNeeded , Reason , REQUISITIONENTRYID, REQUISITIONID, SERVICEID ) AS SELECT FIELD1 , FIELD2 , FIELD3 , FIELD4 , REQUISITIONENTRYID, REQUISITIONID , SERVICEID FROM DM_FDR_DICTIONARYTABLE_18
This section is intended for use by people who need to know technical implementation details of the Service Catalog reporting solution. These include report administrators, responsible for the reporting environment; support personnel who may need to report issues to Cisco TAC; and analysts and designers who want to investigate the options of customizing or enhancing components of the reporting options.
The accuracy and completeness of the data in the data mart is critical. In order to gauge these qualities, it is critical for the source of the data in the data mart to be documented. The table below shows the tables in the OLTP database which contributed to data found in the query subjects available in the Custom Data Mart.
Data Mart Query Subject |
OLTP Database Tables |
---|---|
Dictionary Data (user-specific reportable dictionaries) |
DefDataDictionary DefObjectDictionaries TxRequisitionEntry |
Service form Data (user-specific reportable services) |
DefService DefServiceExtension DefDataDictionay DefObjectDictionaries DefData TxRequisionEntry |
Service |
DefService DefArea DirOrganizationalUnit |
Dictionary |
DefDataDictionary DefDictionaryGroup |
Keyword |
DefKeyword |
Group |
DirGroup |
Organization |
DirOrganizationalUnit |
Customer Requestor Performer Person Queue |
DirPerson DirOrganizationalUnit DirNetworkInfo DirLocation DirAddress |
ServiceRequestFact |
TxRequistionEntry TxRequisition |
ServiceTaskFact |
TxRequisitionEntry TxActivity |
RequisitionTaskFact |
TxRequisition TxActivity |
TaskEffortEntryFact |
TxBilling DefExpenditureType DefBillingClass DefUnitType DirOrganizationalUnit DirPerson |
The data mart ETL processes use a set of tables in the data mart database to configure the ServiceData and DictionaryData dimensions that are exposed to users in the Ad-Hoc Reports and Report Designer options. These tables are created when the Custom Reports Package is installed and populated when data is loaded into the data mart.
These metadata tables are not exposed in the Cognos framework. The contents of these tables are used to specify the dynamically defined dictionary and service-based dimensions which appear as the business view of the data in the Custom Reports Project.
These tables are described below. The description for each column uses an abstract data type; the actual data types will vary, depending on the database (Oracle or SQLServer) in which the data mart resides.
The DM_FDR_ETLDICTIONARYMETADATA table maps a particular reportable dictionary (identified by its DictionaryID) to the DM_FDR_DICTIONARY_n table in which dictionary data is stored (DictionaryTableName). It also tracks how many date, numeric, and varchar fields are used within that dictionary and consequently, within the corresponding data mart table.
Column Name |
Data Type |
Description |
---|---|---|
DictionaryID |
Integer |
Unique identifier for the dictionary |
DictionaryTableName |
Varchar(50) |
Name of the data mart table where the dictionary data is stored |
LastDictionaryDateField |
Integer |
Last datetime type field used in the dictionary table |
LastDictionaryNumericField |
Integer |
Last numeric type field used in the dictionary table |
LastDictionaryVarcharField |
Integer |
Last character type field used in the dictionary table |
The DM_FDR_DICTIONARYMETADATA table maps individual dictionary fields (attributes) (identified by DictionaryID and DictionaryAttributeName) to specific columns of the dictionary tables. For example, the attribute “LastName” in the dictionary RC_REQUESTEDBY may be mapped to (that is, actually stored in) the data mart table DM_FDR_DICTIONARYTABLE_10, in the field “Field2”.
Column Name |
Data Type |
Description |
---|---|---|
DestinationColumnName |
Varchar(100) |
Column name of the table where this attribute is stored |
DestinationTableName |
Varchar(200) |
Table name where the dictionary information is stored |
DictionaryAttributeName |
Varchar(100) |
Name of the attribute in the dictionary |
DictionaryAttributeType |
Varchar(100) |
Data type of the attribute in the dictionary |
DictionaryID |
Integer |
Dictionary ID |
DictionaryName |
Varchar(200) |
Dictionary name |
DictionaryAttributeID |
Integer |
Dictionary attribute ID |
The DM_FDR_ETLMETADATA table holds information on configuring the Custom Reports Package that was specified when the Reporting options was installed, as well as data about the data extraction process (ETL) and last update information to for scheduled run of the ETL Process.
Column Name |
Data Type |
Description |
---|---|---|
DictionaryTablePattern |
Varchar(50) |
Pattern for the name of the dictionary tables; by default DM_FDR_DICTIONARY_; specified via Advanced Reporting installation. Dictionary tables will have the specified name followed by a number. |
Field Pattern |
Varchar(50) |
Field pattern for both dictionary and service tables; by default FIELD_. |
LastDictionaryTableSequence |
Integer |
Sequence number of the last dictionary table currently used in the data mart. |
LastDictRequisitionID |
Integer |
Last requisition ID for which data was written to the dictionary table. |
LastProcessedTime |
Datetime |
Date and time when the ETL to load the custom Reports Package was most recently run. |
LastServiceTableSequence |
Integer |
Sequence number of the last service table used in the data mart. |
LastSvcRequisitionID |
Integer |
Last requisition ID of the service data. |
ServiceTablePattern |
Varchar(50) |
Pattern for the name of the service tables; by default DM_FDR_SERVICE_; specified via Advanced Reporting installation. |
DictionaryTotalDateField |
Integer |
The total number of date fields used for dictionary and service tables. |
DictionaryTotalNumericField |
Integer |
The total number of numeric fields used for dictionary and service tables. |
DictionaryTotalVarcharField |
Integer |
The total number of character fields used for dictionary and service tables. |
ServiceTotalDateField |
Integer |
The total number of date fields used for service tables. |
ServiceTotalNumericField |
Integer |
The total number of numeric fields used for service tables. |
ServiceTotalVarcharField |
Integer |
The total number of character fields used for service tables. |
The DM_FDR_ETLSERVICEMETADATA table holds the information about the tables that are used to store information about each reportable service.
Column Name |
Data Type |
Description |
---|---|---|
LastServiceDateField |
Integer |
Last datetime type field used in the service table |
LastServiceNumericField |
Integer |
Last numeric type field used in the service table |
LastServiceVarcharField |
Integer |
Last character type field used in the service table |
ServiceID |
Integer |
Unique Identifier assigned to the service |
ServiceTableName |
Varchar |
Name of the data mart database table where the service data is stored |
The DM_FDR_SERVICEMETADATA table holds the Metadata information about which service attributes are populated in which columns of the service tables and, also the usage name of each of the columns.
Column Name |
Data Type |
Description |
---|---|---|
DestinationColumnName |
Varchar(100) |
Column name of the table where this attribute is stored |
DestinationTableName |
Varchar(200) |
Table name where the service information is stored |
ServiceAttributeName |
Varchar(100) |
Name of the attribute in the service |
ServiceAttributeLabel |
Varchar(200) |
Caption of the attribute in the service |
ServiceAttributeType |
Varchar(100) |
Attribute type |
ServiceAttributeID |
Integer |
Identifier for the attribute of the service |
ServiceID |
Integer |
Service ID |
ServiceName |
Varchar(200) |
Name of the service |
The nature and number of the dictionaries and their attributes which are added to the Custom Reports Package is dynamically determined and may differ greatly at each Service Catalog installation. To support the required flexibility, the database which supports the Custom Reports Package includes a set of abstract data structures, which hold the dimensional data corresponding to the dictionaries, their attributes, and service configuration using dictionary (form) data. Dictionary contents are mapped to these tables via the DM_FDR metadata tables explained above.
A set of tables captures attributes (fields) for each reportable dictionary in the application. 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 has the name DM_FDR_DICTIONARYTABLE (or alternate pattern supplied via the installation procedure), followed by a numeric suffix, _n. Each table is numbered sequentially, starting with 1. Each instance of this table represents a reportable dictionary.
The DM_FDR_DICTIONARYTABLEs appear in the reporting tools as a set of dimensions within the DictionaryData folder. The name of each dimension is the caption of the corresponding dictionary. (For dictionaries with no caption, the dictionary name is used.) The attributes of the dimension are the fields which comprise the dictionary. The fields are numbered sequentially, starting with 1. The number of each type of field (character, numeric, or datetime) is specified via the application installation procedure.
Column Name |
Data Type |
Description |
---|---|---|
DictionaryID |
Integer |
Dictionary ID |
RequisitionID |
Integer |
Requisition ID |
RequisitionEntryID |
Integer |
Requisition Entry ID |
Field1 through Fieldn |
Varchar(200) |
Varchar fields to hold dictionary data |
Fieldn+1 through n+m |
Numeric |
Numeric fields to hold dictionary data |
Fieldn+m+1 through.. |
Datetime |
Datetime fields to hold dictionary data |
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.
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 |
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:
RequisitionEntryID |
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) |
|
jsmith@company.com |
(NULL) |
(NULL) |
(NULL) |
(NULL) |
The following table lists the database tables and views used for the Service Catalog data mart and exposed to users via the Custom Reports package. The tables/views are mapped directly to corresponding query subjects.
Data Mart Table/View |
Primary Key |
Query Subject/Description |
---|---|---|
DM_DEFSERVICE |
ServiceID |
Service information (which includes Service Group and Service Team information) |
DM_DEFDICTIONARY |
DictionaryID |
Dictionary information |
DM_PERSON |
PersonID |
Person information |
DM_DATE |
DateID |
Calendar information |
VIEW_CUSTOMER |
CustomerID |
Customer information |
VIEW_REQUESTOR |
RequestorID |
Requestor (initiator) information |
VIEW_PERFORMER |
PerformerID |
Information about the person who performs a task |
VIEW_QUEUE |
QueueID |
The queue to which a task is assigned |
VIEW_CALENDARCLOSEDDATE |
ClosedDateID |
The date a task or requisition was closed and the accompanying date hierarchy |
VIEW_CALENDARDUEDATE |
DueDateID |
The date a task or requisition was due and the accompanying date hierarchy |
VIEW_CALENDARSCHEDULEDDATE |
ScheduledDateID |
The date a task or requisition was scheduled to start and the accompanying date hierarchy |
VIEW_CALENDARSTARTEDDATE |
StartedDateID |
The date a task or requisition was started and the accompanying date hierarchy |
DM_REQUISITIONENTRYFACT |
RequisitionEntryID |
Individual requisition entries (services) ordered |
DM_SERVICETASKFACT |
ServiceTaskID |
Tasks performed at the service (requisition entry) level, including delivery tasks, ad-hoc tasks and service group authorizations and reviews |
DM_REQUISITONTASKFACT |
RequisitionTaskID |
Tasks performed at the requisition level, including financial authorizations and organizational unit authorizations and reviews |
DM_TASKEFFORTENTRYFACT |
EffortEntryID |
Effort expended in the performance of a delivery task |
DM_FDR_DICTIONARY_n (n=1, 2, 3, ...) |
RequisitionEntryID |
Corresponding dictionary attribute information |
DM_FDR_SERVICE_n (n=1, 2, 3, ...) |
RequsitionEntryID |
Corresponding service form data attribute information |
The tables which comprise the data mart have been indexed to optimize the performance of queries and reports that retrieve data from multiple query subjects. Because of the dynamic nature of the dictionary and service-based dimensions, no additional indexes have been added to these tables.
The indexes provided in the statically defined fact and dimension tables are summarized below.
Data Mart Table/View |
Primary Key |
Additional Indexes |
---|---|---|
DM_DEFSERVICE |
ServiceID |
SERVICENAME |
DM_DEFDICTIONARY |
DictionaryID |
DICTIONARYNAME |
DM_PERSON |
PersonID |
PERSONFIRSTNAMEPERSONLASTNAME ISQUEUE |
DM_DATE |
DateID |
(none) |
DM_REQUISITIONENTRYFACT |
RequisitionEntryID |
SERVICEID REQUESTORID CUSTOMERID STARTEDDATE CLOSEDDATE DUEDATE |
DM_SERVICETASKFACT |
ServiceTaskID |
REQUISITIONENTRYID PERFORMERID SERVICEID STARTEDDATE COMPLETEDDATE DUEDATE QUEUEID |
DM_REQUISITONTASKFACT |
RequisitionTaskID |
PERFORMERID STARTEDDATE COMPLETEDDATE DUEDATE QUEUEID |
Requisitions (ServiceRequestFact) are joined to all relevant dimensions (shown in the star schema previously included) via “inner joins”. That means that any attempt to use query items from both the requisition and a dimension will show only those requisitions which have a corresponding row in the dimension. This is generally not a factor for all of the statically defined dimensions, since these are always required for all requisitions. For example, by definition a requisition must have a customer and initiator as well as a requested service and all dates associated with the delivery of that requisition.
This does have implications for writing reports. For example, if you start defining a report by choosing a set of customers, then add requisition data filtered for a particular period, those customers who did not order a service in that period will vanish from the report.
It is critically important for the dynamically defined, dictionary-based dimensions. If a dictionary was not used in a particular service, any requisitions for that service will not appear on a report that includes query items from that dictionary-based dimension.
Similarly, for delivery tasks (ServiceTaskFact) and service-level authorizations (RequisitionTaskFact), inner joins relate the fact to all dimension tables except the queue. These facts are joined to the Queue dimension via an “outer join”, which supports optional relationships. This allows the service designer to assign the task to a specific person or functional position, rather than to a queue. If a task was not assigned to a queue, it still appears on the report, but the queue will be blank.
For request-level authorizations (AuthTaskFact), too, the queue is optional. In addition, the service is not relevant, since the authorization is performed at the request level, rather than for any individual services which comprise the request.
The Organizations folder allows you to compose reports on people, organizations, and groups. Service Catalog supports many-to-many relationships between these entities. For example, a person may be a member of many organizations (a business unit and multiple service teams); an organization comprises many people. These relationships are reflected in the data mart design, so you can combine two of these entities on a report and group by either entity. For example, you could report on all organizations, listing their members; or you could choose a person, and list all the organizations to which that person belongs.
Fact-1 |
Fact-2 |
Type of Relationship |
---|---|---|
ServiceRequestFact |
ServiceTaskFact |
Left Outer Join |
ServiceRequestFact |
DeliveryTaskFact |
Inner Join |
ServiceRequestFact |
AuthTaskFact |
Left Outer Join |
ServiceRequestFact |
AllTaskFact |
Left Outer Join |
AllTaskFact |
TaskEffortEntryFact |
Left Outer Join |
DeliveryTaskFact |
TaskEffortEntryFact |
Left Outer Join |
ServiceTaskFact |
TaskEffortEntryFact |
Left Outer Join |