PowerPivot is a tactical solution designed for teams and departments to develop their own ad hoc analysis of diverse data from multiple sources. The existing BI stack from Microsoftis IT driven and enterprise wide, and supports the strategic goals of an organisation.
Sharepoint is used to deliver a personalised experience to each memeber of the organisation and to allow your users to collaborate. Nowhere is this as important as in decision making. So you deploy your PowerPivot tot SharePoint to share it with the rest of the team. Once a PowerPivot is saved in SharePoint it can be used as a source in the same way as a cube e.g. you can build reporting services reports, performance point dashboard and analytic reports based on it as well as being able to slice it direclty fromthe PowerPivot gallery. Tow other key part of this are the ability to refressh the source data in a PowerPivot on a scheduled basis, and to be able to see usage and performance of the PowerPivots from the management dashboard in SharePoint Administration.
Is it an SME solution? yes absolutley as you can get started with some basic trianig and without all the heavy IT wokr ned to build a traditional BI solution. The license costs might seem high but should be offset against reduced service costs, and enoirmous flexibility and agility.
I Hope this helps, if you need to discuss further please contact me via my blog (http://blogs.technet.com/b/andrew)