Extract Salesforce Data into Analytics
Extract Salesforce Data Overview
You just took the first step to get the data you need into Analytics for the sales leadership team. They want to present their performance results when the executive team meets with the new CEO. But the CSV data is just a small part of what they need. Most of the data that interests them is already inside Salesforce—in the Opportunity, Account, and User objects. Now you bring this Salesforce data into Analytics.
Here’s where you are on the data journey.
Say Hello to the Dataflow
Maybe you’re thinking, JSON—ack! Do we have to write code? The good news is that you don’t really need to know anything about JSON. Analytics has tools that write these instructions for you, and you’re going to be using one of them.
A dataflow is not single use. You can use it to create lots of datasets from lots of different objects at the same time. You can also schedule it to run regularly to keep the datasets up to date. Since there’s a chance the dataflow is already in use, it’s a good idea to make a backup before you add new instructions. The dataflow in your Developer Edition org isn’t already in use, but let’s back it up anyway to see how it’s done.
- In Analytics, click the gear icon ( ) and then click Data Manager. The data manager opens in a new browser tab.
- In the data manager, click the Dataflows & Recipes tab.
- On the right of the Default Salesforce Dataflow, click and select Run Now. This runs the dataflow.
- Click Monitor to check the dataflow run's progress. Uh oh—the Default Salesforce Dataflow run failed. Based on the error message, Data Sync has not pulled fresh data from Salesforce into Analytics. Let's manually sync the data.
- Click the Connect tab.
- To the right of the SFDC_LOCAL connection, click and select Run Now. This runs Data Sync for the entire connection, pulling data for all the listed objects from your local Salesforce org into Analytics.
- Click the Monitor tab to track progress of the data sync. Refresh the list until all objects sync with a Successful status message. Now there is a fresh copy of the Salesforce object data in Analytics for the dataflow to use. Let's get back to the dataflow!
- Click the Dataflows & Recipes tab.
- On the right of the Default Salesforce Dataflow, click and select Run Now.
- After the dataflow run completes, click again and select Download.
- Save the JSON file locally and keep it as a backup of your existing dataflow. To go back to this version of the dataflow later, repeat these steps and choose Upload in step 3.
- Click the gear icon ( ) and then click Analytics Studio.
Create a Dataset with the Dataset Builder
Dataset builder—sounds like something that builds datasets, right? Well, kind of. In reality, the dataset builder generates the JSON instructions needed to build a dataset and adds the instructions to your dataflow. The dataflow then does the actual building.
Before you use the dataset builder, consider how the Salesforce objects that you’re extracting are related. As a Salesforce admin, you know that opportunities have lookup relationships to accounts and users. When you create opportunity records in Salesforce, you're entering opportunity field values; you’re also “looking up” field values in the related account and user records.
When you create a dataset you do something similar, but instead of records, you create rows. To help you, Analytics first asks you for the root object. This is simply the lowest object in the hierarchy of objects you’re extracting. In our case, the Opportunity object is the root object. You can only include related objects above the root object in the dataset. For example, if you select Account as the root object, you can include related objects such as User and Parent Account, but not Opportunity because it’s lower.
- In the Analytics Studio, click Create and select Dataset.
- Click Salesforce Data.
- Enter the dataset name. Be as descriptive as you can here so that other people know what you’ve created. Name this dataset Opportunities with Accounts and Users.
- From the Select dataflow... picklist, select Default Salesforce Dataflow. The dataflow that you select here will create the dataset for you. If your org has more than one dataflow, you can choose which one to use.
- Click Next. You see the dataset builder with a list of Salesforce objects. The object you select here is the root object.
- In the Pick a Salesforce object to start search box, enter opp. The list shows matching objects.
- In the object list, click Opportunity. The root object you selected appears on the dataset builder canvas.
- Hover over the object and click the plus (+). You see a list of the object’s fields.
- Click to select each field that you need. If you can’t see a field, start entering its name in the Search by name or metadatasearch box to filter the list. Select these fields:
- Close Date
- Created Date
- By default, the fields are listed in alphabetical order by name. To reverse the order, click the NAME column header, or to sort by field type click the TYPE column header.
- To open a list of objects related to your root object, at the top of the field list, click the Relationships tab. This is where you can select the fields you need from the related accounts and users. Remember, only related objects above the root object are available here. If you select a different root object, you see a different list of related objects.
- In front of the Account ID relationship, click Join. You see the Account object on the dataset builder canvas.
- In front of the Owner ID relationship, click Join. You see the User object on the canvas.
The next step is to select the fields you want from each of the related objects.
- To open a list of account fields, hover over the Account object and click the plus (+).
- Select these fields:
- Account Name
- Billing City
- Billing Country
- SIC Code
- Notice the Relationships tab at the top of the field list. Use this if you wanted to include fields from objects related to the account object, such as Parent Account.
- To hide the field list, to the right of the Account object, click .
- Repeat steps 1–4 to select these fields from the User object:
- Full Name
Let’s quickly recap what you’ve done here. You selected your root object, Opportunity, and the fields you need. You also selected the related Account and User objects and the fields you need from those. When you create the dataset, each row represents an opportunity, with fields from the related account and owner user records.
Let’s finish up by adding these instructions to the dataflow and creating the dataset.
- Click Next. The dataflow that you selected earlier opens in the dataflow editor in the background. Look closely and you can see that your dataset building instructions appear as boxes, or “nodes” in the editor.
- From the Select an app for your dataset picklist, select Sales Performance Datasets. You can edit the dataflow here to make adjustments, but yours is ready to go.
- Click Create Dataset. If all goes well, you see a notification that the dataset has been queued to be created. Behind the scenes, Analytics runs the dataflow to create the dataset.
- Click the Go to the data monitor link and let’s see how the dataflow is doing.
Monitor the Dataflow and Verify the New Dataset
- On the Monitor tab of the data manager, click the DATAFLOWS subtab. The Dataflows subtab lets you see just your dataflow jobs.
- To refresh the view, at the top-right of the monitor, click . The dataflow icon shows the status of the dataflow. If all’s well, it’s a checkmark and you see “Successful” when you hover over it. If the status shows as Queued or Running, continue refreshing the view until the dataflow finishes.
- To see a list of all the times it’s run, click the dropdown in front of the dataflow.
- Click the dropdown in front of the most recent run. You see a list of all the JSON instructions the dataflow has performed.
- On the left of the data manager, click the Data tab.
- On the right of the Opportunities with Accounts and Users dataset, click and select Explore. If you don’t see the dataset, try refreshing your browser.
- On the left of the new lens, under Bar Length, click the Add a measure (+) button. The opportunity Amount field is here as a measure.
- Under Bars, click the Add a group (+) button. The Close Date and Created Date are available here for grouping, along with all the other dimension fields that you selected in the dataset builder.
Schedule the Dataflow
When you create a dataset with the dataset builder, Analytics runs the dataflow for you the first time. After this, you can start the dataflow manually or schedule it to run on a recurring basis. Scheduling is a great way to keep the dataset fresh with the latest data from your Salesforce objects.
- In the data manager, click the Dataflows & Recipes tab.
- On the right of the Default Salesforce Dataflow, click and select Schedule. The schedule settings for the dataflow appear.
- Schedule the dataflow to run every 24 hours at 12:00 AM each weekday by selecting these settings.
- Schedule by: Hour
- Start at: 12:00 am
- Run every: 24 Hours
- Select days: M, Tu, W, Th, F
- This schedule ensures that your explorers have fresh data each morning with their coffee. It also prevents the dataflow from running during business hours. You don’t want your users to see differences in dashboards depending on the time of day.
- Click Save.
Great job! You’ve successfully created two datasets. The SIC Descriptions dataset contains data extracted from a CSV file. The Opportunities with Accounts and Users dataset has data extracted from objects in your Salesforce org. And you’ve scheduled the dataflow to ensure that you always have fresh Salesforce data. The next step is to join data from these two datasets. You want all this data available to explore from a single dataset. Onwards to the data preparation stage!