Analysis Services and Excel Pivot Tables

Excel is the number one business intelligence tool;  It fronts Hyperion, Cognos Financial Planning, and of course SQL Server Analysis Services and Performance Point Server Planning. In Office 2007 there is now excel services so that information workers can park their work on SharePoint where it can be properly secured.   

One issue I have seen is where you decide to publish an excel worksheet to excel services with a connection to an SSAS cube in it.  By default the worksheet will cache the data the user sees when they publish it as well as the layout and the connection to the relevant cube.  That might not be a good idea if the creating user has complete access to the underlying cube, while the intended users of the cube, have very restricted permissions. In this scenario when a restricted user opens the worksheet from excel services they will see everything that the creating user saw when they saved it.

It's simple to correct this behaviour, but very difficult to find where to do this, and in my opinion the default behaviour should be the other way round! Anyway all you need to do is to force a refresh of the data when opening the file in the connection properties dialogue:

   image

This situation also applies to surfacing an excel worksheet in Performance Point Server.

Another issue with Excel services is that it doesn't support the publication of worksheets with the new data mining add-ins installed, and I am assured that this will be fixed as part of future changes to excel services rather than by the new version of the data mining add-in that will ship with SQL Server 2008.

Technorati Tags: Excel,Analysis Servcies,Performance Point Server