As part of my preparation for the upcoming SharePoint Conference, I decided to embark on an exercise to port some of my more popular reports over to OData and Project Server 2013. Indeed, it turned out to be quite the mental exercise – and hopefully I didn’t end up with the cranial equivalent of a stress fracture. Not that, in retrospect, it was hard. I think it was more difficult to get my head around the new concepts and models required than actually doing the work.
So first off, a macro level view of things. Where the 2010 business intelligence story was really about empowering the end users, the 2013 story, is basically that – and more. The critical difference in 2013 is that instead of simply presenting a massaged data set to the end users in the form of data connections, and then allowing the end users to develop reports; now, we’re presenting the raw data to the end users and then allowing them to create the data model in Excel.
In fact, when you start looking at Excel 2013, you can see substantial investment has been made in PowerPivot, and in porting a rudimentary SQL query design editor UI into Excel. I can now take the raw data from SQL and then manipulate it at a base level in Excel before even bringing it into the familiar Excel worksheet interface.
The report I decided to start with was one I originally discussed here. It’s relatively uncomplicated, i.e. it doesn’t require custom fields, but it does require a combination of baseline data, and some fancy footwork to compile multiple data sets. For those of you reading this with on-premises installations, feel free to use those instructions as you’ll have direct access to the SQL data repository.
These instructions are only for those who have their data in the Project 2013 cloud based model –or- for some reason have determined that you’d like to pursue using the OData interface to consume project data.
Assembling Our Toolbox
There’re a couple of tools that we’ll use to develop this report. Primarily, we’ll be using Excel 2013. Out of the box, Excel 2013 can now import data from OData feeds – whereas this required the install of the free PowerPivot for Excel add-in for Excel 2010. PowerPivot comes installed as an optional add-in, which we need to activate to modify the data model. So let me state this very clearly: you don’t need PowerPivot installed to consume OData, but if you want to build relationships between the data tables and manipulate the structure, you’ll need to activate the PowerPivot add-in.
You’ll find it under Options > Add-Ins > Com Add Ins.
I would assume that these instructions should work equally well in Excel 2010 with PowerPivot, but haven’t validated that. Feel free to add your experiences to the comments below.
The second tool would be a working instance of Project Server with the OData feed validated. Navigate to the appropriate URL to confirm that you see the right data.
Finally, it’s not really a requirement, but I found it helpful to have a working instance of LINQPad available. LINQPad is a free tool that you can use to generate LINQ queries and then to turn those queries into a URL to query OData. You don’t need LINQPad, but I found it saved a bit of time, i.e. I could filter the result set and select the fields before pulling them into Excel. Otherwise, you need to pull them into Excel, and then select the columns. If you’re comfortable with simple SQL queries, you’ll probably just find it easier to specify the fields in the query itself.
Next up….setting up the data connections within Excel.