How We Designed the ServiceNow^® Extension

Understand how the ServiceNow^® Extension was built and use the design principles to extend your Visier solution.

Overview

Visier Extensions provide an example of the content you can build in Visier to extend your solution. In this guide, we'll walk you through the design decisions that were considered by Visier experts when building the ServiceNow Extension. Understanding the design decisions behind Visier Extensions is key to building your own extensions to the analytic model. If you have additional use cases beyond the out-of-the-box content in Visier, examine the design decisions to learn how to extend the analytic model with new objects.

If you aren't familiar with Visier's analytic model, take a moment to read Understand Visier's Analytic Model.

Design considerations

In this guide, we'll explore the following key design considerations:

Determine your business case
Figure out what data you need to load
Decide what objects to create
Make calculation decisions

Determine your business case

To start the design process, we must understand what business case we are trying to solve. Listing the business questions we want to answer helps us shape the design of the content and add connections between each analytic object. Frame these as a sentence that can be answered with a numerical value; for example, how many employees are in my organization?

In this case, we want to track and analyze the backlog of support cases. Because the Visier solution already has employee data, we can easily bring in support case data and connect it to the Employee data of the support agents in Visier. By building new objects to analyze support case data, we can independently extend Visier's analytic model to answer questions about the relationship between support agents and support cases. We want to answer the following questions:

How large is the support backlog?
How many support tickets are we closing?
How often are tickets escalated?
What are the most common issues or support ticket types being reported?
Which support agents are resolving tickets the fastest or most effectively?
What is the overall customer satisfaction score for our support cases?
How satisfied are customers with the resolution of their support case?
How would customers rate the performance of support agents who handled their cases?

Figure out what data you need to load

Now that we have the business questions we are trying to solve, we can move on to determining what data we need to load. We can do this by thinking about the data needed to answer the key business questions we identified.

Let’s take a look at the first question: How large is the support backlog?

To answer this question, we need the following data:

Support ticket information: The status of the ticket to determine how many are open or active versus closed, and the ticket creation date to see how long the ticket has been open for. In addition, who is the support agent (if any) that is associated to the ticket. This helps analyze if the agent is overloaded and requires more hiring, or there’s uneven distribution of work.

We also need data to understand the business question: What is the overall customer satisfaction score for our support cases?

To answer this question, we need the following data:

Survey information: The overall average customer satisfaction score across all customer survey responses.

Now that we know we need support ticket and survey data, we need to decide which tables to extract from ServiceNow. To do that, we need to understand the source data. ServiceNow’s Customer Service Management (CSM) Workspace comes with different tables and properties we can reference. For more information, see Components installed with CSM workspaces and Tables installed with Customer Service Management.

We can determine which tables to use from the Installed with Assessments and Surveys and Survey responses and results documentation from ServiceNow.

ServiceNow provides the latest state of each record in one table, as well as three history tables (sys_audit, sys_history_set, and sys_history_line) that contain all changes of any attribute. For example:

sn_customerservice_case
sn_customerservice_case_sys_audit
sn_customerservice_case_sys_history_set
sn_customerservice_case_sys_history_line

We don't need to use all three history tables. We can determine which history table to use from the Auditing and history sets | How they work together documentation from ServiceNow. The documentation indicates that the tables capture the same data but are managed differently, with persistence being the key difference. In our case, the best option would be to use the Audit table (sys_audit) because the records persist forever. Using this table ensures we capture historical changes in the data.

During setup, both tables (latest state and audit) will be loaded to track the full record history. However, initial data loads may only cover a limited time frame, such as the past 5 years or, for the first load, the past 3 months. Therefore, we need to verify the accuracy of the numbers and ensure there are no unexpected spikes in the first month.

After familiarizing ourselves with the source data from ServiceNow, we determined that we need to extract data from the following tables:

Table Name	Description
asmt_metric_result	Assessment Metric Results Table stores individual responses from Support Surveys and other assessments.
sys_user	Users Table stores and manages user information such as usernames, email addresses, roles, and other profile information.
asmt_assessment_instance	Assessment Instance Table tracks individual survey responses at a survey session level. It acts as a container for a user's survey submission.
sn_customerservice_case	Customer Service Case Table stores customer service cases, which track customer issues, requests, or inquiries.
sn_customerservice_case_sys_audit	Audit Table is an audit table for sn_customerservice_case, tracking changes made to cases.

If you want to further build the ServiceNow Extension, then the tables you bring in will depend on your use case, and what data you have available.

Decide what objects to create

Now that we know what data we need to load, we can decide what objects to create. An object can be an analytic object (like a subject or an event), a metric, or an attribute (like a property or dimension).

Subjects, events, and overlays

First, ask yourself what kind of container you should design the sales data as. In Visier, analytic objects (subject, events, and overlays) are containers with attributes. Is the sales data best designed as a subject, an event, or an overlay? To figure that out, we can review the following guidelines.

