IBM dashDB & Cloud-based Cognos Analytics

This post provides a quick look at the Data & Analytics offering from IBM Bluemix. A round-trip exercise on the IBM dashDB illustrated here will include

  • Register with IBM Bluemix to obtain your personal IBM ID
  • Select and set up the dashDB database instance from the Bluemix Data & Analytics category
  • Create a test table in the dashDB database, and load it up with some test data
  • Register with IBM Cognos Analytics
  • Create some test dashboards in IBM Cognos Analytics based on data in your dashDB instance

 

Register with IBM Bluemix to obtain your personal IBM ID

You can sign up with IBM to try out the Bluemix offerings free of charge here. Then connect to the Bluemix Home Page to start exploring lots of exciting things from IBM, including the Data & Analytics – dashDB that we will try out next.

bm03

Click on the Plus sign from the top right corner in the above screenshot to navigate to the next screen to select the dashDB feature.

bm04

Select and set up the dashDB database instance from the Bluemix Data & Analytics category

You will then be taken to a series of screens to start configuring your dashDB instance, and, finally you will arrive at the dashDB Home Page shown below.

bm05

At this point, you have an empty dashDB database. Right click on ‘Connect‘, and then ‘Connection Information‘ to get a summary of what created so far for you.

bm06

Note those orange boxes shown above…they contain the information that you will need for later steps. In addition, you could start creating your test table(s) and load some test data via the menu options ‘Tables’, ‘Load’, ‘Run SQL’, etc. However the interface will be very cumbersome and primitive that I would recommend that you download the following items from ‘Connect/Download Tools

bm07

  • From ‘Drivers and command-line client‘, download the ‘Driver package for Windows 64-bit’ (since my post will be done in Windows)
  • From ‘Database administration software‘, download the ‘Aginity Workbench for dashDB‘ (the other tool IBM Data Studio would also work but I would not recommend because it is very taxing on your desktop/laptop resources).

Create a test table in the dashDB database, and load it up with some test data

  • You can now unpack the driver file ibm_data_server_driver_package_win64_v10.5.exe into any directory such as C:\IBM\dashDBDrivers.
  • Then install Aginity Workbench by running AginityDashDBWorkbenchSetupx64.exe as ‘Administrator’
  • When Aginity Workbench is installed ok, start it to configure your connection to your new dashDB instance.

bm08

The Server and User ID will be the information you get from ‘Connect/Connection information‘ (see one of the screenshots shown earlier).

  • You are now ready to create your test table in your dashDB instance (you can get the Create Table DDL here)

bm09

  • When the test table is ready, you can download and unzip this data file so you can populate your test table F_CARRIER_MOVEMENT from Aginity Workbench. You can also get more test data from the transtats.bts.gov web site.

bm10

Below are the screenshots to show the steps needed to load the csv file into your test table.

bm11

bm12

bm13

bm14

You will need to change all SMALLINT to INTEGER as indicated above.

bm15

Ensure you set ‘Truncate and insert‘ in the ‘If Table Exists‘ option, and click Finish to start loading the data. When done, do a quick count to verify the table now populated ok.

bm16

Register with IBM Cognos Analytics

You now have a dashDB database instance with a test table F_CARRIER_MOVEMENT fully populated with some test data. You are ready to sign up with IBM Cognos Analytics here so you can at last build some cool dashboards to interact with your dashDB database.

  • From the Welcome screen, click on the ‘Manage‘ icon at the bottom left corner so you can bring in data from your dashDB.

bm17

  • Highlight ‘Data servers‘, and click on the Plus sign to add a new dashDB datasource.

bm19

bm20

Select ‘dashDB‘ and continue to the next screen to configure connection information.

bm21

Click ‘Test‘ to verify connectivity, and things should be ok from here for you to create your own dashboards from data in your dashDB instance!

You can learn more about IBM Cognos Analytics here. In the meantime, below is a sample screenshot of the test dashboard based on the air carrier data mentioned in this post.

bm22

 

TaiPh Author

Professional Business Intelligence Architect

Leave a Reply

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