Over my career I’ve noticed that most applications have very limited reporting and charting features. I’ve tried to write some of my own and realized quickly why this is–analytics is hard! Visual query builders, dynamic charting, aggregation of data, it’s all really tough to do right. I also think most application teams start out with the feature list of business processes that need supporting–recording transactions, editing information, etc. I think they often punt on the analytics engine for these reasons–it’s hard and the value is limited if the app doesn’t even do what it needs to do yet. Because of this, most apps come later, if at all to the analytics game.

And because most of the apps we use have lousy analytics, we often don’t know what we’re missing. In my CRM consulting it was very common for people to under-value the analytics compared to the process streamlining aspects. Getting things done more quickly is important, but analytics can help tell you if you’re doing the right things.

Salesforce.com has a great analytics engine. It’s an amazing blend of efficiency and power. Easy things are in the reach of most users, and hard things are possible without coding.  Why are the analytics so good? Salesforce.com is a billion dollar company. They’ve spent amazing amounts of money building their platform, and as part of that, the killer analytics engine. They’ve made it a strategic priority, invested in it, and iterated over 10 years. It really shows.

Great, so Salesforce.com has good analytics. You might think that if you want to use those analytics tools, you need to ditch your app and rewrite the whole thing on Salesforce.com, right? Porting a functioning application is a big project, and while sometimes it might makes sense to do that, a lot of times it doesn’t. You may not have the money to do it. You may be on an open source platform and not want to go to a proprietary one.

But it turns out you don’t need to ditch your app to get Salesforce.com analytics for your data. Using free and open source tools you can bolt the Force.com Free Edition onto your application and very quickly get the power of Salesforce.com analytics for 100 users. For free. I did it myself last weekend. Here’s how it works:

  1. Get your app up and running
  2. Get Force.com Free Edition, or use your existing Salesforce.com organization
  3. Create tables in Salesforce matching the key tables in your app
  4. Create some simple triggers to get all your data relationships right
  5. Set up an extract-transform-load (ETL) server like Jitterbit to pull data from your app and push it to Salesforce.com.
  6. Set up reports and dashboards on that data
  7. Let your data inform the strategic direction of your organization

If you do all this, you can pull data from your application directly into Salesforce.com, automatically updating it as changes are made in your app. Then your end users can get the analytics they can’t get in the app. And your key decision makers, who probably don’t use the app in the first place, can get the top level numbers and trends that help them manage the impact of your organization.

I’ll now go into detail on each of the steps above to show you how it can be done.

Step 1: Get your app up and running

I chose to work with Mifos, an open source application that helps microfinance institutions track their loans and borrowers. Microfinance is a global effort to get working capital in the hands of the poorest people. It’s amazing work being done by organizations all over the world. Mifos happens to be built on Tomcat with a MySQL database.  But which app I use really isn’t important. All that matters is that is has extensive functionality for specific business cases, and it has a database we can directly access. If it does these things, it’s a candidate for Salesforce analytics.

I installed Mifos on an Amazon.com EC2 server, which saved me the trouble of trying to get it to run on my Mac. I love EC2!

Step 2: Get Salesforce

I got a free Developer account for this test case, but you could get Salesforce.com donated to your nonprofit, go with the Force.com Free Edition, or purchase Salesforce.com.

Step 3: Create your schema

Creating analogous tables to all the tables in your app is one of the more time-consuming parts of this process. I sped things up by using Model Metrics’s great Cloud Converter tool. I got the field names and one row of values for each table I cared about–you don’t need to get every table, just the ones that figure into your analytics. I created an Excel tab for each table, and pasted the header names and values into each. I then let the Cloud Converter loose on it, and the tables were created in just a few minutes! There might be some cleanup necessary with field types–Cloud Converter guesses what type the field should be, but sometimes it guesses incorrectly. You can do this by hand, or use the metadata API with Eclipse for the fastest action.

All the tables in your app probably have primary keys–some unique Id field that marks each row. In your Salesforce schema, you want to mark the analagous fields are External Ids. This will allow you to use Salesofrce’s Upsert web service method. Without External Ids, you need to know the Salesforce.com record Id in order to Update it. Upsert is incredibly amazing for this use case–it makes it all so easy. Just pass it the primary key from the app and it will find the right record in Salesforce.

The app you’re coming from has relationships between tables (using the primary key fields I just talked about), so you need to recreate those relationships in Salesforce. Cloud Converter might be able to do this for you, but I didn’t try that out. Create your lookup relationships between the objects and you’re good to go.

