SSAS-like manipulation of data in excel, without SSAS

I have provided users with a view of a large data set through Sql Server Analysis Services, and they find it very easy and intuitive to manipulate.

However, I am now being asked to provide them with access to smaller and smalle开发者_如何学Gor data sets, for which Analysis Services is not a great fit. The reason is that they like the ease of manipulation of the data, and it's pretty flexible in it's presentation of the data.

Also, many of the data sets are available to retrieve via a REST API, in a tabular form, which I'd prefer to use rather than providing database access.

Can anyone recommend any tools or libraries (ideally open source) which:

• provide an SSAS-like interface for building up a pivot table (with attributes grouped together rather than in a flat list)
• can retrieve their data from a web service rather than a traditional DB?

(NB I thought about trying powerpivot, but I'm not really sure what I'd be getting myself into, so if anyone has any experience of using this I'd be interested to hear)

Powerpivot is an excel plugin for excel 2010 that uses the vertipaq engine. It has a language called DAX that is very similar to MDX, more information can be found here

If you wish to use PowerPivot, you have three options:

1) Use PowerPivot from within Excel (it's a free add-in - be sure to install the edition that matches the edition of Excel you have, i.e. 2007 or 2010 and 32-bit or 64-bit). You are using the resources of the client machine in this configuration.

2) Use PowerPivot for SharePoint - this requires SPS 2010 Enterprise. It allows you to host (render) the PowerPivot workbook using resources from the SPS server.

3) Use SQL Server 2012 SSAS installed in Tabular mode (to build a BISM). BI Semantic Models are PowerPivot models which are hosted on a SQL Server instance. This requires a full SQL Server licence, so it's certainly not cheap. However, here you have the greatest flexibility for resources, as you can use (control/monitor) the resouces of your server.

For more information see my deck on the BISM on SlideShare.