Enhancing Excel-Based System Gives Leaders Real-Time Data
Business Case Study
Published: April 2011
An internal Microsoft group's data entry processes have been transformed, because the group's business application can write data back to a SQL Server Analysis Services OLAP cube from an Excel cell. Employees worldwide can now enter or modify data in any spreadsheet cell and submit changes in real time. As a result, decision makers have updated data available immediately and can make better business decisions.
Business Case Study, 179 KB, Microsoft Word file
As the worldwide leader in software for business and personal computing, the vision of Microsoft Corporation is to enable people and businesses throughout the world to realize their full potential.
The Corporate Finance group needed a better system for gathering and analyzing current data for its annual mid-year review process. Because manual processes were used to gather some of the data from many different global sources, there was a delay between data entry and data availability in the user interface.
Microsoft IT created an upgraded version of the Executive Reporting UI Infrastructure solution that is based on the same methodology used in the PivotTable What-If Analysis (writeback) feature in Microsoft Office Excel 2010. Users can enter data directly into the OLAP cube, making it instantly visible to other decision-makers
The Microsoft Corporate Finance group senior leaders depend on real-time business intelligence data to deploy market strategies and make key business decisions. The Corporate Finance group needed a better system for gathering and analyzing current data for its annual mid-year review process. Because manual processes were used to gather some of the data from many different global sources, there was a delay between data entry and data availability in the user interface.
To address this challenge, the Microsoft IT Business Intelligence Engineering Team created a new application based on the PivotTable Write Back feature in Microsoft Office Excel 2010. The team extended this same methodology to individual Excel spreadsheet cells that contain OLAP functions. As a result, data can be written back to a SQL Server 2008 R2 Analysis Services OLAP Cube from any Excel spreadsheet cell.
With this capability, the new application enables users to modify data stored in a SQL Server 2008 R2 Analysis Services data source and publish the changes in real time. The application, called Mid-Year Review (MYR) gives Corporate Finance senior leaders instant access to real-time updated business metrics. Benefitting from the familiarity of Excel 2010, the application is highly flexible and enables IT managers and business users to easily apply changes if needed. Because of its Excel 2010 integration, most changes do not require IT involvement, which helps keep the total cost of ownership low.
Senior leaders within Corporate Finance can now rely on real-time data collected from the field to make well-informed decisions about the business.
For many senior leaders at Microsoft, it is essential to have fast and easy access to up-to-date business intelligence data. Armed with the right data, executives can more effectively create global sales and marketing strategies and measure the success of those strategies, so they can adjust their plans if necessary.
And because these leaders depend on the quality and accuracy of this data to make critical business decisions, the right technology and implementation plays a key role. The Microsoft Corporate Finance group's senior leaders, for example, rely on Microsoft Office Excel spreadsheets and SQL Server 2008 R2 Analysis Services OLAP Cube functionality for business process analysis and decision support.
Starting in 2003, executives and financial analysts in the Corporate Finance group used a business application created by the Microsoft IT Business Intelligence Engineering team called Rhythm of Business (ROB). This interactive Business Intelligence (BI) application provided progress reports for key business metrics.
In 2006, to enable better visualization of data and enhanced abilities to handle growing volumes of data, Microsoft IT migrated the ROB application to Office Excel 2007. In 2008, the application was redesigned as a Web-based user interface leveraging Excel Services and renamed the Executive Reporting UI Infrastructure. The migration to a Web-based interface had several positive impacts: it eliminated the need to install the application, improved the application's reach, and more than doubled the number of users. However, these benefits came at a price, as the developers had to sacrifice the data write-back capability enabled in the rich Excel client application.
Also at that time, the Business Intelligence Engineering team introduced a SQL Server 2008 Analysis Services metadata engine, which helped reduce the churn of implementing changes.
In spite of wide usage, there were still some major challenges facing senior leaders in the Microsoft Corporate Finance group. For example, the group conducts an annual World Wide Mid-Year Review process each January, during which senior leaders needed the Executive Reporting UI Infrastructure open for manually-entered data collection for a one-month period.
During this time, some critical business data was collected including, for example, the total count of favorable and unfavorable press articles regarding Microsoft in various countries, as well as other metrics such as estimates for long-term hardware sales, gaming market trends, or server and tools product-related articles. Such information is not tracked in any systems. Using this data, the group's senior leaders adjust their marketing strategies and reset their goals to cover the next three years.
However, the process of gathering some of this data was very complex and time-consuming, because the Executive Reporting UI infrastructure did not support manual data write-back. Instead, the Mid-Year-Review was conducted using multiple Microsoft Office Excel files, with users in the field keying in data in workbook cells. Around 20 percent of the metrics needed for making million-dollar business decisions were compiled using this offline method. That is because some of these important metrics are not tracked using proper data sources. Employees in the specific geographic regions where this data was collected had to manually enter that data.
These employees then sent Excel files to dozens of other people in the region, and asked them to send the files back with the data filled out. This process was very complex, with employees needing to manage many Excel files with pivot tables and OLAP functions.
Updated files would be sent to an internal data management team, who integrated them into the Corporate Finance systems. This process took a lot of time, and the data itself was prone to errors.
Additionally, because of the lack of a system that supported real-time data entry, this manually-collected data was not stored in a centralized location or available to all users worldwide the moment it was entered into the system. This is a common problem with many systems that do support manual data entry, such as Microsoft PerformancePoint Services. There is frequently a delay between data entry and the moment the data becomes available in the system's presentation layer.
In this case, there was severe lag time between the point at which data was entered in the Microsoft satellite office and the time it became accessible to the Corporate Finance decision-makers who needed it. So, if a senior leader in the Asia-Pacific region showing a presentation in Malaysia needed to validate the data in his or her report, he or she would have to contact an employee in Malaysia to use manual processes to generate files to be fed into the system. If the data was collected using Excel files, the senior leader would then have to wait for a lengthy period of time for the correct numbers to show up in the system.
Because senior leaders often need up-to-date, accurate data for decision-making on the first day of a particular month, real-time data availability was essential. But because Corporate Finance struggled with such complex, hard-to-manage processes, it requested that the Microsoft IT Business Intelligence Engineering develop a centrally-controlled, Excel template-based system that enabled OLAP write-back capabilities. Corporate Finance was especially interested in an Excel template-based system because that product is familiar to both the users and business team members that create and modify them.
In October 2009, the Microsoft IT Business Intelligence Engineering team created MYR—an upgraded version of the Executive Reporting UI Infrastructure solution that is based on the same methodology used in the PivotTable What-If Analysis (writeback) feature in Microsoft Office Excel 2010.
Now, data entered by authorized users is instantly aggregated and processed through business rules defined in the metadata engine, which is a combination of relational and OLAP databases. With writeback enabled, application users can enter data directly into the OLAP cube, making it instantly visible to other decision-makers who use the same SQL Server 2008 R2 Analysis Services database as a data source for their reports. As a result, business team members have the ability to apply complex business rules stored in the OLAP cube – some as long as 10,000 characters – to the data entered by other users located in various parts of the world.
And because data entry and writeback-enabled spreadsheets can be easily and quickly created by any employee already familiar with Office Excel 2010 and its functionality, there is no need to involve IT in change management processes.
The application eliminates the lag time between data entry and live access, making it ideal for multinational corporations like Microsoft, where the system's users are distributed across many countries around the globe.
The IT Business Intelligence Team also enabled automatic merging of the latest version of the Excel file template with the latest version of the VBA code necessary to run the application on the client. Because Office Excel does not contain built-in functionality for submitting data from individual manual cells, the Business Intelligence Engineering Team took advantage of the VBA code and Open XML file formats (available in MS Office products and introduced in the Office 2007 system) to enable that capability. (See article, "Using Excel Services and Open XML to Create Custom Office Business Applications" at http://technet.microsoft.com/library/cc974251.aspx ). The team created a process that automatically merges Office Open XML-based Excel files with digitally-signed VBA binary file parts using an ASP.NET application that runs on Microsoft Office SharePoint Server. The application code changes the workbook type from standard codeless Excel file to macro-enabled, and then inserts client side automation code into it.
With this capability, the Corporate Finance group can add or delete rows of data by altering that information in the "codeless" Excel template, and not have to worry about the program code. Now, if senior leaders need to collect information on metrics for new lines of business, such as the number of cloud subscribers worldwide, they simply need to inform the Corporate Finance data management team.
Using the solution's metadata tool, the team can create and modify new manual metrics in SQL Server 2008 R2 Analysis Services. These new metrics can be added inside the template designated for data entry. The addition or removal of metrics in the cells that support manual data entry and retrieval from the back end is a simple process of writing an Excel formula that does not require specialized coding skills or any other IT involvement.
Because the application uses data entry-enabled workbooks distributed from a centralized SharePoint site, users are guaranteed to get up-to-date content. After an updated template is published to the SharePoint site, new data cells designated for entering new manual metrics data can be updated by any authorized user in the field at that same moment.
The solution also features robust security enforced at the data source. For instance, read/write access can be specified for each single cell in the system by user, which means that submitting value updates to individual cells is only allowed for a certain number of authorized users, ensuring data reliability. (For more details about database security in SQL Server 2008 R2 Analysis Services, please see the article "Enabling and Securing Data Entry with Analysis Services Writeback"). With this new solution, senior leaders within Corporate Finance or other groups can quickly, easily access updated data at any level, for example by region, country, or at the "worldwide" or consolidated level, enabled by SQL Server 2008 R2 Analysis Services. All manual data entries are automatically summarized and processed through the set of complex business rules.
Ultimately, the solution is powerful, flexible, and inexpensive. "We used only two full-time developers to create this application," says Sergei Gundorov, Team Lead, Microsoft IT Business Intelligence Engineering team. "It was simple to develop, because it leverages existing Microsoft technology and takes advantage of built-in calculation engine and writeback functionality of SQL Server 2008 R2 Analysis Services."
In addition to the Corporate Finance Team, similar solutions built on the same code base as MYR are also being used by the Microsoft IT Engineering Scorecard team, the Microsoft Enterprise Services team, and the Microsoft Volume Licensing team. More internal customers will be on-boarded this year.
Makes Critical Business Data Available Instantly
The new solution simplified and stabilized the Corporate Finance World Wide Mid-Year Review process, and gave senior leaders the ability to access real-time business data instantly. The Microsoft IT Business Intelligence Engineering team enabled Microsoft Excel 2010 to become a decision-support system with real-time collaboration capabilities, providing updated data to worldwide users the moment it is entered into the system.
In many other systems, where data is manually entered from disparate locations, there is often lag time of anywhere from several minutes to possibly days. This application enables users from around the world to enter data, which gets integrated instantly into the system. Because the data is aggregated through a SQL Server 2008 R2 Analysis Services calculation engine, it is processed through the series of business rules automatically, so the end user does not have to perform any offline calculations/what-if analysis to see the impact of their manual input on other cell values that are dependent on it. Now, when professionals in Malaysia or Indonesia want to submit new or updated data, the new numbers are available to senior leaders around the world immediately. "Eliminating the lag time for data to get into the system and be presented to decision-makers was a huge win for us in creating this application," says Gundorov.
Guarantees Updated Content
The solution also guarantees updated, accurate financial data, because all users can enter data into workbooks distributed from a centrally located SharePoint site. Users gain access to the data template by visiting a SharePoint site, clicking on a button for the designated geographic location, and then updating or adding a new data value in the downloaded template. "If someone in the Corporate Finance senior leadership team wants to see more data cells open for manual entry, they simply need to ask the data management team to open a particular cell for data entry and publish it to the SharePoint site," says Gundorov. "Such a request can be handled very quickly. And after they publish that updated template to SharePoint, new data values can be entered or revised by any authorized user in the field, with appropriate permission."
Takes Advantage of Familiar Tools
Professionals using the application rely on familiar Microsoft tools, such as Microsoft Office Excel 2010, SQL Server 2008 R2 Analysis Services, and SharePoint Services 2010. Because they are accustomed to working within these tools, there is minimal need for training on how to use the system. Data entry and writeback-enabled spreadsheets can be easily and quickly created by any employee already familiar with Office Excel 2010 and its functionality. Similarly, workers who regularly use SharePoint to collaborate on documents and share information can take advantage of the new data entry-enabled workbooks distributed from the centralized SharePoint site. Authorized employees can also easily publish updated templates to that site.
Provides High Agility
IT teams and employees in the field can also benefit from the MYR application's high agility. With the ability to write data back to a SQL Server 2008 R2 Analysis Services OLAP Cube from an Excel cell, MYR application users can apply complex business rules to the data entered by users around the world. (See article, "Enabling Write-back to an OLAP Cube at Cell Level in Excel 2010" at http://msdn.microsoft.com/library/gg521158.aspx). Because the latest program code gets delivered to the user's computer automatically as part of the Excel workbook, IT managers can quickly and efficiently add more functionality to the application if desired. Overall, because the Microsoft IT Business Intelligence Engineering Team enabled these capabilities in MYR, the cycle of changing a business model or adding or removing new metrics is very short. This is critical because the window for data entry is open for such a short period of time.
The new MYR application transformed how the Microsoft Corporate Finance group conducted its World Wide Mid-Year Review process. Now, system users worldwide can quickly enter or modify data in any spreadsheet cell and submit their changes in real time. As a result, updated financial data or other critical business information is immediately accessible to decision makers that use the SQL Server 2008 R2 Analysis Services database. Additionally, because reporting from SQL Server 2008 R2 Analysis Services can be consumed by a variety of front-end technologies, business users can use Excel, PerformancePoint Services, SQL Server Reporting Services, or any other reporting application to present the data.
With these capabilities, senior leaders within Corporate Finance and other groups can use current data to sense the pulse of their organization. They can rely on updated analytical data to gauge whether or not adjustments to sales targets or forecast goals are necessary. They can also better focus on providing extra resources where they are needed or can share effective business practices with other parts of the organization. Ultimately, this application helps them confidently make better business decisions.
For More Information
For more information about Microsoft products or services, call the Microsoft Sales Information Center at (800) 426-9400. In Canada, call the Microsoft Canada Order Centre at (800) 933-4750. Outside the 50 United States and Canada, please contact your local Microsoft subsidiary. To access information via the World Wide Web, go to:
© 2011 Microsoft Corporation. All rights reserved.
Microsoft, Windows, and SQL Server, and Windows Server 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.
This document is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, IN THIS SUMMARY.