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.
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:
- CaseHistory
- Case
It is also necessary to have access to the following master tables:
- Account
- Contact
- RecordType
- User
- UserRole
- Profile
For Due dates the following objects are necessary:
- CaseMilestone
- MilestoneType
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
- In CData - Connections, add a new source connection for Salesforce. If this option is not in the list, click + Add More, search for Salesforce and click Download & install
- Select Basic as the Auth Scheme.
- Enter the correct User, Password, Security token and Login URL for the account.
- Select API version to be at least 52.0.
- Depending on the Salesforce org settings, the connection may need to be configured to use the Sandbox environment. Go to the Advanced tab, section Authentication and set Use Sandbox to True.
- Create and test the connection.
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.
To create the Job in CData make sure to follow the steps below.
- In CData - Jobs, create a new job.
- Select the source and destination connections created for the Salesforce Account Management app template and create the job.
Advanced job settings
In the Advanced tab in the Job Settings panel, define the following settings:
- Select the Alter Schema option.
- Select the Drop Table option.
- If you are using Automation Suite, add the Destination Schema. Fill out the value you retrieved when you created the destination connection.
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
- Salesforce does not contain data related to "Event_start", "Case_value" and "Team".
Troubleshooting
- If any activity is missing, a new "Field" value needs to be identified and added in the CDATA replicate query, Case_history_input and the models included in the 3_events folder.
ncG1vNJzZmiZoKXBprnPpZitnaOZvKS%2FjZujqJpemLyzsY2woKecn6zAb7rErWadp5Oquqa605qroqeeZMKqvMCtn4KFg5a5pr%2FFqKmcnV%2BIrq2x0p%2Bmq5uVYpavr8idnKesXYKur63GnqSepqRjtbW5yw%3D%3D