3 Universes, Kits, and Reports

This chapter provides a description of the individual universes and their associated reporting kits in Oracle Communications MetaSolv Solution (MSS) Operational Reports.

About Universes, Kits, and Reports

A universe is an organized collection of metadata objects that enables you to analyze and report on information in a format that is non-technical. The universe includes connections to the data sources. You can connect to the universe and run queries and create reports using the objects in the universe without any knowledge of the underlying data structures in the database.

MSS Operational Reports offers three separate universes, which are as follows:

Customer
Ordering
Engineering

Each universe represents a key functional area of MetaSolv Solution. Each universe is supported by individual reporting kits, which in turn contain a set of reports for a specific subject area.

Customer Universe

The Customer universe contains customer information, such as address, service items, and deposit information, along with customer service sales information. This universe lets you keep track of sales performance per individual or product type. The security features allow you to keep track of user names and privileges.

The following reporting kits use the Customer universe:

Customer/Security Reporting Kit
Sales/Telephone Number Inventory Reporting Kit

Customer/Security Reporting Kit

This kit contains reports that provide information about customers, customer service requests, products on customer service requests, and windows related to MetaSolv Solution security.

Table 3-1 lists the reports contained within this kit.

Table 3-1 Customer/ Security Reporting Kit: Report Definitions and Potential Uses

Report Name	Description	Potential Uses
Priority Status Customer Accounts	Prompts for the status of the customer and their priority. Retrieves account number, name, status, and the priority description.	Find accounts with a particular status and defined priority.
Deposit Report	Lists all the deposits made by business or residential customers grouped by state.	Estimate the total amount of deposits for business or residential customers grouped by state.
Active Accounts with No Active Service Items	Lists customer accounts that are in service but have no active service items.	Help identify accounts without any active service items.
User Window List	Prompts for a user and retrieves the windows they have access to, along with the user's status.	Audit MetaSolv Solution security.
Window Security List	Prompts for windows and retrieves the list of users who have access to these windows. In addition, it also lists the status of each user.	Audit MetaSolv Solution security.
Items without Prices	Lists the items that do not have a price listed.	Determine the active items defined without prices in the Product Catalog.
Disconnected Reason	Based on a specified date, retrieves information about account number, customer, type, item, and the reason for disconnection.	Determine the customers that have been disconnected and the reason for disconnection.
Customer Activity	Lists the customer history information, such as product, service request, status, and installation location.	Show what products are assigned to what customers, including the product's status.
Customer by Order Status	Lists all the service requests under a specified status, including their estimated revenue.	Determine the status of a service request.
Impact of Discontinued Product	Lists the customers who have products with price terms. Provides an explanation for the price terms.	Determine what is the economic impact of a discontinued product.
Product by Customer by Offer Price	Lists all the products that have an offer price based on a specified customer.	Determine the offer price and the price type of each product for a customer.
Customer Pricing Information by Product	Lists the customer information, service items ordered by a customer, and pricing information for those service items.	Track product pricing changes and cancellations to notify customers.

Sales/Telephone Number Inventory Reporting Kit

This kit contains reports that provide the following:

Sales information related to PSR orders stored in MetaSolv Solution
Telephone number reports related to the Federal Communications Commission (FCC)

Table 3-2 lists the reports contained within this kit.

Table 3-2 Sales/Telephone Number Inventory Reporting Kit: Report Definitions and Potential Uses