Create a subject if:

The entity has a lifespan and you can analyze changes in its state over time.
The entity has events associated with it at different points in time.
The entity's state is stable for some period of time.
The entity has a history over time.

Create an event if:

The data observes transactional behavior that happens to an entity at a particular time instant.

Create an overlay if:

The object stores aggregate values for a population. It doesn't store individual members' attribute values.
The object contains aggregated data that Visier can compare against metrics and concepts.
The object isn't connected to any other analytic objects.
The object doesn't require Detailed View access.

After some brainstorming, considering the necessary data and guidelines, we decided to create a Support Case subject and a Support Case Survey event. We know that we need a table to store the support case data. Since each support case will have a status that progresses over time and could have long lifetimes and status changes, we should create a subject to store this information. We also need an event for tracking the survey data as the data happens at a particular point in time and is linked to a support ticket. We wouldn't build Support Case Survey as a subject since the response of the survey is relevant to a specific case and the surveys do not need to be tracked over an extended period of time. Instead, we need to track the survey scores at the time they are taken to understand the immediate feedback once the support case is completed, and be able to provide quick follow-ups.

The following diagram shows the subjects and events we need to create.

Note: In the above diagram, PK stands for primary key. A primary key is the unique ID that is auto-generated when you create a subject or event.

Support Case: A ticket or request submitted by a customer inquiring for help on an issue or problem they are experiencing.
Support Case Survey: A survey of a customer's experience of the support case process.

Now, we need to consider how the subject and event are connected to each other. Additionally, we need to connect the support case data with our existing employee data to know which team and customer support agent owns and resolves the most support tickets.

The following diagram illustrates how these elements are interconnected. For example, each support case is owned by one primary support agent (employee), but an employee could have many support cases. Therefore, the connection should be a forward reference from Support Case to Employee. However, to connect support cases to employees using a forward reference, we need the employee's ID. The sn_customerservice_case table we identified earlier does not provide employee ID. One way to solve for this problem is to use the Number field in a table from ServiceNow called sn_hr_core_profile that might have the employee ID loaded. However, this depends on the specific integration and configuration with ServiceNow within your organization. Since not every organization has this specific integration and configuration, we decided to use a more reliable design to avoid this uncertainty.

We can take advantage of the fact that both ServiceNow and Visier should contain the employee's work email address. The Email field is available in the sys_user table we identified earlier. To connect the two, we need to create a Support Case Assignee subject to serve as an intermediary table, linking Support Case to Employee. With this design, we can reliably connect support case data to employee data using the email field instead of employee ID.

Since Support Case Survey is an event, it is automatically associated with the subject it belongs to, in this case Support Case, so we don’t need to do anything extra to connect the two. For more information about references, see References.

Note: In the above diagram, FK stands for foreign key. A foreign key is the reference that connects with the primary key of another subject or event. It establishes the relationship between analytic objects.

What’s the difference between the Employee Cases subject and the Support Cases subject? Aren’t they both cases that have some kind of lifespan that can be analyzed?

If you have already loaded employee case data in your Visier solution, you may be wondering why we are creating a new subject rather than using the existing Employee Cases subject to load your support case data. While both Employee Case and Support Case tickets are cases, they have different purposes and origins. Employee Cases track internal issues between employees and focus on people analytics, with data sourced from a human management system. Support Cases, on the other hand, are customer service tickets centered around productivity and originating from a ticketing-based customer service management platform like ServiceNow.

Due to these fundamental differences, including their data sources and purposes, it wouldn't make sense to combine them for analysis, especially for survey-related business questions. Depending on the use case, it's sometimes best to make dedicated objects and metrics, so you can be explicit and specific. Ideally, we want to minimize future confusion for other users and administrators by creating additional subjects as needed during the design process.

Metrics and attributes

Next, let’s consider what metrics and attributes we need to create.

To answer a business question, we can create a metric. Metrics calculate a business concern that can be quantified as a number. Before writing a metric formula, ask yourself the following questions:

What business question do you want to answer? In this example, we can use the business questions we identified earlier.
What is the metric calculating? In Visier, a metric calculates at least one analytic object, such as Employee.
How does the metric handle time? In Visier, a metric must know what records to retrieve in a time period, such as all valid records in the selected time period or records that are valid at the end of the period.
Which records does it select? Usually, a metric calculates a specific attribute on the analytic object, such as gender or performance rating.
What type of calculation is it? In Visier, you have many aggregation options, such as count, average, and median.
What data type is the metric value? Metrics can return a value as an integer, a percent, in years, in months, as a number, and more.
Is the metric additive? Some metric values can be additive over any dimension or concept, like Headcount, or additive over time, like Employee Exit Count, or non-additive and cannot be summed over time, such as Average Headcount.

