The standard Business Logic script does not have access to external output tables. There are only two output destinations:
The use of the T_SLALOM_OUTPUTS external table is required when additional output is needed over and above the periodic service level result, when the additional output cannot be provided by adding another Metric, or when adding another Metric decreases the calculation performance by going through the same set of records, only to provide a different output.
For example, consider a case where a Metric is set to calculate the percentage of tickets that were resolved in less than a day and a report with the list of all the tickets that failed to be resolved in less than a day should be produced, it is necessary that the formula output each ticket that is found as a failure to an external table, in addition to adding it to the calculation statistics.
With the above requirement, the normal output service level table cannot provide this output, because:
Records are written to the user output tables only for the Agent which is running for the tracking period of the Metric and calculating the exceptions and corrections. For example, if the Metric is defined as having a monthly tracking period, then the output statements (Tools.SaveRecord and Tools.SaveFields) do NOT get executed when the Engine is executing the formula for the other granularities such as HOUR, DAY, WEEK, QUARTER and YEAR
An additional benefit of having this table externally is that it can be used for multiple reporting requirements. Other typical reporting requirements can be generated from these tables in addition to the contractual requirements. For example, a Metric calculating the 'Number of Tickets Closed' in a month, could also be calculating the ticket resolution time and outputting all this information to the output table. This will enable a more detailed analysis of the individual tickets that were closed during the period, along with additional details that may be required under a separate reporting requirement.
The information in these tables is also managed by the Engine recalculation mechanism. During this recalculation process, records are marked as inactive, and a new row is written instead. This is an important point to note since all freeform report SQL statements need to include a check on the IS_ACTIVE field in the T_SLALOM_OUTPUTS table (where is_active = 1) since only these records are current.
Note: When running the Business Logic Scope during the debugging process of the formulas, messages are actually written to the table T_DEBUG_SLALOM_OUTPUTS instead of the T_SLALOM_OUTPUTS table.
When documenting data using T_SLALOM_OUTPUTS, the inserted data is always text (since the fields of T_SLALOM_OUTPUTS are all varchar2). Therefore, date values are converted into text by applying the operating system's format which may change during the application lifecycle. T_SLALOM_OUTPUTS may therefore suffer from inconsistencies in date formats. Moreover, the Business Logic handles UTC dates, where one may expect T_SLALOM_OUTPUTS to hold local timestamps, so in some cases it may be necessary to use the conversion function Tools.GetLocaleDate(date) to work around this.
The following function converts dates into local times and keeps the date format consistency by formatting dates into the "dd/mm/yyyy hh24:mi:ss" format:
Function FormatDate(time)
Dim LocalTime
LocalTime=Tools.GetLocaleTime(time)
FormatDate=Day(LocalTime) & "/" & Month(LocalTime) & "/" &
Year(LocalTime) & " " & _
Hour(LocalTime) & ":" & Minute(LocalTime) & ":" & Second(LocalTime)
End Function
There are two methods that can be used to write to the external table from within the Business Logic formula which are:
Both of these methods of the Tools object are described below in more details:
Tools.SaveRecord tableName, key,[val1],[val2],…
This method saves a record in a table called T_SLALOM_OUTPUTS. The tableName parameter specifies the (virtual) table within T_SLALOM_OUTPUTS to which the information should be written. Each record in the user table has a unique key which specifies the record to which the information should be written. Each record also has up to 20 string-type value fields. The SaveRecord method receives a user table name and a key. It also accepts all the value fields in the user table. (These value parameters are optional and may be omitted.) If a record with the same key already exists, it is updated. (Only the value fields transferred as parameters are updated.) If a record with this key does not exist, it is created.
Tools.SaveFields tableName, key, [fieldName1,fieldVal1], [fieldName2,fieldVal2]
This method is similar to SaveRecord, except that instead of enumerating all values, it provides pairs of field names and related field values. Field numbers can be substituted for field names. The fields names should previously be manually defined in the T_SO_FIELD_NAMES table. This table is used to record the structure of the output tables.
It is recommended that the Business Logic Expert define the structure of the output table before writing to T_SLALOM_OUTPUTS because then the structure and meaning of the fields is already well defined and will make the querying far simpler.
The table structure is:
Using the SaveFields method is preferable as it keeps a documentation of the structure and the meaning of the inserted values.
Example:
Consider a case where it is necessary to produce a list of all of the incidents with the resolution time higher than a defined threshold, in addition to the Metric result calculating the percentage of tickets that were resolved below that threshold. Writing to the output tables will be done in the OnXXXEvent event handler procedure after the threshold validation.
This is illustrated by the following example:
Sub OnIncidentEvent(eventDetails)
If eventDetails("RESOLUTION_TIME")<=SThreshold Then
CountSuccessIncident s= CountSuccessIncidents+1
Else
building the record unique key
KeyString= eventDetails("IncidentID") &eventDetails.Time
Tools.SaveFields "IncidentsTable", KeyString, "CASE_ID",
eventDetails("CASE_ID"),_
"CUSTOMER_REF",eventDetails("CUSTOMER_REF"),_
"TICKET_CREATOR",eventDetails("TICKET_CREATOR"),_
"CREATION_TIME",eventDetails("CREATION_TIME"),_
"SEVERITY",eventDetails("SEVERITY"),_
" RESOLUTION_TIME ",eventDetails("RESOLUTION_TIME "),_
"CLOSE_TIME",eventDetails("CLOSE_TIME")
End If
End Sub
A few suggestions related to the use of the T_SLALOM_OUTPUTS tables are as follows:
Note: Writing to the output tables may have an influence on the performance of the Engine, since writing to a table is computationally intensive, compared to a calculation in memory. Therefore consider carefully whether it is necessary to use this functionality and where it is necessary, and then minimize the number of times the tables are accessed.
It is not possible to use the standard CA Business Service Insight Report Wizard in order to report on the information written to the output tables. In order to report on this information it is necessary to build a free-form report. In essence, this means building a SQL query on top of this table. Because this table contains many logical tables that are being written to by various formulas, it is recommended that someone familiar with SQL (Data Source Expert) creates a view over the T_SLALOM_OUTPUTS in order to make it easy to differentiate between the different logical tables contained within it, and also to make sure that the information is retrieved as planned.
The view definition will already have all of the casting of the string field types to the relevant information type and will also hold all of the filtering required (Logical table, active records, relevant Metric, etc.).
The following is an example of the view creation:
create or replace view kpi_view as
select
to_date(t...) as fieldName,
to_number(t..), …
from t_slalom_outputs t,
t_rules r,
t_sla_versions sv,
t_slas s,
where table_name = 'TableName'
and is_active = 1
and t.rule_id = r.psl_rule_id
and r.sla_version_id = sv.sla_version_id
and sv.sla_id = s.sla_id
and sv.status = 'EFFECTIVE'
|
Copyright © 2012 CA.
All rights reserved.
|
|