Salesforce is the largest CRM out there and with their API they’ve built the door to get to your data. But if you’re seeking a timely business intelligence solution that involves combining your salesforce data with other operational data sources to see across your business, it can be a bit daunting to knock on that door and ship that data to your data warehouse.
In this tutorial, featuring our Simple Data Pipe, you’ll see how easy it can be, and pretty, too, thanks to our friends at Looker.
Get, Build, Analyze
Solving a Real-World Problem for IBM Cloudant
This tutorial started out as a project to solve this very problem for ourselves. We wanted to build an Enterprise Data Warehouse (EDW) to analyze our CRM data, among other things. I teamed up with David Taieb, the Senior Application Architect in our group, to plan our approach: a Node app on Bluemix, OAuth via Salesforce, data movement via IBM’s DataWorks API, a NoSQL data stage on IBM Cloudant, scheduling, and single sign on (SSO). Sound complicated? It was. But now it’s not. We built the Simple Data Pipe to do all that, and you can use it too.
Teaming Up With Looker
When we started designing the EDW, I introduced the team to Looker, an analytics platform I fell in love with last year. You’ll see why in a minute. (Haven’t seen Looker? Watch this quick intro video.) Erin Franz, Analyst at Looker, worked with us to bring our Simple Data Pipe work to life. She architected the LookML that models the Salesforce data in dashDB and built all the reporting you’re about to see. With your Looker instance up and your data in dashDB, we can have you going with a Salesforce reporting solution in minutes. Huzzah!
Simple Data Pipe Technical Summary
Under the hood, the Simple Data Pipe leverages Bluemix, IBM’s open cloud platform for building, running, and managing applications and runs Node.js to communicate with, and move data between, Salesforce AND Cloudant. From there, it’s easy to use Cloudant’s integrations with dashDB, Apache Spark, and other targets. See our recent article for more.
Before You Begin
If you don’t already have the following accounts, set them up now:
- Salesforce.com account with Admin permissions. If you aren’t the administrator, contact your Salesforce administrator to grant you the ability to create and manage connected apps. If you’re not part of an existing Salesforce account, set up a trial Developer Salesforce account.
- Looker Instance. If you aren’t signed up for Looker yet, request a Looker demo instance using your company email. Looker will reach out to you to get you set up for your Salesforce solution using Simple Data Pipe.
- IBM Bluemix. You can sign up for a free trial.
Deploy Simple Data Pipe
The fastest way to deploy this application to Bluemix is to click the Deploy to Bluemix button below — it automatically adds all the services you need: Node.js, Cloudant, and SSO.
If you prefer instead to deploy manually, please refer to the readme.
On the Deploy to Bluemix screen, give the app a name that’s meaningful to you, like Simple Data Pipe, and click Save. (If you have any trouble deploying, make sure you haven’t already reached your Bluemix memory and services quotas. You get 10 with your account’s free trial period.)
To launch the app, click the View Your App button.
Get Started With Simple Data Pipe
Now that you’re looking at Simple Data Pipe in the browser, you’re ready to get going.
Connect your Simple Data Pipe to Salesforce.com
Before you connect Salesforce to Simple Data Pipe, you need to set up Salesforce OAuth.
- In Salesforce.com, access your Setup menu in one of 2 ways:
- If you’re in a trial version, go to the menu on the upper right of the screen and click Setup.
- If you don’t see Setup in the menu, click the dropdown arrow beside your name and select Setup.
- Within the menu on the left side of the page, add an app in one of the following ways:
- In a trial version, go to the Build section, select Create > Apps, and under Connected Apps, click New.
- In other versions, go to the App Setup section, select Create > Apps, and and under Connected Apps, click New.
- To configure the connected app:
- Complete all required fields (marked in red)
- Turn on the Enable OAuth Settings checkbox.
- Enter the Callback URL, which you can grab from your Simple Data Pipe app’s Connect tab. (You’ll be able to find it once you name and create your first pipe run entry.)
- In the Available OAuth Scopes box, select all and click the Add button to send them all to the Selected OAuth Scopes box.
- Click Save.
- Click Continue to get your OAuth Consumer Key and Consumer Secret.
- Keep this Salesforce window open.
- Return to your Simple Data Pipe app. Under the Connect section of your pipe run, you’ll share your Salesforce OAuth key and secret with your Simple Data Pipes app. Copy and paste these values from Salesforce into the corresponding fields in Simple Data Pipe.
- Note the checkbox for Use the SalesForce sandbox. If it applies to you, you’ll know ;-)
- Click Connect to Salesforce. If you get an error here, Salesforce may not have processed your connected app yet. This can take up to 10 minutes. Coffee break!
- If prompted, sign in to Salesforce.
- At the prompt telling you Simple Data Pipe requests access permission, click Allow.
- The Salesforce prompt closes and leaves you back in Simple Data Pipe.
- Click Save and continue.
Move Data to dashDB
In your Simple Data Pipes app you can set up and schedule data movement activities.
- Once connected, you have a choice of what you are going to pipe to dashDB. Under the Pick Tables section for your pipe run, All Tables is the first option when you click into the dropdown, but if you don’t want everything, pick the table you want. Click Save and continue.
- Schedule an activity run (optional): If you want the freshest data every day, use this option. Click Save and continue.
- Now that you’ve connected and picked tables, move data from Salesforce to dashDB by clicking Run Now under the Activity section for your pipe. A progress screen appears.
When the pipe run is complete, you can select View Details to review the tables and the number of records that were moved for each one.
Now that the data is in dashDB, we’re ready to view it with Looker. (You already filled out the form to get Looker spun up, right? Check your email for those credentials.)
Set up Looker to work with dashDB
Looker LookML Installation
When you get your credentials email from Looker, click its sign-up link and follow Looker’s instructions.
Connect Looker to dashDB
In Looker, create your connection to dashDB.
- On the left side of the screen, click Admin and select Connections.
- Click the New Database Connection button.
- Name your connection “pipes”.
- In Dialect, choose IBM DashDB.
- Complete required fields with additional dashDB details.You can find these details in your dashDB console. Return to Bluemix and go to the dashboard. Under the Services section, open your pipes-dashdb-service, click the Launch button, then select Connect > Connection Information.
Note: In the Schema field in the Looker form, enter your DashDB UserID, but capitalize all letters. For example, change dashDB1234 to DASHDB1234.
- Click Add Connection.
Creating a New LookML Project
- In the menu on the left, select LookML > Manage Projects.
- Click the + New LookML Project button.
- If you see a Developer Mode Required message make sure that your Dev Mode is on.
- Name your project.
- Uncheck the Generate Model & Views checkbox.
- Click Create Project.
Still with me?
- You’ll see your new project.
- Click Configure Git.
If you’re new to Git, Looker provides a link on how to create a repository.
- Fork our LookML files into your Github repo: https://github.com/ibm-cds-labs/looker-for-pipes
- Once looker-for-pipes lands in your Github account, paste the repository URL into the Looker form and click Continue.
- Follow the link and Looker’s instructions to copy and save your deploy key on Github. Make sure “Allow write access” is checked when you save it on Github.
- Click the Continue Setup button.
- Click Sync developer mode in your Looker browser to see the views you just uploaded.
- Select LookML > Salesforce to access your project with your views.
Tada!! You’re good to go. With your Pipe installed, configured and scheduled you will now have your SalesForce data refreshing regularly. Your Looker instance will now be available to you with a robust set of dashboards to get your organization going.
Exploring your data with Looker
Let’s look at some of our Salesforce data, shall we? (Royal we—meaning you.) There is a Salesforce dashboard included in the files we provided you from Looker to start you on your data exploration journey. Click Spaces > LookML Dashboards:
Clicking on the title of any one of the reporting components on the dashboard will bring you to what Looker calls a Look.
Looker shows the detail.
For a mental model, think ‘Pivot Table on Steroids’. You are free to modify this Explore by adding or subtracting new dimensions and measures on the left. To display your new data, click Run at top right.
Using Explores and Dashboards
Now you are ready to start learning how to explore your data in Looker.
- Start with the Looker Video Library.
- Looker’s documentation is robust
- Live chat with a Looker Analyst at any time via the icons on the bottom of your side navigation menu.
Securing Simple Data Pipe
Simple Data Pipe uses HTTPS everywhere, so the data in flight is secure. To secure the app in the browser, we recommend you use Bluemix SSO. The SSO service is intentionally left unbound when your initially deploy the Simple Data Pipe. Once you configure Bluemix SSO in this unbound state, Simple Data Pipe will respond automatically when you bind the SSO service to it and restage the application. Follow the README in the repo for our bluemix-helper-sso Node module for detailed instructions.
If you happen to find the Bluemix documentation for binding SSO to your app — they don’t apply in our case. You won’t need to copy any additional Node.js configuration code. We’ve already implemented that portion in the Simple Data Pipe for you!
With a Little Help from Our Friends – Crowdsourcing BI Solution Efforts
Open sourcing this solution is very important to us. As mentioned, the struggle for BI solution architects is mostly about finding solutions, but the bulk of the work for most of them most of the time is in building the data movement piece of it — 90% of the work and 0% of the business impact. With Simple Data Pipe, every BI solution architect has a product to use and a framework to extend. Instead of reinventing yet more ways to move data, you can instead use this tool and extend it, developing yet more connect pipes, Then you’ll have time instead and focus on architecting data-driven reports and solutions.
In the two months that we have put this solution together, we have come up with a list of future enhancements. We are now excited to share our tool and we hope that it inspires you as it has us. Please reach out to us on Github, Stackoverflow, and Twitter to tell us what you think, extend this work, and give us feedback.
- What about errors?
- The Simple Data Pipe has an activity monitor built into it recording each scheduled activity. Should you encounter errors, you will find a meaningful error message that you can troubleshoot. If you run into something we haven’t found yet, reach out on StackOverflow using the tag #SimpleDataPipe.
- Are there limits the amount of data I can move?
- We tested up to 100k Salesforce records without a problem. That’s all we needed. Let us know if you have problems past that threshold.
- I want to source another data source, what do I do?
- If you are a developer who wants to build upon the foundation we are laying and contribute to our project, by all means do! If you have a connector you’d like to see or contribute, please let us know in the comments below or on Stack Overflow. We’re already planning the next connector–as pipes are created to bring in more data sources, they will be released to the developer community.
- Can we use other visualization tools on top of dashDB?
- Absolutely. As this project grows look for more reporting assets being contributed. Or contribute your own! Doing something awesome in D3 that you think could help someone else? Bring it!