Start tracking your progress
Trailhead Home
Trailhead Home

Load and Prepare Your Remote Sales Data

Get Connected

Now it’s time to bring in data from your remote Canada org with a connector. Once that’s done, we talk about the options you have for preparing this Canada data and combining it with your US data.

Connect to and Sync Your Remote Canada Data

  1. In the Your new Salesforce security token email that you received when you reset the security token for the Canada org in step 1, copy the security token.
    Screenshot of the new security token email with the security token highlighted
  2. Return to your US org and in the data manager, click the Connect tab.
  3. Click Connect to Data.
  4. Click Add Connection.
  5. Click the Salesforce External Connector.
  6. Complete the fields.
    1. Connection Name: Canada Sales
    2. Developer Name: Canada_Sales
    3. Description: Connection to data in Canadian Salesforce org
    4. Username: Enter the username for the Canada org you signed up for in step 1.
    5. Service URL: Leave the default value provided.
    6. Password: Enter the password for the Canada org you signed up for in step 1 and paste the copied security token at the end.
  7. Click Save & Test.
    Analytics tests the connection. If the connection fails, check the username and password and try again. Make sure you pasted the security token at the end of the password!
  8. Click Continue.

Now that you’ve created the connection, you can connect to the objects in the Canada org.

  1. On the Select the connection to source dialog, click the Canada Sales connection.
  2. In the object list, click Opportunity and then click Continue.
  3. In the field list, select these fields:
    1. ID
    2. ACCOUNTID
    3. NAME
    4. STAGENAME
    5. AMOUNT
    6. CLOSEDATE
    7. TYPE
    8. LEADSOURCE
    9. OWNERID
    10. CREATEDDATE
  4. Click Continue.
  5. On the Preview Source Data dialog, review the data that you’re going to sync.
    Tip

    Tip

    To view or change a field’s attributes, such as label, type, and precision, click the field and then click The view or edit field attributes button at the top of the Preview Source Data window

  6. Click Save.

    The Opportunity object now appears under your new Canada Sales connection on the Connect tab.

  7. To connect to more objects with the Canada Sales connection, click Connect to Data.
  8. Repeat steps 1–6 to connect to the Account object and select these fields.
    1. ID
    2. NAME
    3. TYPE
    4. BILLINGCITY
    5. BILLINGSTATE
    6. BILLINGCOUNTRY
    7. INDUSTRY
  9. Click Connect to Data.
  10. Repeat steps 1–6 to connect to the User object and select these fields.
    1. ID
    2. NAME
    3. TITLE

You’ve now connected to the three objects you need in your Canada org. But you can’t use them until you sync them. Sync them manually first, to make sure that they work, then schedule them.

  1. On the Connect tab, click The menu button on the right of a connection on the Connect tab on the right of the Canada Sales connection and then click Run Now.
  2. Click the Monitor tab.
  3. Check that the status of the Account, Opportunity, and User sync jobs from the Canada Sales connection is “Successful”. If the status shows as Queued or Running, click The refresh button at the top of the Monitor tab to refresh the view until the sync jobs finish.

    Screenshot of Monitor tab showing jobs for the Account, Opportunity, and User objects from the connected Canada org

  4. Click the Connect tab.
    Notice that the three Canada Sales objects have a green icon to show that the last sync was successful and that they are up to date.
    Tip

    Tip

    Hover over the icon to see the sync status.

  5. On the right of the Canada Sales connection, click The menu button on the right of a connection on the Connect tab and then click Schedule.
  6. Schedule the connection to run every 24 hours at 2:00 AM each weekday by selecting these settings.
    1. Schedule by: Hour
    2. Start at: 2:00 am
    3. Run every: 24 Hours
    4. Select days: M, Tu, W, Th, F
  7. Click Save.

This schedule ensures that the Canada Sales connection syncs after the Salesforce Local connector and before the default dataflow. But right now, this data from your Canada org is in three separate synced objects, not in a dataset. Remember, you want to join the objects together combine them with your US sales data to create a North America sales dataset. Which is what you’ll do next.

But first, you have a decision to make. In step 2, you used the dataflow to join and filter your US opportunity, account, and user data. You can use the same dataflow to join your Canada data and combine with your US data. Or you can use a recipe.

Tip

Tip

Dataflow or Recipe?

Dataflows and recipes both let you make changes to data by applying transformations. Use a dataflow if you want to have precise control over these transformations. For example, you can manually modify the underlying JSON code and apply complex filters and calculations using Salesforce Analytics Query Language (SAQL).

Use a recipe if you want a guided data preparation experience. You create recipes in Data Prep, where you can get smart suggestions on how to transform your data and preview the results of your changes as you add them.

You decide to use the dataflow to join and filter the Canada data, as you are already doing these things for the US data in the dataflow. While you’re at it, you’ll also combine the US and Canada data with the append transformation to create your North America Sales dataset. Here’s your design:

Diagram showing the transformations used to combine and filter synced opportunity, account, and user data from the Canada org in the dataflow

Load, Join, and Filter Canada Data in the Dataflow

  1. In the data manager, click the Dataflows & Recipes tab.
  2. Click the Default Salesforce Dataflow.
  3. Click the digest button (The digest button in the toolbar at the top of the dataflow editor).
    The digest transformation dialog opens.
    Tip

    Tip

    The digest transformation and how to use it

    This transformation pulls data into the dataflow from an object synced through a connection. For example, in your dataflow you want to pull in data from the opportunity, account, and user objects that you connected to in your Canada org. You specify the connection and object, and the fields that you want to include.

  4. Complete these fields:
    1. Node Name: digest_CA_Opportunities
    2. Connection Name: Canada_Sales
    3. Source Object: Opportunity
    4. Fields: Select all fields
  5. Click Create.
  6. Repeat steps 3–5 to add digest nodes for the Canada Account and User objects, selecting all fields. Use these node names:
    1. digest_CA_Accounts
    2. digest_CA_Users

Now let’s augment the opportunities with account data and filter the rows to only show opportunities related to Canadian accounts.

  1. Click the augment button (The augment button in the toolbar at the top of the dataflow editor).

    The augment transformation dialog opens.

    Tip

    Tip

    The augment transformation and how to use it

    This transformation adds columns from a “right” source node to data from a “left” source node using keys to match the data. For example, in your dataflow, you want to add fields from account rows to opportunity rows with matching account IDs. You specify the left (opportunities) node and the right (accounts) node, the key fields (account ID) to match rows, and the fields that you want to add from the right node.

  2. Complete these fields:
    1. Node Name: augment_CA_Opps_Accs
    2. Left Source: digest_CA_Opportunities
    3. Left Key: AccountId
    4. Relationship: AccountId
    5. Right Source: digest_CA_Accounts
    6. Right Key: Id
    7. Right Fields: Select all fields except Id
    8. Operation: Look Up Single Value
  3. Click Create.
  4. Click the filter button (The filter button in the toolbar at the top of the dataflow editor).
  5. Complete these fields:
    1. Node Name: filter_CA_Accounts
    2. Source: augment_CA_Opps_Accs
    3. Use SAQL: Deselected
    4. Filter: AccountId.BillingCountry:EQ:Canada
  6. Click Create.

Now you can augment the user data to the output of the filter node.

  1. Click the augment button (The augment button in the toolbar at the top of the dataflow editor).
  2. Complete these fields:
    1. Node Name: augment_CA_Opps_Accs_Users
    2. Left Source: filter_CA_Accounts
    3. Left Key: OwnerId
    4. Relationship: OwnerId
    5. Right Source: digest_CA_Users
    6. Right Key: Id
    7. Right Fields: Select all fields except Id
    8. Operation: Look Up Single Value
  3. Click Create.

At the moment, the dataflow is creating a US sales dataset, but only loading, filtering, and augmenting the remote Canada sales data. The final task for the dataflow is combining this US and Canada data and registering it as a dataset.

Append US and Canada Data in the Dataflow

  1. Click the append button (The append button in the toolbar at the top of the dataflow editor).
    The append transformation dialog opens.
    Note

    Note

    The append transformation and how to use it

    This transformation combines rows from multiple source nodes into a single dataset. For example, in your dataflow you want to combine rows from your US data with rows from your Canada data. You select the source nodes that have the rows that you want to combine. By default, the data that you’re combining must have the same schema—the corresponding columns must have the same name and field type. If your data doesn’t have the same schema, you can select to allow a disjoint schema. This option lets you include columns that aren’t present in all the data that you’re combining.

  2. Complete these fields:
    1. Node Name: append_US_CA_Sales
    2. Sources: augment_CA_Opps_Accs_Users and augment_User
    3. Allow disjoint schema: Selected
  3. Click Create.
  4. Click the sfdcRegister button (The register button in the toolbar at the top of the dataflow editor).
  5. Complete these fields:
    1. Node Name: register_US_CA_Sales
    2. Source Node: append_US_CA_Sales
    3. Alias: US_CA_Sales
    4. Name: US and CA Sales
  6. Click Create.
  7. Hover over the register_US Sales node and click the trash icon that appears.
    You don’t need this node any more.
  8. Click Update Dataflow.
  9. Click Update Dataflow.
  10. Click Run Dataflow.
  11. Click Go to Data Monitor.

Monitor the Dataflow and Explore the New Dataset

  1. On the Monitor tab of the data manager, click the Dataflows subtab.
  2. Check the dataflow icon. If all’s well, it’s a checkmark and you see “Successful” when you hover over it.
  3. If the status shows as Queued or Running, click The refresh button at the top of the Monitor tab to refresh the view until the dataflow finishes.
  4. When the dataflow has finished, click the Data tab on the left of the data monitor.
  5. On the right of the new US and CA Sales dataset, click The menu button on the right of a dataset on the Data tab and select Explore.
  6. Under Bars, click the Add a group (+) button and select AccountId.BillingCountry.
    Notice that both USA and Canada opportunities are in the dataset.
  7. Under Bars, click AccountId.BillingCountry and select StageName.
    Notice that some of the stages are almost identical, but not quite. For example, there’s a Negotiation & Review stage and a Negotiation/Review stage. This is because the US and Canadian orgs have slightly different stage names
  8. Under Bar Length, Click Count of Rows, click Sum and then click Amount.
    The values displayed on each bar are a mix of US dollar amounts and Canadian Dollar amounts, which makes the totals meaningless.
  9. Close the browser tab.

Good job. You’ve created a dataset of US and Canada sales data. But as you've just seen, there are inconsistencies in the data that you need to clean up. You do this with a recipe in the next module.