I could write a whole case on this, but in a nutshell here is what we have found:
A PowerPivot cube is designed and initially consumed in Excel desktop. In the background, its really a cube structure based on Analysis Services (SSAS) - which explains why it works the way it works. What's cool about PP is that a business user can create their own PP cube almost on the fly without a server, SQL Server and a host of IT pros. The problem is that the desktop PP cube is for "me" analytics - its kind of hard to share unless you "share" or deploy that spreadsheet somehow. So how does it become the proxy for a full blown SSAS deployment?
This is where the SharePoint and PowerPivot solution kicks in.
Using SharePoint, a user would typically upload their spreadsheet to the intranet/internet and then let others consume that content online - including the aforementioned PP cube.
But here is the rub:
- if the user requests the spreadsheet with the PP cube from SharePoint back to their workstation, as a normal spreadsheet, they need to "download" it. This is problematic for any large sized cube. (A basic Adventure Works prototype that we built in PP mode was 1Gb in size. not big for a database - but not insignificant for a download!)
- if the user chooses to interact with the PP cube "online" via Excel Services they do NOT need to download the entire cube. Sounds like the solution until you see how limited Excel Services are for doing analytics (when compared to those other tools people have mentioned). Performance Point as part of SharePoint is another conduit - but it's far from an analytics client and is more of a dasboard solution that requires proper development.
So, users of PowerPivot are kind of stuck between a rock and a hard place. Microsoft then showed us a preview of "Project Crescent" in 11-2010, a new client tool for viewing PowerPivot ("BISM") cubes. It is slick and super fast. But its only coming out with SQL Server Denali and its unclear whether it will have legacy support for the current SSAS cube structures (and MDX itself).
Further, the new BISM framework is such a departure from SSAS/MDX and is so new, I would be willing to bet that it will be 2-3 years before it gains any significant traction in the marketplace (I have clients who are only now contemplating an upgrade to SQL 2008, and its mid 2011!).
Until then you need something like ProClarity - or its replacement - to handle the many thousands of SSAS implementations around the world. Better yet - it would be great if the replacement worked equally well with SSAS UDM cubes as well as the newer BISM/PowerPivot technologies.
And this is where the Pyramid bioXL app kicks in. It does just that. 😉