For a long time, one of the only challenges with storing large quantities of data in SharePoint was the ability to report against that data. The platform of choice for reporting has always been SQL Server, and while SharePoint has a lot of great visualization tools, I.E. PowerPivot, Power View, SSRS, PerformancePoint, etc… this always assumes that the data itself is stored either on a SQL Database or in a SQL Cube using Analysis Services. So what do you do if you actually want to report against SharePoint list data?

SharePoint does has some answers to this, one is the chart web part which is built in. The other is Report Builder which actually can use SharePoint as a data source. The biggest problem with these options is that querying SharePoint list data is SLOW. SharePoint was never designed to house large amounts of data for the purposes of visual reporting (that is separate from Search, which personally I don’t consider reporting). Anything over 5000 rows, or whatever you list view threshold is set to, is bound to cause you issues and inherently sluggish query times. The answer to this is to take data that’s in SharePoint and put it into either a database or cube. I ran into this very situation recently for a customer where I started to build code that would take data in SharePoint Lists and copy them into a SQL Database via a timer job. Eventually this data would also be put into an Analysis Services Cube. Then I thought, wait a minute, maybe there’s an easier way. What if I could just move the data directly to the cube, completely bypassing the need to have a SQL Database, and would also save me the trouble of writing tons of annoying ADO .NET code. As it turns out there is a way, and it’s surprisingly easy!

This does however require that you have installed SQL Server 2012 SP1 Analysis Services in Tabular Mode. In my example, I wanted to import a series of tasks from a SharePoint task list and create a dashboard. Here is my task list: