Data is Our Super Power
So what do the Avengers have to do with dashboards? Absolutely nothing but Ron thought it would be a great theme for our Tableau series showcasing some advanced tricks and techniques. Ron originally created this content for a recent guest speaking event for a client.
In this series we take each one of the superheroes and relate their character or power to some feature that can be used to save our data visualization. (Yes I know it’s a stretch)
This first post begins with Iron Man and his superior engineering capabilities. We sometimes wish our dashboards could do more than just present information and today we will demonstrate how to use database write back features incorporated into the dashboard. Some benefits for having write back capabilities:
Users of dashboards often need to add qualitative commentary to enrich the data being presented from the source systems
Integrating “write back” functionality into a Tableau dashboard allows users to save their unstructured commentary in the database, in a way that can be presented alongside the structured system driven data
Same technique can be used to update any database field, including statuses, targets and other values
A common requirement echoed by business users is to be able to add commentary and additional subjective information to the data being presented in the dashboard
On to the demo - click on the image above to open the tableau dashboard and select AIM for this example. This will cause the panel to open for entering commentary like the image below.
Next enter some text into the box and click the submit button.
So how does it work?
We use a URL Action to submit the form, , which is embedded in the dashboard through a web page, to a RESTful service and save the information to the database. To tag the record we are adding the commentary to we also pass the ID which is evil corp: 1 in this instance for AIM.
Upon refresh the information is read from the database. This example uses a live connection.
The code to the left and also on the dashboard panel is the html code that submits the user entered comment in the text box area to the RESTful service
I have provided the service class which I have posted below.
Enjoy.
package com.cleartelligence.restfull.note;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.ws.rs.Consumes;
import javax.ws.rs.FormParam;
import javax.ws.rs.POST;
import javax.ws.rs.Path;
@Path("save/savenote")
public class Note {@POST
//Only use post for this example
@Consumes("application/x-www-form-urlencoded")
//Simple service will take 2 parameters, ID and note string
public void post(@FormParam("Id") String Id, @FormParam("Note") String Note) {
savenoteInDB(Id,
Note);
}
public boolean savenoteInDB(String Id,
String Note) {
//DB connection string details vars
boolean ret = false;
String username = null;
String pwd = null;
String dbUrl = null;
String jdbcClass = null;
//Get the base naming context
Context env = null;
try {
env = (Context) new InitialContext().lookup("java:comp/env");
} catch (NamingException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
//Get DB conn details from web.xml
try {
username = (String) env.lookup("user");
pwd = (String) env.lookup("pwd");
dbUrl = (String) env.lookup("dbUrl");
jdbcClass = (String) env.lookup("jdbcClass");
} catch (NamingException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
final String DB_URL = dbUrl;
final String USER = username;
final String PASS = pwd;
Connection conn = null;
PreparedStatement stmt = null;
try {
Class.forName(jdbcClass);
conn = DriverManager.getConnection(DB_URL, USER, PASS);
conn.setAutoCommit(false);
String sql = "update table set note = ? where id = ?";
stmt = conn.prepareStatement(sql);
stmt.setString(1, Note);
stmt.setString(2, Id);
stmt.executeUpdate(sql);
conn.commit();
ret = true;
} catch (SQLException se) {
//Handle errors for JDBC
se.printStackTrace();
} catch (Exception e) {
//Handle errors for Class.forName
e.printStackTrace();
} finally {
//finally block used to close resources
try {
if (stmt != null) conn.close();
} catch (SQLException se) {} // do nothing
try {
if (conn != null) conn.close();
} catch (SQLException se) {
se.printStackTrace();
} //end finally try
} //end try
return ret;
}
}