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

Comments

• Anonymous
  June 30, 2009
  Here is a link to one more Excel add-in "OLAP Pivot Table Extensions" that also does similar things (and maybe more): http://www.ssas-info.com/analysis-services-tools/298-olap-pivottable-extensions-

• Anonymous
  June 30, 2009
  A similar add-in to also check out is OLAP PivotTable Extensions: http://olappivottableextend.codeplex.com Looks like PTPower has a more user-friendly UI for designing calcs, but OLAP PivotTable Extensions has a couple of other great features like searching cubes. Glad to see more activity in this space!

• Anonymous
  July 01, 2009
  thanks guys for the links to other add-ins

• Anonymous
  August 26, 2009
  This add-in is great!  Thanks for the tip.