by Chip Wilson and Alan Josephson

Summary: The vision of Software + Services (S+S) is to create application architectures that maximally leverage edge resources—the resources of client devices—to provide end users with rich, intuitive experiences. This vision represents an incremental step toward ubiquitous computing, where technology will no longer intrude on the thought processes of people and force humans to think like machines in order to accomplish their tasks. Microsoft Office with its well-understood interface, rich service set, and established business user base, is a natural foundation for creating S+S business applications.

Although the resources of the client computing device must be leveraged to provide a rich user interface, they are not appropriate for executing business logic and processes critical to the enterprise. The system resources ideally suited to hosting mission-critical business processes and data stores are typically centralized in secure, managed environments under the watchful eyes of the IT department’s operations team. Service interfaces are the key to leveraging these core business capabilities from the myriad client devices that provide rich user interfaces.

Many client technologies and platforms make up the universe of edge computing, ranging from simple Web 1.0 browser interfaces to full-blown .NET applications running on desktops or laptops. Much has been made of recent announcements from Microsoft regarding Silverlight and Google touting Gears—new entries into the client computing space. Although they add exciting new capabilities to many platforms, there are more proven technologies that are better understood by the existing user base. A compelling case can be made for enabling these existing client platforms to be the “Software” in the Software + Services architectural pattern.

Virtually every knowledge worker in every enterprise is familiar with the same suite of productivity applications—Microsoft Office. The simple fact that so many people have learned how to use it makes it a natural foundation for creating business applications.

Recent releases have morphed Office into much more than a set of standalone productivity applications. Office is now a full-fledged application development platform that provides a rich set of services on which to build enterprise applications. Since so many knowledge workers already use these applications to implement mission-critical business processes, there is tremendous momentum behind an emerging class of solutions known as Office Business Applications (OBA).

Beginning with Microsoft Office 2003, it has been possible to build full-fledged applications on top of Office client applications. This capability goes far beyond what was possible with Visual Basic for Applications (VBA). Although mechanisms for integrating .NET assemblies into Office applications are well documented, the particular design patterns necessary to fully leverage and enhance the user interface provided are not well understood in the industry.

Once the techniques for marrying the Office UI to .NET assemblies are mastered (allowing the code to move information in and out of the documents managed by Office), connecting these documents to back-end systems via services is not only logical but also relatively simple, assuming that the work to expose the business logic as a service has already been done. This overcomes one of the biggest drawbacks to implementing significant business logic within Office—the side effect of creating islands of data, or so-called “Excel Hell.” Once the documents are connected to, and acting on, live data in systems of record, they become real-time tools for automating business processes.

Connecting these documents directly to enterprise systems has the additional benefit of enabling source information to be accessed via services, freeing the end user from the tedious chore of manually entering data from enterprise systems and reports into Office documents. Once the process steps automated by Office are completed, the resulting information can be stored back into enterprise systems, again via services, eliminating additional data entry tasks and ensuring the accuracy and timeliness of the data.

Contents

A Common Business Problem
Solution
Creating a Calculation Task
Executing a Calculation Task
Scheduling and Running a Calculation Job
Conclusion
References
About the Authors

A Common Business Problem

For businesses to thrive and keep their competitive edge, financial modeling and analyses must be done on real-time information without requiring users to access multiple data stores. OBAs expedite access to real-time information by eliminating manual processes for acquiring data and obtaining calculation definitions. These solutions enable Microsoft Excel users to perform complex analyses with data sourced directly from central data warehouses, Enterprise Resource Planning (ERP) applications, or external systems via the Web.

Some limitations of the disconnected Office model stem from the islands of data stored on individual hard drives and network shares. First, there is typically no audit trail for the data. This can be especially true when documents are e-mailed around and changes are made by multiple people, creating parallel versions of the document. Second, because the data is not aggregated, it is difficult or even impossible to perform any kind of trending analysis. Last, searching many documents for specific information can be extremely difficult and time consuming.

Real-world examples

To better understand the characteristics of the business problem, it is useful to examine some real-world examples. We present three scenarios where the architectural patterns were used to solve similar business problems in very different contexts and industries.

“Company A” provides employee benefit plans to small U.S. banks and credit unions that want to attract and retain the best executive talent possible. To distinguish itself from the competition, Company A delivers highly customized plans—requiring an intensive administrative process to ensure the highest level of customer service.

Providing that kind of customized service, however, creates challenges. Benefits such as life insurance plans are typically recorded as assets of the banks that are purchasing them for their employees. Therefore, the plans must be handled like other important financial assets that reflect the overall financial health of the institution and that can be affected by external variables such as changing interest rates. Company A must monitor the plans and financing tools continually and provide regular updates to its customers. To do that, they employ trained administrators who can closely manage policy and benefit plan details such as changes in beneficiaries, interest rate fluctuations, retirement calculations, and other factors.

