Excel to the Rescue

Thanks for Marco Russo’s blog, I’ve learned that Microsoft has released an updated version (version 1.5) of the Excel 2002/2003 Add-in for SQL Server Analysis Services. I discussed in details how version 1.0 could be used to generate ad hoc reports from SSAS cubes in my book Applied Microsoft Analysis Services 2005. Although version 1.0 was compatible with SSAS 2005, it was unaware of the new UDM features. For example, it couldn’t differntiate between attribute and multi-level hierarchies. The updated bits target both UDM and SSAS 2000 although not all of the new UDM features (e.g. drillthrough actions) are supported.

Easily overlooked, the version 1.5 of the Excel Add-in represents the first Microsoft OLAP browser that targets UDM. It is not perfect, but it is better than nothing. We can only expect the Excel/UDM integration to improve in Excel 12. So, I feel good about Excel analytics. Unfortunately, I don’t share the same feeling when it comes to integrating UDM with custom applications. In the past, Microsoft Office Web Components (OWC) has fulfilled the role of such a pluggable control. Unfortunately, its COM-based architecture has made it technologically obsolete. At this point, it is not clear what Microsoft plans for OWC and integrating UDM with custom applications.

In my opinion, it is optimistic to expect that all organization will embrace Excel as an OLAP browser. For example, if you develop BI applications, I doubt you will be willing to tell your customers that they need to launch Excel to whip out some cool interactive BI reports. So, I had a dream in which the MS Santa brings me a .NET-based control (let’s call it OlapViewer) which developers can use to integrate .NET applications with UDM. Similar to the ReportViewer VS.NET control, this control could ship with VS.NET. A successor to OWC, the OlapViewer would fully support UDM. It would allow developers to add business intelligence features to custom applications. For example, the developer could programmatically add measures and dimensions to the report. When the end user is done generating the report, she could save the report definition for a later retrieval. How about creating calculated members programmatically? Or, converting the OLAP report definition to a Reporting Services report? Intercepting and possibly changing the MDX query statement?

Alas, for now, this is only a dream. Hopefully, we will see it materialized sometimes next year either from Microsoft or third parties. “The future unknown is”, as the Jedi Master Yodda used to say.