|
SAS Institute
|
|
JMP Orion Integration (JOI)
Documentation
|
|
April 3, 2018
|
|
Version 2.0
|
|
d-Wise Technologies, Inc
www.d-wise.com
1500 Perimeter Park Dr.
Morrisville, NC 27650
lifesciences@d-wise.com
+1.919.600.6230
|
|
Contents
1 Execution
2 Environment Settings
3 Configuration File
3.1 Logging
3.2 Connection to Salesforce
3.3 Connection to Orion
3.4 Allowed Offerings
3.5 Fields Fetched from Salesforce
3.6 Default Values for Orion Fields
3.7 Mapping Rules
3.8 Processing Filter for Testing
4 Mapping Rules
4.1 Mapping Container Objects
4.2 Mapping Arrays
4.3 Mapping Properties
1 Execution
JOI is started by executing a batch file named joi.bat. The batch file navigates to the JOI program folder and executes the main JOI program. Below is a sample of the batch file contents:
For production use of JOI, the program must be executed repeatedly at specified intervals, such as every 5 minutes. This can be accomplished by using a scheduling program (such as the Windows Task Scheduler) to set up a repeating task that executes the batch file.
2 Environment Settings
JOI environment settings are kept in a separate SAS program named joi_environment_settings.sas. This program must be in the same folder as the main JOI program. It is executed by the main program via an %include statement.
The only environment setting needed for JOI is to identify the root folder where JOI is installed. The setting is specified by assigning the folder to a macro variable named root, as in the following example:
%let root=C:\Users\dhopkins\Documents\Projects\SAS_Institute\JOI\2.0;
3 Configuration File
JOI configuration settings are kept in a separate SAS program named joi_config_settings.sas. This program must be in the same folder as the main JOI program. It is executed by the main program via an %include statement.
The configuration settings allow many aspects of JOI’s behavior to be modified without changes to the core programs. Configuration settings include these areas:
- Logging
- Connection to Salesforce
- Connection to Orion
- Allowed Offerings
- Fields Fetched from Salesforce
- Default Values for Orion Fields
- Mapping Rules
- Processing Filter
3.1 Logging
When JOI is executed it logs processing information to a log file. The log files are rotated, i.e., JOI creates a new log file on each day it is executed and deletes log files that are older than a specified number of days. The following macro variables contain configuration settings related to logging:
|
Macro Variable
|
Description
|
|
logFolder
|
Folder in which log files will be stored
|
|
logFilePrefix
|
Prefix to be used in constructing names of log files. Log files are named by adding a date to the prefix, as in the following example where “JOI” is the prefix:
JOI-2018-02-08.log
|
|
logDuration
|
Number of days to retain log files before deleting them
|
3.2 Connection to Salesforce
JOI exchanges data with Salesforce by sending requests to the Salesforce REST service. The macro variables listed below contain configuration settings used to locate and authenticate to the service. Note that the configuration file contains two versions of these settings: one for accessing test data and one for accessing production data. One version of the settings should be commented out, depending on whether the program is being executed for testing or for production use.
|
Macro Variable
|
Description
|
|
salesforceBaseUrl
|
Base URL used in constructing REST endpoints
|
|
salesforceConsumerKey
|
Consumer key
|
|
salesforceConsumerSecret
|
Consumer secret
|
|
salesforceUsername
|
User name
|
|
salesforcePassword
|
Password
|
|
salesforceSecurityToken
|
Security Token
|
|
salesforcePayloadFolder
|
Folder in which to store files containing payloads for requests to Salesforce
|
3.3 Connection to Orion
JOI exchanges data with Orion by sending requests to the Orion REST service. The macro variables listed below contain configuration settings used to locate and authenticate to the service.
|
Macro Variable
|
Description
|
|
orionBaseUrl
|
Base URL used in constructing REST endpoints
|
|
orionPrincipalUser
|
Principal user
|
|
orionAccessKey
|
Access key
|
|
orionSecretKey
|
Secret key
|
|
orionPayloadFolder
|
Folder in which to store files containing payloads for requests to Orion
|
|
orionApiVersion
|
Version of the Orion API to use when making requests
|
3.4 Allowed Offerings
When posting a new opportunity to Orion, JOI must include the Orion ID for the opportunity’s offering. To facilitate this, the configuration program sets up a list of offerings that are allowed, and the Orion IDs that correspond to them. Allowed offerings are added to the list by invoking a macro called allowOffering. The macro takes two parameters, the name of the offering (text) and the ID of the offering (integer). Below is an example that adds three offerings to the list:
%allowOffering(JMP, 1099);
%allowOffering(JMP Genomics, 1371);
%allowOffering(JMP Clinical, 2131);
3.5 Fields Fetched from Salesforce
To construct the payload for a request to Orion, JOI must fetch specific fields from five Salesforce tables:
- Opportunity
- Account
- User
- Contact
- Offering
The configuration program specifies the fields that will be fetched from each of these tables. It does this by creating an empty dataset corresponding to each table, which includes the fields needed from that table. Below is an example that specifies the fields needed from the Account table:
data sfAccountVars;
length
Id $255
Orion_Account_ID__c $255
ref4__c $255
Salesgroup__c $255
;
call missing (of _all_);
run;
Additional fields can be fetched from the Account table (should this become necessary) by adding them to the lengthstatement in the example above. Note that the variables in the length statement must have the exact same names as the fields in the Salesforce table.
3.6 Default Values for Orion Fields
Some of the fields needed to post an opportunity to Orion are set to default values, rather than data obtained from Salesforce. These fields have the same value for every opportunity posted to Orion. The configuration program specifies the values that will be used for these fields, by assigning them to the following macro variables:
|
Macro Variable
|
Orion Field
|
|
defaultStrategicInitiativeId
|
|
|
defaultType
|
/root/opportunity/type
|
|
defaultInputSourceType
|
/root/opportunity/inputSourceType
|
|
defaultMultinationalDeal
|
|
|
defaultComitCreate
|
|
|
defaultRoleId
|
|
|
defaultCustBusObjId
|
/root/opportunity/customerBusinessObjectiveId
|
|
defaultCustBusObjGroupCode
|
/root/opportunity/customerBusinessObjectiveGroupCode
|
|
defaultLikelihoodToCloseId
|
/root/opportunity/likelihoodToCloseId
|
|
defaultMarkedPrivate
|
/root/opportunity/markedPrivate
|
3.7 Mapping Rules
The configuration program includes a set of mapping rules that define the JSON structure that will be used for the Orion payload, and the data that will be used to populate the JSON structure. Three macros are used in the creation of mapping rules, mapJsonObject,mapJsonArray, and mapJsonProperty. The complete set of rules is set up by a sequence of calls to these macros. Details on how to use the mapping rule macros are belowin section 4.
3.8 Processing Filter for Testing
The configuration program provides a processing filter that can be used during testing to limit processing to a specified subset of opportunities. The filter is enabled by assigning a condition clause to the opportunityListFiltermacro variable. The following filter example limits processing to the opportunity with ID=’00629000002SiZnAAK’:
%let opportunityListFilter=id%str(=)%str(%')00629000002SiZnAAK%str(%');
Note that special characters should be masked with the %strfunction, as shown above.
The processing filter should be set to blank during production use of JOI, as in the following example:
%let opportunityListFilter=;
4 Mapping Rules
Mapping rules are used to define the JSON structure that is needed for the Orion payload (i.e., the data sent to Orion to post a new opportunity). The mapping rules define the elements in the JSON structure and the data used to populate each element.
There are three type of elements that can be defined with a mapping rule, and they correspond to the three macros used to create rules:
|
Element Type
|
Macro Name
|
Call Syntax
|
|
Container object
|
mapJsonObject
|
%mapJsonObject(
parent=<parent element>,
key=<element key>
);
|
|
Array
|
mapJsonArray
|
%mapJsonArray(
parent=<parent element>,
key=<element key>,
arraySource=<dataset name>
);
|
|
Property
|
mapJsonProperty
|
Syntax 1:
%mapJsonProperty(
parent=<parent element>,
key=<element key>,
value=<value>
);
Syntax 2:
%mapJsonProperty(
parent=<parent element>,
key=<element key>,
valueSource=<value source>
);
|
4.1 Mapping Container Objects
Containers in JSON are designated by curly braces enclosing the contained elements. All JSON structures include at least one container, i.e., the top-level container (called “/root”). Other containers may be nested within the root container or within other nested containers. The root container does not require a mapping rule in JOI, but all nested containers do. Consider the following JSON example, which includes three nested containers (bold text).
{
"opportunity": {
"accountId": 5110985,
"amount": 530000,
},
"salesGroup": "JP",
"comit": {
"firstPeriodTerm": null,
"dealLength": null,
"form": {
"mlaNeeded": null,
"supplementNeeded": false,
},
}
}
The mapping rules for this example would include three calls to the mapJsonObjectmacro. Each call would specify the key of a container element and the parent under which it is nested, using the following syntax:
%mapJsonObject(
parent=<parent element>,
key=<element key>
);
The parent argument is specified using a syntax similar to a linux folder path, where forward-slashes are used to separate path elements. Thus the nested containers in the example above could be mapped with the following three macro calls:
%mapJsonObject (parent=/root, key=opportunity);
%mapJsonObject (parent=/root, key=comit);
%mapJsonObject (parent=/root/comit, key=form);
4.2 Mapping Arrays
Arrays in JSON are designated by square brackets enclosing the array elements. JOI builds an array by first creating a SAS dataset that contains the records, fields, and values needed to populate the array, then using the dataset as the arraySourceargument in a mapping rule.
The JSON example below includes one array (bold text) that contains two objects, each of which contains three elements. Note that the structure of the array is similar to a SAS dataset containing two records and three fields:
{
"cdhubId": 999,
"accountNameId": 999,
"inputSourceType": "foo",
"ips": [
{
"employeeId": 123,
"roleId": "123_role",
"territoryId": "123_territory",
},
{
"employeeId": 234,
"roleId": "234_role",
"territoryId": "234_territory",
}
],
"comitCreate": true,
}
The mapping rules for this example would include one call to the mapJsonArraymacro. Using the syntax shown below, the call would specify the key of the array element, the parent under which it is nested, and a dataset that will contain the data used to populate the array:
%mapJsonArray(
parent=<parent element>,
key=<element key>,
key=<dataset identifier>
);
The array in the example above could be mapped with the following macro call:
%mapJsonArray (parent=/root, key=ips, arraySource=opportunityIpsJson);
For this mapping rule to work, JOI would need to create the indicated dataset prior to constructing the Orion payload. (Note that creation of array datasets is handled by the createJsonComponentDatasetsmacro). For the JSON example shown above, JOI would need to create a dataset that contains the following records, fields, and values:
|
employeeId
|
roleId
|
territoryId
|
|
123
|
123_role
|
123_territory
|
|
234
|
234_role
|
234_territory
|
Note that the elements within an array do not require separate mapping rules in JOI. The array content is fully defined by the data in the SAS dataset. For the JSON example above, there would be no need to specify mapping rules for employeeId,roleID, or territoryId.
4.3 Mapping Properties
The simplest JSON elements to create are called properties. Each property is a key-value pair. JOI requires mapping rules for all properties that are not contained in arrays. The following JSON example includes eight properties that need mapping rules (green text):
{
"opportunity": {
"accountId": 5110985,
"amount": 530000,
"ips": [
{
"employeeId": 123,
"roleId": "123_role",
"territoryId": "123_territory",
}
],
"comitCreate": true,
},
"salesGroup": "JP",
"comit": {
"firstPeriodTerm": null,
"dealLength": null,
"form": {
"mlaNeeded": null,
"supplementNeeded": false,
},
}
}
The mapping rules for this example would include eight calls to the mapJsonPropertymacro. As shown below, there are two ways to call this macro, depending on how the value is obtained for the key-value pair:
Syntax 1:
%mapJsonProperty(
parent=<parent element>,
key=<element key>,
value=<value>
);
Syntax 2:
%mapJsonProperty(
parent=<parent element>,
key=<element key>,
valueSource=<value source>
);
The first syntax is used when the value being assigned is either a literal value or a value contained in a macro variable, as in the following examples:
%mapJsonProperty (parent=/root/opportunity, key=accountId, value="5110985");
%mapJsonProperty (parent=/root/opportunity, key=comitCreate, value=&defaultcomitCreate);
The second syntax is used when the value being assigned is obtained from a salesforce variable or by executing a SAS macro, as in the following examples:
%mapJsonProperty (parent=/root/opportunity, key=amount, valueSource=salesforce:opportunity.amount__c);
%mapJsonProperty (parent=/root/comit, key=dealLength, valueSource=macro:assignDealLength);
The properties that need mapping rules in the example above could be mapped with the following eight macro calls:
%mapJsonProperty (parent=/root/opportunity, key=accountId, value="5110985");
%mapJsonProperty (parent=/root/opportunity, key=amount, valueSource=salesforce:opportunity.amount__c);
%mapJsonProperty (parent=/root/opportunity, key=comitCreate, value=&defaultcomitCreate);
%mapJsonProperty (parent=/root, key=salesGroup, valueSource=salesforce:account.Salesgroup__c);
%mapJsonProperty (parent=/root/comit, key=firstPeriodTerm, valueSource=macro:assignFirstPeriodTerm);
%mapJsonProperty (parent=/root/comit, key=dealLength, valueSource=macro:assignDealLength);
%mapJsonProperty (parent=/root/comit/form, key=mlaNeeded, valueSource=salesforce:opportunity.mla__c);
%mapJsonProperty (parent=/root/comit/form, key=supplementNeeded, valueSource=salesforce:opportunity.supplement__c);
