Salesforce Incident Management

Process description

Salesforce calls incidents as Cases. the Incident Management process starts when the case is reported and it ends when the case gets solved.

The basic activities for the Incident Management process are opening new cases, assigning them to correct users, working on the issue, resolving and closing cases. Reassignments for the different users may occur during the process depending if the first assignment was correct, if the user assigned is actually the best option for resolving any specific case or if the case needs attention from multiple users during its resolution.

Available tags & due dates

The following tags are available by default.

Tag Description Motivation
Multiple assignments detected Cases that have multiple user assignments. This either represents a lack of skills of the first assigned user to resolve the Issue or inefficiencies in identifying the best qualified user to work on it.
More than 2 user role changes detected Cases that have more than two changes to the user role. Changes in the user role may represent fail attempts to assign the right user with the correct skill set and/or permissions to work on a case. This may also represent multiple teams assigned to work on it.
Priority change detected Cases that have got more than one 'Set Priority...' activity. The change in priority level is commonly used by support users to extend the time they have to solve an incident.
Ticket reopening detected Cases that got multiple activities 'Change Status to Solved'. The reopening of a "Solved" Incident might indicate that the solution provided for an Incident was not satisfactory. This could mean that the support user rushed to close it to comply with the SLA providing a less optimal service.
Ticket directly closed detected Cases that have only 'Create Case' and 'Change Status to Closed' as activities. Directly closing a case may signal that a case has been created unnecessarily or that the case has been worked on and resolved without having previously been registered in the system.
Multiple users for same activity A case has the same activity executed multiple times by different users.

Due dates

Salesforce keeps track of time based on a set of milestones that each case has to go through. These milestones are not standard, the milestones are defined and customized in the Salesforce's system.

Multiple Due dates based on all the milestone categories set in Salesforce are created automatically.

Extraction

This app template uses Salesforce Service Cloud with Salesforce Case Management as source system.

Extraction for the system is done using CData Sync. To set-up the extraction, make sure you have a valid license for CData Sync and have installed CData Sync.

System configuration

To be able to extract data from Salesforce the following configuration in Salesforce is required.

Case history tracking

For case history tracking, the minimal setup requires access to the following transactional objects:

It is also necessary to have access to the following master tables:

For Due dates the following objects are necessary:

API Access

In order to connect via CData, the user establishing the connection must be API Enabled, and must have appropriate read permissions on each of the objects which are being extracted. All fields included in the input tables must be visible for the user extracting the data. In case any fields are not visible, and may not be made visible, permissions should be granted to the user, or a profile with permissions should be used to extract the data.

Additionally, a security token should be generated for the user. This can be done by resetting the current security token in your personal settings, see Salesforce - Reset Your Security Token. The security token is required to establish the connection to Salesforce via CData Sync.

Setting up the source connection in CData Sync

Setting up the destination connection in CData Sync

To set up the destination connection, follow the steps as defined in Create a destination connection (Automation Cloud). Note: If you are using Automation Suite, make sure to select AUTOMATION SUITE as the DELIVERY option and select the correct version of Automation Suite you are using.

Creating the extraction job in CData Sync

To create the Job in CData make sure to follow the steps below.

Advanced job settings

In the Advanced tab in the Job Settings panel, define the following settings:

Setting up environment variables in the extraction job

Important: Make sure you edit the Pre-job Event.

CData Sync allows the use of environment variables in order to drive specific extraction logic in each query.

Variable Description Comment
start_extraction_date Defines first date for which data will be extracted. Mandatory
end_extraction_date Last date for which data will be extracted.

In order to setup the environment variables:

Step Action
1 Go to the Events tab in the Job Settings panel of the job you are creating
2 Edit the Pre-Job Event section to add the code displayed below after <!-- Code goes here -- >. Do not modify the api:info details that are shown by default.
3 Click on Save Changes. 
<!-- Modify environment variables here. -->
<!-- Variable start_extraction_date must be populated.--> 
<api:set attr="out.env:start_extraction_date"  value="2022-01-01" />
<!-- In case a specific end date is needed, replace the value string with the required date in yyyy-MM-dd format. -->
<!-- i.e api:set attr="out.env:end_extraction_date"  value="2022-02-01" -->
<api:set attr="out.env:end_extraction_date"  value="[null | now() | todate('yyyy-MM-dd')]" />
<api:push item="out" />

By default, end_extraction_date will default to today's date. start_extraction_date must always be populated.

Setting up the data ingestion call

After the data has been successfully loaded, the Process Mining platform needs to start the data ingestion process. This is done by calling the End of Upload API. In order to set this up, follow the steps below:

Step Action
1 Go to the Events tab in the Job Settings panel of the job you are creating
2 Edit the Post-Job Event section to add the code displayed below after <!-- Code goes here -- >. Do not modify the api:info details that are shown by default.
3 Fill out the End of Upload API with the value provided.
4 In case you need a debugging log for this call, uncomment the two lines provided in the code below and provide a location for the log file.
5 Click on Save Changes. 
<api:set attr="http.url" value="END_OF_UPLOAD_API"/> 
<!-- <api:set attr="http.verbosity" value="5"/> -->
<!-- <api:set attr="http.logfile" value="D:/mydir/cdata_log.txt"/> -->
<api:call op="httpPost" in="http"/>

