Abstract

Visualizing data through the use of dynamic graphs, charts and other graphics, is often a powerful way of creating meaning from your data. Much of the functionality needed to accomplish this has already been created - see for example Google's Visualization API. The API also lets you connect to some (external) data source and expose the Google Gadgets and Google Visualizations not just on your own website, but on any website that supports embedding the gadgets and visualizations.

A previous article, Google Visualization Components, describes how you can embed these visualizations in your Force.com applications. This article takes the integration a level further by showing you how to expose Force.com data in a way that is compliant with the Google Visualization API.

This lets your carefully crafted public data drive the visualizations—as a result you and others can create and embed powerful and dynamic visualizations. This article also provides code and other resources.

Driving Google Visualizations

Recently, there has been a push for greater transparency and access to data. This has been evidenced by the U.S. Government's new Data.gov website, as well as by Google's Public Data project. This article will not focus on the visualization of your Force.com data, but will instead describe how you can expose select portions of your business' data to the public, thereby enabling these visualizations.

The Google Visualization API enables you to both consume and publish data, as shown in the following diagram:

GViz Architecture.jpg

Here, multiple data sources are driving a number of different visualizations.

This article will focus on how to publish data from your Force.com org to the internet for public consumption, in a format compatible with the Google Visualization API. You, or others, can then create visualizations around that data.

Use Case Ideas

Much of your company's data probably should not be published publicly on the internet, but you can certainly imagine a number of situations where publishing some your company's data could be very beneficial.

For example, if your company is putting on a user conference, your marketing team might want to expose some of the attendees' demographic data so that the conference attendees can create heat maps of shared geographic locations, shared topics of interest, etc.

Or your sales team might want to expose a list of its most popular products so that your customers can see what other customers are buying - similar to the way that Amazon publishes lists of its bestselling items.

Or your support team might want to expose its call volume data so that customers know when they're most likely to be able to speak with an agent directly.

There are many additional reasons why you might want to publish some of your company's Force.com data to the internet. Once you do, your customers may just innovate and come up with additional uses for your data that you had never even thought about.

Getting Started and Demo Use Case

This article walks you through a simple call volume use case, described above. Publishing your support team's call volume data could potentially benefit both your business and your customers. Your customers can use the data to make an informed decision about when to call your support team. And if they do so, your support team will benefit because customers may decide to call during non-peak hours and more evenly distribute the overall call volume.

If you want to follow along with code, you'll need the following items to get started:

  • The Google Visualization Data Source code is part of the broader Force.com Google Visualization project, so you will need to follow the Google Visualizations Install Instructions to get and install the code. This will ensure that the GoogleDataSourceController class is available.
  • You'll need a simple object to store the data that you want to expose. We're going to assume you're using the Call_Volume__c custom object included in the Google Visualization project. This screenshot shows a sample Call_Volume__c object:

GViz Sample Call Volume.jpg

Project Structure

Most of the work of creating a Google Visualization Data Source has been done for you, as part of a class called GoogleDataSourceController. In order to create your own Google Visualization data source, you will need to create a Visualforce page, and expose it to the Internet using Force.com Sites. You will also need to create a Visualforce controller that supplies a couple of important values. Here's a high level overview of how all the code fits together:


GViz Data Source Actor Diagram.png

What's happening here? Well some visualization (say a Google Gadget exposed on your company's home page) has been called up on someone's browser. This is going to request the data (it's the Data Source Consumer), which is going to result in a call to a public Visualforce page. The API can work by simply having the raw data exposed on a web page in a particular format. Our controller, which extends the GoogleDataSourceController, is going to do the job of exposing this data. As you can see from the diagram above, the controller is parametrized, and you can determine which columns or tables should be fetched from the database. The controller then massages the data into the appropriate format, which can finally be consumed by the gadget.

You have to do very little work to get this to work, so let's walk through each of the important items in more detail.

Visualforce Data Source Page

The data source page is the publicly-addressable endpoint that will expose your Force.com data. To create this endpoint, you will need a Visualforce page that has been exposed to the public internet via Sites. The Visualforce page should be very simple – similar to the following page, which we've named CallVolume:

<apex:page controller="CallVolumeDataSourceController" 
           showHeader="false" contentType="text/html">{!Response}</apex:page>

There are five things that your Visualforce page must do:

  • Define the contentType as "text/html"
  • Hide the Salesforce.com tab header.
  • Not put any line breaks between the opening and closing <apex:page> tags.
  • Reference your controller, CallVolumeDataSourceController in this case.
  • Call the getResponse() method that your controller inherits from the GoogleDataSourceController class, which does the work of inserting the data for the visualization.

The above code does all of this.

Data Source Controller

Creating a data source controller doesn't require you to write a lot of code either. Your controller will extend the GoogleDataSourceController class. As a result, your controller will be able to take advantage of the GoogleDataSourceController's methods. Your controller will just need override two of the GoogleDataSourceController's methods: getTableName() and getDefaultColumns(). The CallVolumeDataSourceController sample below is an example of this:

