Although I agree that PowerPivot has a more user-friendly interface than using Analysis Services through standard pivot tables, PowerPivot through the Excel client has some really bad drawbacks when trying to use it instead of Analysis Services.
You need to load all the rows in the table in order to “update” the data. In large data warehouses, this is equivalent to having users run SELECT * queries directly against your database. This is terribly slow for the user and has a high resource cost for your server.
It is extremely easy for someone to either intentionally or unintentionally leave the office with the entire data warehouse in an unsafe manner. Oh!
End-user machines must be powerful enough. I tried using PowerPivot with several small tables (5 million rows or less) on our standard machine to build the company, and it did not have enough memory to update PowerPivot. The only way I can deploy PowerPivot throughout the enterprise is to upgrade all analytics machines to 64-bit Windows 7 with a memory capacity of 6 to 8 GB. Although this may be feasible in a small organization, it is not a smart decision in a large enterprise.
You will not have any good indicators of how people use your data if you transfer PowerPivot with unlimited access to the data warehouse. Yes, you can have indicators of how often people click the refresh button, and you can register the tables that they request, but you will not see how they use the data if you do not check their tables directly. And even then you will get only their final result - not their path to how they achieved the final result.
PowerPivot generates really big files. Even if someone parses the data to a small subset of the shared data, it is still difficult to share files with others, as large PowerPivot files usually exceed the minimum file size limits of the Exchange server. I ran into this in my organization, even though I did not have this problem with Analysis Services files.
PowerPivot does not have a very good security model. Of course, you can limit who gets the data for the first time, but you cannot limit it as soon as it is in the spreadsheet. Analysis Services prevents users from making changes to the spreadsheet if they do not have access to the base cube. It's just so easy to compromise the security of your most valuable business data with PowerPivot.
PowerPivot does not currently scale for very large data sources. I have several multi-billion fact tables that simply cannot be loaded by PowerPivot unless I pre-generated them up to several hundred million rows. PowerPivot works great for small data warehouses, but it does not scale elegantly for large data warehouses.
Please note that my comments above do not apply to PowerPivot through SharePoint. I have not tried the integrated SharePoint product, but many of the above problems seem to have been fixed from the documentation and demos that I saw in the version of the SharePoint product.
Despite all of the above comments, PowerPivot can work as a replacement for Analysis Services if you have a very small or immature data warehouse. If your biggest fact table is a few million rows, then the overhead of creating and maintaining a data warehouse can be ineffective if you are a BI group of 1-2 people. PowerPivot is probably a great new feature for a department that doesn't have a separate BI team, and there are only a few Excel junky analysts. Creating a virtual dataset from disparate data sources with PowerPivot does not require much complexity. But if you want to create a truly professional data warehouse that is reliable, scalable and highly manageable, then I would recommend creating cubes in Analysis Services and using Excel or third-party provider tools to connect to Analysis Services OLAP cubes.