Create a business intelligence and analytics service in Ruby with Analytics Warehouse on IBM Bluemix

10 July 2014
PDF (505 KB)
Share:
Praveen Devarao

Praveen Devarao

Senior Staff Software Engineer

Praveen Devarao

Mario Briggs

Mario Briggs

STSM

Mario Briggs

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 Analytics Warehouse service and deploy it on Bluemix.

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 Analytics Warehouse 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

    Click to see larger image

Step 2. Make queries to the Analytics Warehouse service

 

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

  1. Access the Analytics Warehouse 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 Analytics Warehouse 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']
        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 Analytics Warehouse 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

    Click to see larger image

  2. Specify the AppName in the Create Application form. For example, bluaccel
  3. Bind an instance of the Analytics Warehouse 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 Analytics Warehouse 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 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

    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

Conclusion

 

In this example application, you learned how easily you can access the enterprise class Analytics Warehouse service on Bluemix. With a few clicks, you deployed an application that uses the Analytics Warehouse 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.

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.

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 Analytics Warehouse on IBM Bluemix
publish-date=07102014