Table Replication

Once the job is correctly setup, click on Add Custom Query under the Tasks tab and paste the following queries (each query needs to maintain the semicolon at the end). Make sure you save all changes.

Default Extraction

The following set of queries should be used for the default scenario where Record Types is not used and is disabled. In case this is enable, please add the query in the Alternative Scenarios section.

REPLICATE [Case] SELECT [Id], [AccountId], [CaseNumber], [ContactId], [CreatedById], FORMAT([CreatedDate], 'yyyy-MM-dd hh:mm:ss') as [CreatedDate], [HasCommentsUnreadByOwner], [IsClosed], [Origin], [OwnerId], [Priority], [Status], [SuppliedName], [Type] FROM [Case] WHERE ([CreatedDate] >= '{env:start_extraction_date}') AND ([CreatedDate] <= '{env:end_extraction_date}');
REPLICATE [Contact] SELECT [Id], [Name] FROM [Contact];
REPLICATE [RecordType] SELECT [Id], [Name] FROM [RecordType];
REPLICATE [User] SELECT [Id], [Department], [Name], [ProfileId], [UserType], [UserRoleId] FROM [User];
REPLICATE [UserRole] SELECT [Id], [Name] FROM [UserRole];
REPLICATE [Profile] SELECT [Id], [Name] FROM [Profile];
REPLICATE [CaseMilestone] SELECT [Id], [CaseId], FORMAT([CompletionDate], 'yyyy-MM-dd hh:mm:ss') as [CompletionDate], FORMAT([CreatedDate], 'yyyy-MM-dd hh:mm:ss') as [CreatedDate], [MilestoneTypeId], FORMAT([TargetDate], 'yyyy-MM-dd hh:mm:ss') as [TargetDate] FROM [CaseMilestone] WHERE ([CreatedDate] >= '{env:start_extraction_date}') AND ([CreatedDate] <= '{env:end_extraction_date}');
REPLICATE [Account] SELECT [Id], [AccountSource], [BillingCountry], [BillingState], [CreatedById], [Industry], [Name], [OwnerId], [Type] FROM [Account];
REPLICATE [MilestoneType] SELECT [Id], [Name] FROM [MilestoneType];
REPLICATE [CaseHistory] WITH ReplicateStartDate = '{env:start_extraction_date}', replicateenddate = '{env:end_extraction_date}' SELECT [Id], [CaseId], [CreatedById], FORMAT([CreatedDate], 'yyyy-MM-dd hh:mm:ss') as [CreatedDate], [Field], [NewValue], [OldValue], [DataType] FROM [CaseHistory] WHERE ([Field] IN ('Status', 'Priority', 'Contact', 'Owner', 'ownerAssignment', 'RecordType')) AND ([DataType] IN ('Text', 'DynamicEnum', 'RecordType'))

Note regarding CData Salesforce Provider Version 21.0.8097.0 When a table has an _Incremental Check Column_ field predefined in CData Sync, the SQL query cannot use the same date field in both statements (WITH and WHERE) at the same time. If version 21.0.8097.0 (or an older version) of CData is used, the date filtering should be done in the WHERE clause and should be removed from the WITH statement.

Alternative Scenarios

The following alternative scenario is available:

Record Types is enabled in Salesforce Org

In case Record Type is used the Case query must be amended to add the RecordTypeId field in the query. Replace the Case query with the following query:

REPLICATE [Case] SELECT [Id], [AccountId], [CaseNumber], [ContactId], [CreatedById], FORMAT([CreatedDate], 'yyyy-MM-dd hh:mm:ss') as [CreatedDate], [HasCommentsUnreadByOwner], [IsClosed], [Origin], [OwnerId], [Priority], [RecordTypeId], [Status], [SuppliedName], [Type] FROM [Case] WHERE ([CreatedDate] >= '{env:start_extraction_date}') AND ([CreatedDate] <= '{env:end_extraction_date}');

Input fields

The following tables include the list of fields per input table, their description, data type to be used when formatting the input and the filter flag to identify those that are being used to filter data.

Input types

Below is an overview of the different field types and their default format settings.

Field type Description
boolean true, false, 1, 0
date yyyy-mm-dd
datetime yyyy-mm-dd hh:mm:ss[.ms], where [.ms] is optional.
double Decimal separator: . (dot); thousand separator: none
integer Thousand separator: none
text N/A

Input tables & fields

CaseHistory

Table containing changes performed on each case. This is the historical table from which activities are created.

