Office Development

OBA Solution Patterns In The Real World

Steve Fox

This article discusses:

  • OBA basics
  • OBA patterns
  • Microsoft Office and LOB integration
  • Real-world applications of OBA patterns
This article uses the following technologies:
Visual Studio 2008

Contents

OBA Patterns
Using the OBA Solution Patterns
The Problem
OBA Applications as a Reach Channel
Getting Down to Design
Other Uses

Large-scale systems such as SAP and PeopleSoft, and other comprehensive line of business (LOB) systems, are critical for the successful management of all types of business data and processes. However, not everyone in an organization has access to these systems, so the business data in them is often available to only a select few. This often results in data being extracted out of the system for processing, creating a disconnect between the business data source and the information workers who consume that data.

Office Business Applications (OBAs) address this problem by using Microsoft® Office to bridge the divide between business data in LOB systems and the information worker. Office features allow you to perform tasks such as integrating customer relationship management (CRM) data into Microsoft Outlook® through custom form regions and folders, integrating business intelligence into Microsoft Office SharePoint® Server (MOSS) to provide views into Sales performance data, and even integrating Microsoft Excel® with finance data to provide forecast templates that use data directly from LOB systems, to name a few.

To understand an OBA, you can think of it as a very simple model that includes three major parts: the LOB system, the customized Office client that integrates with the LOB system, and the server component (essentially MOSS) that also integrates with the LOB system. Note that when building OBAs you can also leverage other Microsoft server products such as Exchange Server 2007, PerformancePointTM Server 2007, and so on.

From an architectural perspective, you'll predominantly use a service-oriented architecture (SOA) to integrate with LOB systems, but with an OBA, you can customize the client in a number of ways as the interface to that services layer. More than likely, you'll use Visual Studio® Tools for Office (VSTO) to do this. For example, Figure 1 illustrates an interface with a custom ribbon and a custom task pane. Both interface with a services layer that populates the Excel 2007 spreadsheet with event information from SAP, and both are built using VSTO 3.0—the latest release of VSTO included in Visual Studio 2008 Professional and above. You can also design your interface to use Microsoft Word content controls and bind those controls to data, or extend Outlook with a custom toolbar, form regions, or custom ribbons. For more information on how you can customize the client, check out the "OBA Information Resources" sidebar.

Figure 1 Integrating SAP and Excel 2007

Figure 1** Integrating SAP and Excel 2007 **(Click the image for a larger view)

With MOSS 2007, you have a number of options that are open to you. For example, you can build a site that hosts different types of Web Parts. Popular Web Parts for OBAs include Business Data Catalog (BDC) Web Parts, (which manage enterprise-wide entities and their related services or ADO.NET connections), Excel Services Web Parts (which link spreadsheet data to a Web Part), and Key Performance Indicator (KPI) Web Parts (which provide performance metrics). There are of course many others that you can use in the creation of your Office Business Applications. Figure 2 illustrates a BDC Web Part and an Excel Services Web Part in a SharePoint site.

Figure 2 BDC and Excel Services Web Parts

Figure 2** BDC and Excel Services Web Parts **(Click the image for a larger view)

While the possibilities seem endless, there is help out there to get you started. This help is provided in a core set of seven OBA solution patterns. Next I'll introduce them to you and then actually apply one to the design and development of an OBA.

OBA Patterns

The goal of OBA patterns is to show how you can architect and develop an OBA, show what technologies you would use within a particular pattern, and illustrate how you might go about developing your OBA. Some of the technologies involved include OpenXML, MOSS 2007, Web services, and VSTO 3.0.

Of the seven core patterns, some have sub-patterns. And because OBAs are composite applications that leverage multiple parts of the Office platform, you'll find that there are relationships that span patterns. The seven patterns, along with the goals of each, are listed in Figure 3.

Figure 3 OBA Patterns

Pattern Description
OBA Applications as a Reach Channel Shows how to extend LOB application functionality to a broader user base.
Document Integration Provides guidance on how to generate Office documents from LOB applications and embed LOB data in Office documents.
Composite User Interface Describes the composition of multiple application user interfaces in an Office document or SharePoint Web page.
Complementary Document Workflow Shows how to control and monitor document-centric processes through the use of workflow.
Discovery Navigation Provides a more natural way of interacting with LOB system business data to show how you can discover data by searching across multiple LOB applications.
Collaborative Site Provides guidance on how to augment structured business processes with unstructured human collaboration.
Application-Generated Tasks and Notifications Shows how to use Outlook as a primary user interface to receive and act on LOB application-generated tasks and alerts.

