The Extreme Salesforce Data Volumes webinar and Best Practices for Deployments with Large Data Volumes paper explain an important best practice for Salesforce organizations that contain a lot of data:

Keep the operational data set in your org as lean as possible by archiving "historical" data elsewhere.

When you follow this best practice, operational database access operations have to consider less data and can execute more efficiently than they otherwise would with a larger data set.

Practically speaking, there are multiple approaches that you can use to implement this best practice. You might simply create objects in your Salesforce org to store historical information and build analytics around these objects, or you could migrate historical data to an external system that’s specifically designed for data analytics such as a data warehouse. The choice is yours and depends on your specific use case.

This article is a tutorial that shows you how you can tackle the latter approach. It explains how to implement an external application to store historical records and then integrate it’s user interface (UI) and user authentication into Salesforce so that your users can analyze historical data right alongside operational data. The article uses a combination of Salesforce Platform technology, including Force.com, Heroku, and Force.com Canvas. Read this article to learn how to:

  • Create a Ruby on Rails app to store historical records from Salesforce.
  • Deploy the app on Heroku.
  • Integrate the app's UI and user authentication into Salesforce using Force.com Canvas.

Disclaimer: The sample app in this article uses Heroku Postgres (runs on Amazon Web Services [AWS]) as a data store for historical Salesforce data in this demonstration. Carefully consider what data you archive and where you store this data to ensure that the underlying data center complies with your specific requirements.

Scenario and Requirements

As always, you must have a clear understanding of your application requirements before diving in. The overall scenario for this tutorial is to implement a solution that the previous section describes: keep your Salesforce org lean by creating an external app that serves as a repository for historical records. As the following figure illustrates, historical data moves from Salesforce/Force.com to an app deployed on Heroku, and Force.com Canvas integrates Salesforce and the custom app.


Xsfdc-1.png


Here are some specific requirements for this scenario.

Requirement 1: Maintain a Lean Salesforce/Force.com Org Database

Your Salesforce/Force.com organization contains a large amount of data. The Opportunity object is particularly large with more than one million records. Your company considers Opportunity records closed more than one year ago as "historical" because they are not normally used in day-to-day operations. So this article teaches you how to reduce the overall size of your database by archiving and migrating historical Opportunity records every weekend.

Requirement 2: Automate Efficient Data Archiving

The archiving strategy for your project is straightforward—every weekend, you need to archive Opportunity records that were closed more than one year ago. The most efficient way to move large volumes of data into and out of an organization is with the Force.com Bulk API. If you are new to this API, check out the Force.com Bulk API page for tutorials and other resources.

Considering that your archiving strategy is not complex, there’s no need to write a custom application that uses the Force.com Bulk API—it's much simpler to use one of many free or paid data loading utilities that use the Force.com Bulk API. This article teaches you how to use one such utility, the free version of the Jitterbit Data Loader for Salesforce.com. You can even automate the entire process so that you can concentrate on your weekend household chores.

Requirement 3: Maintain and Provide Access to Historical Data

Your company needs to maintain historical Opportunity records for trend analysis. This article teaches you how to create a simple Ruby on Rails app that can store and display historical Opportunities. You also learn how to deploy the app on Heroku.

Requirement 4: Integrate Operational and Historical Data

Your company's Salesforce users often need to study trends involving historical Opportunities. It would be convenient for users to display historical data analytics alongside operational data, all from within your Salesforce/Force.com app. This article teaches you how to use Force.com Canvas to integrate the analytic app's UI and user authentication into Salesforce.

Step By Step

Now that you have a clear understanding of what you need to do, the following sections explain how you might meet requirements above.

Step 1: Prepare Your Local System

To go through the practical examples in this article, your computer must have Ruby on Rails (RoR) ready to go. This site has some useful links to help you get started, but before diving in, I strongly recommend that you look at the RVM (Ruby Version Manager) site and use rvm to install, manage, and switch among the versions of Ruby and Rails development environments on your system. The environment used to create the app in this article is as follows.

  • Ruby: 1.9.3p327
  • Rails: 3.2.11

