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.
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.
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:
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:
- Learn by building some initial dashboards yourself with the instructions in the chartio-for-pipes GitHub repo.
- 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:
- working with custom schemas
- joining and merging data
- previewing the additional pre-built dashboards that Chartio can, upon request, clone into your account
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.
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.
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.
Locate the dashDB instance you connected earlier and search for the
st customer table.
We want our salesfolk to be able to quickly contact our most delinquent customers. Drag
Delinquent into “Filters,” then filter on “equals 1.”
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.
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.
There, that’s better. Now just change your visualization from table to bar chart.
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:
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.