The client requested a graphical dashboard that would allow them to determine whether resources were being allocated to the most important projects.    One part of this was the addition of new persistent data that was not centrally located or in some cases available at all.   Another harder part was that there were four distinct operational databases with existing data relevant for the dashboard.

The conceptual dashboard requested mashed up quite a few data variables and was perceived as ambitious.   We designed a response that used Ruby-on-Rails communicating with a MySQL database for the new data, and connected to the existing SQL Server databases for the existing data, plus a connection to Active Directory over LDAP.

The concept of a Project was new, and the data in this image was from the new database except for the CRM account and Biz Dev Agreement, which are titles of records attached to data in two of the other databases.

When creating or editing a project, the Rails application guesses the name of any related records in the other databases.  Because the number of records is very large, it is not practical or useful to load the drop down spinners with every alternative.   If the application guesses incorrectly, the user can enter a few characters to improve the guess and the drop downs are reloaded.   The user makes a connection to the proper record by selecting one of the items.   This connection allows perfect analytics relating all the data to each other without guesswork or name normalization.  

We utilized a third party BI tool by Inetsoft called “Style Intelligence”, and we were able to exactly reproduce the multidimensional visualizations requested.  By hovering, clicking and brushing, users can visualize data relationships in ways not possible before our solution was implemented. We have a development role which is basically the back end modeling of the physical databases into relatively easy-to-use virtual entities that can be combined on a single page. Inetsoft’s features include a concept of a “datablock” which contains queries so that the designer can just use related data in the views. In this snapshot the lower left view came from a database that needed more than a dozen queries to get to cost and revenue; now cost and revenue is just an attribute of the underlying account.

These views are looking at live data rather than something that required an ETL step.

Implementation Notes

We used a very elegant solution to load drop down spinners with contextually relevant information. It relies on the ability of RoR to have a view which returns javascript. When a field changes that sets a different context for a drop down, an event is fired off which issues a POST to a view which returns javascript for the data in the drop down. The elegance lies in how this can be captured in a common JQuery command in application.js:

// dynamic drop down trick used in activities form
jQuery.ajaxSetup({
    'beforeSend': function(xhr) { xhr.setRequestHeader("Accept", "text/javascript"); }
});

$.fn.singleSelectWithAjax = function() {
    var that = this;

    this.change(function() {
        if (that.val() != -1) {
             $.post(that.attr('rel'), {id: that.val()}, null, "script");
        }
    });

};

$.fn.doubleSelectWithAjax = function() {
    var that = this;

    this.change(function() {
        if (that.val() != -1)   {
            $.post(that.attr('rel'), {id: that.val()}, null, "script");
            $.post(that.attr('rel2'), {id: that.val()}, null, "script");
        }
    });

};

An example of using this can be found in a view for Activities:

 <%= f.collection_select :project_id, @projects,
		 :id, :account, {  :include_blank => true } , { 'onchange' => 'checkNewProject();', :class => 'actdropdown', 
		 	:id => "activity_project_id", :rel => "/technologies/technologies_by_project",
		 	:rel2 => "/verticals/verticals_by_project" }   %>

The ‘:rel” and “:rel2” symbols are pointers to views which will be fetched by the common javascript and then POSTed to because of the $.post command. For the above example here is the view for /technologies/technologies_by_project.js.erb:

$("#activity_technology_id").html('<%= options_for_select(@technologies.map {|tech| [tech.technologyName, tech.id]}).gsub(/\n/, " ").html_safe %>');

Which basically says to modify the html for the control called activity_technology_id so that the drop down displays the technology name and will send the id of that technology record when sending in the form. The gsub and html_safe method calls are very important.

Lastly, we use JQuery to attach the control to the common javascript method:

$("#activity_project_id").doubleSelectWithAjax();

One surprise for us here is that you can have an “onchange” event in the html and a “this.change()” method in the javascript and they both fire!

So — a common short javascript (two for us because sometimes we wanted to load two controls), a single symbol in each control changing context, a single JQuery line of code to modify each control, and a single JQuery line on the context settign control. It really can’t get any more simple or elegant than that.

We can’t take credit for inventing this trick, we were simply smart enough to use it, and then extend it to cases where more than one drop down is loaded based on context. It is described here.