public class CallVolumeDataSourceController extends GoogleDataSourceController{
    
    public override String getTableName(){ return 'Call_Volume__c'; }
    
    public override List<String> getDefaultColumns() { 
        return new List<String>{ 'Id', 'Name', 'Date__c', 
                                 'Hour__c', 'Number_Of_Calls__c' }; 
    }
}

The getTableName() method defines the custom object that your endpoint will query. The getDefaultColumns() method defines the fields that your endpoint will return if a specific query isn't specified using the 'tq' parameter - we'll discuss this in more detail later in the article.

To see this code in action, check out this sample endpoint: http://gvizdatasource-developer-edition.na7.force.com/demo/CallVolume - It should return something similar to the following text:

google.visualization.Query.setResponse({version:'0.5',reqId:'0',status:'ok',
table:{cols: [{id: "col0", label: "Id", type: "string"},
              {id: "col1", label: "Name", type: "string"},
              {id: "col2", label: "Date__c", type: "date"},
              {id: "col3", label: "Hour__c", type: "string"},
              {id: "col4", label: "Number_Of_Calls__c", type: "number"}], 
       rows: [{c:[{v: "a00A0000000q9fkIAA"},{v: "CallVol-00002"},
                  {v: new Date(2009, 7, 3, 0, 0, 0), f: "8/3/2009"},
                  {v: "9 AM"},{v: 193.0}]},
              {c:[{v: "a00A0000000q9flIAA"},{v: "CallVol-00003"},
                  {v: new Date(2009, 7, 3, 0, 0, 0), f: "8/3/2009"},
                  {v: "10 AM"},{v: 225.0}]},
              {c:[{v: "a00A0000000q9IHIAY"},{v: "CallVol-00001"},
                  {v: new Date(2009, 7, 3, 0, 0, 0), f: "8/3/2009"},
                  {v: "8 AM"},{v: 120.0}]}]}});

GoogleDataSourceController

I've just walked you through an example of implementing the code required for a Google Visualization Data Source. The CallVolumeDataSourceController that we looked at extends the GoogleDataSourceController class. The reason why you don't have to write a lot of code to implement a Google Visualization API-compliant data source is because the majority of the required functionality has already been implemented by the GoogleDataSourceController class.

The GoogleDataSourceController class parses the parameters passed to the endpoint on the query string, including the 'tq' parameter, it maps the queried fields to the appropriate data types, constructs the Google Visualization JSON data structure, and finally, the GoogleDataSourceController formats the response in a way that is Google Visualization API-compliant.

Here are the prototypes for all of the public methods in the GoogleDataSourceController class:

    public String getResponse(){};
    public virtual LIST<String> getDefaultColumns(){};
    public virtual String getTableName(){};

You'll notice that the only concrete method is the getResponse() method. It is responsible for orchestrating the parsing, mapping and response construction processes. In order to construct its response, the getResponse() method will call the two virtual methods which you will have defined in your data source controller: getTableName() and getDefaultColumns().

Specifying Queries Using the 'tq' Parameter

The Google Visualization API supports a query language that is similar to SOQL. In our implementation, the GoogleDataSourceController is responsible for parsing the query and passing it through to the underlying database.

For all Google Visualization data sources, including ours, queries are specified by adding a 'tq' parameter value. For example, instead of just querying the CallVolume data source's default fields by addressing the http://gvizdatasource-developer-edition.na7.force.com/demo/CallVolume endpoint, you can also query for specific fields in a specific order by sending a query statement as part of the 'tq' parameter: http://gvizdatasource-developer-edition.na7.force.com/demo/CallVolume?tq=SELECT Name, Date__c, Hour__c, Number_Of_Calls__c ORDER BY Hour__c

Not all features of the Google Visualization Query Language are supported by the GoogleDataSourceController, as it just passes the query through as SOQL. You can specify the fields you'd like returned in the SELECT clause, conditions for the WHERE clause, and how to order the returned records using ORDER BY, just as you would in SOQL. Other SOQL features are not guaranteed to work.

You will find that the iGoogle gadgets are very useful for protoyping data source queries, and we'll cover using iGoogle gadgets later on in this article.

Configuration Settings

We have walked through the code that is required to expose your Force.com data as a Google Visualization Data Source, but in order to make your Google Visualization Data Source live, you will need to make a number of changes to your org's configuration settings.

Force.com Sites Setup

This project relies upon Force.com Sites, so as a prerequisite, you'll need to set up a Site.

Once you have your Site set up, your Site Setup details should look something like this:

GViz Site detail page.jpg

Next, you'll need to expose your endpoint page to make it publicly available in your Site. To do this, click on the "Edit" button in your Site's "Visualforce Pages" related list:

GViz VF Page Related List.jpg