Historically, these plan administrators worked with a number of systems and software, including Microsoft Access and SQL Server databases, Microsoft Office programs such as Excel, and an array of other document processes, including paper forms. (See Figure 1.)

Figure 1: How excel plays to the Software + Services vision (Click on the picture for a larger image)

“Company B” is a full-service real estate investment management and support services company, managing investments totaling more than $20 billion in North America, Asia, and Europe. The risk management team focuses on maximizing the potential of the company’s overall portfolio by continually monitoring its owned properties and financing instruments. The group maintains a comprehensive asset management database and relies heavily on Microsoft Excel to run analyses on its numerous and varied properties. The team wanted to be able to perform complex risk management analyses with data sourced directly from enterprise systems. The OBA solution enables risk management users to seamlessly retrieve information from the enterprise systems of record and pull it into a familiar, Excel-based application. Real-time data enables better decision-making by providing more accurate and immediate access to information. “Company C” is a commercial bank with a comprehensive Capacity Management initiative. The purpose of the initiative is to bring product management and sales together with bank operations and IT and, as a unified cross-functional team, identify new incremental revenue opportunities for the bank that take advantage of measured and available unused capacity. The cross-functional teams, organized according to a business line management structure, operate in a continuous business performance management cycle, where joint projections and performance targets are set and committed to each quarter, and then reviewed with the capacity management steering committee along with new projections for the subsequent quarter or cycle.

For each product, the capacity management team built Microsoft Excel models for the monthly calculations and generation of results for management information dashboards or scorecards. Connecting these models directly to the enterprise systems that contain the cost and production data required to perform the capacity calculations not only eliminated manual entry of the data into the spreadsheets, but allowed the analysts to perform the capacity analysis in a real-time fashion, providing feedback via the dashboards on an ongoing basis rather than monthly or quarterly.

Solution

Generalizing these three solutions yields the Software + Services architectural pattern, an OBA solution utilizing Excel as a client connected to distributed enterprise systems using Web services. Web services retrieve current information immediately into the user’s worksheet.  Authorized users can make and apply changes to the enterprise systems, ensuring accurate information is immediately available across the entire enterprise.

All charts, graphs, tables, and other reporting tools that reference the changed data are automatically updated to reflect the current, correct information. This solution enables users to seamlessly retrieve information from enterprise systems and pull it into a familiar, Excel-based application. Real-time information enables better decision-making by providing more accurate and immediate access to information. Administration and maintenance of the OBA application are simplified by deploying changes from a central document library.

Real-world benefits

Let’s look at how this general solution provided concrete business value in the three aforementioned scenarios.

Company A streamlined core business processes by better organizing and centralizing its documents. Web services technologies created flexibility in the company’s IT systems, helping managers add and modify plan features for administrators without straining internal resources. Most importantly, the company is continuing its steady annual growth with minimal increases in staff.

The time saved by the solution is equally apparent in other business processes, including those that directly affect Company A’s regular communications with banks. If a bank calls Company A and wants to run a “what-if” scenario on a policy—for example, the bank wants to examine the costs associated with accelerating an executive’s retirement date—it now takes two minutes or less because all data is aggregated and easily accessible. In the past, such a process could have taken up to three days because an administrator would have to search for the relevant spreadsheets and extract the appropriate data.

The analysts at Company B continue using the familiar interface of Microsoft Excel to model their real estate portfolio. Using Excel as a client in an OBA provided them with a rich and powerful environment for interacting with enterprise systems. It enhanced and simplified the analysis processes for the users, giving them the data they need in the way in which they are already accustomed to working.

Web services provide a seamless way to exchange large amounts of information over the network, simplifying how client applications generate requests to the enterprise systems and enabling developers to build intelligence into the application.

Company C leveraged Excel to provide a rich client interface for business analysts to build utilization models founded on the principles of activity-based costing. By enabling these models to pull live resource and cost data from back-end systems, Company C was able to automate the process of determining where unused capacity could generate additional revenue without affecting the fixed costs associated with the product, thereby increasing margins. By providing the calculated utilization information to enterprise systems, management dashboards could present not only the current and periodic capacity utilization information, but could perform trending analysis that helps management make decisions about where to focus future marketing and sales resources.

Technical solution

Having solved three different problems with the same architectural approach, a number of commonalities became apparent and several implemented frameworks are general enough to be used on future projects.