Each one of the patterns represents a single pattern or is composed of sub-patterns that provide more detailed guidance. For example, OBA Application as a Reach Channel is composed of two sub-patterns (Direct Integration and Mediated Integration), which provide specific guidance around an SOA versus a non-SOA solution. I'll discuss the OBA Application as a Reach Channel pattern in more detail later.

What's also important about the patterns is that they provide some guidance on the technologies within the Office platform that you would use to develop your OBA. So, for example, the Document Integration pattern and its sub-patterns suggest OpenXML, VSTO, the BDC, or, more generally, document markup. Figure 4 provides an overview of the sub-patterns, if any, that make up the seven core OBA solution patterns, along with the technologies that you would need when using that particular OBA pattern to design your OBA solution.

Figure 4 OBA Solution Patterns, Sub-Patterns, and Associated Technologies

Patterns Sub-Patterns Technologies Used
OBA Applications as a Reach Channel Direct Integration—custom components that deliver business data in a custom manner in the composite UI without using services. Mediated Integration—middle layer, abstracts the communication between both ends; more loosely coupled, declarative, and service-oriented. VSTO MOSS 2007 BDC
Document Integration Application-Generated Document—generated from LOB system (using server-side batch process). Can also use Direct or Mediated Integration. Intelligent Documents—LOB system data embedded directly in document (content controls, named ranges). OpenXML VSTO BDC Document Markup
Composite User Interface Context-Driven Composite User Interface—custom UI components tied to events and application context (specific data or user profiles). Mesh Composite View—connectable Web Parts to create master/detail composite user interfaces (BI views in SharePoint). RSS and Web Services Composition—mesh composite view for RSS and Web services. Analytics—mesh composite view for data analysis dashboard. Web Parts VSTO (Ribbon, Custom Task Panes, and so on) BDC Excel Services and Dashboards
Complementary Document Workflow LOB-Initiated Document Workflow—documents mapped to workflow are transformed, enhanced, or populated with business data. Additional workflow functionality is embedded in the documents. Cooperating Document Workflow—workflow is part of the document's container, so many people are involved and need to collaborate in a structured way with the document. Windows Workflow Foundation SharePoint Storage BDC
Discovery Navigation N/A Enterprise Search BDC
Collaborative Site N/A Windows SharePoint Services InfoPath® Forms Services
Application-Generated Tasks and Notifications Simple Task and Notification Delivery—one-way flow of a task and assignment to someone through e-mail notification. Task Synchronization—two-way synchronization of tasks in Outlook. Intelligent Tasks and Notifications—describes how to take actions based on the assigned task. Form-Based Tasks and Notifications—describes how to integrate InfoPath forms to provide richer validation and automation of business rules. Outlook 2007 and MOSS Integration VSTO (Custom Task Panes, Outlook Form Regions, and so on) InfoPath Forms Services

You can use the patterns as a part of your consumption of other OBA content to get a feel for what constitutes an OBA and the technologies you would use to build one, or use the patterns in the design of your own OBA. There also are a number of Reference Architecture Packs available. These are OBA architecture (and application) guidance packs for specific industries—for example manufacturing or banking. For more information, see the "OBA Information Resources" sidebar.

Now that we've covered some of the OBA pattern essentials, let's apply the OBA solution patterns to real-world software design.

Using the OBA Solution Patterns

To begin, let's first look at where in a software development lifecycle (SDLC) process you would actually consult with the OBA solution patterns. I begin by identifying a problem. If you don't have a problem (or a need), I'd question why you're developing the software in the first place. Once you identify the problem, you're next going to identify the requirements that map back to that problem. These two steps are the most important part of the process. If you don't get them right, problems will cascade throughout the SDLC. Once you've nailed down the requirements, you can begin to design.

In the design phase, you're going to ask yourself a number of questions. For example: do the requirements specify a particular environment (thus implying design constraints)? Is certain software (either off the shelf or custom) required to make this work? What hardware is needed? Figure 5 illustrates how you might proceed in the design phase to incorporate the OBA patterns.

Figure 5 OBA Patterns in the Design Phase

Figure 5** OBA Patterns in the Design Phase **(Click the image for a larger view)

With a set of requirements in hand and an understanding of an OBA, you'd next take a look at the OBA patterns to determine if one pattern fits with your requirements and, if so, which one. Further, you would want to dig into a specific pattern to see if a sub-pattern maps to your requirements. Assuming you do find a mapping between one of the patterns and your requirements, you could use the pattern as a template for design.

The Problem

As I've discussed, software development typically starts out with some problem. For our example, the problem is that a sales management team needs to view up-to-the-minute sales data. To tackle this problem, we're going to need to know a little bit more about the team, the environment, and some baseline requirements.

