I will start this post with a bit of a rant. There are two types of people in the Business Intelligence world:
- Those who do not like Excel
- Those who do
Most of the right-click-hating low tech users belong to the first category (they are also usually the same people who like bubble gum and shiny things). I like bubble gum as much as the next guy, but along with those of us who are viewing business intelligence as something more than pretty gauges and dials, I consider Excel as the ultimate tool for just about every facet of BI.
Over the last two releases, Microsoft has added a lot of very powerful features to Excel to make it easier for a user to explore and analyze data in Analysis Services cubes. One can connect Excel to Analysis Services by clicking on Data->From Other Sources->From Analysis Services. That creates a connection that can then be used as a source of not only pivot tables and charts, but also a number of Cube…() functions that have been added to Excel in 2007 in order to make Excel even more robust and sophisticated reporting and analytical tool.
These functions break down the limits of pivot charts and pivot tables in terms of what’s possible with respect to the look and feel of the report. There are however some gotchas that one needs to be mindful of and also some opportunities to improve the performance of the Excel reports that are sourcing their data from Analysis Services. Here is a good article that suggests some ideas to optimize the performance.