A versatile business logic framework allows workbook designers (skilled Excel users who understand a workbook’s data relationships and their presentation) to change the system’s business rules without bringing in a developer to recompile and redeploy the code. Role-based access to workbook metadata provides a special user interface for these designers, enabling them to perform such tasks as changing workbook behavior, altering menu items, controlling how data from enterprise systems is mapped through Web services to workbook fields, and hiding or showing worksheets or individual rows or columns based on data.

A security framework makes use of the Code Access Security feature of .NET. Using code access security reduces the likelihood that the application could be misused by malicious or error-filled code. Another benefit is its ability to enable system administrators to specify the resources that an application can use and restrict user access to specific workbooks or servers based on their role.

To address the issue of simultaneously maintaining multiple versions of the Excel workbook across a company, a general-purpose versioning framework was created that allows developers to update the assemblies associated with the documents and the data contained therein. Before the results can be submitted to the enterprise systems, the client component of the OBA determines whether a newer version of the document exists by making an inquiry through a Web service. If so, .NET assemblies are downloaded, which upgrade the workbook’s data, business logic, and user interface. If the workbook data satisfies its upgraded validation logic, the upload proceeds with the correctly versioned data.

To execute the business rules and data mappings specified in the business logic framework, an execution framework reads the business logic, stored as metadata, from the workbook. It then interprets and executes the instructions based on a simple, extensible instruction set. This interpreted approach lets developers easily add new instruction types as needed and provide them to non-developer workbook designers through user-friendly wizards.

Capacity Optimization Application

In order to better understand the solution architecture, we examine Company C’s capacity management solution in greater detail. The focus here will be on how a workbook developer leverages the business logic framework to connect workbooks to enterprise systems through Web services and “execute” the calculation tasks created for the workbook in both client and server scenarios. The general solution comprises four components (see Figure 2):

·         a Calculation Job Scheduler for configuring the time-based execution of calculation jobs.

·         a Calculation Processor Module for configuring, saving, and executing a calculation job (used in both client and server scenarios).

·         a Calculation Processor UI that implements the Calculation Processor Module’s user interface.

·         a Calculation Job Processor Service for running calculations on the server using Excel Services invoked from a Windows service.

Figure 2: Business logic framework components (Click on the picture for a larger image)

Creating a Calculation Task

The Calculation Processor Module is responsible for modeling and sequentially executing the steps that comprise a calculation task. A Calculation Task is a named execution unit consisting of a simple set of Calculation Steps that describe how to:

·         invoke Web service methods to move data into and out of the workbook

·         move data within the workbook itself

·         wire up Excel’s UI events to react to user interactions (only executed when the workbook is opened on the client).

In addition, a calculation task also has a set of user-supplied inputs to kick off task execution.

A workbook designer uses the Connection Wizard to add specific Web service method invocations to a calculation task (see Figure 3). Upon specifying the Uniform Resource Identifier (URI) to a Web service’s WSDL (Web Service Definition Language), its methods and their signatures are retrieved and presented to the workbook designer who then adds a method of interest to the calculation task as a calculation step.

Figure 3: Creating a calculation step from a Web service method (Click on the picture for a larger image)

Once added, the calculation processor dynamically generates an XSD (XML Schema Definition) corresponding to the Web service method’s inputs and outputs and adds it to the workbook as an Excel XML map. The workbook designer then maps individual elements of the XML map to worksheets using drag and drop from the XML Source Task Pane (see Figure 4). These will either be scalar values mapped to individual cells or collections of repeating elements mapped to Excel 2007 tables, formerly known in Excel 2003 as Excel lists. While XML maps preserve the hierarchical structure of Web service proxy objects, they become de-normalized when mapping to Excel tables on worksheets.

Figure 4: Mapping a generated Web service method’s schema (Click on the picture for a larger image)

When service-enabling existing workbooks, it is frequently desirable to preserve the presentation of data in the workbook. Many times, similar data is not presented in the tabular form dictated by the mapping of repeating elements in XML maps. In addition, a limitation of Excel’s XML maps is that their cell mappings cannot overlap those of other XML maps. In these cases, it is sometimes necessary to move data from one range of worksheet cells to another to get the service inputs and outputs to “line up.” A Copy Data calculation step type lets the workbook designer specify how to duplicate data to other locations in a workbook.

In scenarios where Excel is used on the client to edit and update data, sometimes business logic on retrieved data dictates dynamic changes to the Excel user interface. One such workbook data manipulation step might, for example, hide and show data on worksheets based on specific data values. A Modify UI step type lets the workbook designer wire up such interactions.

