Here’s a quick run down of some considerations for using PowerPivot as a data source for Performance Point 2010.
First, make sure your integrated Microsoft Business Intelligence SQL Server 2012 Sharepoint 2010 environment has Service Pack 1 applied http://www.microsoft.com/en-au/download/details.aspx?id=35575. The assumption here is you already have SQL Server 2012 PowerPivot integrated with Sharepoint 2010. Now, as a small side consideration, if you think you may want your Power View reports to hit a multi dimensional Analysis Services cube, you could consider installing this update http://www.microsoft.com/en-au/download/details.aspx?id=35822 which provides this capability but also includes all the cumulative updates up to SQL Server 2012 Service Pack 1. The catch is its CTP. Once you’re done you create the multi dimensional data source against your Power View report as follows http://social.technet.microsoft.com/wiki/contents/articles/14704.create-a-power-view-report-with-a-multidimensional-model-data-source.aspx.
Moving on to Performance Point on PowerPivot, there are a few patches that you need to consider. First, a fix is available that addresses a few problems, you may want to read about it and download here …
Second, you also must have the ADOMD 10 driver installed on the server on which you are authoring the Dashboard in order to access a PowerPivot workbook as a data source. Once you’re done, instructions for how to create your PowerPivot data source in Performance Point included at the end of this article but note Performance Point Time Intelligence is not supported for PowerPivot data sources.
It is interesting Microsoft have not included a link to the ADOMD 10 driver in these instructions. You can however find the ADOMD 10 driver on the SQL Server 2008 R2 Service Pack 2 Resource Kit http://www.microsoft.com/en-au/download/details.aspx?id=30440.
I think thats about it, feel free to share your experiences on this.