Excel OLAP add-in

Excel already has always had excellent support for analysis services, but to get the best form it you need to use a version of office later than than the version of SQL Server you are connecting to.  So for analysis services 2000 excel 2003 is OK but if you connect to analysis services 2005 then you are a lot better of with excel 2007 as this version understands the newer features.

One thing you can't do in any version of Excel is write your own analysis services (mdx) formulae.  There's no technical reason for this as all excel is doing in getting the data is sending an mdx query in XML/A form.  That has been fixed with an OLAP add-in for Excel project that has just been posted on Codeplex. The tool also allows you to see the mdx that's passed to analysis services, and my top tip for this would be to used it to design queries based on cubes for reporting services reports.

image

One word of caution about keeping analysis services calculations away from the cube is that they can only be seen by going through excel and so I would see this tool being used as a prototyping tool for new calculations, and when these are ready to go in to production they are stored in the cube and available for all to use.  Why? One version of the truth - hard to achieve and very easy to break.

Technorati Tags: Analaysis services,cubes,Excel 2007,BI