Step 4: Create your triggers

The ETL server will create your Salesforce records just fine, but it won’t make sure they’re related to the correct parent records for you. To do this you need simple triggers on each object to look at the foreign key from your app find the right Salesforce object, and relate the two. Here’s an example where the Account Payment object looks for the parent Account when it’s inserted or updated. If it finds it, the relationship gets recorded and the Account Payments show up under the Account:

trigger accountPaymentParentfinder on account_payment__c (before insert, before update) {
	
	Set<Integer> accountIds = new Set<Integer>();
	
	Map<Integer,Id> accountIdMap = new Map<Integer,Id>();
	
	//loop through the trigger set and build a set of all the Account Ids
	for(account_payment__c myAccountPayment : Trigger.new){
		if(myAccountPayment.account_ID__c!=null){
			accountIds.add(myAccountPayment.account_ID__c.intValue());
		}
		
	}
	
	//go query for those Account Ids and create a map connecting them with the Salesforce Ids
	for(account__c thisaccount : [select id,account_Id__c from account__c where account_Id__c IN :accountIds]){
		accountIdMap.put(thisaccount.account_Id__c.intValue(),thisaccount.id);
	}
	
	//put the right Salesforce Id in the lookup relationship
	for(account_payment__c myAccountPayment : Trigger.new){
		if(myAccountPayment.account_ID__c!=null){
			myAccountPayment.account__c = accountIdMap.get(myAccountPayment.account_ID__c.intValue()); 
		}
	}
}

You need to do this for each object. If an object has more than one lookup relationship, you’ll need to create the Set and Map structure for each. They all follow the same pattern. Easy peasy.

Step 5: Set up your ETL server and transformations

I chose Jitterbit for my ETL server. They have a free Community edition but I used a 30 day trial of their Enterprise edition, only because there was a machine image on Amazon.com EC2 for the enterprise version. Installation was a 10 minute affair.

The Jitterbit client is where you create the operations that pull data from the source and get it where you want it to go. I created an operation to log in to Salesforce.com and store my session Id. Then you pretty much create one operation for each table in your app, grabbing the data and passing it to Salesforce.com’s Upsert web service method. This will try to update a record that exists with that External Id. If it can’t find the record, it will create one. Brilliant.

It’s a ton of drag-and-drop mapping–this app field maps to this field on the Salesforce object. But once you get the hang of it, you’ll crank through all your tables.

When your operations are set up you run them, starting with the login, until all your data comes over. These operations can be scheduled to run as often as makes sense to you. You can pull over only data modified since you last pulled. I created a very simple data flow–one way from my app to Salesforce.com. But you can really do just about anything. You can send data back to your app. You could pull from 25 different apps all to Salesforce. It’s up to you.

Step 6: Set up your reports and dashboards

Once your data is in Salesforce.com and related correctly, start creating your reports and dashboards. What I find is that when I create a dashboard the next one immediately comes to mind. Loan default rate over time. But does marital status have an impact on that? What about number of children the borrow has? And what are the patterns for defaults? Do loans tend to default more frequently during the dry season? In the months before harvest? Do non-agriculture loans behave differently? People who work at microfinance institutions ask themselves these questions. They see patterns and want the data to confirm or challenge their assumptions. Because Salesforce.com makes analytics easy, users can ask these questions and get immediate answers. Dashboards can even be sent to them via email every week.

Then step back and take a look at your analytics. Here are some I created with a sample data set in my app:

I was very pleased with the story I am just starting to tell about the loan programs at this pretend microfinance institution. I think the take away is this: Salesforce.com can add value by helping you be creative in telling the story of your data. It’s a story that might be hard for you to tell right now. But with very little cost other than time and technical ability, you can add flexible, user-driven analytics to any database application by following this recipe.

You can add this power to your app. But the really amazing part is how easy it is to share this kind of setup. Once you create a schema that matches Mifos or OpenMRS or CiviCRM you can package that up in Salesforce and share it with anyone. Just a couple clicks and they can install all the objects, triggers, and dashboards for that app. Jitterbit operations can be shared as well. You export them as a “jitterpak” and anyone can use them with their setup. Just change the login information, point to your Salesforce.com web services and it should work without changes.

And perhaps the most important thing is that you’re not embedding Salesforce.com into your app. You’re not requiring that everyone using the app use Salesforce.com. I hop this example shows that Salesforce.com can be “bolted-on”, adding value along side your mission-critical application. The cost can be very low, the impact can be high, and you can do it without making big decisions about app architecture direction.