Teaser: real time Business Intelligence in SharePoint
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.