We don't use SQL Server, we use Postgresql for our data warehouse and only use PowerPivot as the end-user interface. We have 2 primary concerns 1) responsiveness of model for end users -- it's got to be usable and 2) BI maintenance headaches when you have multple models needing to be updated and maintained.
We've opted for an optimised single model approach (currently has 4 fact tables and about 10 dimensions but it's likely to grow a bit so fairly complex). This holds all our measures (of which there are very many). Currently there are 10 sheets with one pivot per sheet. As you can imagine, this is very slow. So we've adopted a "shredding" methodology. We maintain this single model but for distribution we shred it into 1-2 closely related sheets per workbook. This has improved performance significantly for end-users and has solved our maintenance problem.
The main problem now is that for us, BI, working with the master non-shredded file with all the pivots is slow and likely to become slower as time goes by. Any suggestions from others who are *NOT* using the full Microsoft stack (e.g. analysis services) on how to balance model maintainability and final usability?
Thanks