Report Name	Description	Potential Uses
Reservation	Lists reserved telephone numbers based on a specified date.	Determine numbers that are reserved.
PIC Change	Shows PIC type and the PIC number for the account # and customer based on specified dates.	Provide correct PIC information.
E911 Address List	Shows customer address based on telephone number.	Ensure that the E911 information is correct.
Sales Module Audit	Audits accounts with: More than one agent/salesperson Both an agent and a salesperson Neither an agent nor a salesperson. Lists the customer.	Audit accounts.
Toll Free Telephone #'s	Shows the customer toll free numbers based on customer account number.	Determine the customer toll free numbers.
Cancelled Orders	Lists the cancelled service requests.	Find all the cancelled service requests within a date range.
Salesperson Order Summary	Shows customer, service request, product, revenue, and offer price for a given date based on the salesperson.	Retrieve data about orders based on the salesperson.
Commercial Sales Production Flash	Shows the sales module description and allows you to drill down to the salesperson based on product. Also shows the number of service requests and the revenue for the specified product.	Determine which products are being sold by which salesperson.
Ported Telephone #'s	Lists all ported numbers and associated customers.	Determine which numbers are portable and the associated customers.
Sales Role Summary	Counts service requests and revenue by role type. You can drill down by role type for a more specific report.	Count all the service requests per salesperson.
TN Thousands Group	Reports the percentage of utilization or fill rate for each NXX.	FCC Numbering Optimization Reporting.
TN Aging of Disconnected Numbers	Provides a list of numbers with disconnected status, with a transfer of calls, to remove the bit within our switches, which sends calls to AIS.	FCC Numbering Optimization Reporting.
TN Capacity	Provides an overall picture for all telephone numbers within a service provider's inventory.	FCC Numbering Optimization Reporting.
TN Capacity with Rate Center	Provides an overall picture for all telephone numbers within a service provider's inventory. This includes the rate center that is associated with the telephone numbers.	FCC Numbering Optimization Reporting.
LIDB/CNAM Database Service Provider Ported NXX Notification	Provides notification of the first instance of a ported NXX to the LIDB/CNAM database service provider to accept portable LIDB/CNAM records.	FCC Numbering Optimization Reporting.
LIDB/CNAM Database Service Provider Ported NXX Notification with Rate Center	Provides notification of the first instance of a ported NXX to the LIDB/CNAM database service provider to accept portable LIDB/CNAM records. This includes the rate center that is associated with the NXX.	FCC Numbering Optimization Reporting.
TN Reservation Aging	Identifies which customers reservations have exceeded the allowed time frame (currently 45 days).	FCC Numbering Optimization Reporting.

Ordering Universe

The Ordering universe contains the objects necessary to generate information on all types of service requests, such as ASR, LSR, PSR, ISR, and so on. This universe contains objects related to work management and provisioning that let you uncover problems in your service fulfillment.

The Ordering universe contains the following reporting kits:

Service Request Reporting Kit
Work Management Reporting Kit
Provisioning Reporting Kit

Service Request Reporting Kit

This kit contains reports that are specific to service requests. The report is generated at the order level, not at the detail level.

Table 3-3 lists the reports contained within this kit.

Table 3-3 Service Request Reporting Kit: Report Definitions and Potential Uses

Report Name	Description	Potential Uses
Service Order Tracking	Provides a tool for tracking the progress of service requests. This report includes information about ISR, LSR, and PSR. It includes columns for organization, service type group, order number, and the type of service request.	Identify problem areas with service requests.
Order Log	Provides a summary list of service requests, including ISR, LSR, and PSR data, and columns for organization, service type group, order number, and type of service request. The report also gives service request date information, including the received date, the due date, and the completion date. There is also a column that indicates if the due date was met.	View a summary of service requests and identify potential problem areas.
Detail Circuit Completion	Provides a summary of statistics, dates, and other details about service requests for a particular organization. This report includes all service request types.	Track service requests by circuit IDs.
Order Status	Lists all the service requests that have a late DLRD or RID task. This report includes high-level order information and a list of circuits accompanying the service requests.	Resolve potential customer complaints due to delays in circuit design.
Open Orders By Date	Lists the open service requests for a given date range. This report is first sorted by work group, and then by task scheduled completion date. The report also generates counts for total service requests, service requests in a work group, and service requests in a work queue.	Generate a work log or view past due or upcoming service requests.
PON & RPON Mismatch	Compares the purchase order number (PON) and the related purchase order number (RPON) and reports any differences found between the two values.	Identify service requests that have differences between the PON and the RPON.
MetaSolv Solution Billing Report	Lists the service requests that have been modified.	Identify items that may require a change in billing. This report is efficiently utilized in the batch mode by querying for service requests that have changed in the last 24 hours. However, you can run this report in the on-line mode.
Open Orders without a Due Date Task	Lists all the incomplete service requests that do not have a due date task. This report includes information about the service request and its associated tasks.	Audit service requests that do not contain due date tasks.
Open Order Inventory with Circuit Count	Lists all the open service requests that are not due date complete. This report is grouped by receipt date and sectioned by month. A list of open service requests is displayed with all other open service requests received in the same month. The number of circuits on the service request is also included in this report.	Track order inventory. Use this report as a starting point for tracking potential customer relations problems.
Completed Orders Not Billed	Lists all the service requests that are complete but have not been sent to the billing system.	Prevent revenue leakage by identifying unbilled service requests.
Service Request Tracking Summary Report	Calculates the completion time for a service request that has been due date completed.	Calculates the average process time for a service request.

Work Management Reporting Kit

This kit contains reports that concentrate on the Work Management module in MetaSolv Solution.

Table 3-4 lists the reports contained within this kit.

Table 3-4 Work Management Reporting Kit: Report Definitions and Potential Uses

