Avengers Save Dashboards Tableau Data Write Back


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.



Finally press the refresh button at the top of the Tableau dashboard and your text entry will appear on the dashboard.

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;

}

}

Ready to Make Analytics Work for You

Learn more about how we can help get your project started

New England Office

275 Grove Street, Suite 2-400

Newton, MA 02466

(617) 340-7740

Tri-State Office

100 Overlook Center, 2nd Floor

Princeton, NJ 08540

(609) 246-0029

Services

  • White LinkedIn Icon
  • White Twitter Icon
  • White Facebook Icon

More

 © Cleartelligence, Inc. 2018

All Rights Reserved