Technical References

2-6
Cisco Prime Service Catalog 10.0 Reporting Guide
OL-31037-01
Chapter 2 Data Mart Schema
Custom Reporting Data Model
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