|
Customer Profile
|
Situation
|
Solution
|
Benefits
|
|---|
|
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
|
- Makes
Critical
Business Data
Available
Instantly
- Guarantees
Updated Content
- Takes
Advantage of
Familiar Tools
-
Provides High
Agility
|
Situation
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.
Solution
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.
Benefits
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.
Conclusion
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:
http://www.microsoft.com
http://www.microsoft.com/technet/itshowcase
© 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.