Report Name	Description	Potential Uses
Non-Work Day List	Generates a list of non-work days for a specific organization or group of organizations. Non-work days include both weekends and holidays.	Review your work calendar and view the days that are not available for task scheduling.
Work Queue Assignment	Lists task assignments for a selected work queue.	Identify the workload for each work queue.
Work Queue Unavailability	Indicates the period of time during which an employee is unavailable.	Schedule or identify production downtime by analyzing the time period when employees are out of the office.
Work Queue Ownership	Provides details on which employees own each work queue.	Level resources.
Provisioning Plan Assignment List	Lists provisioning plans, the tasks associated with the provisioning plans, and the default work queue for each task.	Evaluate your provisioning plans and the tasks associated with those plans. It may be useful when associating new products with provisioning plans.
On Time Task Analysis by Work Group	Calculates the number of tasks completed on time by considering the task actual completion date. This report also displays an on-time percentage of tasks completed by work group, provides a graphical view, and drill-down capabilities.	Evaluate the efficiency of each work group in an organization.
Open Jeopardy With Escalation Information	Lists all the open service requests with a jeopardy code.	Identify open late tasks. This report enables you to view late tasks and the associated escalation information.
Closed Jeopardy Report	Lists all the jeopardy items closed within a specified date range.	Analyze the use of jeopardy. It allows you to determine if the codes are being used accurately by identifying the jeopardy code as it relates to late and potentially late service requests.
Work Queue Interval	Analyzes task completion using interval days. Expected intervals are compared against actual completion intervals and an average interval in days is calculated.	Determines if the expected completion intervals on provisioning plans need to be adjusted to meet actual results.
Jeopardy Report By Code and Task	Lists all service requests based on solicited task information. You can specify the task type, task status, and jeopardy code type. Based on the information you specify, this report returns a list of service requests and any associated child service requests.	Determines how jeopardy and why missed codes are used across different task types.

Provisioning Reporting Kit

This kit contains reports that pinpoint specifics about work management throughout the provisioning process.

Table 3-5 lists the reports contained within this kit.

Table 3-5 Provisioning Report Kit: Report Definitions and Potential Uses

Report Name	Description	Potential Uses
Provisioning Plan Detail List	For the selected organizations, indicates the relationship between tasks, such as predecessors and followers.	Show the relationships between tasks in a provisioning plan.
Provisioning Plan Summary	Lists all provisioning plans and their associated organizations, jurisdictions, and service type groups.	Show the provisioning plans. View the products and organizations that can use these provisioning plans.
Provisioning Plan Usage	Provides information on how frequently a provisioning plan is used.	Audit provisioning plan usage.
Task Completion Performance	Provides information on how often a selected task type meets its due date.	Determine the groups that complete their work on time.
Task Due List	Lists the tasks that are due for a specific time period for service request types ISR, LSR, and PSR. Also contains columns for service request #, organization, and service type group.	Identify late open tasks and their associated service requests.
Task Due But Not Complete	Shows work queues with tasks that are due but not complete and the information about the related service request. This report includes data related to ISR, PSR, ASR, and LSR.	Distribute workloads by displaying the current late tasks by workgroup.
Why Missed Code Occurrence	Lists tasks that have been assigned why missed codes and the number of times a why missed code is assigned to each task type.	Determine the reasons for the service requests being delayed.
Circuit Status	Contains circuit information. This report returns information for all the circuits in the selected status.	Monitor circuits of all statuses.
Late and Potentially Late Incomplete Tasks	Lists scheduled completion intervals, actual completion intervals, and the number of days late for tasks and work queues.	Identify tasks or work queues that are late or potentially late.

Engineering Universe

The Engineering universe contains the objects necessary to report on equipment capacity, inventory, circuits, and trouble management. This universe contains objects that allow you to create concise or detailed reports.

The Engineering universe contains the following reporting kits:

Equipment Reporting Kit
Trouble Management Reporting Kit

Equipment Reporting Kit

This kit contains equipment audit reports and circuit/facility capacity management reports.

Table 3-6 lists the reports contained within this kit.

Table 3-6 Equipment Reporting Kit: Report Definitions and Potential Uses

