Effective Reporting on SharePoint Content - Part 1

by Patrick Penn, SharePoint Architect, Enthusiast and Entrepreneur.

A big thanks goes to our team member Benjamin Linder for realizing our thoughts.

Have you ever asked yourself how to create powerful reports based on Microsoft SQL Server Reporting Services (SSRS), involving SharePoint data without querying the productive SharePoint content database directly?
If you're interested in professional reporting, joining SharePoint data, you have to think about it, because this type of query is definitly not supported by Microsoft and there are some other good arguments for not doing so.

Just a peek to the result

Rendering live charts with SPServices and morris.js using the Datawarehouse and live SharePoint data.

Solutions and Constraints

Intensivly using SharePoint can make you very productive with a small amount of effort.
But how about creating reports based on data within your SharePoint farm? You can use some technologies to do this like:

or

  • There are also some tools out there f.e. from IntLock, HarePoint or AvePoint, which may help you at least regarding administrative reporting like storage usage, compliance, governance and general usage analytics etc.

But if you decide for one of these ways you have to deal with one or more constraints like licensing, high costs, dependecies to a solution provider, complex and hard to maintain custom solutions and database handling or restrictions in data access or sourcecode handling and compiling.

Remain Close to the Standard

If you have implemented SharePoint it may be obvious that you also use Reporting Services, because you already use SQL Server. So, wouldn't it be cool to use standard reports in SSRS (maybe with Dundas) to show what's up in your SharePoint environment? And it would be much cooler if you're able to extend and enrich the data by yourself to gain more benefit out of your analysis without a dependency to any solution provider.

We have planned to consider the following requirements:

  • Absolutely SDK conform SharePoint Datawarehouse
  • Highly flexible regarding onpremise-, cloud-based or mixed hosting
  • Highly flexible regarding extending and enriching SharePoint data
  • Simplifying SharePoint data structure
  • Simple to deploy and maintain
  • Avoid the risk to paralyze the production environment by using expensive and or deadlock triggering SQL queries
  • Gain more insights by creating reports based on historic data and visualize trends
  • Ensure compatibility with SharePoint 2010/2013 and future releases

The Main Question

Reporting on single lists is simple. But what if you want to report on data or documents in an entire Sitecollection or Webapplication or better, joining and aggregating these data?

Our Plan

We started to plan an architecture based on Sitecollection scoped Timerjobs for scanning SharePoint contents and a Webservice, using Microsofts most actual Entity Framework, to store and manage the scanned data in a Datawarehouse database.
Because of its service-oriented structure, the architecture supports many hosting strategies, including Azure and on-premise, for example.

Example Early design concept without the use of the Entity Framework webservice

The Timerjob

One important fact was to keep the Timerjob as simple as possible to allow a highly flexible data gathering mechanism, without the need to redeploy the Timerjob in the farm, everytime we need a new entity or an entities property to scan.
So, keeping the Timerjob mostly stupid, we have to provide a server-based configuration to instruct the Timerjob about the entities and relating properties for gathering the necessary data.
In this case we have specified a simple query syntax to define the data, which is used by the Timerjobs generic methods.

The Webservice

For the Webservice we use Microsofts Entity Framework to be effective and flexible especially for migration scenarios.
We also implemented an automatic object mapping based on ValueInjecter, so we are independent of any SharePoint specific assemblies, which makes thinks much easier.

Some of the SharePoint entities will be transformed and simplified to provide more comfort in querying the SharePoint Datawarehouse Database.

It's also planned to implement a JSON interface to provide the data to javascripts for rendering charts within SharePoint, but without the need of Reporting Services.

Interesting? Stay tuned at Twitter, we deliver more details soon.