Companies that have cubes on Analysis Services 2000 and still have not completed a migration to SSAS 2005/2008 might be interested in connecting to AS2000 from PowerPivot. Unfortunately, there are several limitations about using AS2000 as a data source for PowerPivot.
Connecting issues
The client PC where PowerPivot for Excel runs requires the PivotTable Service installed to be able to connect to AS2000. This might seem a non-issue because it is the same requirement for any existing AS2000 client (in particular, it requires the PivotTable service). However, chances are that existing AS2000 clients are on older virtual machines or servers accessed through Terminal Server, while Excel 2010 might be installed on another server (used by remote connections) or on the client PC. At the end, it is often the case that the PivotTable service is not present on the same PC where PowerPivot for Excel runs.
Furthermore, because the PivotTable Service is only available in 32-bit version, it cannot be used by the 64-bit version of Excel.
Publishing issues
A workbook containing a connection to AS2000 requires the 32-bit PivotTable Service to refresh data. Thus, if such a workbook is published on SharePoint 2010 (which is only available in 64-bit version) its data cannot be refreshed. This could be a serious blocking issue for using PowerPivot to create dashboards using also AS2000 data (ideally, a dashboard for the company would get data from all the cubes available on different servers).
Possible workarounds
A possible workaround could be the one of using a 32-bit instance of SQL Server to create some views on a SQL database which queries the AS2000 cube through a Linked Server connection. On the server where this instance of SQL Server runs it would be necessary to install PivotTable Service too.
With this workaround MDX queries would be defined on the SQL Server side and the end user wouldn’t be able to create its own MDX query through MDX designer. At the end, the connection would be simple for the user creating a PowerPivot connection (just select SQL Server, choose a database and the right view) but they would depend on the IT department to get new queries for their reports.
Another workaround is using a 32-bit instance of SQL Server Reporting Services 2008 R2 to expose AS2000 cube data through a Data Feed. In this case, the MDX query would be in the report definition and the end user would be able only to use existing report or designing his own reports with Report Builder (using a 32-bit client with the PivotTable service installed on the client). Probably this is the workaround that don’t require assistance from IT department. However, users will need to understand how to create a report that exposes a Data Feed in a way that is good for further analysis using PowerPivot.