Report Name	Description	Potential Uses
Relay Racks with Invalid Equipment Names	Lists all the relay racks with names that are not 8 characters long. This report is easily customized for all types of equipment and name lengths.	Ensure that the naming conventions are being followed.
Card Inventory	Prompts for CLLI codes and provides a list of cards with their associated shelf location.	Inventory cards installed for specific locations.
SONET Ring Host Relay Rack	Shows the relay rack that is the host for a specific FNI. Additional information includes switching code, network type, CLLI code, and mounting position.	Identify critical equipment in SONET applications.
Relay Racks Without 11 Character CLLI Codes	Shows all the equipment (grouped by CLLI code) that does not have an 11-byte CLLI code. This report also includes the vendor name, part number, comm code, and location information.	Inventory relay racks. Ensure that the naming conventions are being followed.
Port Usage on DTC/DTCI	Provides the status of DTC/DTCI (Digital Trunk Controller/Digital Cross-connect System) equipment. Also includes the notes and the associated CLLI code.	Assists in capacity management.
DACS Equipment Utilization	Determines the hardware used and spare capacity of DACS equipment.	Provide capacity management for DACS equipment. Use this report as a projection tool for purchasing equipment.
NPA/NXX Threshold Utilization	Provides a summary of telephone numbers grouped by their statuses. The status groups are Unassigned, In Service, Pending, Disconnect, Pending Disconnect, Reserved, Suspended, Transitional, and Unavailable.	Determine the telephone number statuses in the inventory.
Location Facility	Shows vacant positions on facility circuits and provides a summary of all the circuit types on the facility based on user-prompted CLLI codes and rate codes.	Determine the facilities and facility groups in the inventory.
Equipment Specifications	Shows what equipment is used in MetaSolv Solution, including detailed information on the description, vendor name, part number, acronym, transmit level, mounting positions, type, issue number, communications code, and mounting position sequence.	Determine what equipment specifications are currently available to assist in the requisition or configuration process.
Shelf Fill	Lists shelf equipment by location name. You can choose the CLLI code that you want to query. Includes a sum of the cards per shelf. Additional information includes the relay rack, shelf name, shelf acronym, rate code of the card, number of cards assigned, and CLLI code.	Determine all the shelf equipment for a specific location in the inventory.
Capacity Utilization	Lists the vendor, CLLI, and capacity information based on a selected vendor.	View equipment capacity.
Circuit Capacity	Graphically displays the following: Originating and terminating locations Facilities that are inventoried or vacant	Track capacity between locations.
Facility Circuit Position Status	Shows vacant circuit positions on facility circuits and provides a summary of all CP statuses based on user prompted CLLI codes.	Determine the facilities and facility groups in the inventory.
SONET Capacity Report	Shows the following: Total positions and vacant positions The percentage assigned on SONET facility circuits by CLLI code	Determine the facilities and facility groups in the inventory.
Equipment Assignments on Selected Circuit Report	Based on a circuit ID, the report displays all the equipment associated to the circuit by location.	Shows any equipment that is involved in case of circuit outage. You can use this report to display all locations and equipment involved in a SONET ring.
Equipment with Circuit Assignments by Location Report	Lists all the equipment in your network that have circuit assignments and their associations in all locations.	Displays equipment capacity. Displays information about the equipment occupied in each location down to the circuit assignment level.

Trouble Management Reporting Kit

This kit contains reports that cover the many aspects of trouble tickets in MetaSolv Solution.

Table 3-7 lists the reports contained within this kit.

Table 3-7 Trouble Management Reporting Kit: Report Definitions and Potential Uses