Here's what we know about this particular environment: the sales management team has 100 members, all of whom are all running Windows Vista®, the 2007 Office system, SharePoint, and a recent version of Internet Explorer®. The members are mostly remote, but they all can access a central SharePoint site or server over a corporate network.

The requirements, which are simplified for illustrative purposes, are as follows: because they are not consistently connected to the corporate network, the sales team records their sales data in an Excel spreadsheet throughout the day, and then manually reenters the data into an LOB system when connected to the corporate network. Additionally, there are specific steps in this process such as data validation which are also performed manually. The team wants a system that can support the intermittently connected sales team and can automate many of the manual processes, such as validation. (The sales data entry and validation process takes an average of 30 minutes each day. The team wants to reduce this time to 5 minutes.) Because the sales team is already comfortable using tools like Excel and Internet Explorer, the management team wants an Excel-based solution and a SharePoint view so that the members can build reports on their team management site.

Looking at the problem definition, you can quickly see that what the sales management needs is a way to extend their LOB applications used for managing sales data directly to their remote sales force. Comparing this definition to the list of OBA solution patterns, we find a good match in the OBA Applications as a Reach Channel pattern.

OBA Applications as a Reach Channel

The goal of this pattern is to show you how you can extend LOB system functionality to a broader user base using an Office application as the vehicle. The pattern implementation will give more people access to the LOB system's business data. Following this pattern, you can integrate with the LOB system using Outlook or Excel as a rich client, for example, or a SharePoint Web Part as a server-side thin client. In this case, the pattern is appropriate because you're rolling out an OBA to a small organization, so Excel and SharePoint will provide two good ways of interfacing with the sales data. In fact, ideally you'd like to have a solution that provides both client-side and thin-client access to the LOB system. As such, our Sales Data OBA looks like what's shown in Figure 6.

Figure 6 Client-Side Access and Thin-Client Access to the LOB System

Figure 6** Client-Side Access and Thin-Client Access to the LOB System **
(Click the image for a larger view)

Figure 6a Client-Side Access and Thin-Client Access to the LOB System

Figure 6a** Client-Side Access and Thin-Client Access to the LOB System **
(Click the image for a larger view)

Figure 6b Client-Side Access and Thin-Client Access to the LOB System

Figure 6b** Client-Side Access and Thin-Client Access to the LOB System **
(Click the image for a larger view)

Through a closer review you notice that there are two sub-patterns to the Applications as a Reach Channel: Direct Integration and Mediated Integration. So let's take a closer look at each of these.

With Direct Integration, access to the underlying LOB system is integrated directly into an Office client or SharePoint Web Part. Using this pattern, you would typically build connections directly into the back-end process or data source and should have minimal additional business logic.

While Direct Integration is common, it is difficult to reuse across multiple systems. Mediated Integration requires creating a services layer that mediates integration between the LOB system and the custom client components (for example, the custom task pane), thus facilitating reuse across multiple systems and loose coupling between the client and server. For the Sales Data OBA, this is good news. We can now create a Web service that wraps around the sales data module (where all of the sales data is entered) and integrate that Web service with all client interfaces. So if you want to integrate the LOB system with Excel, you can. You can then use this service through a reference you create within Visual Studio 2008 or through the BDC within SharePoint.

Now you're getting somewhere: you've nailed down the problem statement and requirements and are ready to apply an OBA pattern. The resulting architecture leverages the Mediated Integration pattern; that is, an OBA that leverages Web services to provide sales functionality in Excel and SharePoint. Notice that things are slightly different in Figure 7 (as compared to Figure 6). That's because the BDC only operates as a read-only vehicle for data visualization, whereas you can issue read/write calls from the client-side customization. This must be reflected in the architectural diagram.

Figure 7 Sales Data OBA Architecture

Figure 7** Sales Data OBA Architecture **(Click the image for a larger view)

Now the architecture includes an Excel 2007 add-in, a MOSS 2007 site, and a Web service which acts as a mediated connection back to the LOB system. Further, you now have an understanding of the technologies involved, so you can write the more detailed specification and build the development environment (using the technologies involved in the OBA solution pattern).

You might assume that at this point you can put the OBA solution patterns aside. However, you might want to compare the pattern you chose against other possible patterns within the core seven solution patterns. This will validate your architecture and might spark some ideas for a version 2.0 of your solution that might include more of the servers, tools, and services of the Office development platform.

One final step is to consider what it would take to build each of the components in the architecture. I'll provide a high-level overview along with some additional links where you can drill down on each of the technologies I've discussed.

Getting Down to Design

OBA Information Resources

