Analysis Services with excel as front end - is it possible to get the nicer UI that powerpivot provides

Question

I have been looking into PowerPivot and concluded that for "self service BI" and ahoc buidling of cubes it has its uses. In particular I like the enhanced UI that you get from using PowerPivot rather than just using a PivotTable hooked up to an analysis services datasource.

However it seems that hooking up PowerPivot to an existing analysis services cube is not a solution for "organisational BI". It is not always desireable to suck millions of rows into excel at once and the interface between PowerPivot and analysis services is very poor in my book.

Hence the question is can an existing analysis services solution get the enhanced ui features that power pivot brings, without using powerpivot as the design tool? If powerpivot is aimed at self service/personal BI then it seems bizare that the UI for this is better than for bigger/more costly analysis services solutions.

Answer 1

Although I agree that PowerPivot has a nicer UI than using Analysis Services via standard pivot tables, PowerPivot through the Excel client has some really bad drawbacks when trying to use it in lieu of Analysis Services.

You have to download all the rows into your spreadsheet to "refresh" the data. In large data warehouses, this is equivalent to having users run SELECT * queries directly against your database. It's horribly slow for the user and has a high resource usage cost to your server.

2. It is extremely easy for someone to either intentionally or unintentionally walk out of the office with your entire data warehouse in a non-secure manner. Ouch!

3. The end-user machines need to be pretty powerful. I tried using PowerPivot with a few small tables (5 million rows or less) on our standard company machine build and it did not have sufficient memory to refresh PowerPivot. The only way I can see to deploy PowerPivot across the enterprise is to upgrade all of the analyst machines to 64-bit Windows 7 with at least 6GB to 8GB of RAM. Although this can be feasible in a small organization, it is not a reasonable solution in a large enterprise.

4. You won't have any good metrics on how people are using your data if you hand out PowerPivot with unrestricted access to your data warehouse. Yes, you may have metrics on how frequently people hit the refresh button and you may be able to log which tables they are querying, but you won't see how they use the data unless you audit their spreadsheets directly. And even then, you will only get their final result -- not their path to how they got to the final result.

5. PowerPivot generates really, really big files. Even if someone drills the data down to a small subset of the total data, it is still difficult to share the files with others since large PowerPivot files generally exceed minimum Exchange server file size limits. I've encountered this at my organization despite never having had this problem with an Analysis Services files.

6. PowerPivot does not have a very good security model. Sure, you can restrict who gets to the data the first time, but you can't restrict it once it is in the spreadsheet. Analysis Services prevents users from making changes to the spreadsheet if they don't have access to the underlying cube. It's just so easy to compromise the security of your most valuable business data with PowerPivot.

7. PowerPivot does not currently scale for very large data sources. I have several multi-billion fact tables that just can't be downloaded by PowerPivot unless I pre-aggregate them down to a few hundred million rows. PowerPivot works really well for small data warehouses, but it doesn't elegantly scale to large data warehouses.

Please note my above comments don't apply to PowerPivot via SharePoint. I haven't tried the SharePoint integrated product out, but many of the above concerns seem to have been addressed from the documentation and demonstrations that I've seen of the SharePoint version of the product.

Despite all of the above comments, PowerPivot could work as a replacement for Analysis Services if you have a very small or immature data warehouse. If your largest fact table is a few million rows, then the overhead of building and maintaining a data warehouse may not be cost effective if you are a BI team of 1-2 people. PowerPivot is probably a great new feature for a department that doesn't have a dedicated BI team and only has a handful of Excel junky analysts. It doesn't take much sophistication to put together a virtual data mart from disparate data sources with PowerPivot. But if you want to build a truly professional data warehouse that is secure, scalable, and highly manageable, then I would recommend building cubes in Analysis Services and either use Excel or a 3rd party vendors tools for connecting to the Analysis Services OLAP cubes.

Answer 2

Now that SQL Server 2012 is released, you may want to take a look at using one or more SSAS BISM models, rather than PowerPivot. You get interop with PowerPivot, but you can now build your model using SSDT (in Visual Studio) and can get more control over security and can host on a dedicated server.

I'll be presenting live and online this spring and summer of the BISM - here's my latest deck on slideshare - http://www.slideshare.net/lynnlangit/sql-2012-bism

Now that Office 2013 preview is out, you can check out PowerView inside of Excel (PowerPivot) without the need to have SharePoint. It remains to be seen when MSFT will remove the dependency on Silverlight (i.e. move to HTML5). The preview release of Office 2013 that I got in September still included PowerPivot which required Silverlight. I am looking forward to the release built on HTML5. Here's a deck by Jen Underwood to give you an idea of what PowerView looks like.