Dream to Learn is shutting down...

We are very sorry to say that Dream to Learn will be shutting down as of December 28th, 2019. If you have content that you wish to keep, you should make a copy of it before that date.


1COMMENTS1RECOMMENDS

An Idiot's Guide to Integrating SALESFORCE with R - Part 3 - Dashboard Destiny

84
POSTED IN: Data Analytics & Visualization Blog

 

This is Part 3 of 3 of a set of blogs where I wanted to answer the question "Can people leverage SalesForce Dashboards as an extension and distribution mechanism for R reports in their companies"  

The short answer is, with some tuning YES

Part 1 We surveyed the tools - though the first package in R seems to be pretty solid and did not go much further.  Part 2 was basic read write.  Part 3 pulls it all together.

It's getting late, so mainly want to get thoughts down before bed - refine later perhaps

  • - The library(RForcecom) does everything you need it to do
  • - SalesForce is a bit fiddly for someone new to it, but after about 2 hours, you can figure most of it out
  • - Dashboards Can be set up and saved

SalesForce-Side Import tools

As I was getting to know the SF tools - I checked out several "Wizards" and Import tools - to test a very ligthtweight data set to see if I could even get the charts working (I did, with some fiddling) 

Salesforce can accept the data, but BECAUSE IT IS TUNED FOR SF/SALES - AND NOT BUILT FOR A GENERIC DASHBOARD - Need to "hack" a little - and work with what is here. At least given what I know, I had to.

For example, there is no wildcard integer to count element - that I could see, so I used ANNUAL REVENUE as a nice Integer, that is graphable, to import my number magnitude.

Option #1 - YOUR_NAME-> MY SETTINGS -> IMPORT -> DATA IMPORT WIZARD

Be careful, SF will want your field names to match SF fields - OR BE READY TO MAP on import.  View a list of Salesforce data fields.

Option #2 - SETUP -> ADMINISTER -> DATA MANAGEMENT -> CROSS ORG DATA -> DATA IMPORT WIZARD or DATA LOADER (did not try)

Also checked this out - some Ok background - not required: http://www.salesforce.com/_app/video/data_loader/help/Overview_video.jsp 

 

Import Wizard

There is an import wizard for SF - recommend playing with this with a simple CSV first - get the plain vanilla data coming in.  Learn fields and nuances.

 

CSV text - pay attention to Field Names

 

If some misalignment - you must map fields to fields

 

REPORTS - CREATE AND SAVE

  • Create a new LEADS report to be the SF fuel for dashboards
  • 1- FILTER down to what you want
  • 2 - SAVE
  • 3 - if you want to GRAPH - you will need to GROUP. 

 

 

We are landing data in "LEADS" for this example.

 

Here is Report of Leads - can filter

 

REPORTS VIEW - can filter here and launch chart IF there is grouping done. and summary.

 

 

CHART - After grouping, grey goes away and can chart. Group + Sum by Revenue

text

 

 
Just testing dashboard - using manual/wizard input CSV  
 

 
Data:
 

 
PERMISSIONS - I was having problem and tweaked modify all data.  made other changes. Got it working, so unclear if this needs change. 
 

 

 
WRITE FROM R - 1 Record! (works) 
 

 
Dashboard with Dummy Data + 1 new record (works!) 
 

 

 
GOV Data (statenames) - new - here is what it looked like after brief R upload. 
 

 

RESULTS (Works!)

 

CODE  

######### Idiot's Guide to Integrating SalesForce & R  - PART 3
#  First section guided by instructions here: https://hiratake55.wordpress.com/2013/03/28/rforcecom/
#  IF you want to go a little deeper - this is some 'long form' code to play with
#########

# install.packages("RForcecom") # # this installed OK for me on 4/26/2015 R 3.1.2 running on a PC Win 7
  library(RForcecom)

username <- "ryan(at)dreamtolearn( dot )com"
password <- "PASSWORD+KEY"
instanceURL <- "https://na24.salesforce.com/"
apiVersion <- "27.0"  ## left this default - did not check
session <- rforcecom.login(username, password, instanceURL, apiVersion)
#session

rforcecom.getServerTimestamp(session)