Report Name	Description	Potential Uses
Ticket Type Summary	Provides a summary of the count of the priority 1 through 4 tickets for a user-specified time frame grouped by the trouble type code.	Analyze trouble traffic and network dependability during a given time frame.
Ticket Detail	Lists trouble tickets from a user-specified date range. The information includes the Ticket ID, creation date, circuit, type code, and description.	Identify the tickets opened for common causes, in addition to analyzing trouble traffic and network dependability during a given time frame.
Open Tickets	Lists the trouble tickets that are: Open Externally referred Lists the Ticket ID, creation date, responsible organization, current state, escalation level, and when the trouble ticket was externally referred.	Identify the trouble tickets that are open and externally referred.
Monthly Priority Level Summary	Shows a count of tickets based on their priority and the month they were created.	Trend analysis of network dependability.
Mean Time to Repair Detail	Provides detailed information on the time to repair for tickets that were externally referred. The end user selects what external organizations are to be included and what time frame from the close date of the ticket should be used. Fields included in this report include the type code, found code, cause code, time to repair, circuit ID, person who created the ticket, and ticket ID. The report is grouped by the external organization to which the ticket was referred.	Analysis of the response time of external organization to specific tickets in order to identify possible communication problems with organizations or poor service.
Infant Circuits with Trouble Summary	Lists the trouble tickets that were created on circuits that have been in service for less than 30 days.	Trend analysis of installation problems and network dependability.
Infant Circuits Trouble Detail	Provides detailed information on tickets where the circuit has been in service less than one calendar month. Details include the ticket ID, time to repair, creation date, circuit ID, trouble type, trouble found trouble cause, and circuit cross reference. The details are grouped by the responsible organization.	Identify specific installation problems and recurring problems with the network.
Externally Referred Ticket Detail	Provides detailed information on the tickets that are externally referred. The end user selects what external organizations to include and the time frame for the tickets from the creation date. The information includes the ticket ID, time to repair, amount of time the ticket was externally referred, creation date, external ticket ID, circuit ID, circuit cross reference, ticket description, trouble found, and trouble caused. All the information is grouped by the organization the trouble ticket was externally referred to.	Analyze the tickets that required external resolution and the time required to address the problems in specific situations.
Business Unit Ticket	Provides detailed information on tickets for specific administrative organizations. The information includes the ticket ID, circuit ID, ticket creation user ID, trouble description, closed comments, and total duration. The information is grouped by the administrative organization. You are prompted to select the administrative organization to which the tickets are assigned to, and the time frame from close date.	Analyze tickets to specific Administrative organizations and the efficiency of the organizations in resolution of the tickets in a timely manner.
All Trouble	Provides detailed information on trouble tickets. The information includes the ticket ID, time to repair, date created, circuit ID, trouble type, trouble found, trouble description, cause code, and circuit cross reference. You can select the time frame from the ticket creation date.	Analyze all tickets created in a specific time frame and the time to resolve each ticket.
Trouble Tickets by Hour and Day	Summary of tickets created by the hour and day to determine staffing needs for customer support.	Assist the staffing needs of the customer service organization and identify peak and off-peak hours and days.
Affected Customer	Provides a summary of customers that are affected by an equipment outage. The end user is prompted to enter an equipment name, serial number or spec to identify the equipment that has failed. The information provided in the report includes the customer contact information.	Identify the effects of equipment failure and identify critical equipment to assist in required spare parts.
Trouble Ticket Organizations Summary	Lists administrative organization information.	Determine the administrative organization that owns a specific trouble ticket.

Navigating the Engineering Universe

Navigating the Engineering Universe requires familiarity with the equipment hierarchy, circuits, and trouble management.

Equipment Hierarchy

Figure 3-1 shows the relationship structure in the MetaSolv Solution data model.

Figure 3-1 Relationship Structure for Equipment, Port Address, and Mounting Position

Description of "Figure 3-1 Relationship Structure for Equipment, Port Address, and Mounting Position"

In the Equipment Hierarchy structure, a piece of equipment can have other equipment installed on it and a piece of equipment can be installed on another parent piece of equipment. For example, a relay rack provides mounting positions to accommodate other equipment, such as shelves. A shelf occupies mounting positions on the relay rack. A shelf contains a certain number of slots (mounting positions) to accommodate installation of cards. Cards occupy slot positions on the shelf.

The Engineering universe in MSS Operational Reports is a graphical representation of the static SQL that is generated when the reporting objects are built and joined together. Because the reporting objects reflect static SQL statements, you must define a finite number of equipment levels that reflect the flexibility of the equipment hierarchy in the underlying data model. Typically, the equipment hierarchy does not span more than three levels (for example, relay rack, shelf, card). However, you can have as many as six levels of equipment (for example, room, section, row, relay rack, shelf, card). Therefore, the Engineering Universe is designed with seven possible equipment levels.

Figure 3-2 illustrates the equipment hierarchy.

Figure 3-2 Equipment Hierarchy Flowchart

Description of "Figure 3-2 Equipment Hierarchy Flowchart"

The extent of the equipment levels used in building reports for a given organization depends on how the equipment hierarchy has been established within MetaSolv Solution. In many cases, reports can be based on the objects at any equipment level, because the numbered versions of the tables are aliases of the base table. The iterative structure of the universe design enables you to traverse the hierarchy of related equipment.

Circuits

Similar to the equipment hierarchy, two levels of circuit-related objects form a hierarchy for the same purpose with respect to the parent/child relationship. Circuits can have other circuits associated to them through their circuit positions.

Figure 3-3 shows an example of how the port address at the bottom of the equipment hierarchy is associated with a circuit.

Figure 3-3 Circuit Hierarchy Flowchart

Description of "Figure 3-3 Circuit Hierarchy Flowchart"

Trouble Management

Two additional aliases exist in the Engineering universe to support the Trouble Management classes and objects. These aliases are not used for supporting hierarchical structures as in the case of equipment and circuits. The Circuit table and the Network Location table are aliased to relate them specifically to other objects that are relevant to Trouble Management reporting. These objects and their relationships are self-explanatory. There is no complex hierarchical organization of objects to comprehend in order to build relevant reports from these objects.