PowerPivot’s limitation to SSAS
Marco Russo just published “Using SSAS 2005/2008 as PowerPivot Data Source: Query Designer” on his blog. It is an excellent article solving various issues in PowerPivot.
It is obvious that the version 1 PowerPivot has limitations to use SSAS as data source. It requires some workarounds to make it fully work.
In my opinion, I would really prefer to feed data into PowerPivot from a data mart (relational database) instead of SSAS. And normally, the SSAS cube is get the data from the underlying data mart anyway. Compared to the SSAS cube, the data mart is flat and doesn’t have any problem e.g. hidden columns. SSAS is excellent for building corporate wide BI report while PowerPivot is great for individual to get data from the data mart to do data analysis.