Technology behind the small business dashboard

Here at Fuzzy Math, in addition to all of the other projects we have going on, we”ve been working on creating a dashboard to help the powers that be (aka, bosses) help keep track of what is going on in the company, who”s working on what, how the blog is doing, and so forth over a given time period.  With that in mind, we set out to pull in data from the various resources we use and combine them in a meaningful way.  In this post, we”ll explore what resources we”re using and how we accessed them.

Tech

For our purposes, we decided that a PHP script would be a simple yet effective way to retrieve and combine all of our data.  In addition to all the ease of OOP, PHP allowed us to make easy calls to the various APIs we would be using via the cURL module and handle XML via SimpleXML.  We also ended up utilizing the GData package from the creators of the Zend Framework.  This made the authorization process with Google all too easy.

Google (settings spreadsheet and authentication)

In addition to info like hours and project status, Fuzzy Math keeps track of several indicators on a daily or weekly basis.  Financial information, project proposals in the works, etc.  By keeping this info in a Google Spreadsheet, we not only had an effective method for storing this information and allowing multiple users to update the file, but our dashboard could display the relevant data.  Using Zend”s Gdata package, we can authenticate with Google and access the data.

We also use the spreadsheet as a means to store configuration data for the rest of the script, rather than hardcoding the variables in the file.  Variables like the Twitter screenname to use, the mapping between blog usernames to Basecamp usernames, Basecamp company ID, and any other information the script needs are stored here.  This also acts as a form of security, as only users with access to the spreadsheet will be able to see see the dashboard data.

Basecamp

Chances are you”re already using Basecamp for something, it is a pretty standard tool for group management, time tracking, and more.  In our case, we wanted to pull the time tracking data and project milestones for use in our dashboard.  Luckily, Basecamp has a pretty expansive API to help users access their data.  We wanted our data organized as “milestones by project” & “hours per project & total hours by person.”  This meant that we needed that we would be making several calls to the API to get everything we wanted.

Before we get into specifics about queries, let”s talk about tokens.  Tokens are used by Basecamp to restrict who has access to what resources.  Each call to the API includes a token for security purposes, and this token acts as a filter for what will be returned.  You can find yours on the “My Info” page.   With that in mind, if you”re using the Basecamp API to develop a multi-user application, make sure you”re using an appropriate token.  You wouldn”t want too much or too little information shown.

Back to the queries.  The first thing we need to do is get our companies projects & employees.  Well, actually, the first thing you need is your company ID, but we didn”t feel this was necessary to retrieve every time the dashboard updates, as it isn”t too likely too change.  The easiest way we found to look this up (although there is probably a much more obvious way we just didn”t know about) was to look up a known user”s data.  User”s ID”s are shown when looking at their “My Info” page, and a person”s info includes the company they are working for. Viola, company ID.  Write it down somewhere, you”ll need it.

With company ID in hand, you can get lists (formatted as XML) of current projects & company employees, via the appropriately named “get projects” and “get people” functions.  We only needed the names and IDs for these so that we could look up further details and show their names on the dashboard.  Iterating over the list of IDs you can retrieve the project milestones (upcoming, completed, and overdue), and time reports for each employee.

Other companies might also be interested to know that the API allows access to “To-do Lists.”  We at Fuzzy Math aren”t big users of the feature, so we didn”t include it in our dashboard.

Twitter

Within Fuzzy Math, there isn”t one person who has responsibility for the Twitter account, and as such it was deemed a helpful addition to the dashboard.  As you would expect, Twitter has an API for developers to make use of .  We didn”t need to do anything too fancy, so we didn”t have to bother with authentication.  Essentially all we did was pull in the recent timeline for our Twitter account (FuzzyMath), and do a count of messages over the given time period.  The user_timeline function gave us an xml feed with just this information.  A quick parse with SimpleXML and we were all set.  If we were to start using separate twitter account for each employee or somehow differentiate between who tweeted what, things could get more complicated.

Blog

Similarly to the Twitter account, the Fuzzy Math blog is updated by several employees.  Unlike Twitter though, we could actually tell who wrote what, as each has their own username.  To keep the process simple, we made use of the existing blog feed, parsing the xml for the authors and post count information.

Putting it all together

With all of our data in hand, it was just a matter of combining the information together as needed.  Through a bit of haphazardly written PHP, we built an XML string containing milestone info, company-wide totals, and individual totals.  Getting the blog info to play nicely was a bit of a snag, as the Basecamp user names didn”t match those from the blog.  A simple mapping array solved that problem, and after a bit of debugging, we had our info ready for display.  Of course, actually displaying the information is a whole other story.