## BASIC TEST - working? connected?
soqlQuery <- "SELECT Id, Name, Phone FROM Account WHERE AnnualRevenue > 50000 LIMIT 5"
rforcecom.query(session, soqlQuery)


################# 
##### Ok - one more (you can keep going :)  - # You can play with other Objects --
# Contact, Account, User, Task, and Note all work fomr here:
# https://www.salesforce.com/developer/docs/api/Content/sforce_api_objects_list.htm 
# but some do not (not sure why)

#####  LAST ONE TASK$NAME
objectName <- "Lead"
obj_desc <- rforcecom.getObjectDescription(session, objectName) # 35 columns
obj_desc <- data.frame(obj_desc$name) # kill everything except label
obj_desc # these are the 35 attributes for USER
# Includes
'
            obj_desc.name
4                LastName
7                    Name
9                 Company
18                  Email
25          AnnualRevenue
26      NumberOfEmployees
47             SICCode__c
'

# OK - I went into SF UI and created 4 dummy tasks and want to see a read (included "fresh apples" and "fresh pears" in two comment sections)
############### SEARCH RECORD
queryString <- "DEF" # lets' find the CSV records for this task we pushed manually
rforcecom.search(session, queryString)

###### METHOD ONE: PULL DATA OUT (Works)
objectName <- "Lead"
fields <- c("Name","Company","Email","AnnualRevenue")
data <- data.frame(rforcecom.retrieve(session, objectName, fields))
# if you do this, you need to subset the data you want
data <- data[ which(data$Company =='DEF'), ] # subsets the junk and jusr our focus
data
###### METHOD TWO: SQL (Works)
soqlQuery <- "SELECT Name, Company, Email, AnnualRevenue FROM Lead WHERE Company = 'DEF'"
rforcecom.query(session, soqlQuery)


##### OK - great - that's reading - how about writing? # let's try just one
objectName <- "Lead"
fields <- c(
        LastName = "David Hasselhoff", 
        Company = "DEF",
        Email = "great_swimmer@baywatch.com",
        AnnualRevenue = "1234567"
        )
fields
rforcecom.create(session, objectName, fields)

## Woo hoo!  Mini Hero moment! - works.  Blessed be the hoff.

######### let's create some dummy data "GOV"
library(stringr)
dataset <- (tolower((sample(state.name))))
dataset <- str_replace_all(dataset, fixed(" "), "")
dataset <- data.frame(dataset)
dataset$LastName <- NULL
dataset$LastName <- dataset$dataset
dataset$Email <- paste(dataset$dataset,"@",dataset$dataset,".gov", sep="")
dataset$Company <- c("GOV")
dataset$AnnualRevenue <- sample(1000:1000000, (dim(dataset)[1]), replace=F)
dataset$dataset <- NULL # dont need the seed anymore. thanks
head(dataset)
# Looks like this
#LastName                           Email Company AnnualRevenue
#1          iowa                   iowa@iowa.gov     GOV         64531
#2    california       california@california.gov     GOV        761985
#3    washington       washington@washington.gov     GOV         39494

### OK - can we push this to SalesForce mothership?

##### OK - great - that's reading - how about writing? # let's try all of them
objectName <- "Lead"
dataset <- data.frame(dataset)
head(dataset)

for (i in 1:50 ) {
  fields <- c(
    LastName = paste(dataset$LastName[i]), 
    Company = paste(dataset$Company[i]),
    Email = paste(dataset$Email[i]),
    AnnualRevenue = paste(dataset$AnnualRevenue[i])
  )
  fields
  rforcecom.create(session, objectName, fields)
}
# WORKS!
# and when we go to Salesforce dev env - data is there, 
soqlQuery <- "SELECT Name, Company, Email, AnnualRevenue FROM Lead WHERE Company = 'GOV'"
rforcecom.query(session, soqlQuery)
# WORKS

  

 

 

Interested in more content by this author?

About the Author

Ryan Anderson

Ryan Anderson

Hi! I like to play with data, analytics and hack around with robots and gadgets in my garage. Lately I've been learning about machine learning.

About this blog

Description is...<br/>Data Analytics & Visualization Blog - Generating insights from Data since 2013

Created: July 25, 2014

Englishfrançais

Up Next