Field Stores Type Filtering
CaseId Case's key related to the change in the log history. text
CreatedById User's key that added the change to the log history. text
CreatedDate Timestamp that the change was added to the log history. datetime time-based filter
DataType Changed data's type. text in ('Text','DynamicEnum')
Field The type of change added to the log history. text in ('Status','Priority','Contact','Owner','ownerAssignment')
Id System's key for this table. text
NewValue Value added post change. text
OldValue Value previous to the change. text

Case

This is a transactional table for cases.

Field Stores Type Filtering
AccountId Account's key related to the case. text
CaseNumber Interface's case number. text
ContactId Customer's contact key. text
CreatedById User's key for the user that created the case. text
CreatedDate Date the case was created. datetime time-based filter
HasCommentsUnreadByOwner Identifier for unread comments in the case. boolean
Id System's key for this table. text
IsClosed Identifier for closed cases. boolean
Origin Channel by which the ticket was created. text
OwnerId User's key for the owner of the case. text
Priority Priority level of the case. text
RecordTypeId Record type key that categorizes cases. text
Status Last status fo the case. text
SuppliedName Customer's name. text
Type Case's type. text

Account

This table stores data related to the accounts from which cases are linked.

Field Stores Type Filtering
AccountSource The source of the account record. text
BillingCountry Country portion of billing address. text
BillingState State or province portion of billing address. text
CreatedById User's key who created the account, including creation date and time. text
Id System's key for this table. text
Industry Primary business of account. text
Name Account's name. text
OwnerId Account owner's key. text
Type Type of account. text

Contact

This object contains master data related to the case's contact.

Field Stores Type Filtering
Id System's key for this table. text
Name Contact's name. text

RecordType

This object contains master data related to the case's record type.

Field Stores Type Filtering
Id System's key for this table. text
Name Record type's name. text

User

This object contains master data related to users.

Field Stores Type Filtering
Department User's department. text
Id System's key for this table. text
Name User's name. text
ProfileId ID of the user's profile. text
UserRoleId ID of the user's role. text
UserType Type of user. text

UserRole

This object contains master data related to the user role.

Field Stores Type Filtering
Id System's key for this table. text
Name Name of the user role. text

Profile

This object contains master data related to the user profile.

Field Stores Type Filtering
Id System's key for this table. text
Name Name of the user profile. text

CaseMilestone

This object contains start dates and target dates for the main steps of the process. These milestones will be used for the due dates model.

Field Stores Type Filtering
CaseId Case's key related to the change in the log history. text
CompletionDate Date the Milestone was actually completed. datetime
CreatedDate Date the milestone was created. datetime time-based filter
Id System's key for this table. text
MilestoneTypeId MilestoneType's key related to milestone categories. text
TargetDate Expected date for completion of the milestone. datetime

MilestoneType

This object contains names of the milestones.

Field Stores Type Filtering
Id System's key for this table. text
Name Category names fot milestones. text

Configuring transformations

Seed files

Automation_estimates_raw

This seed file is used to add automation-related properties to each activity, used for the automation potential dashboard. For more information, see Simulating Automation Potential.

Field Type Description
Activity Text Display name for the activity
Event_cost Double Cost associated with the activity
Event_processing_time Integer Processing time associated with the activity (in milliseconds)

Due_dates_configuration_raw

This seed file is used to define properties for the due dates. For more information, see Due Dates.

Field Type Description
Due_date Text The name of the due date
Due_date_type Text The Due date type
Fixed_costs Boolean An indication whether costs are fixed or time based
Cost Double Fixed costs: The amount of costs. Variable costs: The amount of costs per Time and Time_type
Time Integer A number indicating the amount of time in case of time-based costs
Time_type Text Type of time period for cost calculations. This can be any of the following values: day, hour, minute, second or millisecond

Automated_users_raw

This file is used to set the users that are automated.

Field Type Description
User Text User that is considered automated.

Dbt variables

Below is an overview of the variables that must be configured based on the Salesforce configuration for the end customer. These variables are defined in the dbt_project.yml file.

Variable Type Description
date_format string Format for parsing date fields.
datetime_format string Format for parsing datetime fields.
use_record_types boolean Defines whether the record type conversions will be used. This should be disabled in Salesforce Orgs where record types are not used. Setting this to true will enable record types to be queried and added to the transformations. Default is false.

Design specifications

Objects

Salesforce Service Cloud has only one object and is created based on the "Case" table.

Object Input data
Cases Case

Activities

The activities are created from records in CaseHistory table. The activities are based on the "Field" field in Case_history_input. Some activities are also based on "Old_value" and "New_value" fields. The list of activities is the following:

Activity Description
Assign Owner Assign user responsible for working on the case.
Auto-assign Owner Auto-assign user responsible for working on the case.
Change Record Type to... Contains a set fo activities related to Record Type of cases.
Change Status to... Contains a set fo activities related to statuses of cases.
Change Contact Change clients contact user data.
Create Case Create a new case.
Remove Contact Remove clients contact user data.
Set Contact Set clients contact user data.
Set Priority to... Identify priority level.

Design details

Limitations

Troubleshooting