4-page Case Study - Posted 3/9/2007
Views: 373
Rate This Evidence:
Improved Office Business Application Empowers Better, Faster Business Decisions
Situation
In June, 2003, Microsoft® developed an Office Business Application called “Rhythm of the Business” to empower Microsoft financial analysts and senior executives to make more informed and timely business decisions. The application provides real-time business intelligence in the form of spreadsheets and graphics of global sales, measured against key business metrics. The spreadsheets consolidate data provided by geographically diverse business groups, each using a standard template to record and confirm required business information. The consolidated data can 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.
The Rhythm of the Business application is used by more than 3,500 Microsoft employees. The majority of the application’s users are able to annotate the underlying data with comments. Every month, a small subset of the application’s users is permitted to finalize and approve the data. This information is then consolidated and presented to top executives every quarter in a spreadsheet that helps them accurately and consistently view and analyze the effectiveness of current global sales, marketing, and financial strategies.
Growing Complexity Requires New Solution
The Rhythm of the Business application was originally built on Microsoft Office Excel® 2003 spreadsheet software and comprises over 40 worksheets, including over 17,000 cells used to exchange data with Microsoft SQL Server 2005 databases. Using Excel formulas to apply business rules to the data from over 40 separate feeds, the application presents required performance metrics to decision makers. However, as its use expanded, business leaders soon wanted more flexible data visualization and dynamic, real-time notifications for more timely decision making.
To meet these growing needs, Microsoft needed a solution that would:
- Support the growing complexity of data and business requirements.
- Enhance both the scope and detail of business insight.
- Improve the ways that information could be viewed and measured.
One limit which hindered the application is the number of unique cell format combinations available in Excel 2003. Once Rhythm of the Business hit the 3,667 limit, additional data had to be reformatted to fit existing format combinations. This significantly increased the expense of managing the application and reduced productivity by creating bottlenecks and requiring ongoing discussions over which format combinations were least expendable.
Additionally, the limit also reduced the ability to merge cells. This resulted in less flexibility and accuracy in viewing, measuring, and analyzing data, impacting data presentation. “Business users kept coming to us, asking us to add or change cell formats and we had to say, ‘No,’ or we needed users to determine what current cell format they would be willing to trade for the new cell format combination,” says Sergei Gundorov, Development Lead, Rhythm of the Business.
Another limiting factor was the number of possible conditional formatting rules. Because Excel 2003 allows only three, data could not be represented in all the ways that business users required. In addition, the Excel 2003 version did not meet user requirements for data visualization to illustrate trending data or business exceptions. In the previous version, according to Gundorov, “Flipping on something like a traffic light meant a lot of work, including the use of a combination of a [Microsoft] Wingdings® font and conditional formatting, combined with nested conditional Excel formulas.” The result of all this made it difficult to quickly identify and respond to business trends.
Smooth, Cost-Effective Migration Path Needed for Vital Application
Because Rhythm of the Business provides ongoing, vital business intelligence, it was crucial that the migration to a new solution be as smooth and seamless as possible for both the developers and users. So, while there was much excitement regarding the expanded limits, improved extensibility, and visualization capabilities available with Microsoft Office Excel 2007, there were migration concerns, including how to effectively:
- Migrate thousands of lines of well-tested Visual Basic® for Applications code to managed code.
- Quickly and seamlessly leverage key features.
- Empower users to easily transition to the new Ribbon UI.
Solution
In October 2005, the Rhythm of the Business development team began exploring the most effective approach to migrate the application to the 2007 Microsoft Office system. Initially, the team was impressed with how the Excel 2003 version loaded and worked, even on the August 2005 “Pre-beta” version of Microsoft Office Excel 2007 (See Figure 1). Gundorov says, “In our experience, every custom solution continues to work in Office 2007. The Office team did a phenomenal job here.”
The Migration Process
Although the existing application worked in Excel 2007, both users and developers were eager to take advantage of enhanced features of Excel 2007, which would require additional development effort. The challenge became how to migrate and expand the extremely rich application features developed over the last three years in Visual Basic® for Applications in a way that was both smooth and cost-effective.
Figure 1 - Rhythm of the Business UI opened in Excel 2007, showing default behavior of the menus and command bars on the Add-Ins tab.
Gundorov explains, “While none of the command bars or menu items lost their functionality when we opened the application in Excel 2007, we didn’t want to have the menus and command bars sit on the Add-ins tab, which is the default behavior.” He continues, “There is nothing wrong with the well-tested Visual Basic code we had. So, for our situation, we chose to create a way to tie the Visual Basic functions to the Ribbon UI and .NET custom controls on Custom Task Panes. By doing so, we were able to start leveraging the enhanced functionality of Excel 2007 as quickly as possible without a complete managed code re-write.”
This was done by creating a simple abstraction on top of the Visual Basic code. This strategy helped keep the costs of migration down and allowed the team to focus more on leveraging the enhanced functionality of Excel 2007. (For an article detailing the code used for this approach, go to: http://msdn2.microsoft.com/en-us/library/bb194905.aspx)
The result of the migration is a more customizable, people-centric, collaborative solution built on the 2007 Microsoft Office system platform (Figure 2). The key features of the new application, now in production, include:
- Expanded cell format limits
- Enhanced user interface
- Improved access to actions and information
- Greater visualization capabilities
Limits No Longer Limiting
In Excel 2007, the limits of the number of unique format combinations and conditional formatting rules now far exceed the restrictions in Excel 2003 (see Table 1).
Out of the box, Excel 2007 offers 64,000 unique format combinations and a virtually limitless number of conditional formatting rules, bound only by the memory restrictions of the client PC. “Now, when we are asked to change or increase how data can be entered, viewed, or analyzed, instead of saying, ‘No,’ we can easily meet the need,” says Gundorov. “And, users do not need to waste valuable time reformatting data or discussing trade-offs. Instead, they can focus on their work.”
Enhanced User Interface with Ribbon UI
The results-oriented Ribbon UI in the 2007 Office system has replaced the traditional command bar, providing users a ribbon of tabs highlighting their most relevant tasks (see Figure 3). The extensibility of the ribbon and use of custom icons made it very easy for developers to create a UI that is unique to the Rhythm of the Business application. This not only simplifies the use of the application by tailoring the UI to the user; it also eliminates any confusion between different applications built on Excel 2007.
Figure 2 - Diagram of Rhythm of the Business UI Components
As expected, the new UI did require Rhythm of the Business developers to spend a few days learning how to code against the Ribbon items. However, in addition to coding, using the classic programmatic model, the development team created a way to code against Ribbon items in a command-bar fashion at run-time by using an abstraction layer to expose properties of the most common Ribbon UI controls and by creatively leveraging built-in call-back functions and properties. This strategy allowed developers to extend the Ribbon UI for their application in a familiar, object-oriented way, while gradually learning and taking advantage of the new programmatic model.
This feature has required developers to invest some time to reach proficiency. However, according to Si Farahmandnia, Senior Applications Developer, “It has been my experience, so far, with the 2007 Office system that whatever has not come easily has come smoothly and has been well worth the effort for the improved functionality.”
“It has been my experience, so far, with the 2007 Office system that whatever has not come easily has come smoothly and has been well worth the effort for the improved functionality.”
Si Farahmandnia, Senior Applications Developer
To improve user transition to the new UI, developers took advantage of the extended Supertip feature of the 2007 Office system. Now, when a user’s mouse crosses over a button, for example, instead of seeing only a brief label or phrase, a whole paragraph can be displayed to more thoroughly explain the function of the button and give any other information deemed useful. For example, if there is confusion between two similar functions, controlled by two separate buttons, the paragraph displayed for each button can provide the necessary clarity. According to Gundorov, “Most people aren’t going to read the manual, so the extended Supertip is a lifesaver for both the user and the tool’s Help staff.”
Figure 3 - Rhythm of the Business UI Built on Excel 2003 (left) and UI Built on Excel 2007 (right)
Improved Access to Actions and Information Using Custom Task Panes The new version of the Rhythm of the Business tool includes several custom, application-level task panes, a platform capability introduced in the 2007 Office system. These task panes provide users with access to the information and actions they need when they need them. The custom task panes have also eliminated the use of pop-up forms that users were previously obliged to wade through to get to what they needed.
Custom task panes can also be dragged and dropped on each screen to the location that best suits the individual user. And, because the task panes are not document-centric, they do not disappear when a workbook is closed or loses focus. Gundorov adds, “The custom task panes are far better than the Visual Basic forms and again, you can re-use as much of the legacy Visual Basic code or managed code as you want. In addition, with custom task panes, we were able to easily write our own controls, taking advantage of all .NET controls, including Microsoft Windows® Forms, in some cases with only one line of code.” He adds, “While not part of our current solution, we even tried using Windows Presentation Foundation graphics and they look great!”
Greater Visualization Capabilities
Another feature of the new application is the rich and varied data visualization capabilities of Excel 2007. Developers can easily display comprehensive business intelligence data using whatever visualization executives require. According to Andrew Petro, Senior Product Manager, “The new version allows us to use traffic lights. This feature drives the main component of our entire product.”
Gundorov concludes, “Because we are also now able to make use of the virtually unlimited number of conditional formatting rules to organize information, we are using the extensive visualization capabilities to provide top level executives with the exact business intelligence they need, displayed in a way that is easy to understand, analyze, and act upon.”
Benefits
Migration of the Rhythm of the Business application to the 2007 Office system has led to the following benefits:
- Improved presentation of complex business intelligence
- Cost-effective, easy migration
- Enhanced user productivity
- Increased developer productivity
- Faster template updates
Improved Presentation of Complex Business Intelligence
The most important benefit of the new application is its vastly improved ability to present comprehensive, complex business intelligence in a way that is easy to view and analyze. This empowers executives to quickly spot trends and exceptions, measure the success of current business strategies and initiatives, and respond in a timely manner. In fact, the extensive conditional formatting rules, extensibility, and visualization capabilities of Excel 2007 have made it possible to implement the most important worksheet in the entire Rhythm of the Business workbook: The Subsidiary Scorecard (see Figure 4). This scorecard presents such extensive, comprehensive data in such a clear, concise fashion that it will be used as the key measurement of the whole company’s performance in the coming year.
Behind this straightforward, action-oriented scorecard lies:
- 19,152 data cells (XML mapped and UI formulas)
- Over 40 data feeds
- 4382 rows of data
- Complex business metric calculation rules
- Asymmetrical data that can be placed anywhere on a worksheet through the single-cell XML mapping feature (This allows analysts to take any cell, define a business rule for what type of data to pull into that cell from the database, and then capture this information in the spreadsheet.)
Cost-Effective, Easy Migration
By developing a simple abstraction on top of the Visual Basic code, the development team was able to keep the costs of migration down, while leveraging the enhanced functionality of Excel 2007. This approach allowed the team to migrate the Rhythm of the Business to the 2007 Office system in a way that did not require extensive re-coding or disrupt business, while providing the best of what Excel 2007 has to offer.
Features of the 2007 Office system also enabled users to easily move to the new version of the tool. According to Julie Clark, Product Manager, Microsoft, “Users of the earlier version had a very difficult time deciphering from the icons what each button on the toolbar was for. The larger icons and added text in the new Ribbon UI now make it much clearer to users which buttons to use for each function.” She adds, “The new Ribbon UI is also much clearer than the command bar in the previous Office 2003 version. In a recent tool training session for users from different business groups, I was able to easily identify the exact button to push for each function and users were easily able to follow along."
Enhanced User Productivity
The majority of the 3,500 users of the application must regularly contribute, correct, and confirm the business data that is required by their specific worksheet. Because the application is now far easier to navigate using the unique, customizable features of the Ribbon UI and Custom Task Panes, workers spend less time entering sales numbers and more time increasing them.
Increased Developer Productivity
The new application has also improved developer productivity through the advantages of .NET Framework 2.0 programmability, such as managed controls and managed code, including multi-threading, object oriented design, enhanced component re-usability, and abstraction layers. According to Gundorov, “Now, instead of denying requests or asking for trade-offs from business users, the development team can quickly and easily—often with just one line of code—provide any necessary additions or changes.” Thus, instead of discussing how to get around what can’t be done or waiting for developers to figure out a way to get it done, users can quickly get the business intelligence they need and developers can spend their time expanding the capabilities of the application.
Developer productivity has also been improved by the application’s ability to contain multiple components, written in different languages. This has made it easy to assign various aspects of the migration to developers with different types and levels of experience. This aspect of the migration process has also created a collaborative environment where Visual Basic developers and managed-code developers are able to combine their expertise. This synergy has increased both the productivity of the development team and the knowledge of the individual developers.
Gundorov states, “As we migrated the application, the Visual Basic developers were able to use what they’ve known, but also take it to the next level, using managed code. And, from the other side, the developers on the team with managed code experience were able to contribute their skills to code against Office, leveraging new programmability features.”
Farahmandnia adds, “Now, multiple developers can work on the application concurrently both onshore and offshore.”
In addition, after each business cycle, developers used to spend at least eight hours performing workbook clean-up. Now, with the expanded limits and features of the new application, no clean-up is required after each major release cycle (See Table 2).
Smaller File Sizes for Faster Template Updates
The new XML file formats of the 2007 Office system has meant faster template updates for end users. Previously, for each template update, the file that had to be sent to approximately 3,500 tool user desktops was 8MB. Now, the file contains more data points, yet its size has been reduced to only 2.1MB, significantly reducing network traffic (See Table 2).
More Benefits to Come
The Rhythm of the Business development team continues to improve the efficiency and manageability of the application by migrating current Visual Basic code piece by piece to managed code with no impact on the users. Gundorov explains, “With every piece that we migrate for more tool functionality, we are able to gain more control, making it possible for us to create and change many aspects of the application with only one line of code.” He continues, “Fully migrated, the number of lines of code that would be reduced would be hundreds, if not thousands.” (See Table 2). This puts us on an entirely new level with so much more control and so many more efficiencies.”
The development team is also looking forward to leveraging other features of the 2007 Office system, including Excel Services. This will empower the team to expand the BI capabilities through cube formulas for data retrieval, reduce the number of lines of code in the database, and improve the ability to store and manage business rules in the database, making data retrieval virtually instantaneous.
“With all we have done, we have only scratched the surface of what is now possible with the 2007 Office system,” states Gundorov. “The development team is also looking forward to leveraging other features of the 2007 Office system, including Excel Services and vastly expanded built-in Business Intelligence support”. He concludes, “The sky’s now the limit of what we can do to provide business leaders with the clear, comprehensive business intelligence they need.”
Microsoft Office System
The Microsoft Office system is the business world’s chosen environment for information work, providing the programs, servers, and services that help you succeed by transforming information into impact.
