By: Neil Barkhina

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:

Once you have installed SQL Server 2012, the first step is to open up SQL Server Data Tools and create a new Analysis Services Tabular Project

If it asks you for a compatibility level make sure you pick SP1, as the RTM version was giving me problems when I actually imported the data. The specific error I was getting was “column does not exist in the rowset”, so again make sure you have SP1 version of SQL 2012 with the latest version of the Data Tools.

Then you will click to create a new connection and select the “Other Feeds” option

Now the key is to enter your SharePoint site collection URL appended with “/_vti_bin/listdata.svc”

After entering in a Service Account that you will use to connect to the SharePoint site, you will select the list that you want to import. Check off the list and click on Preview and Filter

Now you can select only those columns you want to import. This is useful because SharePoint lists have a lot of hidden fields which you probably don’t need in terms of reporting

Now you can go and create additional columns and measures the way you would normally do in Tabular Mode or PowerPivot. Once you’re done, and have picked the instance of Analysis Services for your deployment server, just right click on the project and click on Deploy

Now you’re all set to create those beautiful SharePoint dashboards. Here’s a quick PowerView I was able to spin up in SharePoint 2010 after deploying the model to the cube. Enjoy!