Tweet

Loading Salesforce Data into Analytics Cloud

Introduction

In my earlier post on Analytics Cloud (aka Salesforce Wave) I showed how to load data from a CSV file. Another common use case is to load Salesforce data, such as Cases and Opportunities, for example to see how performance has improved (or otherwise!) over time.

Setting Up

To get started, access the Data Monitor page by clicking the gear icon:

this takes you to the Dataflow View. The Analytics Cloud provides a Default Salesforce Dataflow that you can download from the drop down on the right hand side of the page:

This will download a Dataflow Definition file in JSON format. The default data flow is documented in the Analytics Cloud Implementation Guide, so I won’t go into further detail on that here. Instead I’ll walk through a Dataflow Definition that I use to bring the case data from my Salesforce instance. The full JSON file can be downloaded from the Related section below.

Loading Case Data

The following JSON stanza pulls the Case records and creates an interim dataset called “Extract_Cases” - the dataset name is important as will be explained later:

    "Extract_Cases":{
        "action":"sfdcDigest",
        "parameters":{
            "object":"Case",
            "fields":[
                {
                    "name":"CaseNumber"
                },
                {
                    "name":"ContactId"
                },
                {
                    "name":"AccountId"
                },
                {
                    "name":"Type"
                },
                {
                    "name":"Hours_Worked__c"
                },
                {
                    "name":"Subject"
                },
                {
                    "name":"Status"
                },
                {
                    "name":"Description"
                },
                {
                    "name":"CreatedDate"
                },
                {
                    "name":"ClosedDate"
                }
            ]
        }
    }

this pulls all of the case data, but the account and contact information is only available as Salesforce IDs, which isn’t particularly helpful if I want to investigate performance for specific customers, so I need to augment the dataset with the Account and Contact details. The first thing I need to do is extract the Account and Contact data to interim datasets:

    "Extract_Contacts":{
        "action":"sfdcDigest",
        "parameters":{
            "object":"Contact",
            "fields":[
                {
                    "name":"Id"
                },
                {
                    "name":"Name"
                }
            ]
        }
    },
    "Extract_Accounts":{
        "action":"sfdcDigest",
        "parameters":{
            "object":"Account",
            "fields":[
                {
                    "name":"Id"
                },
                {
                    "name":"Name"
                }
            ]
        }
    }

 Once I have the Account/Contact datasets, I can augment the interim Case dataset, first with the contact name. Note that I have to specify the “Extract_Cases” interim cases dataset as the “left” parameter value and the “Extract_Contacts” dataset as the “right”. the left/right_key parameters define the fields from each dataset that tie the contacts to the cases, The results of the transformation are stored in the interim dataset “Transform_Augment_CaseWithContactDetails" :

    "Transform_Augment_CaseWithContactDetails":{
        "action":"augment",
        "parameters":{
            "left":"Extract_Cases",
            "left_key": [ "ContactId" ],
            "relationship": "CaseContact",
            "right":"Extract_Contacts",
            "right_key": [ "Id" ],
            "right_select":[
                "Name"
            ]
        }
    }

Next, I augment the Account name - note that the “left” parameter dataset value is “Transform_Augment_CaseWithContactDetails” - the dataset created after augmenting with the contact name.  If I use the original “Extract_Cases” dataset I discard the contact information I’ve worked so hard to add. This is something I have real trouble remembering for some reason!

    "Transform_Augment_CaseWithAccountDetails":{
        "action":"augment",
        "parameters":{
            "left":"Transform_Augment_CaseWithContactDetails",
            "left_key": [ "AccountId" ],
            "relationship": "CaseAccount",
            "right":"Extract_Accounts",
            "right_key": [ "Id" ],
            "right_select":[
                "Name"
            ]
        }
    }

Now that the Account/Contact information has been added to the Cases, I can register the fully augmented dat set so that it can be used in the Analytics Cloud: 

    "Register_Dataset_ClosedCases":{
        "action":"sfdcRegister",
        "parameters":{
            "alias":"AllCases",
            "name":"AllCases",
            "source":"Transform_Augment_CaseWithAccountDetails"
        }
    }

I then upload the Dataflow via the same drop down that I downloaded it from:

and then choose the Start option from the same menu to execute the load:

Once the dataset is loaded, I receive an email notification and I can then start exploring it, for example to see the average days worked for cases grouped by customer:

(I can only show the BrightGen figure as this dataset contains real customer data!)

Related

• Getting Started with Salesforce Wave
• Cases.json

Follow @bob_buzzard