In this blog series we’ll be experimenting with the most interesting blends of data and tools. Whether it’s mixing traditional sources with modern data lakes, open-source devops on the cloud with protected internal legacy tools, SQL with noSQL, web-wisdom-of-the-crowd with in-house handwritten notes, or IoT sensor data with idle chatting, we’re curious to find out: will they blend? Want to find out what happens when IBM Watson meets Google News, Hadoop Hive meets Excel, R meets Python, or MS Word meets MongoDB?
Follow us here and send us your ideas for the next data blending challenge you’d like to see at email@example.com.
Today: SugarCRM meets Salesforce. Crossing Accounts and Opportunities
Businesses use Customer Relationship Management (CRM) systems to keep track of all their customer related activities – creating leads and opportunities, managing contacts and accounts, sending quotes and invoices, etc. As long as it is somehow related to the stream of revenue, it is (or at least should be) stored in a CRM system.
Since there is more than one CRM solution on the market, there is a distinct chance that your organization uses multiple CRM platforms. While there might be sound reasons for this, it also poses a significant challenge: How do you combine data from several platforms? How do you generate a single, consolidated report that shows you how well the sales activities of your whole company are going?
One option is to export some tables, fire up your spreadsheet software of choice, and paste the stuff together. Then do the same thing next week. And the week after. And the week after that one (you get the point). Doesn’t sound too enticing? Fear not! This is KNIME, and one of our specialties is to save you the frustration of doing things manually. Fortunately, both SugarCRM and Salesforce allow their users to access their services via REST API, and that is exactly what we are going to do in this blog post.
There are a couple of prerequisites here. First of all, you obviously need accounts for SugarCRM and Salesforce. If you don’t have them but still want to try this yourself, you’ll be happy to see that both companies offer free trial licenses:
You can learn more about how to use the REST APIs of SugarCRM and Salesforce here:
Topic. Get a consolidated view of all customer data from two separate platforms
Challenge. Query data from SugarCRM and Salesforce via their APIs
Access Mode. KNIME REST Web Services
The Challenge here is to access data from a Salesforce CRM and data from a SugarCRM and blend them together. Luckily I have access to installations of both CRM systems.
The workflow depicted in figure 1 accesses a SugarCRM system (upper branch) and a Salesforce CRM system (lower branch). Let’s see which steps are required more in detail.
Accessing data from SugarCRM
SugarCRM can be accessed via REST API. The data can be retrieved via an authorized GET Request, that is a GET Request using an authorization token. Authorization token is obtained via a POST Request.
- The first step then consists of retrieving the authorization token via a POST Request. For the POST Request we need: the URL of the authorization endpoint and the parameters to fit in the request body. The request body has to be delivered in JSON format.
The first wrapped metanode in the upper branch of the workflow in figure 1, named “Parameter Input”, creates the appropriate request body in JSON format (see Figure 2).
- The required parameters are entered via String Input nodes: Username, Password, Client ID, Client Secret (it can be empty). Additionally, the URL of authorization endpoint shaped as described in https://<site_url>/rest/v10/oauth2/token, is also entered.
- After merging all flow variables and transferring all of them to a table, the column for grant_type is added through the Constant Value Column node. Option grant_type can assume many values, but for the purpose of this blog post, it will be “password”.
- The Columns to JSON node packages all parameter columns into a JSON object, which will be used as request body.
- After executing the “Parameter Input” wrapped metanode, we get a table with two columns: a JSON request body containing the credentials and the URL endpoint.
- Next, the JSON structure request body and the endpoint URL are fed into a POST Request node to get the access token. In the configuration window, we feed the URL column into the appropriate setting in the “Connection Settings” tab. Then, in the “Request Body” tab we feed the JSON column in “Use column’s content as body” setting. Let’s run the node. If everything works out, we should get a HTTP status code 200 as well as a JSON column named “body” as a response.
- The column named “body” is transformed back into a KNIME data table through the node JSON to Table. One of the resulting columns is named access_token and contains the authorization token we need to access SugarCRM data. This token is finally transformed into a flow variable, named “access_token”, and then injected into two GET Request nodes.
- The first GET Request node queries account data; the second GET Request node queries opportunity data. We configure the two GET Request nodes appropriately. For a list of available endpoints, see: http://support.sugarcrm.com/Documentation/Sugar_Developer/Sugar_Developer_Guide_7.9/Integration/Web_Services/v10/Endpoints/
URL is set as https://<site_url>/rest/v10/Accounts or https://<site_url>/rest/v10/Opportunities followed by “&” and some optional parameters.
Here, we only ask for five records (max_num=5) and only for records created by the current user (my_items=TRUE).
The final request URL would then be something like:
Hint. To get all available records, use max_num=-1.
In the Request Headers tab, click “Add header parameter”. For Header key, use “OAuth-Token”. For Header value, use “test” or some other placeholder. Next, go to tab Flow Variables, identify setting “Request header key selector”, and set “access_token” as string variable for item “0”.
Let’s now execute the node and check the results. Again, we want a 200 status and a JSON output column.
- The JSON we got as a response from the server has all the data we need. The JSON Path node will parse the JSON structure and extract the data we are interested in. For this example, I extract only Account ID, Account name, industry, country, and ID of the user assigned to this account.
Hint. Make sure to use a collection query rather than a single query since we want the data for all records.
- Next, the Ungroup node transforms the collection items into single data rows.
- Finally, the Constant Value Column node adds a source identifier, which is “SugarCRM”.
Accessing Data from Salesforce
In principle, the procedure to access data from Salesforce is very similar to the procedure used for SugarCRM.
- Also here we start with a “Parameter Input” wrapped metanode, similar to the one we used to access data from SugarCRM. Also in this metanode we enter the credentials to access the system. If you don’t know how to create Salesforce API credentials, have a look here: http://www.calvinfroedge.com/salesforce-how-to-generate-api-credentials/. Note that for Password, you need to use the Security Token.
- In Salesforce, all parameters need to be URL-encoded before reaching the body of the POST Request. The FormEncodedHttpEntityCreator node performs URL-encoding. This node is part of the KNIME Palladian extension. This node returns a binary column, and therefore we use a Binary Objects to Strings node to convert the parameter object back into String format.
- The parameter options in the newly generated String represent the body for the POST Request. Again in the POST Request node, we set the endpoint URL column and the parameter String column to use as Request Body. Here we also need to add a Request Header with Header Key “Content-Type” and Header value “application/x-www-form-encoded”.
Executing the POST Request node should also return a 200 status and a JSON column containing the access token.
- We then convert the JSON structured response to a KNIME table, we extract the access token and we pass it to the GET Request node in the shape of a flow variable.
For use as a request header, the access token needs to have the format “Bearer <access_token>”. This is done in the String Manipulation (Variable) node.
- Next we use a GET Request node to access the data within Salesforce. For a list of the objects and fields you can query, log in to Salesforce, go to Setup -> Objects and then Fields -> Object Manager.
- The query is entered in the URL field of the “Connection Settings” It takes the following format: https://<your_instance>.salesforce.com/services/data/v39.0/query?q=<query_string>
- The query string is an SOQL query. At first glance, SOQL looks very much like SQL, so people familiar with SQL syntax should not have any problems here (for more information, see here: https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql.htm).
- Here is an example query (note the spaces encoded as “+”):https://eu11.salesforce.com/services/data/v39.0/query?q=SELECT+Name,+AccountNumber,+OwnerId,+BillingAddress,+Industry+FROM+Account
- In the Request Headers tab, we add the authorization token. We click “Add header parameter”. As Header key, we use “Authorization”. As Header value, we use “test” or some other placeholder. Next, go to the Flow Variables tab, identify the Request header key selector field and set the flow variable containing the token for item “0”.
- Let’s run it and hope for a 200 status! If execution is successful, we get a JSON structured response containing the requested data.
- Also in this case, we rely on two GET Request nodes. One node extracts information about the Accounts and one node about the Opportunities in the system.
- Next, we use a JSON Path node to extract the relevant data.
- This is followed by some more data processing, after which we can join the account data with the opportunity data. To wrap things up, we add a column that identifies Salesforce as the source of this data.
Blending the data from Salesforce with the data from SugarCRM
The only thing left now is to concatenate the SugarCRM data and the Salesforce data. Will they blend? Indeed, we use a Concatenate node to put together the two sets of data.
The final workflow is reported in figure 1. It successfully blends data from SugarCRM and Salesforce and it is downloadable from the KNIME EXAMPLES server under:
Yes, they blend! Thanks to the REST API integration in KNIME Analytics Platform, we were able to extract data from a SugarCRM system and from a Salesforce system and to blend them in a single table (Fig. 3).
We can now create a nice report based on the extracted data, send it around, and make everyone happy - all those promising opportunities together at last.
Need to do the same thing again next week? Just re-execute the workflow! (Or let KNIME Server automate that for you). No more manual data exports, no more copy and paste, but a lot more time for other things.
In this blog post, we used the SugarCRM and Salesforce REST APIs to retrieve data from both systems. While this is a great thing to do in itself, there’s still a lot more to it! The GET Request and POST Request nodes are very powerful tools within the KNIEM Analytics Platform. You can access a number of other REST services, authenticated and not, available on the Internet, for reading, searching, extracting, and even modifying and writing records!
Coming Next …
If you enjoyed this, please share it generously and let us know your ideas for future blends.
We’re looking forward to the next challenge. For the next challenge we will tackle the world of quickly accessible databases, by trying to blend artificially simulated data and real world data into a single system. Will they blend? Will it be a cyborg?
* The link will open the workflow directly in KNIME Analytics Platform (requirements: Windows; KNIME Analytics Platform must be installed with the Installer version 3.2.0 or higher)