Cisco Prime Service Catalog 10.0 Reporting Guide December 19, 2013 Cisco Systems, Inc. www.cisco.com Cisco has more than 200 offices worldwide. Addresses, phone numbers, and fax numbers are listed on the Cisco website at www.cisco.com/go/offices.
THE SPECIFICATIONS AND INFORMATION REGARDING THE PRODUCTS IN THIS MANUAL ARE SUBJECT TO CHANGE WITHOUT NOTICE. ALL STATEMENTS, INFORMATION, AND RECOMMENDATIONS IN THIS MANUAL ARE BELIEVED TO BE ACCURATE BUT ARE PRESENTED WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED. USERS MUST TAKE FULL RESPONSIBILITY FOR THEIR APPLICATION OF ANY PRODUCTS.
CONTENTS About this Guide CHAPTER 1 v Advanced Reporting Data Mart 1-1 Overview 1-1 In this Chapter 1-2 Reporting and Advanced Reporting Modules 1-3 Reporting Architecture and Components 1-4 Overview 1-4 Data Mart Architecture 1-4 Refreshing the Data Mart 1-4 Standard Reports Package 1-5 Service Catalog Data Mart – Advanced Reporting Running Reports 1-6 Overview 1-6 Accessing Reporting Options 1-6 Reports 1-7 Prebuilt Reports Inventory 1-15 1-6 Advanced Reporting Module 1-16 Accessing the Advanced
Contents Key Performance Indicators (KPIs) Dashboards 1-44 KPI Administration 1-45 1-44 Metrics and Attributes 1-45 Metrics 1-45 Measures of Service Volume: Who is the Customer? Measures of Task Delivery Performance 1-46 Attributes 1-47 1-46 Standard Reports Design 1-48 People, Roles and Groups 1-48 Service Design Details 1-49 Request Management 1-50 Service Volumes and Activity 1-51 Data Mart Administration 1-51 Role-Based Access 1-51 Data Mart Source Data 1-53 Service-Form Reporting Metadata 1-54 Dyn
About this Guide Objectives The Cisco Prime Service Catalog Reporting Guide explains the reporting capabilities provided by Cisco Prime Service Catalog, and how to make best use of those capabilities. It gives you an understanding of the overall lifecycle of service request data, including how it is extracted, transformed, and loaded into the Service Catalog Advanced Reporting Data Mart.
Conventions This document uses the following conventions: Convention Indication bold font Commands and keywords and user-entered text appear in bold font. italic font Document titles, new or emphasized terms, and arguments for which you supply values are in italic font. [ ] Elements in square brackets are optional. {x | y | z } Required alternative keywords are grouped in braces and separated by vertical bars.
Obtaining Documentation and Submitting a Service Request For information on obtaining documentation, submitting a service request, and gathering additional information, see the monthly What’s New in Cisco Product Documentation, which also lists all new and revised Cisco technical documentation, at: http://www.cisco.com/en/US/docs/general/whatsnew/whatsnew.
Cisco Prime Service Catalog 10.
CH A P T E R 1 Advanced Reporting Data Mart • Overview, page 1-1 • Reporting Architecture and Components, page 1-4 • Running Reports, page 1-6 • Advanced Reporting Module, page 1-16 • Best Practices for Service Design and Reporting, page 1-36 • Key Performance Indicators (KPIs), page 1-44 • Metrics and Attributes, page 1-34 • Standard Reports Design, page 1-48 • Data Mart Administration, page 1-51 Overview This chapter describes the Reporting and Advanced Reporting capabilities provided
Chapter 1 Advanced Reporting Data Mart Overview These reporting capabilities are seamlessly integrated into the Service Catalog framework, but are implemented using tools from the IBM Cognos Series 8 Business Intelligence (BI) solution toolset. These tools are summarized below. • IBM Cognos 8 Connect allows Service Catalog to display Cognos reports and other reporting objects. • IBM Cognos 8 provides production-quality reports to business users and nontechnical users.
Chapter 1 Advanced Reporting Data Mart Overview Section Description Metrics and Attributes, page 1-34 Describes measures and attributes available in the reporting options and how to effectively measure service volume delivery and task delivery performance Standard Reports Design, page 1-48 Gives the database design for all tables used to construct standard reports and KPIs Data Mart Administration, page 1-51 Provides additional information on the administration and configuration of the Service Cata
Chapter 1 Advanced Reporting Data Mart Reporting Architecture and Components Reporting Architecture and Components Overview This section describes the architecture of the Service Catalog reporting solution. It can be read by anyone curious about how the Cognos components are used and the role each plays in the solution.
Chapter 1 Advanced Reporting Data Mart Reporting Architecture and Components 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.
Chapter 1 Advanced Reporting Data Mart Reporting Architecture and Components Service Catalog Data Mart – Advanced Reporting The Advanced Reporting module allows users to build custom reports and queries from data in the Service Catalog data mart, capturing data from Service Catalog. Service Catalog Data Mart The Service Catalog Data Mart is based on the Custom Reports Data Model package.
Chapter 1 Advanced Reporting Data Mart Reporting Architecture and Components Reports Choose Reporting > Reports to display the Reports home page, as shown below. Reports Title Bar The title bar at the top of the Reports page includes user options, as summarized in the table below and explained in detail in the following sections.
Chapter 1 Advanced Reporting Data Mart Reporting Architecture and Components Running a Report Drill down through the report folders until the report you are interested in appears. Click the report name to run the report with its default output format (HTML). In addition, the following reporting options may be available via the icons directly underneath the report name and the More link. Reporting Option Explanation Change properties of the report.
Chapter 1 Advanced Reporting Data Mart Reporting Architecture and Components Reporting Option Explanation Only Report administrators can modify public reports or folders. However, report users may copy reports to their private folders or create private views of the reports, and manipulate these. Available only via the More… option. Add a bookmark to the current report to browser favorites.
Chapter 1 Advanced Reporting Data Mart Reporting Architecture and Components Step 4 Alternatively, some reports require multiple filters. For example, a report organized by People might ask you to first specify the Organizations to appear on the report, then specify people of interest within those organizations. In this case, the “Next” and “Previous” buttons are enabled. Click Next to display the next set of Choices, and choose the Results you want.
Chapter 1 Advanced Reporting Data Mart Reporting Architecture and Components a user's customizations can be applied. Typical customizations include the ability to save the report filter criteria (parameters) previously entered; to schedule the report, either one time or on a recurring basis; to save previously run versions of the report output; and to change the report properties such as the default output format. To save a report as a report view, click Keep this version, then Save as Report View.
Chapter 1 Advanced Reporting Data Mart Reporting Architecture and Components Emailing Report Output Two e-mail delivery options available are: • Include the report (as an attachment) in the e-mail. This is the default behavior when the report properties are set to “Send the report to me”, so the option is not available. • Include a link to the report in the e-mail—the recipient must be Service Catalog users with permission to run reports.
Chapter 1 Advanced Reporting Data Mart Reporting Architecture and Components • Check Show users in the list then click on the Service Catalog namespace (directory) in the list of Available entries on the left side of the page. • A list of roles (which have an associated reporting capability) and individual users (who have been granted one of those roles) appears in the Available entries column.
Chapter 1 Advanced Reporting Data Mart Reporting Architecture and Components User Preferences Cognos offers two ways of viewing the reports and folders, represented by icons at the top right of each page. The selected view is highlighted. The “Details View” includes a brief description of each report. The “List View” is the default view.
Chapter 1 Advanced Reporting Data Mart Reporting Architecture and Components Entries at the bottom of the page pertain to the user's location/locale. The time zone is used when scheduling reports. The default time zone is the time zone where the reporting server is located. In a distributed implementation, users need to set the time zone to their current location in order to easily schedule reports to be run. There is no support for rendering of non-English form data or facts table content at this time.
Chapter 1 Advanced Reporting Data Mart Advanced Reporting Module Report Title Folder Description Groups by Organizational Unit People, Roles & Groups Administrative report which lists groups and shows their Organizational Unit Groups by People People, Roles & Groups Administrative report which lists People and shows their groups Organizational Units by Group People, Roles & Groups Administrative report on groups and their organizational units Organizational Units by People People, Roles & Gro
Chapter 1 Advanced Reporting Data Mart Advanced Reporting Module • The Home page provides shortcuts for running the Standard reports, without having to choose the Reporting module from the Service Catalog drop-down menu. • The Ad-Hoc Reports tab provides access to IBM Cognos Query Studio, for writing queries against the data mart. • The Report Designer tab provides access to IBM Cognos Report Studio, for writing professional quality reports against data mart.
Chapter 1 Advanced Reporting Data Mart Advanced Reporting Module The dimensions and facts are grouped within the corresponding folders. In addition, the Organizations folder holds data on the organizations, groups, and people in use at the site. The Service Bundles folder holds data on the service bundles and child service which were associated to the parent service to form the bundle. As you expand the folders, all of the dimensions and facts become visible.
Chapter 1 Advanced Reporting Data Mart Advanced Reporting Module • The DictionaryData folder lists all dimensions based on dictionaries which were specified as Reportable, and were therefore loaded into the data mart. Each reportable dictionary is available in the DictionaryData folder. Each dictionary query subject includes all fields in the dictionary, whether they were hidden or not in any or all services.
Chapter 1 Advanced Reporting Data Mart Advanced Reporting Module Figure 1-1 Service Request Star Schema Diagram ĂůĞŶĚĂƌ^ƚĂƌƚ ĂƚĞ ĂůĞŶĚĂƌ ůŽƐĞĚ ĂƚĞ ĂůĞŶĚĂƌ ƵĞ ĂƚĞ ^ĞƌǀŝĐĞZĞƋƵĞƐƚ&ĂĐƚ ZĞƋƵĞƐƚŽƌ ƵƐƚŽŵĞƌ ^ĞƌǀŝĐĞ ŝĐƚŝŽŶĂƌLJϭ ŝĐƚŝŽŶĂƌLJϮ ŝĐƚŝŽŶĂƌLJϯ ŝĐƚŝŽŶĂƌLJ͘͘Ŷ Task-Based Facts The Service Catalog data mart provides five views of the task-based facts.
Chapter 1 Advanced Reporting Data Mart Advanced Reporting Module When creating a report or query, always use the Fact table whose contents best match the type of tasks to be included in the report. Fact table contents are summarized in the table below.
Chapter 1 Advanced Reporting Data Mart Advanced Reporting Module Organizations The Organizations folder contains information on the groups, organizations, and people configured at the site. Query subjects in this folder cannot be joined with dimensional or fact data, but only with each other. Corresponding query items can be found in query subjects within the Dimensions and Facts folders.
Chapter 1 Advanced Reporting Data Mart Advanced Reporting Module Query Item Description Supervisor Full Name Full name of the customer's supervisor, in First Name Last Name format Cust_SupervisorFirstName First name of the customer's supervisor Cust_SupervisorLastName Last name of the customer's supervisor Cust_SupervisorEmail Email address of the customer's supervisor Cust_SupervisorLoginName Login name of the customer's supervisor Customer Status Customer status; valid values are “Active” a
Chapter 1 Advanced Reporting Data Mart Advanced Reporting Module Queue The queue dimension describes the queue to which a task was assigned.
Chapter 1 Advanced Reporting Data Mart Advanced Reporting Module Supervisor Full Name Full name of the supervisor, in First Name Last Name format Req_SupervisorFirstName First name of the supervisor Req_SupervisorLastName Last name of the requestor's supervisor Req_SupervisorEmail Email address of the requestor's supervisor Req_SupervisorLoginName Login name of the requestor's supervisor Service The service dimension includes a hierarchy (service team, service group, service) for organizing data
Chapter 1 Advanced Reporting Data Mart Advanced Reporting Module Query Item Description Service Group Name Name of the service group to which the service bundle belongs Service Team Name Name of the service team responsible for the service bundle Standard Duration Standard duration specified in the service bundle definition Durations Units Units in which the standard duration is displayed Hours per Business Day Number of hours per business day Price Service bundle price Service Bundle Statu
Chapter 1 Advanced Reporting Data Mart Advanced Reporting Module CalendarClosedDate The CalendarClosedDate dimension provides a hierarchy for structuring queries about the date a requisition or task was closed. Using query items in this dimension provides an easier way to filter or group by dates, rather than having to choose a complete date and use an expression to extract, for example, just the month or week you are interested in.
Chapter 1 Advanced Reporting Data Mart Advanced Reporting Module Data Mart Facts The query subjects in the Facts folder provide information about the tasks and service requests logged via the application service catalog. ServiceRequestFact (Requisitions) The ServiceRequestFact provides information about services ordered. Folders group metrics for On-Time and Request Status Counts.
Chapter 1 Advanced Reporting Data Mart Advanced Reporting Module Query Item Description Cancelled Count Number of requests that were cancelled by the user Completed Count Number of requests whose delivery plan has been completed Ongoing Count Number of requests submitted but not yet closed Rejected Count Number of requests rejected by an approver Delivery Cancelled Count Number of requests whose delivery was cancelled by a service manager Service Request Status Current status of the service r
Chapter 1 Advanced Reporting Data Mart Advanced Reporting Module Query Item Description Planned Effort (Hours) Planned effort specified for the task in the service definition Planned Duration (Hours) Configured duration for the task to be performed, in hours, as specified by the service designer Actual Duration (Hours) Actual duration taken for the task as performed, in hours, as calculated by the system, based on the customer's calendar Performer’s Actual Duration (Hours) Actual duration taken
Chapter 1 Advanced Reporting Data Mart Advanced Reporting Module Query Item Description Category The category in which the effort was expended ContributorID Person ID of the person who performed the task for which effort entry was required Contributor Full Name Full name of the contributor in First Name Last Name format ContributorFirstName First name of the person who performed the task for which effort entry was required ContributorLastName Last name of the person who performed the task for w
Chapter 1 Advanced Reporting Data Mart Advanced Reporting Module Query Item Description Last Name Last name of the person Home Organization Unit Name Name of the person's home OU Building Building number/name in which the person is located BuildingLevel Building level/floor of the person Office Number Office of the person Cubicle Number Person cubicle number Street Address1 First line of the street address of the person Street Address2 Second line of the street address of the person Cit
Chapter 1 Advanced Reporting Data Mart Advanced Reporting Module Service Bundle The Service Bundle query subject provides access to all service bundles in the repository.
Chapter 1 Advanced Reporting Data Mart Advanced Reporting Module Metrics and Attributes Metrics and attributes in the data mart are derived using the computations explained below. Task Duration Three measures of task duration are available in the task-based fact tables: • ActualDuration measures the elapsed number of work hours, according to the customer's calendar, that it took for the task to be performed.
Chapter 1 Advanced Reporting Data Mart Advanced Reporting Module Service Start Date and Time STARTEDATE and STARTEDDATETIME for a service are initially set to the time when the customer submits the service request. STARTDATE and STARTEDDATTME values are updated as soon as authorizations are completed and the delivery moment begins.
Chapter 1 Advanced Reporting Data Mart Best Practices for Service Design and Reporting Running Custom Reports and Queries When report designers create custom reports or queries, they may save them either to the Public folders (the Reports folder is the root public folder) or to their private folder (“My Folder”). Reports saved to the private folders are runable/accessible only by the person who developed them.
Chapter 1 Advanced Reporting Data Mart Best Practices for Service Design and Reporting • Design guidelines for reportable objects • Configuring the data mart to support these design decisions • The effects of changes in dictionary and service design on the data mart What does it mean to make items “Reportable”? Any dictionary or service may be designated as reportable. Dictionaries A “reportable” dictionary appears as a query subject in the DictionaryData folder of the Service Catalog data mart.
Chapter 1 Advanced Reporting Data Mart Best Practices for Service Design and Reporting • 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.
Chapter 1 Advanced Reporting Data Mart Best Practices for Service Design and Reporting Services 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.
Chapter 1 Advanced Reporting Data Mart Best Practices for Service Design and Reporting which loads the data mart. The query subjects are still available in the reporting framework; the table which holds the dictionary or service data is NOT available for use with another dictionary or service, and still counts as one of the number of tables in use.
Chapter 1 Advanced Reporting Data Mart Best Practices for Service Design and Reporting Note SQLServer cautions against having tables with a row length greater than 8k (8192) bytes. This would impose a significant constraint on the size of the service dimension tables. Since no such limits are present in Oracle, you can increase the number of columns of each data type and the size of the text column up to a 32k total row size limit.
Chapter 1 Advanced Reporting Data Mart Best Practices for Service Design and Reporting • If your requirements for the number of fields per dictionary (or service) exceed the defaults, multiply the number of fields of each type times the size allocated to the data type (the DATA_STRING_MAX_SIZE for character data, 7 bytes for each date field and 20 bytes for each numeric field). If the result exceeds the row size limit for the target database, review your requirements.
Chapter 1 Advanced Reporting Data Mart Best Practices for Service Design and Reporting 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.
Chapter 1 Advanced Reporting Data Mart Key Performance Indicators (KPIs) Changing field types 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.
Chapter 1 Advanced Reporting Data Mart Metrics and Attributes Indicate which KPIs to include by checking the check box to the left of the Report Namein Select Key Performance Indicators Page. Choose the order (1–4) that the selected KPIs will display in your dashboard quadrant by using the Order drop-down menu. Click Reset to clear your choices and start again, or Cancel to exit the popup window without making any changes. Click Save KPI Selection. Changes are reflected on your Dashboard.
Chapter 1 Advanced Reporting Data Mart Metrics and Attributes Metric Name Description On-Time % The percent of services or tasks completed on or before their calculated due date Standard Compliance % The percent of services or tasks completed within their configured duration Measures of Service Volume: Who is the Customer? In the way that many customers’ services are implemented, the name of the person who is the end customer of a service delivery is stored in the order form for the service.
Chapter 1 Advanced Reporting Data Mart Metrics and Attributes Which Duration to Use? Prebuilt reports about the duration of tasks performed during service delivery use the PERFORMERACTUALDURATION measure. This measure takes the performer’s working calendar into account, so that weekends and other nonworking hours are not counted against their time working on the task.
Chapter 1 Advanced Reporting Data Mart Standard Reports Design Billed* The organization which is billed for the service. This is the same as the customer for the service unless a different organization is chosen as the Bill To: Customer when the ordered is reviewed (after a request has been saved, but before it has been submitted.) Only an organizational unit that has been designated as Billable can be chosen as the Bill To: Customer.
Chapter 1 Advanced Reporting Data Mart Standard Reports Design The same query items, with a different set of filters, are used in the “People by Organizational Unit” report: Service Design Details Like the reports in the People, Roles, and Groups folder, those in the Service Design Details folder are fairly easy to produce. Simply choose the desired items from the Dictionary and Service dimensions and group as desired.
Chapter 1 Advanced Reporting Data Mart Standard Reports Design To exactly duplicate the appearance and behavior of the standard report, the following activities are required in Report Designer: • Modify the report title so it is left-justified. • Include a Search-and-Select Filter for the dictionary on a prompt page. Request Management The Request Management folder includes two aging reports, which break down tasks into “buckets”, based on the number of days late the task is.
Chapter 1 Advanced Reporting Data Mart Data Mart Administration This report is essentially a pivot report. The metric that is pivoted is the “age” of the open task. To produce this report: • Place the appropriate attributes/dimensions on the report work area—the queue organization; the queue name; and the task name for the Delivery Tasks fact. • Compute the age of the task by taking the difference (in days) between the current date and the task due date.
Chapter 1 Advanced Reporting Data Mart Data Mart Administration Service Catalog provides a set of base roles which provide access to reporting capabilities. In addition, administrators can define custom roles, which incorporate both base roles and custom roles, to facilitate adding and maintaining users with privileges appropriate to their responsibilities.
Chapter 1 Advanced Reporting Data Mart Data Mart Administration Role Analytics Administrator Site Administrator Capabilities Description • View Request Center Reports • Ability to view the KPI dashboard and run all reports in the Reporting module • Ad-Hoc Reports • Access to Ad-Hoc Reports in the Advanced Reporting module • Report Designer • Access to Report Designer in the Advanced Reporting module • KPI Administrator • Ability to manage public Reporting folders, the dashboard, and Cog
Chapter 1 Advanced Reporting Data Mart Data Mart Administration Data Mart Query Subject OLTP Database Tables Customer DirPerson Requestor DirOrganizationalUnit Performer DirNetworkInfo Person DirLocation Queue DirAddress ServiceRequestFact TxRequistionEntry TxRequisition ServiceTaskFact TxRequisitionEntry TxActivity RequisitionTaskFact TxRequisition TxActivity TaskEffortEntryFact TxBilling DefExpenditureType DefBillingClass DefUnitType DirOrganizationalUnit DirPerson Service-Form Repo
Chapter 1 Advanced Reporting Data Mart Data Mart Administration 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 DM_FDR_DICTIONARYMETADATA The DM_FDR_DICTIONARYMETADATA table maps individ
Chapter 1 Advanced Reporting Data Mart Data Mart Administration Column Name Data Type Description 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.
Chapter 1 Advanced Reporting Data Mart Data Mart Administration 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
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.
Chapter 1 Advanced Reporting Data Mart Data Mart Administration Data Mart Database Objects 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.
Chapter 1 Advanced Reporting Data Mart Data Mart Administration 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.
Chapter 1 Advanced Reporting Data Mart Data Mart Administration 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.
Chapter 1 Advanced Reporting Data Mart Data Mart Administration The Service Catalog ETL processes for all packages use Cognos Data Manager Runtime components to generate executables that are deployed into the reporting server as part of the application installation procedure. These scripts use Cognos SQL to read the data from the OLTP source, allowing for greater degree of portability of the catalog between heterogeneous database environments.
Chapter 1 Advanced Reporting Data Mart Data Mart Administration ŽŐŶŽƐ d> ; ĂƚĂ DĂŶĂŐĞƌͿ DĞƚĂĚĂƚĂ ĨŽƌ ŵĂƉƉŝŶŐ ƚĂďůĞƐ ƚŽ ĚŝĐƚŝŽŶĂƌŝĞƐ ƵƐƚŽŵ :ĂǀĂ ƉƌŽŐƌĂŵ ĂƚĂ ŵĂƌƚ ĂƚĂ &Žƌŵ ĚĂƚĂ ; >K ͬyD>Ϳ CUSTOM Reports Package Process Flow – Part 1 K>dW ZĞůĂƚŝŽŶĂů ĂƚĂ Figure 1-3 Form Data Custom ETL The custom Java program not only loads form data from reportable dictionaries and services into corresponding dimensions in the data mart, it also tracks which dictionaries and services have been loaded.
Chapter 1 Advanced Reporting Data Mart Data Mart Administration Select Data from the Transactional Database Select new or changed data from the OLTP database, based on extraction views which include the columns required in the data mart and which filter by comparing the time stamps in the source data to the date and time the ETL process was last run.
CH A P T E R 2 Data Mart Schema • Custom Reporting Data Model, page 2-1 Custom Reporting Data Model Data Mart Schema Design and the Business View 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).
Chapter 2 Data Mart Schema Custom Reporting Data Model Star Schema Design for AllTaskFact (All Tasks) Cisco Prime Service Catalog 10.
Chapter 2 Data Mart Schema Custom Reporting Data Model Star Schema Design for AuthTaskFact (Authorization Tasks) Cisco Prime Service Catalog 10.
Chapter 2 Data Mart Schema Custom Reporting Data Model Star Schema Design for DeliveryTaskFact (Delivery Tasks) Cisco Prime Service Catalog 10.
Chapter 2 Data Mart Schema Custom Reporting Data Model Star Schema Design for ServiceRequestFact (Requisitions) Dictionary and Service-Based Dimensions 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” section on page 1-58 for more information).
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.
INDEX Data Type Conversions A Details View Ad-Hoc Reports 1-1, 1-17 Advanced Reporting Running 1-7, 1-14 Dictionaries 1-16, 1-36 Changing 1-17 1-42 Reportable Advanced Reporting Module Attributes 1-40 1-3, 1-6, 1-16 1-34, 1-47 1-37 Dictionary Dimension Dimension 1-23 1-18 CalendarClosedDate CalendarDueDate C 1-27 1-27 CalendarScheduledDate CalendarClosedDate Dimension CalendarDueDate Dimension CalendarStartedDate Dimension 1-17 Dictionary 1-23 Dynamically Defined 1-41 Keywor
Index Data Mart Defined 1-28 O 1-17 Task-Based Organizational Unit Query 1-20 Form Data Reporting (FDR) Organizations Folder 1-4 1-32 1-22, 1-31 P G Group Query Performer Dimension 1-31 Person Query 1-31 Prebuilt Reports I Preferences IBM Cognos Series 8 1-1, 1-5, 1-15 1-14 Public Folders 1-2 1-23 1-7 Q J JFreechart Query, Creating 1-5 Query Items 1-35 1-18 Query Subject K Defined Key Performance Indicators (KPIs) KPI Administration KPI Inventory Keyword Dimension Tas
Index Creating 1-35 Details View Developing Task-Based Facts 1-36 Emailing 1-12 List View 1-7 Pages T 1-7 Task-Based Query Subjects 1-29 Tasks Duration 1-10 1-34 Rescheduling Prebuilt 1-15 Running 1-6, 1-8 Saving 1-20 1-35 TaskType Dimension 1-26 1-11 Service Volumes and Activity Title Bar 1-51 1-7 Report View Defined Saving 1-10 1-10 Request Management 1-50 Requestor Dimension 1-24 Role-Based Access Running a Report 1-51 1-8 S Schema Design 2-1 Service Bundle Folder
Index Cisco Prime Service Catalog 10.