OBA development is a fairly broad area, so I've compiled a few recommended readings:
6 Microsoft Office Business Applications for Office SharePoint Server 2007
microsoft.com/mspress/books/9471.aspx
Programming Microsoft Office Business Applications
microsoft.com/mspress/books/12194.aspx
Getting Started with Office Business Applications
msdn2.microsoft.com/bb614541
OBA Central
obacentral.com
OBA Reference Architecture Packs
msdn2.microsoft.com/bb265266
VSTO Developer Portal
msdn2.microsoft.com/aa905533
Office Developer Portal
msdn2.microsoft.com/office
SharePoint Developer Portal
msdn2.microsoft.com/sharepoint
Creating a Database Connection by Using the Business Data Catalog Definition Editor
msdn2.microsoft.com/bb736296
Creating a Web Service Connection by Using the Business Data Catalog Definition Editor
msdn2.microsoft.com/bb737887
Integrating LOB Systems with the Microsoft Office System
msdn2.microsoft.com/bb896607

With your architecture in hand, you'll want to understand how you go about building this OBA. You know you'll need to have a MOSS site setup (or have admin privileges on the existing site so you can develop against it). If you choose to set up a new site, you'll need to install MOSS (and all of the prerequisites) on Windows Server® 2003 (or Windows Server 2008). You'll also need to make sure you have Visual Studio 2008 Professional Edition installed, along with the 2007 Office system Professional Edition and the Office Primary Interop Assemblies (PIAs)—which is a setup option in Visual Studio 2008. You'll also need access to your LOB system because you'll need to develop and test a Web service against an instance of it.

It will take at least a day to set up this environment. I would proceed by setting up the server operating system, setting up MOSS on the server, installing Office on the server, and then installing Visual Studio 2008 (and the PIAs) on the local machine. At this point, you'll be able to test access to any services that you create (or that exist already).

Once your environment is set up, you'll need to create the Web service. For the Sales Data OBA, you will likely want to create at least two Web services: one that gets sales data (which is displayed in the Excel client add-in and the MOSS site) and one that updates the data from the Excel client add-in. While the LOB system is generic, you could use the Web service tools to create a service that wraps more domain-specific functionality around, for example, an existing business module—using SAP speak.

Different LOB systems will obviously work in different ways, but the goal should be to end up with a service that you can consume within the BDC and within your VSTO add-in. To get help here from a related area (that is SAP and Office integration), you can review the OBA Starter Kit for SAP, which provides help on how to create Web services for SAP (msdn2.microsoft.com/bb498189).

Now that you've created the Web service, you can create the VSTO add-in. To do so, use Visual Studio 2008 to create either a VSTO application-level add-in or document-level solution. Each will consume a Web service in a similar way. You'll likely create a custom task pane in your add-in to manage the sales data (a filter to control which data is shown) and a custom ribbon to manage views on that data (to create a chart, exercise functions against the data, and so forth). There is a lot of great information on how to do this on the VSTO Developer Center at msdn2.microsoft.com/aa905533.

The next step would be to add the Web service to the MOSS site. This would mean creating an application definition file (ADF) for the Web service (which means either hand coding an XML file or using a tool like the BDC Definition Editor or BDC MetaMan to create it), importing the ADF into SharePoint, creating a BDC Web Part, and then tying the Web Part to the ADF to complete the integration between the BDC Web Part and the LOB system. For more information on how to do this, see msdn2.microsoft.com/bb737887.

To deploy this solution means having both a client-side deployment mechanism and the MOSS site deployed where the sales team has access to the BDC Web Parts and Web services. You can publish the VSTO client-side assembly to the SharePoint site, and the sales management team can then download the assemblies to their local clients, which will check for updates on the server via a deployment manifest each time the add-in is invoked.

Deploying the BDC Web Part requires that you have an existing MOSS site set up and configured for use by the team because the deployment process for the Web Part involves creating a Web Part on the existing site. For more information on deployment, see both the VSTO Developer Center (msdn2.microsoft.com/aa905533) and the SharePoint Developer Center (msdn2.microsoft.com/sharepoint).

Other Uses

While I walked you through how I might apply OBA patterns, you might want to apply them in a different way to suit your own purposes. For example, you might use them solely to understand existing and suggested patterns as guidance for your architectural design and never need them again, or you might use them in your design as an architectural template for your OBA solution, or you could use them repeatedly as a way to provide structure to your OBA solutions—and subsequently make them a prerequisite for your specification process. As we move forward, you'll likely see additional guidance on OBA development, which will probably include, among other things, enhancements to the existing patterns. Be on the lookout for it!

Steve Fox is a Program Manager with the Visual Studio Tools for Office (VSTO) team at Microsoft. He divides his time working externally with VSTO customers, providing VSTO training, delivering VSTO sessions at conferences, and working internally with the development team to help evolve the VSTO product.