Excel add ins for Analysis Services

Excel and analysis services are pretty open technologies so it’s very easier for anyone to grow their own stuff in for a richer experience, and even Microsoft provide things like the data mining add-ins which follow this principle.

These add-ins are not confined to Microsoft and they are not always free.  What caught my eye recently is PTPower which free from SQL Server Power.  This is actually a Microsoft guy,Tyler Chessman.  What it does is to allow you to create calculated members and edit MDX from within Excel…

the Show MDX button above brings up this dialog box..

I would often want to cut and paste this MDX and use it as the basis of a reporting services report so that I could deliver a fixed view of a cube consistent with the analytical view in excel.

The Calculations option underneath it gives you this …

..to edit your calculated members and this all looks spookily familiar.  Of course the ability to do this would normally mean you would need to have permission to alter the cube, but Tyler has done this in such a way that the calculated members get saved in the workbook not the back-end cube.   This could cause chaos as your business logic is now in a local excel workbook which is what you were probably trying to avoid by using analysis  services in the first place! However you can mitigate this by publishing this spreadsheet in excel services.

This kind of functionality will also come in useful when the in memory analytics come out in SQL Server 2008 r2/ Excel 2010 and no doubt Tyler will adapt all of this to work with the new release when it comes out next year (if indeed he needs to).

Technorati Tags: analysis services,olap,cubes,mdx studio,excel,sql server