Excel was without a doubt the killer app of the 90’s and continues to be a great tool today for businesses large and small—perhaps too great a tool. If IT is unable to build a tool, then Excel’s ease of use lets power users do it, with or without the blessing of the IT team. We’ve all seen those applications that Excel power users create and share around their organizations. As a consultant, I’ve seen those applications that reside on one guy’s desktop and provide an essential role in the day-to-day functions of the business. In each case I’ve never failed to be impressed with the amazing things that Excel can do, and I’ve been equally impressed with the artful inventiveness of the application’s creator. However, like works of art, these spreadsheets are fragile and easily broken. Although the business people who built these Excel wonders were well-meaning and skilled in Excel they were not professional developers. Without exception their applications were prone to breaking, could not deal with changing business requirements, and needed constant maintenance. And the scary thing was that only one person—the spreadsheet’s creator—really understood how the application worked. Sometimes this made the spreadsheet creator a bottleneck to the business workflow. In worst case scenarios, business could grind to a halt if the spreadsheet’s creator were unavailable for some reason or if the application were corrupted or accidently deleted. These work stoppages could be very serious, especially if there were a major problem restoring the application.
PowerPivot for Excel 2010
The SQL Server 2008 R2 managed self-service business intelligence (BI) feature called PowerPivot for Excel 2010 (formerly known by its code name “Gemini”) is designed to address these types of situations. It sounds like an oxymoron, though, doesn’t it? “Managed self-service managed BI.” How can something be self service and managed at the same time? The strategy is to empower both IT and business power users—the power users get to solve business problems with the familiar Excel tool and the IT team has the ability to centrally store, secure, and distribute the Excel spreadsheets throughout the organization using SharePoint.
About Those CALs
Of course, to take advantage of this managed self-service BI scenario you need to have the latest and greatest Microsoft technologies. You’ll need the Enterprise Edition of SQL Server 2008 R2 for the BI functionality. In addition, you'll need Office 2010 and the new SharePoint Server 2010 Enterprise Client Access License (CAL). The Excel component in Office 2010 will use a new storage engine which is built on the SQL Server Analysis Services (SSAS) OLAP engine. This new storage engine is wrapped in a DLL that will be delivered as a free add-in that you can download from the Microsoft website. The new engine uses extreme data compression, and it enables Excel to work with hundreds of millions of rows with subsecond response time. The SharePoint 2010 Enterprise CAL provides Excel Services along with a number of other enterprise-oriented features. SharePoint enables IT to centrally store and easily distribute Excel worksheets that contain essential BI and other business applications.
Managed self-service BI is all about allowing business people to take advantage of the tools they know and love while providing the IT group the ability to manage these assets in a way that makes IT part of an efficient workflow process rather than being seen as a hurdle to go around. While it may not be the Holy Grail of computing self-service, managed BI promises to enable business professionals and the IT team to work together using their discrete skills to the advantage of the organization.