In addition to creating calculation steps, the workbook designer also specifies well-typed inputs for setting up a calculation task using a Calculation Task Input Wizard to specify input names and their data types. The calculation processor dynamically generates an XSD corresponding to the inputs, which is then added to the workbook as an Excel XML map and mapped to worksheet cells, as previously shown in Figure 4. When the calculation task is run in the Excel client, the user is presented with a generated form prompting for input values (see Figure 5). When run by the calculation job processor on the server, input values that were specified using the calculation job scheduler (stored in the calculation job data store) are used.

Figure 5: Running a calculation task (Click on the picture for a larger image)

Executing a Calculation Task

The Execution Engine is the heart of the calculation processor module. It is responsible for interpreting the Web service method invocation and copy data steps and executing them in the order specified by the workbook designer in a calculation task. When Excel is run on a client, the execution engine directly accesses the Excel object model, using the XML maps to bind data to/from worksheets. When run on the server, it uses Excel Services to perform the data binding, calculation, and retrieval.

Current limitations to Excel Services prohibit workbooks with XML maps from being opened on the server. To get around this, the calculation processor module extracts the binding information stored in the XML maps from a workbook, removes the XML maps, and presents a “clean” copy to Excel Services. Web service method invocation steps use this binding information to determine how to set and get cell ranges within the workbook through the Excel Services API. This approach is akin to a “push” model where data is fed to the document using the explicit control structure specified in the calculation task. An alternate approach that leverages Excel’s User Defined Functions (UDFs) to wrap Web service calls is possible but has several limitations, including:

·         reliance on the spreadsheet’s dependency graph for the sequencing of service method invocations (no explicit control)

·         only a predetermined amount of data can be returned from UDFs since the array return value types must be bound to fixed Excel ranges

·         additional maintenance is required to deploy and trust UDFs under Excel Services.

The Persistence Manager abstracts storage of the calculation processor metadata. The serialized metadata is stored as a Custom XML Part (new to Excel 2007) within the Office Open XML package that represents the workbook. In this way, all information about the calculation job is stored within the workbook itself, available to the calculation job processor service.

The Calculation Client’s user interface can be implemented either as an Excel add-in or as an Excel customization that adds menu items to the Excel user interface for accessing a set of Windows forms. These forms extend the Excel user interface through a set of wizards so that a non-developer workbook designer can configure, save, expose a calculation task for execution, directly execute it, or step through its calculation steps. A different class of user, unable to modify the calculation, might be granted restricted access to only the execution interface.

Scheduling and Running a Calculation Job

The Calculation Job Scheduler is an application through which an administrative user configures a workbook and its calculation job for timed execution (see Figure 6). The information supplied is:

·         a path to the workbook

·         identification of which calculation task from the workbook is to be run

·         its schedule for execution (recurring or a single execution date)

·         initial inputs to the calculation job as determined by the calculation task’s metadata

·         a path to the folder where audit snapshots of the workbook are to be stored.

Figure 6: Scheduling a calculation job (Click on the picture for a larger image)

The Calculation Job Processor Service runs as a Windows service and invokes the calculation processor module when a calculation job’s scheduling criteria are satisfied. Initial inputs that were supplied when the job was scheduled are passed to the calculation and the workbook’s calculation job is executed using Excel Services. Optionally, a snapshot of the workbook (after the calculation task has been completed) can be saved to a specified location to provide an audit trail of the calculation job.

Conclusion

This architectural approach to solving an extremely common business problem has been utilized multiple times now. It has proved to be robust and reliable, providing tremendous business value. In one case, this solution completely revolutionized the business model of the company by allowing it to offer services that no competitor could even approach.

The Software + Services vision of rich user interfaces on client devices invoking services in the cloud is one well-suited to the Microsoft Office system, especially since the user interface provided by Office is so ubiquitous and well understood.

References

·         Denise Partlow, EMC Global Services (formerly Geniant, LLC). “Improving Data Integrity in Financial Analyses.” April, 2006.

·         Denise Partlow, EMC Global Services (formerly Geniant, LLC). “Real Estate Firm Simplifies Risk Analyses with Web Services and Excel Smart Clients.” April, 2006.

·         Microsoft Office Business Applications http://office.microsoft.com/en-us/products/FX102204261033.aspx

About the Authors

Chip Wilson is an Enterprise Architect with EMC Global Services. His recently published book, Transparent IT: Building Blocks for an Agile Enterprise (www.TransparentIT.com), describes a detailed framework and roadmap for adopting a service-oriented architecture and creating an agile enterprise.

Dr. Alan Josephson is a Senior Practice Consultant with EMC Global Services, specializing in Microsoft Office system development. He has created custom Smart Client and Office Automation solutions for clients in the financial, insurance, and energy sectors.

This article was published in the Architecture Journal, a print and online publication produced by Microsoft. For more articles from this publication, please visit the Architecture Journal Web site.