Additionally, we can create analytic object attributes (properties, dimensions, and concepts) that describe and give meaning to the object. To decide whether an attribute should be created as a property, dimension, or concept, we can review the following guidelines.

Create a property if:

The attribute evaluates the analytic object on a per-ID basis. For example, the Age property evaluates on each employee ID to provide an age for every employee.
The attribute won't be used as a group by in visualizations.
The attribute can be used in other objects, like metric formulas or dimensions.

Create a dimension if:

The attribute groups data. For example, the Age Range dimension groups employees into specific age ranges based on the Age in Months property.
The attribute will be used as a filter or group by in visualizations.
The attribute can be used in other objects, like concepts or custom dimensions.

Create a concept if:

The attribute gets its values from a property or dimension.
The attribute will be used as a filter or group by in visualizations.
The attribute reclassifies data from other objects into new groups, such as:
- Selects values that fit into the same group. For example, Millennials is a concept that selects all employees with a Birth_Date property between January 1, 1981 and December 31, 1996.
- Selects values to represent hierarchical relationships between calculations. For example, Employee Exit Model is a concept that groups exit-related calculations into a hierarchy where Resignation, Retirement, and Other Voluntary exits roll up to calculate Voluntary Turnover.
- Selects values to represent the processes or sequences that an analytic object goes through. For example, Applicant Process is a concept that groups applicants by the stages of their application, such as Applied, Screen, Interviews Started, Offered, and Background Checked.
- Selects values to represent movement that an analytic object experiences. For example, Employee Movement is a concept that groups changes to an employee's location or organization as a move in, out, or within a location or organization.

Considering these guidelines, let's look at the first question of how large is the support backlog. To answer this, we need to know the current number of open support tickets and how many of them are still unresolved. Based on this information, we need to create the following metrics:

Total Support Cases
Open Support Cases
New Support Cases

For completeness, let's decide what objects we want to create for each business question we identified earlier.

How many support tickets are we closing?

To answer this, we need to know how many tickets got resolved or closed in the period. Based on this information, we need to create the following metric:

Support Cases Closed in Period

How often are tickets escalated?

To answer this, we need to identify which tickets got escalated, and compare it with the total number of support cases to get a ratio. Based on this information, we need to create the following metrics:

Escalated Support Cases
Escalated Support Cases Ratio

What are the most common issues or support ticket types being reported?

To answer this, we don't need to create a new metric. Instead, we need to track the ticket type. In order to do this, we need to create a Support Case Category leveled dimension.

Which support agents are resolving tickets the fastest or most effectively?

To answer this, we need to know who is assigned to the ticket, how many tickets are currently open versus how many are older than a certain number of months. Based on this information, we need to create the following metrics:

Aged Support Cases
Aged Support Cases Closed in Period
Average Open Support Cases Age
Open Support Cases per Agent
Aged Support Cases Ratio
Employees with Open Support Cases
Support Cases Closed in Period

What is the overall customer satisfaction score for our support cases?

To answer this, we need to know the average response score for all customer responded surveys. Based on this information, we need to create the following metric:

Average Support Case Survey Response Score

This metric will also help us answer our other survey-related business questions:

How satisfied are customers with the resolution of their support case?
How would customers rate the performance of support agents who handled their cases?

Make calculation decisions

Now that we've determined which metrics to create, we need to define their value calculations. This determines how we write the metric formulas. To write a metric formula, we must be familiar with the Visier Formula Language and the structure of a metric formula. If you aren't familiar with the Visier Formula Language, take a moment to read Visier Formula Language (VFL).

Let's examine the key considerations that guide our calculation decisions.

How should we calculate closed in period?

It is important to be clear about what we are looking for when we say we want to count closed support cases. We could mean to count all the cases that are no longer open, or we could mean to count all the cases that became closed in the time period to see how much work was done in the past week or past month.

In our case, we are interested in the latter when building the Support Cases Closed in Period metric. We want to understand how many tickets were closed as a measure of the productivity of the support team over a given time period.

Therefore, we need to only count those cases with a Closed Date within the period. Using this logic, we can write the formula for Support Cases Closed in Period as follows:

Copy

on Support_Case validUntil instant filterBy(Support_Case.Closed_Date >= start(periodOf(instant)) && Support_Case.Closed_Date < end(periodOf(instant))) aggregate count(Support_Case.Support_CaseID)

How should we calculate customer satisfaction scores?

Every support case may result in a survey filled out by the customer to measure their satisfaction. This survey may have multiple questions, and each question may use a different rating scale. Some might be 1-5, and some might be 1-10. One good practice is to use normalized values because it is the closest column to create consistency among the various answers.

The normalized_value column from the asmt_metric_result table in ServiceNow already provides this value that we can load directly into the Response Score property. Therefore, the calculation of the Average Support Case Survey Response Score metric would be the average of this score. Using this logic, we can write the formula for Average Support Case Survey Response Score as follows: