Teaser: real time Business Intelligence in SharePoint


I am making progress on my SPELL project. Its main component, the SPELL JavaScript library ($P), recently reached version 0.6 and has been implemented on a couple sites. I expect to reach version 0.7 by the end of the month and version 1 by the end of the year.

To showcase some of the capabilities of the SPELL library, I have set up a live demo featuring airline ontime statistics. In this demo, you can explore data across 3 dimensions: State (first level), Month and Carrier (second level). The third level are the list items themselves.

The first level is rendered via Google GeoCharts (for v1 SPELL will have other map options). The second level – matrix view – is a custom SPELL solution that mimics an Excel pivot table (much simpler though). You saw it in the slides if you read my previous post.

“Real time” refers to the fact that data is directly pulled from the SharePoint list. The charts always read the latest updates, as opposed to traditional BI patterns where data transfers are done at regular intervals. And there is no need for intermediate storage, as would be the case for Excel Services for example.

Note: for practical reasons the demo takes some shortcuts. For example the map doesn’t collect data directly from the list.

 I collected the airline data  (January to July 2012) from the Bureau of Transportation Statistics, I’ll add August as soon as it becomes available. Obviously interacting with more than 3 million items would not be reasonable, so I have done some pre-processing to aggregate the data by month and state. This brings the number of items down to ~500/month, for a total of 3,500 since the beginning of the year.

This is the downside of this solution: because it directly interacts with lists, it is subject to SharePoint’s usual limitations, for example the 5000 item threshold on views in SP 2010. Technically the tool could work with much bigger data sets, but this would require some adjustments and might not be desirable.

The solution showcased here has no ambition to compete with well established tools like PerformancePoint, SSRS or even Excel, but rather to offer a lightweight alternative. It presents a number of advantages that make it attractive :

  • Compatible with SharePoint 2007, 2010, Office 365 (evaluation of SP 2013 in progress)
  • Can easily be implemented by an experienced end user. No server side install, files are simply uploaded to a document library.
  • Direct, real time access to the SharePoint data (which means for example that it follows site, list and item level permissions).
  • Pure html (no Flash or Silverlight), accessible from both desktop and mobile devices.

This could for example be the ideal tool for a mid-size matrix organization, with multiple teams working on multiple programs. Managers could monitor the organization health, use the matrix view to analyze trends (what brings my organization up/down, a specific project or a specific team?), and finally access the items themselves.

17 thoughts on “Teaser: real time Business Intelligence in SharePoint

  1. Hi Christophe, I’m certainly looking forward to the first release of SPELL. Might well save me a lot of time in my work.


    • I plan to send this month the first samples to the people who have registered to the interest list.
      Note that SPELL is a coaching program offered by my company, at this point there is no plan to make all the support materials (which include the JavaScript library) public. That said, I’ll certainly release a free entry-level version…

  2. Looking forward to seeing what this can do. I’m exploring BI for my copmany using PerformancePoint, but finding a few too many limitations if your data is not on a SQL server with analytics installed. This looks promising.

    • Well, as I said in the post, this solution has no ambition to compete with well-established applications. It is a first step into dashboarding for users who don’t have access to PerformancePoint (SP 2007, Foundation, Office 365, some corporate MSS), or are scared by the PerformancePoint learning curve. It’s an inexpensive option that focuses on ease of implementation and ease of use.

      If you are already working with PerformancePoint, this might just be a complement for specific use cases.

  3. I too am looking forward to lightweight Office 365 SharePoint BI opportunities… Please add me to your interest list!

  4. Hi Christophe,

    First of all thanks you very much for all the work done by you…you are really a lifesaver…. This is a great site!

    I’ve got the Overdue traffic signal script working perfect,Just wondering if you can help with adding an extra piece. My knowledge of coding is pretty limited I’m working with these columns Due date, Overdue Status (formula column), Stages column. The due date column being the column used in the calculated column forumla, and the stages column is a choice column. I’ve managed to add a part to the formula that says if stage is completed marked “Closed” and no traffic light, this is working fine. But I need you help to get black color bullet if the stage is completed.
    Currently I’m getting red bullet for Overdue tasks, green for no action required task.

    I’ll really appreciate the help! Thank-you

  5. Pingback: Interactive demo: charting in SharePoint « Path to SharePoint

  6. You really make it seem so easy with your presentation but I find this topic to be
    actually something which I think I would never understand.

    It seems too complicated and extremely broad for me.
    I am looking forward for your next post, I will try to get
    the hang of it!

  7. Pingback: Script insertion via a Page Viewer Web Part | SharePoint Samurai

Comments are closed.