I recently ran into PTPower . PTPower is a free Excel add-in which fixes 3 topics on my personal Excel/Analysis Services features whislist:
- An easy way find the underlying MDX SELECT statement of an Excel Pivot table
- Smoother development and debugging of calculated members
- Add calculated measures to an pivot table on the fly from Excel
PTPower is exposed as an Excel 2007 Add-In - within the "PivotTable Tools" Ribbon.
Just click the Show MDX button and PTPower will show you a popup window with the underlying MDX statement of the pivot table. The MDX Statement is color highlighted and ready to be copy/pasted in MDX Studio, Report Builder, ... Very simple, but just works :-)
The second feature is even cooler. Developing and debugging calculated measures in BIDS is a bit cumbersome. Most of the time I use the script view in BIDS for that. For every change in the MDX statement you have to deploy the cube (or at least the MDX scipt using BIDS Helper) and switch back and forth to the cube browser or Excel to see the result.
PTPower allows you to build and test your calculations from Excel using a nice GUI. When you click the Calculation button a GUI similar to the calculation tab in BIDS pops up. Write your MDX calculation, click Save, click Close. Excel will refresh your pivot table and you will see the results immediately.
Calculations created via PTPower are implemented as session members. This means they are not saved to cube, but only available in the current session. When you save the Excel file and reopen it, PTPower will recreate the session members.
And this is very cool, because it allows end-user to add their own calculations from Excel. Altough they will need to learn MDX. Which might be a bit challenging for most end-users ;-)
Anyway, PTPower is nice addition to my Analysis Services toolkit. And is tooltje van de week.