Once RoR is ready, you need a local database to support basic application development that your apps can use, as well as a companion client that you can use to work with your databases. The best practice is to use the same database system and version with both your application development and deployment environments. Considering that Heroku is the deployment target for this tutorial, and that Heroku’s default shared database system is PostgreSQL (version 9.2 at the time of this writing), it makes sense to configure your local computer with PostgreSQL.

Note: Feel free to use another database system if you like. However, beware that with this tutorial, you are going to create SQL queries that use date filter conditions, and that date format specifications can vary among databases. When I first developed this app with SQLite, queries that worked just fine with SQLite in my local application development environment didn’t work with the app once it was deployed on Heroku with PostgreSQL.

When installing something like PostgreSQL and its companion utility pgAdmin, save yourself a lot of time and setup headaches by using your computer’s native package/software manager, whenever possible. For example, on a Mac, use Homebrew, and on Ubuntu, use Synaptic Package Manager.

Once you install all of your software, create a very quick Ruby on Rails app and confirm that everything is configured properly.

Next, you need to prepare your system for application deployment to Heroku. If you don’t already have a Heroku account, sign up for a free Heroku account. Then complete all the steps on the Getting Started with Heroku page to prepare your system and Heroku account for deploying apps. This preparation includes installing the Heroku Toolbelt.

Step 2: Get a New Developer Edition Org

Anyone can complete this tutorial using a free Force.com Developer Edition (DE) org. In just a few minutes, you can provision your own cloud application development environment. Look for an email, set your initial password, and then you are logged in and ready to continue.

Step 3: Tour the Standard Sales App

Every Developer Edition org provides access to the prebuilt apps, including the Sales app, that salesforce.com is known for. Part of the Sales app is a standard object called “Opportunities.” As you might imagine, this object manages "sales opportunities."

If you are new to Salesforce, complete the following simple steps to become familiar with the Opportunity object.

After you set your initial password, you are on the Force.com Home page.

  1. In the upper-right corner, look at the Force.com app menu to confirm that the drop down list of apps is currently “Sales.”
  2. Click the Opportunities tab.
  3. Change the view to “All Opportunities.”


Xsfdc-2.png


Once you have a list of all your opportunities, click any opportunity and review the fields in the Opportunity Detail section on the Opportunity detail page to get a feel for the information that the Opportunity object tracks.


Xsfdc-3.png


In particular, notice that there are “Stage” and “Close Date” fields, which you will use to drive when an Opportunity record is considered “historical” and can be archived.

Step 4: Create a Force.com Canvas App on Heroku

You need an external app to house historical Opportunity records that you plan to archive. In this scenario, you’ll build a simple Ruby on Rails app and deploy it on Heroku, a component of the Salesforce Platform. Once you create the external app, you have to integrate its UI into the Salesforce Sales app using Force.com Canvas.

To create a skeleton app on Heroku that’s pre-integrated into your Developer Edition org using Force.com Canvas, follow these steps from your Force.com browser window.

  1. From Setup, click Canvas App Previewer.
  2. Click Heroku Quick Start and fill out the form.
    • Template: Ruby - Quick Start Template
    • Canvas App Name: Historical Opportunities
    • Heroku App Name: historical-opportunities-#####, where ##### is a random five-digit suffix. If you get an app naming conflict, change this suffix until you succeed in creating the app with a unique name.
    • Heroku Username and Password: your Heroku credentials
  3. Click Create.


Xsfdc-4.png


In just a few minutes, you should have a template Ruby on Rails app running on Heroku, an app that’s already integrated into your DE org with Force.com Canvas. Once the Canvas App Previewer page refreshes, click the new link in the left sidebar for the Historical Opportunities app. The canvas then displays the UI of your new app.


Xsfdc-5.png


Feel free to play with this live app that lets you post Chatter updates to your profile feed. Once you are done, click Close to exit the Canvas App Previewer and return to the Force.com Setup area.

Step 5: Clone the Heroku App Locally

