Using the SalesforceConnector to See Account Data on a Map

Introduction

FME can read Salesforce data using the Salesforce reader, the SalesforceConnector transformer, or using the REST API in an HTTPCaller transformer. In this example, we will use the SalesforceConnector to read in account information (BillingCountry) then style the data before writing it out to KML, which we can view in Google Earth.  
 

Requirements

• Access to Salesforce with permissions to read data

Step-by-step Instructions 

1. Connect to Salesforce
Open FME Workbench and start a blank workspace. Add a Creator to the canvas then attach a SalesforceConnector. 
In the SalesforceConnector parameters, click on the drop-down for Salesforce Account, then select Add Web Connection. In the Salesforce Connection dialog, change the Connection Name to something meaningful (if you desire), then click Authenticate. 

Next, the Salesforce Login dialog will appear, log in to your Salesforce account using your username and password. If your company uses a custom domain in Salesforce, see Connecting to Salesforce in FME to set up the Web Service before continuing.

After creating the connection, in the SalesforceConnection parameters, set the SOQL SELECT Statement to: 

SELECT BillingCountry FROM Account

If you need help creating the SOQL statement, Salesforce has a handy tool called Workbench, where you can easily create your statement or REST calls.
Finally, for Attributes to Expose type in BillingCountry, then click OK.  


2. Read in Country Boundaries
We need to add spatial data to our Salesforce as there currently is none. Let’s add a dataset containing country boundaries. Add an Esri Shapefile reader to the canvas and browse to the countries.shp dataset which is available for download from the Files section on this article. 

 
3. Remove Null Data
Some of the accounts coming from Salesforce don’t have an associated BillingCountry, so let’s remove those. Add a Tester to the canvas and connect it to the SalesforceConnector. In the parameters, set the test to BillingCountry Attribute Has a Value. 

Any records that are null will be filtered to the Failed output port. If you wanted to update your data, you could write out these null values to Microsoft Excel then use that to review your records in Salesforce. 
 
4. Join Datasets
Now that we’ve removed the null values, we can join our datasets based on the country name. Add a FeatureJoiner to the canvas and connect the Countries reader feature type to the Left input port and the Tester Passed output port to the Right input port. 

In the FeatureJoiner parameters, set the Join On Left to name and the Right to BillingCountry then click OK. 

 
5. Inspect Output
Let’s run the workspace and confirm the countries match up. Run the workspace using Feature Caching then inspect the UnjoinedRight output port. In this Salesforce dataset, we have a couple of countries with a different naming convention then what is in the Countries dataset. Let’s fix that.
 
6. Map Country Attributes
Add an AttributeValueMapper transformer to the canvas and connect it between the Tester Passed Port and the FeatureJoiner. 


In the AttributeValueMapper parameters, set the Source Attribute to BillingCountry, then set the Destination Attribute to BillingCountry. Additionally, since we don’t want to map all of the attributes, set the Default Value to BillingCountry. 
For the Value Map set the following:

Note, your attribute mapping may be different depending on how your Salesforce Accounts are set up. 
 
6. Count Accounts Per Country
Before we start styling the data, let’s create a count attribute so we know how many accounts are associated with each country. Add an Aggregator to the canvas between the AttributeValueMapper and the FeatureJoiner. 
In the parameters, enable Group Processing, then Group By BillingCountry. Next, set the Count Attribute to Count and click OK. 

 
7. Style KML Content Balloons
The data is now ready to be styled. First, let’s style the KML Content Balloons. Add a KMLPropertySetter to the canvas and connect it to the Joined output port on the FeatureJoiner. In the parameters, set the Name to:

@Value(name): @Value(Count)

Next for Descriptive Balloon, change Content Type to HTML, then set Content to:

<b>Number of accounts: @Value(Count)</b>

Finally, change Include Attribute Table to No, then click OK. 

 
8. Sort Countries Alphabetically
To find the countries quickly in the navigation in Google Earth, let’s organize them alphabetically. Add a Sorter to the canvas and connect it to the KMLPropertySetter. In the parameters, set the Attribute to name and the Alpha/Num to Alphabetic Ascending. 

 
9. Color Based On Count
Let’s color the countries based on the number of accounts in that country. To do this, we will use the GradedColorizer custom transformer which is available from FMEHub.  Add a GradedColorizer to the canvas and connect it to the Sorter. In the parameters, set the Attribute to Color On to Count, then set the Color Scheme to Rainbow. Next, set the Classification Method set to Quantile then set the Number of Intervals to 10. 


10. Style KML Areas
We need to assign the attributes created with the GradedColorizer to KML specific attributes. You can do this manually with an AttributeManager or use the KMLStyler. Add the KMLStyler to the canvas and connect it to the GradedColorizer. In the parameters, set the Color to fme_color and the Fill Color to fme_fill_color, then click OK. 

 
11. Write to KML
The countries are ready to be written out to KML. Add an OGC/Google KML writer to the canvas. Browse to a location to save the dataset then name the file AccountsByCountry.kml. Set the Feature Type Definition to Automatic, then click OK. 


In the Feature Type dialog, set the Feature Type Name to Areas then click OK. Connect the Areas writer feature type to the KMLStyler. 

 
12. Create Center Point
We will be adding a KML diagram as well as labels to our output file, but to place these correctly we will need to determine the center point of each country. Add a CenterPointReplacer transformer to the canvas and connect it to the Sorter. We can accept the default parameters. 
 
13. Create Diagram
Next, let’s create a KML diagram. Add the KMLDiagrammer custom transformer to the canvas and connect it to the CenterPointReplacer. In the parameters, set the Z Value Attribute to Count and the Z Value Scale to 1000, then click OK. 

 
14. Remove Attribute Table
There is another KMLPropertySetter in the KMLDiagrammer custom transformer which overwrites what we input into the KMLPropertySetter we set up in Step 7, specifically removing the Attribute Table from the Content Balloon. This attribute can be manually changed using an AttributeCreator. Add an AttributeCreator to the canvas and connect it to the KMLDiagrammer. In the parameters, create a new parameter called kml_attr_in_description and give it the value of no. 


15. Write Diagram to KML
The diagram is ready to be written out. On the top menu bar go to Writers > Add Feature Type to add the writer. In the Feature Type dialog, change the Feature Type Name to Diagrams, then click OK. Connect the Diagrams writer feature type to the KMLDiagramer output port. 

16. Run Workspace and View Output in Google Earth
Run the workspace, then view the output dataset in Google Earth.