Cloud Data Warehousing, Brought to You by the Simple Data Pipe and dashDB

A data warehouse (or “operational data store,” a.k.a. ODS) with one data source isn’t particularly interesting. But an ODS with more than one, is. Consolidating data from different web services sets the stage for running the analytics your business wants.

In my other tutorial on analyzing Salesforce data, I described how to use our Simple Data Pipe example app to move Salesforce.com JSON into dashDB, IBM’s cloud data warehouse service. In this article, we’ll explore combining that CRM data with added context from Stripe, a popular Web payments system, and visualizing our data with Chartio‘s BI platform.

To recap, here’s what we’re working with in this article:

  • Stripe: Built for developers, the Stripe API makes it easy to integrate its payments system into web and mobile apps.
  • Chartio: Cloud business intelligence platform that combines powerful analytics with easy visualizations and dashboards that make data more accessible to everyone in your company.

Reports

While our goal is to consolidate different data sources for richer analysis, visualizing each data source on its own is also useful. Chartio does a good job here. With your ODS set up, you can easily isolate different sources in their own dashboards.

Chartio dashboard of Salesforce.com data
Chartio dashboard of Salesforce.com data
Chartio dashboard of Stripe.com data
Chartio dashboard of Stripe.com data

You can also easily combine different data sources into the same Chartio dashboard. Now your reporting story starts getting interesting — and your CEO starts smiling because “This! This is what I was talking about!” ← That’s CEO excitement right there.

Here we have both Salesforce and Stripe reporting on one dashboard, with all the data available for exploration:

Chartio dashboard of Salesforce data combined with Stripe data
Chartio dashboard of Salesforce data combined with Stripe data

Pre-reqs

We assume that readers have experience with our first Simple Data Pipe tutorial and have used it to populate a dashDB instance with Salesforce.com data. An additional tutorial by Patrick Titzler, Configure Simple Data Pipe for Stripe.com, will walk you through the OAuth process for Stripe. Complete these two tutorials before proceeding further [1, 2].

Starting with Chartio

We have a special registration page with Chartio to give our intrepid readers free access while completing this tutorial: http://landing.chartio.com/demo-ibm. And if you already have access, then great!

You have two options for getting these dashboards into your Chartio system:

  1. Learn by building some initial dashboards yourself with the instructions in the chartio-for-pipes GitHub repo.
  2. Get quick access to a wider range of handy Salesforce/Stripe dashboards by asking Chartio to clone them into your environment auto-magically.

All the Steps for Chartio & dashDB

A.J. Welch from Chartio (@ajw0100) has prepared a wonderful set of visual instructions that take you from connecting Chartio to dashDB, all the way through JOINing data and dashboarding your combined Salesforce and Stripe analytics: https://github.com/ibm-cds-labs/chartio-for-pipes.

With his permission, I’m going to recap some of his initial instructions, specifically these: connecting to dashDB and creating your first chart. I’ll skip some steps here for brevity, but I want to highlight that there are more awesome step-by-step instructions in A.J.’s GitHub repo, like:

Connecting Chartio to dashDB

dashDB is one of many data source connections that comes built into Chartio. Adding it is as simple as selecting “dashDB” as the data source type and entering your account details from the VCAP_SERVICES environment variable on IBM Bluemix.

connect dashDB as a Chartio data source
dashDB is a built-in data source for Chartio

When you’re done, you’ll receive a notification that schema extraction for your dashDB data source has started, and another when extraction is complete. Select your newly created data source and check the schema tab to verify that your tables were correctly imported.

Checking the schema tab in Chartio
Check your table import

If you’re wondering about any “Overflow” tables, don’t worry too much. These come from the IBM DataWorks integration with Cloudant as part of our Simple Data Pipe, well, pipeline ;-) DataWorks is the data prep and shaping service on the Bluemix app platform. If DataWorks finds any schema errors while it’s moving JSON from various sources, staging it in Cloudant (a “NoSQL” native-JSON data store based on Apache CouchDB™), and converting that JSON into a relational format for dashDB — it will stash that data in an overflow table so you won’t lose it. We can safely ignore them in our tutorial.

Creating Your First Dashboard

We’ll start with a simple example that only visualizes the Stripe data we stored in dashDB. We’ll build a dashboard of the top 5 delinquent customers by plan amount. Your salespeople will love this one :D

Creating a new dashboard in Chartio is easy. Once you’ve named and categorized it, it’s time to add an element to your chart.

Adding an element to a chart in Chartio
Build your first dashboard

Locate the dashDB instance you connected earlier and search for the st customer table.

Searching for a table in Chartio
Choose your dashDB connection and table

We want our salesfolk to be able to quickly contact our most delinquent customers. Drag Email into the “Dimensions” field and Delinquent into “Filters,” then filter on “equals 1.”

Setting chart dimensions and filters in Chartio
Set the dimension and filter

Now to identify which customers owe the most. Find the St Customer Subscriptions table and drag Plan Amount into the “Measures” field and specify to sort by descending order.

Adding additional tables and fields to a Chartio chart
Set some measures

Limit the result set to 5 and click “Refresh Chart.” You’ll have the top-5 delinquent accounts, but their plan amounts will be rather large sums. That’s because they’re listed in cents. Find the “Add Step” button to divide the values under Plan Amount by 100 and convert to dollars.

Convert Stripe data to dollars in Chartio
Hundred-dollar bills, y’all

There, that’s better. Now just change your visualization from table to bar chart.

Changing Chartio columns into a bar chart
Let’s all go to the bar — chart, that is

Under “Chart Settings,” you can title your chart and label its y-axis. When you’re done, save your chart and Chartio will publish your dashboard. Here’s what your completed dashboard should look like:

Completed Chartio dashboard for top-5 delinquent accounts from Stripe data
You win. We all win. The end?!

There’s More. A Lot More.

We’ve seen how simple it is to visualize important business data that might have otherwise been trapped within the Stripe API. With the Simple Data Pipe configured and connected to Chartio, this information suddenly becomes a lot more accessible to knowledge workers and business execs within your company. That’s a win for everyone involved.

You can do a whole lot more with Chartio. Be sure to visit the chartio-for-pipes repo on GitHub for additional tutorials on creating custom schemas and JOINing data from different sources — and rolling it all up into more sophisticated dashboards. There are also instructions for contacting Chartio to access a wider suite of pre-built Stripe/Salesforce dashboards you can use for your own business. With the Simple Data Pipe, IBM dashDB and Chartio, you’ll be up and running in no time.

Join The Discussion

Your email address will not be published. Required fields are marked *