Using Excel Services and Open XML to Create Custom Office Business Applications
Published: August 2008
Since 2003, Microsoft executives and financial analysts have relied on an Office Business Application (OBA) solution originally called the Rhythm of Business (ROB) to manage the business worldwide.
Recently, ROB has been upgraded to the Microsoft® Office System 2007 and in the process, Microsoft IT discovered how they can meet the diverse requirements of the business and leverage new functionality utilizing the new capabilities in Microsoft Office SharePoint Server® 2007 such as Excel Services and InfoPath Forms as well as SQL Server® 2005 Analysis Services.
Products & Technologies
Microsoft needed to expand and upgrade the business intelligence application that executives and analysts used extensively to scale globally and meet the needs of a diverse organization. User requirements had increased and MSIT needed to focus on more out of the box solutions instead of custom created applications. With the Office System 2007, MSIT and the business users found a flexible and agile environment to meet both business and IT requirements.
Through Excel Services, business users, instead of IT personnel, can build custom Web-based dashboards by using their existing Excel knowledge and without writing any code, greatly reducing the development and maintenance cost of business intelligence applications.
Deployed in June 2003, the ROB application was designed to be an interactive business intelligence application that provided financial analysts and top executives at Microsoft with up-to-date progress reports for key business metrics. With this data, executives could develop global sales and marketing strategies, as well as measure the success of these strategies globally.
Although the ROB application provided valuable information, it also had limitations. Originally built to run on Microsoft Office Excel 2003, the usage requirements for it grew quickly as employees and executives continuously added more data to analyze and demanded more and better ways to visualize trends in the data. This growing complexity of the data and business requirements rapidly expanded beyond what Office Excel 2003 could support.
The first and most obvious fix for this issue was to migrate the ROB application to Office Excel 2007. The upgrade was a smooth transition that caused little downtime or disruption to the use of the ROB application. After completing the customizations needed to support some of the legacy features of the application, Microsoft Information Technology (Microsoft IT) began to explore how the more advanced capabilities of the 2007 Office system could enhance the upgraded ROB application. During this exploration, Microsoft IT found that the new built-in business intelligence capabilities of the 2007 Office system provided greatly expanded benefits. The result of this discovery was a whole new platform upon which a new OBA was built; ROB v2.
Two years later, the ROB application has been completely redesigned and is now renamed to the Executive Reporting UI Infrastructure to reflect the flexibility and extensibility of the new application. Currently, this OBA provides a Web-based user interface (UI) that is used on a global basis by more than 5,250 users globally. Teams using this application within Microsoft include the Senior Executive Leadership (SLT) team that used the original ROB application and the OEM Business group, and it is now gaining traction with the Microsoft Business Solutions group.
This case study is intended for chief information officers, technical decision makers, and enterprise IT professionals who support sales, marketing, and finance operations. It demonstrates how Microsoft IT's effort to upgrade its own business intelligence application resulted in a new and vastly improved OBA.
What started out as a simple upgrade of the underlying technology soon became a a full change of the platform on which the ROB application was built to enable greater insight and self-service for users across the company. Office Excel 2003 did not meet user requirements for how much data could be handled as well as how it displayed data trends. Executives wanted little to no downtime due to the importance of the application in making business decisions, Microsoft IT planned a graduated upgrade. The original plan was to upgrade in two stages: The first stage was simply to get the tool working using Office Excel 2007 data capabilities, and the second was to update the ROB application to take advantage of the upgraded feature set in Office Excel 2007. It was during this second stage that Microsoft IT discovered the capabilities in the 2007 Office system that led to redesigning the entire platform on which this business intelligence application runs.
Migration to Office Excel 2007
Because the ROB application provided vital business intelligence, it was crucial that the migration to upgrade its capabilities be as smooth and seamless as possible for both the developers and users. Additional worksheets and a growing volume of data had pushed the ROB tool and the underlying application to its limits. The development team needed to quickly and seamlessly leverage the upgraded features of Office Excel 2007 with little to no downtime for the ROB application.
In general, the ROB application opened and worked in Office Excel 2007 with few adjustments needed. However, the ROB application relied heavily on the use of Microsoft Visual Basic® for Applications (VBA) to view and manage data. Microsoft IT found that the VBA code migrated seamlessly to the new version of Excel. However, while these customizations displayed on the menu and toolbar in Office Excel 2003, after the migration all of the customizations were moved to the Add-In tab in the Office Excel 2007 ribbon. Because these customizations were so heavily used, Microsoft IT felt that this would hinder the ability of users to quickly find what they needed.
Microsoft Office 2003 uses toolbars and menus as a way for users to view and make changes to documents, whereas the 2007 Office system uses ribbons. Microsoft IT decided that the best solution was to add an application-specific tab to the ribbon for existing VBA customizations. Any new features to the UI were added through managed code in several custom task panes. This UI upgrade took about three weeks, with the equivalent of 1.5 employees working on it during that time.
For more information and a code sample of the customized ribbon UI that Microsoft IT implemented, refer to the MSDN article "Custom Task Panes, the Office Fluent™ Ribbon, and Reusing VBA Code in the 2007 Office System" at http://msdn.microsoft.com/en-us/library/bb194905.aspx.
Use of VBA Code
Although the existing application worked in Office Excel 2007, both users and developers were eager to take advantage of enhanced features of Office Excel 2007, which required additional development effort. The challenge became how to migrate and expand the extremely rich application features developed in VBA over three years in a way that was both smooth for business users and cost-effective for development. Microsoft IT developers wanted to eventually change these the existing VBA code to managed code that would run more efficiently, but due to time constraints, they did not want to include this migration in this stage of the upgrade. To allow the development team to migrate the VBA code to managed code in a cost-effective manner without time constraints, Microsoft ITcreated a custom interface called IFunctionWrapper.
IFunctionWrapper serves as an abstraction layer between the VBA and managed code bases, enabling Microsoft IT to take a more gradual approach to migrating from VBA to managed code. The IFunctionWrapper interface created a two-way highway between the code bases, allowing them to coexist. With this innovation, the ROB developers could use parts from each code base that were working properly without requiring an immediate upgrade to managed code.
IFunctionWrapper consists of just a few lines of code. To view an article that includes the code used for IFunctionWrapper, go to http://msdn2.microsoft.com/en-us/library/bb194905.aspx.
Exploring Features of the 2007 Office System
After the ROB application was working in Office Excel 2007, Microsoft IT could then explore the new features available in the 2007 Office system to see how they could enhance the ROB application. Microsoft IT wanted to find a way for users to create their own modifications, which could reduce and hopefully even eliminate IT involvement in these types of content changes.
The executives who use the ROB application wanted to make the application available over the Web so that it could be accessed globally. Instead of opening a template locally on the user's computer, the executives wanted a server-side application that did not require any client-side code. They also wanted tight control over how and when the information on the spreadsheets was updated. It was the search to support this feature that led Microsoft IT to explore Excel Services as a viable option, part of the Microsoft Office SharePoint Server 2007. The result is a more customizable, user-centric, and collaborative business intelligence application built on the SharePoint platform.
Excel Services enables users to load, calculate, and render Office Excel 2007 workbooks as interactive reports that can be accessed through a Web browser. Excel Services is a shared service of Microsoft Office SharePoint Server 2007 and is not a component Office Excel 2007. Think of Office Excel 2007 as the authoring tool for spreadsheet data, and Excel Services as the reporting tool for this data. Users author spreadsheets in Office Excel 2007, and then post these to a document library on a SharePoint site. With the right permissions, the workbooks, or parts there of (you can choose to display a collection of sheets, a named item, or combination) are rendered in a thin-browser experience without requiring the client.
Simplified Report Content Authoring and Revision
Excel Services provides the ability to present Excel documents as web parts in a dashboard, or allow users to view the entire workbook or a sheet(s). Dashboards are Web pages that contain a set of Web Parts that display data.. Dashboards and Web Parts are designed to provide a customizable overview of information stored in an application file, in this case an Office Excel 2007 workbook.
Office SharePoint Server 2007provides dashboard templates and a library of Web Parts out of the box that help authors to quickly customize the appearance of a Web page, as well as provide filters to dynamically change the information based on user input or actions that take place on the page. Using multiple Web Parts, a dashboard can display information such as reports, charts, metrics, and key performance indicators (KPIs) from various data sources.
The main Web Part that Excel Services uses is the Office Excel Web Access Web Part (EWA). Office Excel Web Access displays data and charts from an Office Excel 2007 workbook and also enables interaction with this data from within a Web browser. It gives users the look and feel of Office Excel 2007 by including sheet tabs, outline buttons, and drop-down arrows. Office Excel Web Access also allows connections to other Web Parts. Figure 1 shows the UI of Excel Services .
Figure 1UI of Excel Services in SharePoint 2007
A workbook author uses the Publish to Excel Services feature in the Office Excel 2007 client to publish the workbook for use in Excel Services. Before publishing the workbook data, the workbook author marks portions of the workbook as Viewable on the Server. This enables the workbook author to specify which parts of the workbook, such as individual worksheets, named ranges, or charts, will display in the Office Excel Web Access Web Part.
Excel Services uses the View Only permission that is set in Office SharePoint Server 2007. All users who are added to the Viewers group in Office SharePoint Server 2007 get this level of permissions by default. View Only permission allows the user to view data, apply filters, and even perform what-if analysis, by manipulating designated values. Users with View Only permission cannot access the workbook or change the values in any cells of the original workbook. Users who have the Open Item right can open the full workbook, including items that have not been designated as server viewable.
Whereas Office SharePoint Server maintains access control lists (ACLs) and performs authentication for the workbooks, Dynamic Dimension Security in SQL Server 2005 Analysis Services and Kerberos can be used to help protect the data. Constrained delegation is the preferred configuration for deploying Excel Services and Analysis Services in a distributed environment. It is available for communication between the servers that host Excel Services and the back-end data sources.
Single Version of the Truth
In a business environment, workers often need to see critical data at a specific point in time. For example, in a comparison of sales, inventories, revenues, and profits between fiscal quarters, a worker wants to control the specific date and time that the data readings are taken. If a user changes the information in an Office Excel workbook, unexpected differences in calculations and results can lead to uninformed decisions. Controlling the specific time and date of the data is often referred to as creating "a single version of the truth." Having one version of the truth verifies a consistent view of the data.
Excel Services facilitates the ability to create one version of the truth by using a master workbook that contains the cumulative financial data. Office SharePoint Server enables users to tightly control who updates and has access to the master workbook, even after the data is published through Excel Services. Office SharePoint Server also tracks the changes made when users update the workbook and, if necessary, enables users to roll back to a previous version.
New Ways to Connect to Data
Beyond exploring the business intelligence capabilities of Office Excel 2007, part of redesigning the ROB application was also taking advantage of Office SharePoint Server 2007 data connection capabilities. When using Excel Services, the information needed to connect to a data source can be stored in the workbook or in an Office Data Connection (.odc) file. The .odc file contains information about how to locate, log in to, query, and access the external data source. The workbook author or an administrator can create the connection information by using Office Excel 2007, and then save the connection information to an .odc file. Using .odc files simplifies administration of data connections by centralizing all of the needed information, especially when many users share the data and the connection information needs to be updated.
Office SharePoint Server can maintain a document library, called a data connection library (DCL), as a location to store and manage these data connection files. An authorized user can store, share, and manage .odc files in the DCL. Administrators can even configure Office SharePoint Server and the user's client computer to automatically detect changes to the connection file so that it can get the most up-to-date version.
New Ways to Store Data
Using Excel Services led Microsoft IT to change not only how the application connected to the database, but also how the data was stored and then integrated into the Office Excel 2007 spreadsheet. The ROB application used a relational database to store the data displayed in the Office Excel spreadsheets. Microsoft IT used XML mappings to get the needed data from the relational database to display in the ROB application UI. Each time a user wanted to add some data to a workbook, Microsoft IT had to create a new XML mapping. Microsoft IT wanted to reduce the amount time needed for the creation of new requests for XML mappings as well as management of the existing XML mappings.
The information presented in Excel Services can use data that is saved in the master workbook or can use data from multiple external data sources, such as a relational database or an online analytical processing (OLAP) cube. As a result, Microsoft IT completely rewrote the backend of the application to use SQL Server 2005 Analysis Services. This enabled Microsoft IT to change the type of database used for its OBA from a relational database to an OLAP cube. The result of this change enabled users to retrieve data from the data source with no involvement from Microsoft IT, and provided quicker responses to changes in the underlying data. XML mappings were replaced by user-friendly cube functions. A financial analyst can start using these functions with minimal or no training. When a cube function is used, Office Excel 2007 automatically provides a list of OLAP-based connections that are defined in the workbook. Additionally, when building expressions in a cube function, Office Excel 2007 lists available dimensions, hierarchies, measures, and member names directly from the cube, so that users do not need to enter this information manually.
Rich Text User Commentary with Image Support
The most important feature of the ROB application was the ability that it gave users to discuss the data. Users could manually enter commentary to explain the data and what the data represented. This gave users the forum that they needed to identify, explain, and escalate issues. Microsoft IT used ActiveX controls to include this commentary in the ROB application UI. These controls included the author's e-mail alias, as well as a date and time stamp along with the commentary text.
Microsoft IT began using an InfoPath form to support user commentary in the new Executive Reporting UI Infrastructure application,. Out of the box, the InfoPath form supports rich text, meaning that the text can include formatting so the user can emphasize the text with fonts and character formatting like bolding or italics without the need for ActiveX.
The ROB application included images in the commentary, and the users expected the same feature in the new Executive Reporting UI Infrastructure application. To accommodate this expectation, Microsoft IT created a custom picture conversion component by using ASP.NET. The InfoPath form could capture commentary text as well as inline images and save these to the database in Extensible HTML (XHTML). The custom Web Part retrieves the images from the database and converts them so that they can properly render and display in the Web browser.
Extending the Solution
The design of Excel Services and the Office Excel Web Access Web Part does not support some of the workbook features that are in the ROB application. Instead of removing these features, Microsoft IT discovered a solution to continue to provide these features even though they have to be stripped from the workbook when it is published to Excel Services. The plan was to add these features back when the workbook is exported from Excel Services to the Office Excel 2007 client. This solution became reality because of the Open XML file formats available in the 2007 Office system.
Note: The Open XML file format is a European Computer Manufacturers Association (ECMA) standard. Open Office XML File Formats are an extension of the Open XML standard in the 2007 Office system.
Open XML file formats separate the contents of a document from the presentation information. The different types of data in the document are each saved in separate files. For example, a document that contains images, content, metadata, and embedded code has a separate file for each of these content types. All of these files are saved together as a compressed package file. By adding a .zip extension to the document, a user can open the package and view the different files within it.
Microsoft IT found that this new file format enabled them to add back features that were not allowed when viewing data in Excel Services and the Office Excel Web Access Web Part. A server-side process can read and change any number of items in the compressed package without affecting the rest of document. Adding just a few lines of code can modify the package to add back functionality when the user exports the workbook from Excel Services back to the Office Excel 2007 client.
A user working with data from a workbook in the Office Excel Web Access Web Part can use the Open in Excel option at any time to export the data back to the Office Excel 2007 client. The Content Editor Web Part enables a Web page author to include a function that captures the active session workbook byte stream from the Web browser. After this byte stream is captured, it can be manipulated before the workbook is sent to the Office Excel 2007 client to restore or add features that are incompatible with Office Excel Web Access.
Single Item Select Filter Web Part
One issue that emerged when Microsoft IT designed the new Executive Reporting UI Infrastructure application was that multi-select controls included in Office SharePoint Server 2007 do not enable the user to select just a single child of any parent items listed. Currently, all built-in Office SharePoint Server 2007 filter Web Parts that support interaction with OLAP allow only multiple selections. To address this issue, Microsoft IT created a custom Web Part called the SharePoint Single Select AS Filter Web Part. This filter enables the selection of a single item so that only that item's data is displayed. The code for this Web Part is published at http://code.msdn.microsoft.com/ASSingleSelectWP.
High Quality Printout of Reports
Although a user can print from the Web browser, the resulting printout will not have the rich details that the Web browser can display. The ability to print from the Web browser and have the same detail that the user is viewing is another feature that was expected as part of the Executive Reporting UI Infrastructure application. As a solution to this issue, Microsoft IT added a Print to Excel button to the Web page. When the user clicks this button, a new window opens an .aspx page. The .aspx page retrieves the active workbook, manipulates its contents by using the Open XML package object model, and then streams the bytes back to the Office Excel 2007 client. An exact copy of the workbook with the same layout of the data as it is displayed in the Web browser can then be printed using Office Excel 2007 printing capabilities.
The code that Microsoft IT uses to add this functionality is included in the MSDN article "Creating Business Applications by Using Excel Services and Office Open XML Formats" at http://msdn.microsoft.com/en-us/library/cc540662.aspx.
In 2003, Microsoft launched its new business application named the Rhythm of Business. Used by analysts and executives, the application used Office Excel 2003 spreadsheets to consolidate data provided by geographically diverse business groups, each using a standard template to record and confirm required business information. The consolidated data can then be viewed in various ways, enabling more effective analysis of current marketing strategies and business investments and improved agility to respond to changing business climates at local and global levels.
By 2006, the need for better visualization of the data and enhanced abilities to handle large and growing volumes of data led to a plan to upgrade the ROB application. Microsoft IT needed to find the most effective way to provide a quick and seamless way to migrate the application to from Office Excel 2003 to Office Excel 2007. To immediately benefit from the upgraded data capabilities while keeping the migration time to a minimum, Microsoft IT began using the application in Office Excel 2007 with only minimal changes.
As Microsoft IT explored the business intelligence features in the 2007 Office system, it found that much of the customizations in the ROB application could be replaced with out-of-the-box features that were better and more powerful than the original application. This new user-customizable platform for a business intelligence application needs only minimal IT involvement. Any user with the appropriate SharePoint permissions can create a dashboard with just a few clicks. Users can choose which Web Parts they want to display on the dashboard, as well as where they want those parts displayed.
The key features of the new application, now in production, include:
- Increase in users from 3,000 to 5,250
- Improved data visualization and charting capabilities
- Expanded cell format limits
- Enhanced user interface
- Improved access to actions and information
- Global application access
- Removal of IT resources from the day-to-day application use
- Higher quality printouts of the application data
For More Information
For more information about Microsoft business intelligence solutions, including demonstrations, product training, white papers, and webcasts, go to http://www.microsoft.com/bi.
For more information about configuring Excel Services, see the "Configure Excel Services" section of the Microsoft TechNet Web site at http://technet.microsoft.com/en-us/library/cc303438.aspx.
For more information about the customized ribbon UI that Microsoft IT implemented and a code sample, see the "Creating Business Applications by Using Excel Services and Office Open XML Formats" MSDN article at http://msdn.microsoft.com/en-us/library/cc540662.aspx.
For more information about the IFunctionWrapper that Microsoft IT created to preserve VBA functionality in the Rhythm of Business application, go to http://msdn2.microsoft.com/en-us/library/bb194905.aspx.
For more information about Microsoft products or services, call the Microsoft Sales Information Center at (800) 426-9400. In Canada, call the Microsoft Canada information Centre at (800) 563-9048. Outside the 50 United States and Canada, please contact your local Microsoft subsidiary. To access information via the World Wide Web, go to:
© 2008 Microsoft Corporation. All rights reserved.
This document is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, IN THIS SUMMARY. Microsoft, ActiveX, Excel, Fluent, InfoPath, PivotTable, SharePoint, SQL Server, and Visual Basic are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries. The names of actual companies and products mentioned herein may be the trademarks of their respective owners.