Create a business intelligence and analytics service in Ruby with the dashDB service

Add a warehouse database plus analytics tools to your app on IBM Bluemix

08 May 2015
PDF (495 KB)
Praveen Devarao

Praveen Devarao

Senior Staff Software Engineer

dW Connections

Mario Briggs

Mario Briggs


dW Connections

The dashDB service (formerly known as the Analytics Warehouse service) available in IBM Bluemix™ provides a powerful, easy-to-use, and agile platform for business intelligence and analytics tasks. It is an enterprise-class managed-service powered by the in-memory optimized, column-organized BLU Acceleration data warehouse technology. With a few clicks of the Bluemix UI, create a ready-to-use business intelligence and analytics service for your application. Then, walk through the steps to create a simple chart-based application that uses the dashDB service and deploy it on Bluemix.

WATCH:See IBM Bluemix in action

WATCH:Webcast: Ruby Business Intelligence and Analytics Service on IBM Bluemix

Before you start


To build the application in this article, you need:

  • Familiarity with the Ruby programming language
  • Familiarity with these Ruby modules:
    • Sinatra: Ruby web application library
    • ibm_db: Ruby driver/Rails adapter for IBM Data Servers
    • Google Charts: A simple Ruby wrapper over the Google Chart API
  • Familiarity with using the cf cloud foundry command line tool

In a step-by-step process, you build and deploy a Ruby-based Sinatra application on Bluemix that uses the dashDB service.

Step 1. Create a basic Sinatra app

  1. Create a Gemfile for your application that lists the necessary gems for this application:
    source ''
    gem 'sinatra'
    gem 'ibm_db'
    gem 'googlecharts'
    gem 'rack'
  2. Install the dependencies:
    bundle install
  3. Create a simple Sinatra app with method get '/' to render the index page. Let's name the file bluaccl.rb.

    This method is called when a user views the root page.

    require 'rubygems'
    require 'sinatra'
    require 'ibm_db'
    require 'json'
    require 'gchart'
    Tilt.register Tilt::ERBTemplate, 'html.erb' #Set template engine as erb for Sinatra
    get '/' do
      erb :index
  4. Under the views folder, create an index.html.erb file with a greeting message that is rendered by method get '/':
    <h1>Hello Sinatra</h1>
  5. Create a rackup file, named
    require './bluaccl'
    run Sinatra::Application
  6. Run your application with the command:
    $ rackup

    Go to the link http://localhost:9292 and see your Sinatra app.

    Screen capture of a basic Sinatra app that is up and running

    Click to see larger image

Step 2. Make queries to the dashDB service


Now you're ready to access the dashDB service from your application by using the ibm_db gem and then run queries against it.

  1. Access the dashDB service with the ibm_db gem. Retrieve database credentials from the VCAP_SERVICES environment variable when your application is running on Bluemix.
    #Parse VCAP_SERVICES to get Analytics Warehouse Service credentials
    # we are running inside PaaS, access database details from VCAP_Services
     $db = JSON.parse(ENV['VCAP_SERVICES'])["AnalyticsWarehouse"]
     $credentials = $db.first["credentials"]
     $host = $credentials["host"]
     $username = $credentials["username"]
     $password = $credentials["password"]
     $database = $credentials["db"]
     $port = $credentials["port"]
    # we are running local, provide local DB credentials
     $host = "localhost"
     $username = "bludbuser"
     $password = "password"
     $database = "BLUDB"
     $port = 50000
  2. Connect to the dashDB service, execute queries, and process result sets using the ibm_db gem's APIs.

    Click to see code listing

    require 'ibm_db'
    def getDataFromDW
    #Connect to database using parsed credentials
      conn = IBM_DB.connect "DATABASE=#{$database};HOSTNAME=#{$host};PORT=#{$port};PROTOCOL=TCPIP;UID=#{$username};PWD=#{$password};", '', ''
      #Run the analytic SQL
      stmt = IBM_DB.exec conn, $profitAnalysisSQL
      data = {}
      while(res = IBM_DB.fetch_assoc stmt)
        if data.has_key?(res['PRODUCT'])
          data[res['PRODUCT']][res['YEAR']] = res['PROFIT']
          profit = {}
          profit[res['YEAR']] = res['PROFIT']
          data[res['PRODUCT']] = profit
      IBM_DB.close conn
      return data

