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

Comments

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.

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 'https://rubygems.org'
    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
    end
  4. Under the views folder, create an index.html.erb file with a greeting message that is rendered by method get '/':
    <html>
    <h1>Hello Sinatra</h1>
    </html>
  5. Create a rackup file, named config.ru:
    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
    Screen capture of a basic Sinatra app that is up and running

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
    if(ENV['VCAP_SERVICES'])
    
    # 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"]
    
    else
    
    # we are running local, provide local DB credentials
    
     $host = "localhost"
     $username = "bludbuser"
     $password = "password"
     $database = "BLUDB"
     $port = 50000
    end
  2. Connect to the dashDB service, execute queries, and process result sets using the ibm_db gem's APIs.
    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']
        else
          profit = {}
          profit[res['YEAR']] = res['PROFIT']
          data[res['PRODUCT']] = profit
        end
      end
      IBM_DB.close conn
      return data
    end

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
      else
        array2013 << profit
      end
      if(profit > max)
        max = profit
      end
    end
  end
#Render the Bar chart using the gchart library and return the img html tag for display
  Gchart.bar(
           :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
end

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
    Screen capture of the Catalog tab in Bluemix
  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
    Screen capture of the Bluemix Service List Dashboard
  4. Deploy the app to Bluemix.
    Set the cf target and then deploy your application to Bluemix with the db2rubybuildpack.
    
    $ cf push bluaccl -b https://github.com/ibmdb/db2rubybuildpack
  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
    Screen capture of the accessing applications route
  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
    Screen capture of index page for the application
  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
    Screen capture of bar graph that shows Profit by Product Brand statistics

Conclusion

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.


Downloadable resources


Related topics


Comments

Sign in or register to add and subscribe to comments.

static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Big data and analytics, Cloud computing, Information Management
ArticleID=965822
ArticleTitle=Create a business intelligence and analytics service in Ruby with the dashDB service
publish-date=05082015