Guest Profile Security Settings

Next you'll need to modify some of the security settings for your Site's associated Profile. To get started, first click on the "Public Access Settings" button for your Site. GViz Public Access Settings.jpg

Next, click on the "Edit" button in the "Enabled Apex Class Access" related list, and select your controller, in this case the CallVolumeDataSourceController, as well as the GoogleDataSourceController class. Add them to the Enabled Apex Classes list.

GViz Enabled Apex.jpg

You'll also need to enable Read access for the Call_Volume__c custom object for your Site's associated Profile.

GViz Custom Object Access.jpg

While you're editing the Guest Profile's security settings, now is a good time to take a look at the Field Level Security settings for the Call_Volume__c object as well:

GViz FLS - All.png

If you click on the 'view' link for the Call_Volume__c object's Field Level Security Settings, you should see something like this:

GViz FLS - Call Volume.png

From here you can adjust the visibility of the fields on the Call_Volume__c object.

Creating Sample Data

Lastly, you'll need to create at least one Call_Volume__c record. In order to do that, you'll first need to expose the Call Volume Custom Tab to your Profile. If you're in a Developer Edition org, this might be the System Administrator Profile. Once you're on the appropriate Profile, click on the "Edit" button and change the Custom Tab Settings value for the Call Volume Custom Tab to "Default On".

GViz modify system admin profile.jpg

Lastly, go to the Call Volume Tab and create a record.

GViz New Call Volume.jpg

Now, you should be able to view all of the data in your Call_Volume__c custom object by navigating to something like http://{!YourSubdomain}.{!YourInstance}.force.com/{!YourSite}/CallVolume. The resulting output should look like this:

GViz Data Source Output.jpg

Data Source Consumers

Now that your data source is publishing data, you can think about how to consume the data.

Google Gadgets

The quickest way to consume data from a Google Visualization Data Source is by using an iGoogle gadget like the Simple Table Gadget. Here's a screenshot of the setup for the Simple Table Gadget consuming the http://gvizdatasource-developer-edition.na7.force.com/demo/CallVolume endpoint:

GViz iGoogle Setup.jpg

The http://gvizdatasource-developer-edition.na7.force.com/demo/CallVolume enpoint is live, so you can do this yourself if you want. Here's a screenshot of what the Simple Table Gadget should look like in action:

GViz iGoogle.png

You can do this right now:

Google Visualizations

As you might expect, you can also use Google Visualizations to consume your published data - you'll just need to write a little bit of JavaScript code. Here's an example of a Visualforce page consuming the CallVolume endpoint.

The most interesting part of this page is the JavaScript that loads the Google Visualization and points it at the CallVolume endpoint:

<!-- Import the GViz API -->
<script type="text/javascript" src="http://www.google.com/jsapi"></script>
    
<!-- Load the columnchart GViz package. -->
<script type="text/javascript">
    google.load("visualization", "1.1", {packages: ["columnchart"]});
</script>

<!-- Visualize the Data Source's data. -->
<script type="text/javascript"> 
   	
    // Specify the GViz Data Source endpoint:    
    var query = new google.visualization.Query(
    'http://gvizdatasource-developer-edition.na7.force.com/demo/CallVolume'); // You'll need to replace this url with your own data source url.
              
    // Specify the query that we want to run:      
    // Notice that you can only query for records in the Custom Object that is exposed by your endpoint.
    query.setQuery('SELECT Hour__c, Number_of_Calls__c ORDER BY Hour__c');
        	  
    // Execute the query with a callback function:
    query.send(drawColumnChart);
	
    function drawColumnChart(response) {
        var data = response.getDataTable();
		
        var chart = new google.visualization.ColumnChart(
                    document.getElementById('chart_div') );
        chart.draw(data, 
                   {width: 400, 
                    height: 250, 
                    legend: 'none', 
                    title: 'Today\'s Call Volume', 
                    is3D : false,
                    borderColor : 'black',
                    focusBorderColor : 'white',
                    colors : ['#032F8B']
                   });   		        	   
    }
</script>

Note that the code makes use of the standard Google Visualization JavaScript API to create the graph and connect with the datasource. As a result this code can be hosted and run on any web server - simply paste it into an HTML file.

Security

One last word about security - this project asks you to grant unknown users access to portions of your Force.com instance. Done incorrectly, this can be a dangerous proposition. If you're uncertain about any aspect of using this code, I encourage you to err on the side of caution - the code has not been thoroughly tested by security experts, and is not warranted as such.

Summary

This article shows how to expose data on Force.com in a format that makes it compatible for use a data source for Google Visualization. As a result, the data can then be used in any number of visualizations, such as the Google Gadget and embedded visualization demonstrated in the article.

References

About the Author

Jesse Lorenz is a Technical Evangelist at salesforce.com, helping independent software vendors, product teams and other partners to architect and build innovative applications on Force.com.