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

Note: if you do not have access to Salesforce, the template in the Files section has a CSV dataset containing generated account 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 a meaningful name (if desired), 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 the Attributes to Expose type in BillingCountry, then click OK.  

2. Read in Country Boundaries

We need to add spatial data to our Salesforce instance, as it currently lacks this information. 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 of 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 want to update your data, you can write out these null values to Microsoft Excel and then use it 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 'Join On Left' to 'name' and '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 few countries with a different naming convention than those 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 that your attribute mapping may differ depending on how your Salesforce Accounts are configured. 

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 quickly find countries in the navigation of 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 accomplish this, we will utilize the GradedColorizer custom transformer, which is available on 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 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 and labels to our output file. To place these correctly, we 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 assign 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.