Step 3. Create a bar graph with Googlecharts


Next, we describe how to draw a bar graph that is based on the data that is retrieved from the dashDB service by using Google Charts. Google Charts provides various options to generate several graph types such as line graphs, bar graphs, or pie charts.

This code example uses the Ruby Googlecharts module, a simple wrapper over the Google Chart API to draw a bar graph for the data we retrieved from the database.

def renderBarGraph data
  array2011 = [] #Array group that contains profits for Brands respectively for year 2011
  array2012 = []
  array2013 = []
  productNames = []

  #Render a Bar chart that shows profits of each Product Brand in comparison year-to-year

  data.each do |product,profitHash|
    productNames << product
    profitHash.each do |year, profit|
      if(year == 2011)
        array2011 << profit
      elsif (year == 2012)
        array2012 << profit
        array2013 << profit
      if(profit > max)
        max = profit
#Render the Bar chart using the gchart library and return the img html tag for display
           :title => "Profit by Product Brand",
           :data => [array2011, array2012, array2013],
           :background => 'efefef', :chart_background => 'CCCCCC',
           :bar_colors => '0000DD,00AA00,EE00EE',
           :stacked => false,
           :size => '600x400',
           :bar_width_and_spacing => '15,0,30',
           :legend => ['2011', '2012','2013'],
           :axis_with_labels => 'x,y',
           :axis_labels => [productNames.join('|'), [0,(max/2).to_f,max.to_f].join('|')],
           #:format => 'file', :filename => 'custom_filename.png') #To save to a file
           :format => 'image_tag',:alt => "Profit by brand img") #To be rendered as an image on web page

Step 4. Stitch it all together


Now you need to put the code examples in the previous sections together into a functioning application.

First, put the code examples from the previous sections into a file that is called bluaccl.rb and configure the 'get' methods of the app to call the getDataFromDW and renderBarGraph functions.

Next, modify the index.html.erb file to provide information about the application and a button that executes the query and displays the charts.

Access the code for these steps in the DevOps Services (JazzHub) repository.

With these steps complete, we are ready to deploy the app on Bluemix.

Step 5. Deploy the app on Bluemix

  1. Log in to Bluemix. On the Catalog tab of Bluemix, select the Ruby Sinatra run time to create a Ruby Sinatra application: Screen capture of the Catalog tab in Bluemix

    Click to see larger image

  2. Specify the AppName in the Create Application form. For example, bluaccel
  3. Bind an instance of the dashDB service to the application.

    Click the application in the Dashboard tab of Bluemix and select Add a new service. From the set of Services, select the dashDB service and add it to the application:

    Screen capture of the Bluemix Service List Dashboard

    Click to see larger image

  4. Deploy the app to Bluemix.
    Set the cf target and then deploy your application to Bluemix with the db2rubybuildpack.
    $ cf push bluaccl -b
  5. Access the route of your app to see the running application.

    Click Overview on the left navigation pane of your application:

    Screen capture of the accessing applications route

    Click to see larger image

  6. To start the app in a new tab or window, click the hyperlink for your application and view the index page: Screen capture of index page for the application

    Click to see larger image

  7. Let's see the reports in our application. Click Show profit by Product to see the Profit by Product Brand bar graph chart: Screen capture of bar graph that shows Profit by Product Brand statistics

    Click to see larger image



In this example application, you learned how easily you can access the enterprise class dashDB service on Bluemix. With a few clicks, you deployed an application that uses the dashDB features. This quick-and-easy Ruby application responds to web requests and uses the ibm_db and Googlecharts modules to present results that are mined from the data warehouse.

BLUEMIX SERVICE USED IN THIS TUTORIAL:The dashDB service helps you move your data into a next-generation columnar in-memory database, run complex analytical queries with in-database algorithms, and integrate with analytic and business intelligence tools.

Add a comment

Note: HTML elements are not supported within comments.

1000 characters left

developerWorks: Sign in

Required fields are indicated with an asterisk (*).

Need an IBM ID?
Forgot your IBM ID?

Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name

The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.


All information submitted is secure.

Zone=Big data and analytics, Cloud computing, Information Management
ArticleTitle=Create a business intelligence and analytics service in Ruby with the dashDB service