15 May 2012 18 Comments

Tableau + PhpGrid = an (almost) instant gratification data entry tool

Impatient? Skip down to the bottom for a brief screencast demonstration.

We, over here at Linium, love Tableau Server. Big time.

It takes quite the tool to be able to wrap around all my procedural data hacking and make it look like it came that way. Custom internal apps + Salesforce data + consultant time-tracking data + Quickbooks data etc - it all fits, works, comes together and looks BEA-utiful thanks to my sick "Data Artist" skills (cough, cough, kidding).

Sounds good, right? Gee, you could almost (gasp!) run your entire freaking business on Tableau! Perish the thought! :)

When doing a multi-department integration (data automation) client project, even when you're front-ending it with a product as good as Tableau, there comes a time when your end-users, practice leads, etc, ask for something that isn't exactly inside Tableau's wheelhouse - say, "data entry".

Let's say you're trying to consolidate a mess of "resource planning" Excel spreadsheets so they can be reported on in your Tableau datasource - WITH the rest of the company data.

1) You COULD write an SSIS / DTS package in SQL Server to allow you to pull in this data (from various Excel files) on a semi-daily basis, but anyone with experience doing THAT knows that they'd rather take a boot to the head instead of constantly chasing after arbitrary document structure changes and user error.

2) You COULD write a simple custom web app on the intranet and then import that data into the OLAP cubes that already exist (if you've got the time), but then we're sending business peeps all over the damn intranet for different reasons for the same data. Not to mention another "app" to maintain.

Besides, we're trying to force them to use their fancy Tableau server login for EVERYTHING company-wide, so why would we want them going to yet ANOTHER internal web property. People tend get all pissy about shit like that.

"Pop Quiz, Hot Shot! What do you do? WHAT DO YOU DO???
Shoot the hostage. Shoot the hostage? Harry, no. "

Howard Payne knows!

Howard Payne knows!

A quicker and more "elegant" solution?

Use a cool package like PhpGrid and embedded web parts in Tableau dashboards to allow end-users to change the data on the fly.

POC - Tableau and PHPgrid enabling a user-updatable resource plan report

POC of Tableau and PHPgrid enabling a user-updatable resource plan report

Impatient? Skip down to the bottom for a brief screencast demonstration.

Is it a hack?

Not really.

Is it using Tableau in a way most wouldn't envision?


This can be VERY useful for anyone who needs to report on a dataset that needs to have a bit of end-user entry that is BEYOND the tool-data we are reporting on. Is that clear? Clear as mud, eh?

Ok. Let me try and illustrate a use-case. For example, you could be reporting on a proprietary employee time-tracking system. We create our vizzes, reports, and all that hootenanny off the stock, (virtually) untouchable database. Then - after the initial roll-out and consumption, the various Project Managers want certain added fields and metrics implemented into the report that our time-tracking system does not support.

How about these for starters:

  • Project Percent Completion
  • Cost-To-Complete
  • Current Project Issues
  • Planned Hours Per Employee per Project for the rest of the year
  • etc.

Now we're back to the choices I mentioned earlier. Which all take quite a bit of time and effort, and will STILL be less-than-ideal. What can we do?

Here are our needs for this use case:

  1. Some extra tables in our datasource (whole new database, whatever)
  2. Ability for end-users to modify the table data at will
  3. The interface to be flexible and easy to understand (for said end-users)
  4. Deliver it in a way that appears seemless to Tableau (the platform we are pushing)

Fastest and quickest way to deploy some data-editing features? A cool like package called PhpGrid. It's a fully customizable HTML5 "editable datagrid" implementation using ADODB5 (so it has lots of datasource support) and PHP. Also, its easy to use, and its damn cheap.

You can get an editable SQL table up in a matter of minutes - customized, prettified and formatted for your use in only hours. The best part is, even with all the AJAX-y magic of HTML5 and jQuery going on - it won't interfere with the rest of your Tableau dashboard at all.

Set up a secondary Apache/PHP webserver on your Tableau server (or where ever) using a different port and get it running. Build your tables for the "new" data (keep in mind that you're going to have to report on these tables and tie them into the "main" tables at some point - so design them with a smidgen of care).

Set up a PHP page to display and make that table editable (create, update, delete) via PhpGrid.

Now depending on the level of interactivity required in the initial dashboard - you may not have to do a whole lot more than that on the PHP side. For my use, I needed to allow the user to select what practice they were editing within the dashboard and refresh the PhpGrid data accordingly. I did this with some simple php $_GET['var'] variables and then populated those variables with a Tableau Dashboard Action.

Now users are free to add / change / delete resource plans for particular employees - and with a quick refresh, they will be able to see it reflected in the viz (as well as all the other vizzes - since I set THIS particular datasource to read directly from the database and not be made into an extract)

Brief Screencast Demo - you know, to kick a dead horse

  • http://twitter.com/acotgreave Andy Cotgreave

    Fantastic stuff, Ryan. How long did it take you to set up this example? And how maintainable do you think it would be as the use case evolves? What if I needed to add another chart/table to the dashboard, for example?

    • http://ryrobes.com/ Ryan Robitaille

       Thanks Andy, not long – maybe an afternoon? And most of that time was spent tweaking the formatting on the phpGrid pane.

      For this particular example I think adding and changing things would be no big deal – since the whole dashboard uses the same data source (the underlying table that phpGrid is editing) and my table is fairly simple.

      My real reason for this was to have the table being edited also be joined to several other reports in different workbooks (with that SQL table being read by Tableau as a “live” datasource), so changes could be made in the data entry area (with the security rights involved there, obviously) and then seen Server-wide.

      My initial example was hours forecasting – but I envision using the same concept for salesperson quotas and commission percentage. Each person has their own numbers – so when the sales ROI / Overview report is rendered it can take all that into account (calculation-wise) all that for each salesperson / sales department in question.

      Know what I mean?

      I think, it opens up a lot of clever report tweaking opportunities – depending on the need.

      • Dipankar Kumar

        Hi Ryan,
        I luckily stumbled upon this link and looks like it saves me. I have to give a quick demo to a client which requires data add/edit functionality on the underlying tables. Nothing cooler than to embed that part in tableau dahboard itself as you have shown. May I request you to share the code snippet so that I can do the same?

  • Jesse G

    Very cool, man.  Ill definitely keep this in mind when folks ask for data entry as part of the solution dashboard.  Thanks for posting!

    • http://ryrobes.com/ Ryan Robitaille

       Thanks man. It came in handy for me many times so far – gives you that little bit of extra flexibility to make Tableau even MORE useful (if that was possible). :)

  • Mel Stephenson

    Another great post, Ryan. We had a similar itch we tried to scratch a while back, where we wanted to use Tableau data to help fine tune recommended stock levels in our inventory package. We did it the ugly way whereas this is very elegant and gives so much scope to do more. Keep up the great work!

  • RK

    Great article. Thank you.
    My Tableau site is a Internet web site.
    What can be the security challenges with this approach. Are there ways we can use Tableau authentication to the phpGrid web page? Appreciate your inputs.

  • Frank Cook

    I’m using phpGrid from phpgrid.com trying to do something similar to what Ryan’s doing as an embedded iframe inside Tableau. It works very well so far even. Thank you!

    • Bhavesh Garg

      Hi Frank. I am also trying to do the same but I am not able to do it. Could you please help me with it’s detailed proccess on how to do it or by sharing your code snippets.

  • John Munoz

    Ryan, fantastic post! This is exactly what I was looking for…the ability to capture input data and write the data to a data base from w/in Tableau.
    Unfortunately, I’m not very web service savvy and am having trouble getting phpgrid to run. I have the XAMPP components (Apache/Tomcat/MySQL/etc) configured and running, and can hit http://localhost on my server, and I see that things are running, but I can’t, for the life of me figure out where to install phpgrid and what location to put in its conf.cfg file.
    Any chance you could share some details around how you got phpgrid working with Tableau?

    • Aabhas

      Have you been able to figure it out? If yes please help me too

  • Kris Erickson

    “As far as the end user goes, they don’t give a shit if you [mix the technology,] they just want to get their work done”

  • http://bensullins.com/ Ben Sullins

    What do you think about using something like google docs spreadsheets or forms that then get sync’d to the Tableau server as a .csv every 5min using python? I love this, but I’m iffy on setting up custom tools just for Tableau if we could instead use something that is already in use.

  • Nate

    Is there a way to join this with .net?

  • Suneelkumar kesana

    Thats very good explanation.
    I am new to tableau, could you please help me to understand more details like
    How to embed php data grid in tableau?
    Is it possible to share the sample work book Demonstrated.


  • Jimmy

    This post was unbelievably helpful. Thanks!

  • Ruma

    Dear Ryan, can pHpGrid used with Tableau Desktop? We have a requirement wherein we need to provide the user with a what-if functionality for which he needs to change the Part volume.

  • Bart De Reu

    Hi, i wish there was a way to tell me how i can go ahead and install a new apache next to the tableau one. We are running Tableau server on a azure VM windows server 2012 , and i tried installing WAMP, XAMPP, and many other. They all fail to start the apache, i tried several ports but it just won’t start :(