This is the primary denormalized table of service requests from Unicenter products, all filtered down to basic metrics for use in Service Intelligence. Products included in the mix are Unicenter Service Desk, Argis, and eventually Service Management.
| Column Name | Column Datatype | Column Null Option | Column Comment |
| rec_uuid | byte(16) | not null | SI Record uuid |
| si_sla_name | nvarchar(20) | with null | Name of the attached SLA for the si_rec_prop record. |
| ref_num | nvarchar(16) | with null | Internal Reference ID for Service Desk r11 record. used for dynamically linking to source records in Service Desk from SI. |
| summary | nvarchar(81) | with null | Resolved summary value from call_req, and/or chg_req records in Service Desk. |
| category | nvarchar(256) | with null | Resolved Category value for each SI Record from Service Desk. |
| status | nvarchar(100) | with null | The current resolved status value found in Service Desk for each Service Intelligence record. |
| state | nvarchar(100) | with null | The Service Intelligence State of the record value. This will always equate to O=Open, C=Closed, H=On Hold. |
| open_date | date | with null | The date/time stamp in UTC that the originating record was created in Service Desk, or Argis. |
| close_date | date | with null | The date/time stamp in UTC that the originating record was completed in Service Desk, or Argis. |
| time_open | integer | with null | This will be derrived from subtracting the open_date from the close_Date. Results will be number of seconds, which will somehow have to be translated into a duration by Ingres. Ingres currently has no Duration data type as of this writing. |
| reopen_flag | integer | with null | Integer that indicates that the record has been completed, and then re-opened. This is an integer as opposed to a Boolean as a record can be re-opened any number of times. |
| est_cost | money | with null | This value is evaluated based on the Change Order or Issue records summed estimated cost for completion. |
| act_cost | money | with null | This value is evaluated based on the Change Order or Issue records summed actual cost for completion. |
| est_duration | integer | with null | This value is evaluated based on the Change Order or Issue records summed estimated duration for completion. |
| act_duration | integer | with null | This value is evaluated based on the Change Order or Issue records summed actual duration for completion. |
| assigned_org | nvarchar(100) | with null | The de-normalized name of the organization assigned to address the record. Applies only to Change Orders and Issues type records. |
| customer_loc | nvarchar(100) | with null | De-Normalized value of the customers location. |
| customer_org | nvarchar(100) | with null | De-Normalized value of the customers organization |
| assignee_loc | nvarchar(100) | with null | De-Normalized value of the assignees location. |
| assignee_org | nvarchar(100) | with null | De-Normalized value of the assignees organization |
| group_name | nvarchar(100) | with null | De-Normalized value of the group assigned to the Service record. |
| group_loc | nvarchar(100) | with null | De-Normalized value of the groups location that is assigned to the Service record. |
| group_org | nvarchar(100) | with null | De-Normalized value of the groups organization that is assigned to the Service record. |
| percent_done | integer | with null | Derrived value for Issues and Change Orders that is determined by taking the number of workflow tasks associated with any given change order or Issues, and dividng the total number of tasks by the count of those in a "complete" status. Result will be an integer which is presented as a percentage. |
| is_child | integer | with null | Boolean value indicating that this Service Intelligence record is a child of another SI Record...used so for performance and indexing purposes in Ingres. |
| violate_status | integer | with null | Value that indicates how many times a service intelligence record has had its SLA violated. |
| time_to_violate | integer | with null | Value that indicates how soon until a service intelligence record has its Service level Agreement violated |
| ci_hw_family | integer | with null | Count of Configuration Items attached to the SI Record that are from the Hardware family. |
| ci_sw_family | integer | with null | Count of Configuration Items attached to the SI Record that are from the Software family. |
| ci_svc_family | integer | with null | Count of Configuration Items attached to the SI Record that are from the Service family. |
| ci_cmp_family | integer | with null | Count of Configuration Items attached to the SI Record that are from the Computer family. |
| ci_misc_family | integer | with null | Count of Configuration Items attached to the SI Record that have no family, or none of the defined kpi based families. |
| approved | integer | with null | Boolean value indicating that all workflow tasks of task type "Approval", are indeed set to a task status of "Approved". |
| last_update_date | date | with null | Last date that the service intelligence record was updated. Directly pulled from last_update_date in call_req, chg_req, or iss_req. |
| last_update_by | nvarchar(100) | with null | De-Normalized name of the user that last updated the Service Intelligence record. |
| priority | nvarchar(20) | with null | The priority set on the Service Intelligence record at the time of that data refresh. |
| active | integer | with null | Boolean value indicating that the record is active. Null/0 = inactive. |
| type | char(10) | with null | Character value that indicates the source object type for the Service Intelligence record this is. I= Service Desk Incident P=Service Desk Problem R=Service Desk Request S=Service Desk Issue C=Service Desk Change Order |
| root_cause | nvarchar(100) | with null | The de-normalized value of the Root Cause affiliated with those Service intelligence records that are completed. |
| open_date_day | integer | with null | The day of the week that the Service Intelligence record was opened. (1 - 7) |
| open_date_month | integer | with null | The month of the year that the Service Intelligence record was opened. (1 - 12) |
| completion_date_day | integer | with null | The day of the week that the Service Intelligence record was Completed (1 - 7) |
| completion_date_month | integer | with null | The month of the year that the Service Intelligence record was completed. (1 - 12) |
| assignee | nvarchar(100) | with null | The denormalized name of the assignee value in the si_rec_prop |
| need_by | date | with null | Need by date is used to sort change orders and issues by the dates that their respective customers are requesting the completion of the change or issue. |
| data_source | varchar(256) | not null | Source that the data was received from |
| tot_dur_cost | money | with null | |
| persid | nvarchar(64) | not null | Persistent record ID from Service Desk |
| time_spent_cost | money | with null |
Product Name: Unicenter Service Intelligence
Table Type: Table
Relationships:
| Primary Key | Related Table | Related Column |
| rec_uuid | si_log_prop | rec_uuid |
| rec_uuid | si_ci_rec_rel | rec_uuid |
| rec_uuid | si_wf_prop | rec_uuid |