You need to customize the skeleton Ruby on Rails app so that it can support historical Opportunity records and analytics. To do this, you need a local copy of the source code so that you can edit it.

To get the location of the git repository to the app source code:

  1. Log in to Heroku.
  2. From your account’s app dashboard, click the new app you just created (i.e., historical-opportunities-####).
  3. From the app’s detail page, click Settings.
  4. Next to the app’s Git URL, click the "copy" button to copy the URL to your clipboard.


Xsfdc-6.png


Now clone the source code locally:

  1. Open a terminal window (or equivalent for your operating system).
  2. Enter a command to change your working directory to a location that you can use to organize local application development. For example, cd development/apps
  3. To create a local clone of the remote git repository, use the URL you copied in the previous step and enter the following command, in which ##### corresponds to your app name:
git clone git@heroku.com:historical-opportunities-#####.git

The command output in your terminal window should be similar to the following:


Xsfdc-7.png

Step 6: Modify Your Local App’s Bundle

Note: If you have any problems with you local app source code modifications in Steps 6 through 12, see the sample app git repository on Github.com.

It’s time to update the local app source code to support archived, historical opportunities and display corresponding data using a simple graphical UI.

To modify the source code of your local Ruby on Rails app, you can use a combination of operating system commands and your favorite text editor—all typical Ruby on Rails app development workflows. For example, if you are using a Mac and TextMate, you might go to your terminal window and enter the following commands.

cd historical-opportunities-#####
mate .

Then, you can start editing the various files that make up your app.

The first thing you need to do is modify your app’s Gemfile. It should include a minimum of the following lines.

source 'https://rubygems.org'

gem 'rails', '3.2.11'
gem 'bundler', '1.2.3'

gem 'pg', '0.14.1'
gem 'gchart', '1.0.0'
gem 'omniauth', '1.1.2'
gem 'omniauth-salesforce', '1.0.3'

Some notes about these gems:

  1. If you want to use different versions of these gems, make appropriate changes to the version numbers.
  2. pg is a gem that facilitates access your local PostgreSQL database.
  3. gchart is a gem that simplifies use of the Google Chart API.
  4. omniauth and omniauth-salesforce are gems that simplify the use of OAuth2.

Once you modify and save your Gemfile, return to the terminal window and update your bundle.

bundle install

You should see the following message when your bundle is successful.

Your bundle is complete! ...

Otherwise, resolve any gem problems and rebundle.

Step 7: Provide Full Support for Rails

The minimalistic template app does not support all standard rake tasks, which you’ll need to manage your local database. But it’s easy to enable this functionality—just open the config/application.rb file, add the following line after the first uncommented line in the file, and save your work.

require 'rails/all'

Step 8: Configure Your Local PostgreSQL Database

Your local app development environment needs a local database. First, create a local PostgreSQL database login to use with your new Rails app. For example, you might create a new login role using the pgAdmin utility or a pgsql command such as the following.

CREATE ROLE opportunties_dev LOGIN
  ENCRYPTED PASSWORD ...
  NOSUPERUSER INHERIT CREATEDB NOCREATEROLE;

Please Google around for more information about PostgreSQL database administration if you need more help than what this article provides.

Now use those new database credentials to configure your Rails app’s access to the local database. Start by creating a config/database.yml file with the following lines.

development:
  adapter: postgresql
  username: opportunities_dev
  password: PASSWORD
  database: opportunities_dev

Make sure to specify the correct values for each setting (e.g., username and password) and save the file. Then create the local database using the following command from your current Terminal window.

RAILS_ENV=development rake db:create

Step 9: Scaffold the Opportunity Object

An easy way to get a working Opportunity model, controller, and set of views into your template Ruby on Rails app is to use the Rails scaffold command from the command prompt in a terminal window. For this tutorial, create an Opportunity model that stores a minimum amount of data from the Opportunity object.

rails g scaffold opportunity sfdc_id:string name:string amount:decimal stage:string lead_source:string closed_on:date order_number:integer —no-timestamps

Make one minor change to the generated db/migration/timestamp_create_opportunities.rb file—add a precision and scale to the amount field.

class CreateOpportunities < ActiveRecord::Migration
  def change
    create_table :opportunities do |t|
      t.string :sfdc_id
      t.string :name
      t.decimal :amount, :precision => 8, :scale => 2
      t.string :stage
      t.string :lead_source
      t.date :closed_on
      t.integer :order_number
    end
  end
end

Migrate the database and start your local server with the following commands:

rake db:migrate
rails server

Then open the URL http://localhost:3000/opportunities and use the scaffold pages to add at least four Opportunity records so that you have some data to play with. Make sure to create at least four records that vary as the following screen shows—a record won and a record closed in both 2012 and 2013, and all records with valid amounts. This data distribution will generate nice charts for you in the next step.


Xsfdc-8.png

Step 10: Implement Charts for Historical Opportunity Analytics

For this app, the Opportunities index action should display information about historical opportunities in your database using charts instead of text. The first step to accomplish this is in replacing the default index action code (everything between def index … end, inclusive) in app/controllers/opportunities_controller.rb with the following code.

  def index
    # Variables that store totals for Opportunities won and lost
    opportunities_won_total  = Opportunity.sum(:amount, :conditions => ['stage = \'Closed Won\''])
    opportunities_lost_total = Opportunity.sum(:amount, :conditions => ['stage = \'Closed Lost\''])

    # Pie chart of total won vs lost Opportunities
    @chart_by_total = GChart.pie3d do |g|
      g.title = "Opportunities Overall"
      g.data = [opportunities_won_total, opportunities_lost_total]
      g.colors = ["00FF00,FF0000"]
      g.legend = ["Won","Lost"]
      g.width  = 350
    end

    # Array variables that store sum total Opportunities won or lost for each year
    opportunities_won_year  = Opportunity.select("date_part('year', closed_on) as year, sum(amount) as total").group("date_part('year', closed_on)").where('stage = \'Closed Won\'').order("year")
    opportunities_lost_year = Opportunity.select("date_part('year', closed_on) as year, sum(amount) as total").group("date_part('year', closed_on)").where('stage = \'Closed Lost\'').order("year")

    # Variables to get max and min totals for setting graph axis range
    owm = opportunities_won_year.max_by{ |o| o.total.to_i }.total.to_i
    owl = opportunities_lost_year.max_by{ |o| o.total.to_i }.total.to_i

    ow = [owm, owl].max

    # Line chart, won vs lost, by year
    @chart_by_year = GChart.line do |g|
      g.title = "Opportunities by Year"
      g.data  = [
        opportunities_won_year.flat_map(&:total).collect{|i| i.to_i},
        opportunities_lost_year.flat_map(&:total).collect{|i| i.to_i}
      ]
      g.colors = [:green, :red]
      g.legend = ["Won", "Lost"]

      g.width  = 450

      g.axis(:left) do |a|
        a.range = 0..ow
      end

      g.axis(:bottom) do |a|
        a.labels = [opportunities_won_year.flat_map(&:year).join('|')]
      end
    end

    respond_to do |format|
      format.html # index.html.erb
      format.json { render json: @opportunities }
    end
  end

The comments in the code explain the basic purpose of this new controller action: to set up variables for the following replacement app/views/opportunities/index.html.erb view.

<h1>Historical Opportunities</h1>

<table>
  <tr>
    <td style="padding:45px">
      <%=image_tag(@chart_by_total.to_url) %>
    </td>
    <td style="padding:45px">
      <%=image_tag(@chart_by_year.to_url) %>
    </td>
  </tr>
</table>

Together, the new controller action and view use the gchart gem to leverage the Google Chart API. Refresh the http://localhost:3000/opportunities app URL and you should see something similar to the following.


Xsfdc-9.png

Now for user authentication.

Step 11: Integrate User Authentication with OAuth2

Only Salesforce users with the appropriate permissions should be able to see historical opportunities. Because you are going to integrate the UIs of the Salesforce and remote app, it makes sense to integrate the security of the Ruby on Rails app and your Salesforce org. This tutorial shows you how to use an OAuth2 with Force.com Canvas.

OAuth2 with Salesforce is easy to implement in a Ruby on Rails app using the Omniauth gems that you’ve already specified in your Gemfile. The first step is to create a simple initializer for Omniauth by creating the following config/initializers/omniauth.rb file.

Rails.application.config.middleware.use OmniAuth::Builder do
  provider :salesforce, ENV['CANVAS_CONSUMER_KEY'], ENV['CANVAS_CONSUMER_SECRET']
end

Notice that the above code specifies the OAuth2 provider as salesforce, then references two environment variables: CANVAS_CONSUMER_KEY and CANVAS_CONSUMER_SECRET. You must set these to the key and secret of a Connected App in your Salesforce org. The Heroku Quick Start wizard that you used to create the template Ruby on Rails app also configured a Connected App in your org. To take a look at this configuration, complete the following steps from the Force.com Setup area.

  1. Under App Setup, click Create | Apps.
  2. In the Connected Apps section, click Historical Opportunities.

A Connected App specification provides an OAuth2 integration point from your org to a remote app. To make this page work for your needs, click Edit and make changes similar to the ones in the following screen capture.


Xsfdc-10.png


Note the following and save your modifications.

  1. Set the suffix of the Callback URL to auth/salesforce/callback
  2. Set Selected OAuth Scopes to Access your basic information
  3. Set the suffix of the Canvas App URL to opportunities
  4. Set Access Method to OAuth Webflow (GET)

Normally, you’d also have to set the environment variables that the Omniauth configuration is expecting (CANVAS_CONSUMER_KEY and CANVAS_CONSUMER_SECRET), either by declaring these variables in your app itself or by declaring environment variables on your app’s host. This process would entail copying the values for the Consumer Key and Consumer Secret settings from the Connected App detail page.


Xsfdc-11.png


Fortunately, the Heroku Quick Start app wizard has already configured these environment variables for you. To confirm things, go back to your terminal window, stop the local server (e.g., with Command-C or Control-C), then execute the following command.

heroku config

You should see some output similar to the following.

=== historical-opportunities-34813 Config Vars
CANVAS_CONSUMER_KEY:           …
CANVAS_CONSUMER_SECRET:        …
DATABASE_URL:                  postgres://… 
...

The key and secret values should line up with the settings in your Connected App configuration.

Next, you must create the framework necessary to support Omniauth-driven OAuth2 authentication, namely a User model and Sessions controller. Use the following command to generate a User model and corresponding migration file.

rails g model user provider:string uid:string name:string nickname:string

Edit the generated migration file, db/migration/timestamp_create_users.rb and make it match the following source code.

class CreateUsers < ActiveRecord::Migration
  def self.up 
    create_table :users do |t|
      t.string :provider, :null => false
      t.string :uid, :null => false
      t.string :name, :null => false
      t.string :nickname, :null => false

      t.timestamps
    end
    add_index :users, [:provider, :uid]
  end

  def self.down
    drop_table :users
  end
end

You can now migrate your local database to create the Users table using the following command in your Terminal window.

rake db:migrate

Update app/models/user.rb so that it matches the following source code.

class User < ActiveRecord::Base

  def self.create_with_omniauth(auth)
    create! do |user|
      user.provider = auth['provider']
      user.uid = auth['uid']
      user.name = auth['info']['name']
      user.nickname = auth['info']['nickname']
    end
  end

end

Now for the Sessions controller. In your terminal window, create a new controller with this command.

rails g controller sessions

Then update app/controllers/sessions_controller.rb so that it matches the following source code.

class SessionsController < ApplicationController

  def callback
    auth = request.env['omniauth.auth']
    user = User.find_by_provider_and_uid(auth['provider'],['uid']) || User.create_with_omniauth(auth)
    session[:user_id] = user.id
    redirect_to session[:return_to]
  end

  def destroy
    session[:user_id] = nil
    redirect_to root_path
  end

end

To use the OAuth2 configuration in your Opportunities controller, edit app/controllers/opportunitites_controller.rb and add a filter for all Opportunity controller actions at the beginning of the file.

class OpportunitiesController < ApplicationController
  before_filter :login_required
  
  def login_required
    session[:return_to] = request.fullpath
    if session[:user_id]
      @current_user = User.find(session[:user_id])
    else
      redirect_to '/auth/salesforce'
    end
  end
 …

And finally, edit config/routes.rb to support the OAuth2 flow. At a minimum, routes.rb should contain the following source code.

CanvasRuby::Application.routes.draw do
  resources :opportunities
 
  match '/auth/:provider/callback' => 'sessions#callback'
  match '/signout' => 'sessions#destroy', :as => :signout
  root :to => 'opportunities#index'
end

The previous steps are just basic Omniauth configuration. If you want more background, please Google “Omniauth configuration.”

Step 12: Deploy the Modified Source Code to Heroku

Now you are ready to deploy the modified app to Heroku and test your OAuth2 authentication flow. In the terminal window, update your local git repository and then push it to Heroku.

git add .
git commit -am 'analytics'
git push origin +master

Once the push completes, load https://historical-opportunities-#####.herokuapp.com/opportunities/new in your browser (replace ##### appropriately). You should see the start of the OAuth2 flow, which should look like this.


Xsfdc-12.png


Click Allow to authenticate, then create a record similar to the one you did for your local database. Use the same URL above to create three other records and then you can test the index action of the Opportunities controller with the URL https://historical-opportunities-#####.herokuapp.com/opportunities


Xsfdc-9.png


Note: If you don’t see the OAuth2 flow, it most likely is because you are using the same browser that you use to log in to your Force.com Setup area. Try a different browser so that you are not sharing cookies among browser windows/tabs.

Congratulations, your remote app is now complete with user authentication that’s integrated into the Salesforce authentication system.

Step 13: Integrate UIs with Visualforce

Now it’s time to integrate the app’s UI into your Salesforce Sales app. You can do this very easily with Visualforce.

In your Force.com Setup area, complete the following steps.

  1. Under App Setup, click Develop | Pages | New.
  2. Create the page as shown in the screen below, then click Save.


Xsfdc-14.png


Notice that the source code for the new Visualforce page is very simple: It contains one tag, apex:canvasApp, that does nothing more than specify the name of the app and display attributes.

Next, make it easy to get to the new page by creating a custom tab for your Sales app.

  1. Under App Setup, click Create | Tabs.
  2. In the Visualforce Tabs section, click New.

Fill out the form on Step 1 of the wizard as the following screen shows, then click Next | Next, limit the new tab to the Sales app, then click Save.


Xsfdc-15.png


Finally, you can put the new tab next to the standard Opportunities tab by completing the following steps.

  1. Click Customize My Tabs in your personal settings.
  2. Promote the Historical Opportunities tab under the Opportunities tab, then click Save.


Xsfdc-16.png


Now you can click the new Historical Opportunities tab to display the UI of your remote app right from within the Salesforce Sales app. (Because you have already authenticated, you won’t see the OAuth2 flow.)


Xsfdc-17.png


Awesome, you’re almost done. Now for some data migration.

Step 14: Migrate Data from Salesforce to Heroku Postgres

The final step is to get data, specifically historical Opportunity records, flowing from Salesforce to Heroku Postgres. There are many ways to accomplish this. This tutorial shows you how to use the free version of a third-party utility, the Jitterbit Data Loader for Salesforce.com. This utility is ideal for your scenario because you can transfer data directly from Salesforce to a PostgreSQL database without having to deal with any intermediate steps.

Once you download and install this utility, start the application. The first thing to do is configure a connection to your Salesforce org. To do this, you first need a security token. Just click Reset My Security Token in your personal settings, then check your email and copy the security token that you receive. To configure a Salesforce org connection in the Jitterbit application, complete the following steps.

  1. Click File | New | New Salesforce Org.
  2. Enter your username, password, and security token appropriately.
  3. Click Test Salesforce Login to make sure the connection works properly.


Xsfdc-18.png

Next, configure a Jitterbit connection to your Heroku PostgreSQL database. Before you can do this, you need to gather some information using these commands, where the URL in the second command is the shared database URL given to you in the output of the first command.

heroku pg:info
heroku pg:credentials HEROKU_POSTGRESQL_color_URL

The output of the second command should resemble the following.

Connection info string:
   "dbname=dbString host=hostString port=5432 user=userString password=passwordString sslmode=require"

All those *String values above will be real values that you need to use to configure the connection in Jitterbit. Use these values to create a secure JDBC connection string in the format required by Heroku PostgreSQL.

jdbc:postgresql://<host>:<port>/<dbname>?username=<username>&password=<password>&ssl=true&sslfactory=org.postgresql.ssl.NonValidatingFactory

Copy this new connection string, then switch back to Jitterbit.

  1. Click File | New | Target.
  2. In the Select Type window, click Database, then Continue.
  3. Fill out the top part of the form to match what the following screen shows. Make sure to specify all the parameters above (e.g., Server Name, Database Name, Login, and Password) using the string values you get from the output above.
  4. Click Construct connection string manually.
  5. Paste in the JDBC connection string you created previously.
  6. Click Test Connection to make sure the connection works properly.


Xsfdc-19.png


Now that you have connections to both endpoints, you can create a query to move data from Salesforce to Heroku. With the paid version of Jitterbit, it’s best practice to create a Bulk Process/query to get the job done because it uses the Force.com Bulk API. But because the free version doesn’t support loading bulk query results into a remote database, you can use the SOAP-based alternative Query.

  1. Click File | New | New Query.
  2. Select the Salesforce connection you created previously, then click Next.
  3. Select Opportunity, then click Next.
  4. Use the query builder to build a query that selects all opportunity records that closed more than one year ago, or simply enter the following query into the Query string field, then click Next.
     
    SELECT Id, AccountId, Amount, CloseDate, LeadSource, Name, StageName FROM Opportunity WHERE StageName like 'Closed%' AND CloseDate < N_DAYS_AGO:365

  5. Click Database, select the Heroku PostgreSQL connection you created previously, then click Next.
  6. Click Insert Only, then click Finish.

Note: You can also create a schedule for a query to have it run automatically.

Now you have to create a column mapping. Drag the columns from the Opportunity object on the left to the appropriate fields on the opportunities table on the right. When you are finished, the mapping should look similar to what appears in the following image. When you are ready, click Finish.


Xsfdc-20.png


Before you test out the query, it’ll be more fun if you return to Force.com and edit the sample Opportunity records so that they have a good data distribution for the query to target. Specifically, make sure there are several records with a Stage of “Closed Won” and Close Dates that vary by year, more than one year from the current date. Do the same for “Closed Lost” records. For example:


Xsfdc-21.png


Once your data is ready, return to Jitterbit and run the query you created previously. If everything works properly, the Operation monitor section of the screen should indicate success.


Xsfdc-22.png


Return to your Force.com org and click the Historical Opportunities tab to refresh your analytics page. Depending on the sample data you tweaked in the Salesforce org, the charts should look dramatically different than before.


Xsfdc-23.png


Sales look great!

Extra Credit

The previous steps show you how to meet most of your requirements. To finish the job, you much complete the following steps, which I’ve left to you as extra credit.

  • Use a Jitterbit bulk process to permanently delete the historical records from Salesforce after they are safely migrated to Heroku Postgres.
  • Set the query to run on a schedule every Saturday while you are doing your household chores.
  • Modify the CSS for your Rails app so that the text styles more closely match the Salesforce UI styles.
  • Read the Force.com Canvas Developer’s Guide and use additional Force.com Canvas features to pass state between the Salesforce org and remote app, perhaps to build features that let users drill down into the historical data.

Related Resources

About the Author

Steve Bobrowski is an Architect Evangelist within the Technical Enablement team of the salesforce.com Customer-Centric Engineering group. The team’s mission is to help customers understand how to implement technically sound Salesforce solutions. Check out all of the resources that this team maintains on the Architect Core Resources page of Developer Force.