Introduction: Analysis Services Reporting in Excel
For me, the Holy Grail that has been missing from Microsoft’s BI platform is an Excel Add-In that provides the type of functionality that you can find in Hyperion’s Essbase Add-in for Excel (which only works with Hyperion’s much more expensive product BTW). Hyperion’s add-in allows you to easily navigate through a cube using a simple series of mouse clicks. For example, you can drill up/down a hierarchy, remove items, and focus on items. Granted, you can do this in a PivotTable, but you are constrained to the features and limitations of a PivotTable. For example, formatting is a PITA in PivotTables. I think that it all boils down to experience. I want a tool that is transparent – so the focus of the process is on the analysis of data rather than the use of the tool.
Rather than waiting for the killer product to come, you could just build it yourself. Over the next series of posts, I’m going to discuss a basic approach to building some of the functionality that is needed for interactive reporting against Analysis Services data. Specifically, I’ll walk through a basic example that provides simple drill-up/drill down functionality without going through a PivotTable.
Of course, all of the standard “tutorial” warnings apply here. The code I’ll be presenting is intended to help demonstrate conceptual ideas. It is intentionally stripped of features and light on error handling. Likewise it does not have any security considerations built in. The sample will use the Food Mart 2000 sample cube that ships with Analysis Services. Stay tuned!