In an earlier post, I presented some interactive reporting based on custom categorization and aggregation of data available from Capital Bikeshare.  Those reports used Excel pivot tools and SQL Server Reporting services using both relational T-SQL and an Analysis Services cube I constructed to make the desired navigation and aggregation easier to report.

My eventual goal is to use these examples to begin exploring implementation of similar reporting using Azure cloud services.  Although Azure cloud services offer virtual machines for stand-alone SQL Server implementations, I want to start off with a project that uses their platform-as-a-server Azure SQL that as far I can tell does not include SSAS or SSRS.  Also, I was interested in finding alternatives to SSRS because I find the working with the graphical interface to refine a report to be very tedious compared to coding it directly using an IDE and good libraries.  To do this, I set out to replicate the SSRS reports using a ASP.net MVC project, with Razor views, Bootstap css, and javascript libraries (eventually choosing Google Charts and jquery DataTable).

This was my first experience with the MVC model-view-controller paradigm for data-analytics reporting.  I have always admired the concept as my prior reporting has followed a similar REST approach.  My prior projects used query parameters in the URL to expose to the user all the information about what the report will deliver, although it was not as human readable as the ideal MVC routing approach.   Unlike common MVC approaches that are focused on transactional databases using keys to retrieve data, my report navigation inevitably involves passing many parameters that proved to be very tricky to get MVC routing to work correctly.  This was especially true for passing Analysis Services parameters that involve very lengthy unique-name fields often containing characters that the default settings of MVC reject as being potentially unsafe (the “&” symbol, for instance).   To get this to work reasonably well, I would have to add more custom code to reconstruct the unique names from the shorter caption names and since these may also contain scary-to-MVC characters like “&” I would need to redesign the cube to complement each dimension hierarchy with some unique numeric key to use for parameter passing.

Having cryptic key-numbers in the URL is no better than my earlier approach with query strings so there was no benefit to gain from continuing to use the http-get approach for passing parameters between approaches.   I opted to use forms with buttons instead.  The form replaces the hyperlink so the navigation retains the satisfying preview number of the higher-level report to inform the user what to expect if they drilled into the data.   HTML5’s button tag makes this easy because the button tag merely replaces the anchor tag for hyperlinks.   Each button will have its own form with hidden fields for passing the necessary parameters (unique-names from SSAS dimensions).

My first reports were simple HTML tables (that looked decent with the default Bootstrap theme).  I quickly found Jquery datatables to be easy to use and offer some nice features such as paging and column sorting.  The bigger challenge is to find a simple charting library.  I first tried Google Charts but these were designed for presentation charts for pages with just a handful of charts displayed at a time.   My ideal reports will have dozens of charts: one for each cell in the table.

I also tried jquery sparklines as a more obvious choice but ran into a css conflict causing its tooltip feature to be unusable.   This experience only confirmed my long-standing bias against doing active code at the client: it is big burden to get it to work and to keep it working.  I much more prefer to have charting done at the server side and deliver prepared image files to the client although I recognize the potential of annoying the users with images that either are too small or too large for their devices.   Satisfying them takes more work than I can afford.    I stuck with Google charts for the current project.

The first set of charts involved passing the entire result set of an SSAS query as a flat model available to the client and the attempting to use C# code embedding using Razor to organize the data into a denser report.  The following two images show the top-level and drill-down reports I produced.

In the top report, I have a button for each direction of travel for each row.  The drill-down report used Razor to rearrange the results to prepare the tabular data within a row to supply a rich Google chart showing trip volumes per hour period of the day.  It took about a day’s effort to get this to work well because there is so much work to be done on the view that mixes HTML with C# code, making coding and debugging difficult.   I’m used to this kind of tedium.  It reminds me of old times.  But it is not as quick as Reporting Services (even if I am annoyed by clicking through dozens of dialog boxes instead of just typing what I want).

To make things easier, I decided to build more comprehensive models with strongly typed classes to capture the SSAS results and arrange the classes into lists so that at the view level I can quickly pick the desired fields using the IDE’s intellisense within nested foreach loops that intuitively walks down the hierarchy.   This is a view-model approach: the model I construct is customized for the particular view.   This also matches my prior experience where I optimized tables to match each report.   The easiest reports to build are those that access a model that has the data pre-arranged for the report’s goals.

To make this work for the SSAS reports turns out to be a lot of coding.  The code itself is very simple as each class is very simple.  It is the number of classes involved that makes this tedious.  I ended up with separate classes for each dimension variable, each row of dimension variables, and each table of rows.  Once created, the building of the report in the Razor-embedded view is very easy.   This is the principle of conservation of user-friendliness: making something easier in one place just makes it harder someplace else.  In this case, the effort is shifted to simple classes and methods with very mature technologies for developing those classes.

This is a compromise I can live with, but the original design would require making entirely new classes if I used the same rows in different places in the hierarchy of reporting.  I redesigned the model to use abstract base classes to capture the basic navigation work and then made the specific classes inherit from these.  In particular, I used reflection to allow the report to generate a list of table-classes to pass to the model.  The table classes used reflection to dynamically create the desired child tables for a particular report.  This will allow me to reuse the table in various combinations for different reports.

I used this implementation to produce the following navigation based on trip categories:

Again I used form-buttons to pass parameters to the drill-down report.   The drill down report used 4 levels of hierarchy (or a 4D pivot): week-of-year for rows, day-of-week for columns, subscriber-type for column colors, and 2-hour periods for columns.  The view code for this was easy to use in terms of retrieving the desired information for each level (although I had to add some logic in the view to appropriately handle nulls in sparser reports).

This is a design approach that I find most natural to my style of thinking about reports.   However, my preference is to generate the images at the server and deliver them to the client.    This approach may involve a longer wait at the client for the images to be prepared but the client will get an indication that the server is busy with its request (also server-side chart algorithms tend to be very fast relative to javascript).  This particular exercise reinforced my preference because the number of charts needing javascript rendering (70 charts with the default 10-row option of jquery-datatable).  While the javascript was busy preparing all of the visible charts, the browser became unresponsive.   I would need a more elaborate javascript algorithm to make the chart updates asynchronous to each other or to add some type of progress bar.   This is doable, but I can see where this can get tedious.  A more efficient charting option (such as sparklines) would help.   My preference is to do this type of work at the server using the advantages of strongly-typed languages for more rapid development, debugging, and testing.