Getting Started with Office Business Applications (Part 2 of 2)
This content is outdated and is no longer being maintained. It is provided as a courtesy for individuals who are still using these technologies. This page may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist.
This article is an excerpt from 6 Microsoft® Office Business Applications for Office SharePoint® Server 2007, from Microsoft Press (ISBN 9780735622760, copyright Microsoft Press 2007, all rights reserved).
The members of the Microsoft Office SharePoint Server and Windows SharePoint Services teams help design, develop, and support SharePoint Products and Technologies.
Microsoft Most Valuable Professionals (MVPs) are exceptional technical community leaders from around the world who are awarded for voluntarily sharing their high-quality, real-world expertise in offline and online technical communities. Microsoft MVPs are a highly select group of experts that represents the technical community's best and brightest, and they share a deep commitment to community and a willingness to help others.
No part of this chapter may be reproduced, stored in a retrieval system, or transmitted in any form or by any means—electronic, electrostatic, mechanical, photocopying, recording, or otherwise—without the prior written permission of the publisher, except in the case of brief quotations embodied in critical articles or reviews.
In this section, we'll describe the breadth of approaches that can be used to extend and integrate LOB systems and Microsoft Office. We call these approaches OBA application patterns. These application patterns in many cases are based on real-world implementations, and in other cases they are built on features and capabilities of Office 2007 that have a generalized solution rooted in input from customers and partners. Multiple patterns can and typically are combined in a single OBA, and more sophisticated patterns emerge within scenarios involving specific applications and industries.
Customers and ISVs can often implement these application patterns in Office 2003, although for just about every case, Microsoft Office 2007 and the latest release of Visual Studio Tools for Office makes implementing the application patterns simpler and offers richer possibilities.
The objective of this pattern is to extend LOB application functionality to a broader user base using Office applications as the channel. A complementary object in many cases is to eliminate a duplication of effort An example of extending LOB functionality to a broader set of end users is in an employee self-service scenario such as updating employee personal information. An example of reducing duplication of effort is a feature with which consultants can classify time for meetings entered in Outlook to track their time against billable projects. This pattern can be useful in situations where system adoption is spotty because users see low value in the system and are annoyed by the duplication of effort; e-mail messages containing requests for updates are submitted to a few people who in turn re-key the information into an LOB system; or LOB functionality that goes unused because training and maintenance efforts required to extend the LOB application interfaces to every user are not sufficiently justified.
A developer can pursue the integration of Office applications and an LOB system directly or through a mediated approach.
Direct Integration Pattern
In the direct integration pattern, access to LOB interfaces is projected directly into an Office client or is extended to an existing feature, such as in the calendaring example above. The back-end process remains unchanged and is minimally augmented by additional logic. Integration using custom developed Web Parts that directly access LOB systems and present functionality and services without a mediator also falls into this category. A good example is a solution that presents a Business Service Page (BSP) from an SAP system as a Web Part on a SharePoint site. (See Figure 1-7.)
Mediated Integration Pattern
While direct integration is a great approach for realizing value quickly and potentially making us of the frameworks organizations have built around service-oriented architectures, this approach requires coding and doesn't facilitate the discovery and re-use, or construction or re-use of a composite solution across systems.
A metadata driven approach to systems, however, enables loose coupling and reuse to be achieved more easily, as well as post-development ad-hoc composition using more advanced frameworks. Microsoft pioneered this approach with the Information Bridge Framework (IBF). While Microsoft is no longer investing in IBF, in the 2007 Office system the BDC performs similar functions for managing entities and their related services across Web services and data sources (ADO.NET) for Office SharePoint Server solutions.
The mediated integration variant is a pattern that incorporates metadata stores like the BDC (see Figure 1-8) to provide an additional level of abstraction over direct integration. This allows read-only views to be presented in SharePoint without any development and for the creation of compositions using SharePoint Web Parts. The BDC can be extended with custom code to support more sophisticated write-back operations in order to promote decoupling and re-use of the controls. In addition to mediating the Web service interfaces, the BDC framework provides common approaches to services, including security, with a single sign-on mechanism based on a credentials mapping.
Today a significant amount of information within an enterprise resides in documents. By one independent estimate, structured LOB storage accounts for only thirty percent of the information in the enterprise. The rest is stored in documents on user desktops, often replicating (through manual means) information contained in LOB systems. The Office 2007 system and Open XML document formats provides ways to close this gap and create documents that contain LOB related data that can be processed. This set of application patterns describes incremental approaches along this path. Documents generated from business data stored in an LOB system can be simple documents like campaign letter or a table exported to Excel or they can be complex, like a proposal, contract, or budget spreadsheet.
This is the most common pattern for integrating Office and LOB systems. In this pattern, outlined in Figure 1-9, the LOB system merges business data with an Office document. This pattern commonly occurs in batch-oriented server-side processing, although client-side generation is also feasible.
Prior to the 2007 Office system, this pattern has been a challenge to scale because an Office client was generally required to perform sophisticated document generation. With 2007 Office system and the Open XML file formats, document generation becomes simpler, far more scalable, and richer.
Open XML is an Ecma standard and more information and samples on Open XML can be found on community site http://OpenXmlDeveloper.org.
Many LOB processes generate business documents that are used to exchange information with customers and partners, and information workers often create documents based on information in an LOB system. In many cases, Office documents facilitate a more intuitive user experience and provide richer functionality than a native LOB application for visualizing, analyzing, and manipulating LOB data. Some of the examples of such document-centric business processes are proposal generation, contract approval, budgeting, and forecasting. The data in these documents is dynamic and transactional. That is, structured content persisted within the document can be used to update an LOB system, call an LOB service, or start a workflow in the LOB system. A snapshot of the LOB information moves with the document, turning the document into a container of information that can be acted on by client- or server-side processing logic. As capabilities such as search become more intelligent, they will be able to parse embedded structured information intelligently and provide information workers with richer options. Document-centric processes are perfect candidates for building an OBA using the Intelligent Document patterns.
Embedded LOB Information Pattern
In this pattern, LOB data is embedded in Office documents created in applications such as Word or Excel. (See Figure 1-9.) A custom task pane (CTP) can provide a user interface to LOB data by using either the Direct Integration pattern or the Mediated Integration pattern described previously. With the Open XML file format, the ability to embed structured information and process the document has never been easier, through the use of standard XML capabilities supported in all modern frameworks. An information worker can browse or search LOB data and merge (embed) it into a document. For example, a user could browse and search a product catalog from an LOB system using controls in a CTP and merge the product information into a sales quote document. The LOB data is embedded in the body of the document, or it can be embedded as an XML document part. In Word 2007 documents, the data in an XML document part can be presented within the document by binding it to Word Content Controls. This provides abstraction between data and presentation and hence is a better approach than embedding data directly in the body of the document.
Embedded LOB Template Pattern
A more sophisticated approach, when incorporating the layout of LOB data within a document would be beneficial, is to create document templates, as shown in Figure 1-10. A template combines metadata from an LOB system with document markup (content controls, XML schema, bookmarks, and named rages) that is bound to specific instances of LOB information at a later stage. The creation of such a template uses the aspects of the embedded LOB information pattern using a CTP, although in this case the add-in provides metadata information rather than instance information from the LOB system. The metadata is used to mark up the document schema to describe the embedding of LOB content within the document. This technique enables end-users without the involvement of developers to create sophisticated document templates that can be processed automatically.
The markup, along with the Office Open XML file format, is subsequently merged with an instance of the LOB data to create a document. The merging can take place through an add-in within one of the Office clients to provide an interactive user experience and enable offline capabilities, as shown in Figure 1-11.
The template is also ideal for server-side data merging and batch processing of multiple or complex documents, as shown in Figure 1-12.
LOB Information Recognizer Pattern
In this pattern, a fragment of document content is recognized as semantically meaningful information within the context of an LOB application. The recognition can be enabled through metadata and document markup (content controls, XML schema, bookmarks, named ranges, etc.) or by using Smart Tag technology provided by Office applications. Once such content is recognized, interesting actions can be taken on that information. In a server-side scenario, recognized information can be extracted and used to update the LOB data or start a workflow. In the client-side scenario, recognized information can be used to present a context-sensitive user interface using a CTP or the Ribbon. For example, in a proposal document, a customer name is recognized because it is bound to a "Customer Name" content control. The CTP then shows customer detail information and order history from an LOB application. Another example is recognition of a product SKU as a Smart Tag using regular expressions. The user can view product details and drawings by selecting appropriate smart tag menu options.
Businesses often augment LOB processes with document exchanges outside the LOB system. Examples include sales pipeline forecasting, budgeting, proposal generation, and incident management. These exchanges are often ad-hoc and take place through e-mail. With complementary document workflows, enterprises can better control and monitor the document-related processes that need to occur and ensure that the processes are audited and meet compliance requirements. For cases with potentially significant variation in how information workers handle different types of information, best practices can be built into document handling routines through the use of workflows. The complementary document workflow pattern can be combined with the intelligent document pattern to enhance the underlying business process contained within an LOB system.
LOB Initiated Document Workflow Pattern
Businesses often use information in an LOB system to generate documents that need to be reviewed, approved, and possibly edited. For example, if a report needs to be regularly reviewed for compliance reasons, the report can be generated and published to a SharePoint repository to track reviewing. The SharePoint document workflow would ensure that the review occurred, and raise exceptions if proper procedures and timelines are not followed. One way to achieve more control over these generated documents is for the LOB system to publish the document to a SharePoint document library with an associated workflow. For instance, a system might publish a financial report that requires review by an auditor and approval by the controller in SharePoint, and have SharePoint manage the approval workflow. (See Figure 1-13.)
Alternatively, a default document type such as an InfoPath form can be associated with a document library. A workflow could be triggered by something as simple as adding a document to a document library, as shown in Figure 1-14.
Cooperating Document Workflow Pattern
In more complex scenarios, a series of interactions might take place between documents and LOB systems. (See Figure 1-15.) For example, when performing sales pipeline forecasting, a company's management chain often needs to do different levels of review, roll-up, and so on. Additionally, at each stage in the process, certain actions might not be permitted. For instance, once a forecast has been submitted for roll-up, the forecast might no longer be editable. As the sales forecast moves through stages of refinement, intermediate results may be published to the LOB system so that management can extract rolled up views of their organization. To achieve this scenario, a cooperating workflow could be created in SharePoint, following either of two approaches to achieve integration with the LOB system. In the first approach, the LOB Initiated Workflow pattern can be combined with the Intelligent Document pattern. With this composite pattern, the document contains LOB data, and a LOB add-in to the Office client (e.g. Word or Excel) interacts with the back-end system using the data embedded within the document.
The workflow provides logic, while the intelligent document provides the interaction mechanisms to the LOB system based on its current state. The disadvantage of this approach is that the overall state is not as easily tracked because it is shared between the intelligent document interactions on the client and the workflow state on the server.
A second approach is to develop a workflow specific to the problem domain that interacts with the LOB system and enriches the workflow executing on SharePoint, as illustrated in Figure 1-16.
Not all functionality, however, can be delivered on the server side and achieve as rich an interaction as with intelligent documents, but the two approaches can be used in conjunction. For instance, a sales pipeline forecasting spreadsheet could enforce some of the rules and manipulations based on the LOB information embedded in the intelligent document, and once the pipeline is approved, the server-side SharePoint workflow could extract the information from the Excel document and publish the results back to the LOB system. Since this is a more complex pattern, the following scenario is included as it provides a simplified view of sales pipeline forecasting where Excel, a SharePoint workflow, and LOB processes are used in conjunction. (See Figure 1-17.)
Search is becoming more pervasive in businesses. Search engines like Microsoft Office SharePoint Server for Search are also integrating the ability to index LOB entities. Actions can be associated with these entities in the form of URLs. When an entity is included in the results of a search by an information worker, these actions are presented and serve as a launch point for initiating LOB operations. This type of approach is more consistent with how users often want to work. Rather than open a specific application, users prefer to find the information they need to operate on and then choose the work they need to accomplish in that particular entity. For entities that information workers typically deal with in a LOB system, making the information searchable within SharePoint and attaching actions to that information that launches the LOB application extends application usability and increases usage.
The BDC is used for integrating content from LOB systems into the search index, as illustrated in Figure 1-18. Capabilities are supported for doing incremental index updates as well.
Once data is indexed, searches can result in the discovery of related LOB data, and navigation into LOB applications can be initiated from the results page, shown in Figure 1-19. Custom tabs in the search center results can categorize the results for your application as well:
Through the use of search properties, a single entity can be mapped to multiple LOB systems as long as the entity contains sufficient information for populating the parameters for the LOB system URL.
While traditional LOB systems like customer relationship management (CRM), enterprise resource planning (ERP), and product lifecycle management (PLM) are good at enforcing and executing structured business processes, they fall short at facilitating the unstructured human collaboration that precedes the structured LOB business process. In many cases, unstructured collaboration requires the participation of users who do not have any need to use the LOB system. Traditionally, such collaboration takes place through e-mail. But as team size, the amount of information, and process time spans increase, this method becomes ineffective and leads to errors and delays. The Collaborative Site pattern addresses this situation.
Microsoft Office SharePoint Server provides a team site template that can be used to collaborate around a specific business problem. A team site can provide document libraries, discussions, task lists, a team calendar, and tools for project management among many other collaboration features. The site can be secured to restrict access to only certain users.
The Collaborative Site pattern utilizes a dedicated team site to collaborate around a specific LOB entity or process like a sales opportunities, service requests, budgeting, forecasting, and so on. In other words, there is one team site per LOB entity or process as required. The context of the LOB business entity can be used to provision the team site and prepopulate site contents. A link between the LOB business entity and the team site is maintained so that users can navigate from the LOB application's user interface to the SharePoint team site. Richer user experiences can be offered by embedding elements of the team site's user interface in the LOB user interface to facilitate in-context collaboration. Users who do not have access to the LOB system can collaborate by accessing the team site through a web browser. These users may need the context of the LOB system business object within the team site to effectively contribute to the content, and this can be achieved by bringing LOB data to the surface through a Web Part. The Web Part can use the Direct Integration or Mediated Integration pattern to access LOB data.
As an example, this pattern can be used in a CRM application to manage sales opportunities. If a particular sales opportunity is large and requires many people from different groups—like sales, engineering, and legal—to collaborate on it, a SharePoint team site can be created for collaborating on that specific opportunity. Since the CRM system maintains a list of team members, it automatically grants those members access to the team site. The CRM user interface displays the URL for the team site so that users can navigate to the site while viewing the opportunity in the CRM. Team members from the engineering and legal departments who do not have access to the CRM system can open the team site in their web browser and contribute to the content. Since they need some context of the sales opportunity—for example, customer information and opportunity size—a CRM Web Part is added to the team site to show that information from the CRM system.
Users often need to access and gather information from multiple LOB systems to compose a complex business document. In other cases, information such as customer data can be related to multiple LOB systems. Situations like these require the composition of multiple application user interfaces. A pattern such as the Composite User Interface pattern allows developers to build a solution independent of other LOB applications that lets a user compose data from multiple LOB applications into a container such as a Microsoft Office document or a web page on a SharePoint site.
Office Client-Based Composite User Interface
This pattern uses the Office client application shell as a container for user interface composition. A set of Ribbon elements (tabs, groups, and controls) and custom task panes are implemented for each LOB system. The Ribbon groups and controls are placed on a custom tab or on one of the built-in tabs. When a user initiates actions with the LOB system through a Ribbon element, the corresponding task pane is displayed and presents the LOB data relevant to selected action. For example, while working on a proposal document, a user could click a Product Catalog button on a Siebel CRM tab that displays a task pane for viewing product catalog information within Siebel. Then the user can click on a View Inventory button on a SAP tab to view product inventory information in a SAP task pane. When adding functionality with custom Ribbon elements and custom task panes, the standard conventions of Office should be followed for consistency. In particular, a CTP should become visible as a result of a user gesture such as clicking a button on the Ribbon, and the user should close the CTP when they complete their work. (See Figure 1-20.) Automatically showing and hiding the custom task pane can be confusing and should be avoided.
This pattern can be combined with the Intelligent Document pattern to provide a context-sensitive user interface. In this variant, when a user selects the content of the document that is recognized as LOB-relevant information, the corresponding LOB Ribbon user interface elements and CTP are made visible. This pattern can also use contextual tab sets and context menus to provide LOB actions relevant to recognized data. For example, when a user selects a product diagram in a document, Word 2007 displays the Picture Tools contextual tab set. An SAP PLM add-in could recognize the picture as a product diagram and add an SAP PLM custom tab to the tab set. A user could click a View Product Data button on that tab to view product data from the PLM system in a CTP. A Siebel CRM add-in also recognizes the same picture and adds a Siebel CRM custom tab to the Picture Tools tab set. The user can click the View Product Promotion button on the Siebel CRM tab to view promotional data in a Siebel CTP.
Composite Web User Interface
This pattern uses a web page on a SharePoint site as a container for UI composition. It leverages the MOSS Web Part infrastructure to bring data from multiple LOBs into a single web page. The MOSS Web Part infrastructure is built on the ASP.NET Web Part framework. MOSS provides built-in Web Parts such as BDC Web Parts, Excel Services Web Parts, filter Web Parts, and many others. An ASP.NET Web Part can use the Direct Integration pattern, and a BDC Web Part uses the Mediated Integration pattern to access LOB data. MOSS allows end users to compose custom web pages by selecting Web Parts from the gallery and connecting them together to build a composite web UI. When two Web Parts are connected, one Web Part can send data to another Web Part, which can change its content based on the data received. One Web Part can send data to more than one Web Part. This connection capability is what enables UI composition.
The Analytics pattern is a specialized Composite Web UI focused on presenting a data analysis dashboard to the end user. It leverages Excel Services and Excel Services Web Parts provided by MOSS. Excel Services allow rendering of Excel spreadsheet in a web browser. Users such as financial analysts, business planners, and engineers use Excel heavily for data analysis and visualization. They can create complex Excel workbooks using formulae, tables, PivotTables, and charts and can use a data connection to an LOB system to populate the content. These Excel workbooks can be published to MOSS and their output can be viewed through the Excel Service Web Part. Excel Services Web Parts can be connected to other Web Parts, such as a data filtering Web Part, BDC Web Part, and ASP.NET Web Parts to create an analysis dashboard.
Another important Web Part provided by MOSS is a key performance indicator (KPI) Web Part. It allows users to define a KPI based on data in any SharePoint list, including a BDC list. A BDC list presents data from an LOB system and the KPI Web Part renders the indicators based on that real-time data, illustrated in Figure 1-21.
Many LOB applications assign tasks and generate notifications for end-users. These tasks and notifications can be viewed and updated when a user logs into the LOB application. Because a typical organization uses multiple LOB applications, managing tasks and responding to notifications across these applications becomes cumbersome, often resulting in poor task management and delayed responses. This problem can be alleviated by delivering LOB tasks and notifications to Microsoft Outlook. There are several patterns to implement such functionality depending on the complexity and richness of the desired solution.
Simple Task & Notification Delivery
In this pattern, an LOB system delivers tasks and notifications to users as Outlook tasks and e-mail messages. This flow of information is one way; in other words, if a user makes any changes to the tasks and messages in Outlook, those changes are not sent back and or reflected in the LOB system. The details of a task and notification are embedded in the body of the item. HTML can be used to format the content in the item, including links to the LOB application, where a user can obtain more information and take appropriate actions.
In a "push" variant of this pattern, the LOB system delivers tasks and e-mail messages to Microsoft Exchange Server. Users can use Outlook, Outlook Web Access (OWA), or a Pocket Outlook (Smart Phone/Pocket PC) client to read and manage the information, as shown in Figure 1-22.
In a "pull" variant of this pattern, an Outlook add-in pulls task and notification data from the LOB system and creates Outlook tasks based on that information. Alternatively, an LOB system can make task and notification information available as a RSS feed, and users can subscribe to it using Outlook 2007. This approach works well with notifications but might not work as well for tasks because users do not have a task management experience (change due date, priority, status, etc.) with an RSS feed. (See Figure 1-23.)
In this pattern, an LOB system sends tasks to users through Exchange or Outlook, and the tasks are synchronized bidirectionally. That is, the LOB system can update the task that was sent previously to a user's inbox, and the user can update the task in Outlook and the changes are propagated to the LOB system. For example, let's say an LOB system creates a workflow task and sends it as an Outlook task to a user. When this user completes the task, she marks the task as "Complete" in Outlook. The task status change is propagated to the LOB system, and the LOB system takes appropriate action.
There are two variants of this pattern based on the synchronization choice.
Direct Task Synchronization. In this variant, tasks are synchronized by Outlook and the LOB system communicating directly with each other. An Outlook add-in is responsible for synchronizing the tasks in the LOB system and Outlook. In a push scenario, an LOB system sends tasks and updates to Exchange Server. When the Outlook client receives these tasks and updates, it creates a new task or applies updates to the existing task. The same add-in detects whether a user makes changes to the task in Outlook and propagates those changes to the LOB system. The Outlook add-in needs to handle conflict resolution and offline scenarios (e.g., a user updates the Outlook task while the LOB system is not accessible).
Mediated Task Synchronization. In this variant, Microsoft Office SharePoint Server (MOSS) 2007 acts as a mediator between the LOB system and Outlook to provide task synchronization. It leverages two key features provided by MOSS to simplify synchronization logic, the ability to synchronize a SharePoint task list with Outlook 2007 tasks and its event mechanism that can call custom code when the contents of the task list are modified. In this pattern, the LOB system publishes tasks to a SharePoint task list, which can be a team task list shared by all team members. Since a team task list is shared, the LOB system should assign tasks to team members by setting the Assigned To property for the task. Alternatively. the LOB system can publish tasks to individual users' personal task lists. The SharePoint task list is replicated to and is kept in sync with Outlook 2007 tasks through native sync capability. When a user updates the task in Outlook 2007, the changes are pushed automatically to SharePoint. SharePoint raises an event indicating that the change has occurred, which allows custom code to update the LOB system. So MOSS and Outlook 2007 handle task synchronization, conflict resolution, and offline mode. The solution provider is responsible for implementing the logic to push LOB tasks to the SharePoint task list and handling task list change events raised by SharePoint to update the LOB system. (See Figure 1-24.)
Intelligent Tasks & Notifications
Information workers need to take action based on the tasks or notifications that are sent by an LOB system. Usually this activity involves logging on to the LOB system, finding information they need, and updating it. This scenario can be optimized by allowing users to take such actions in Outlook within the context of a task or an e-mail item. For example, when a manager views an e-mail message sent by a human resources system to approve the vacation request for an employee, an Outlook custom task pane could show the employee's current vacation balance and vacation policy. The Outlook Ribbon could provide controls for approving or rejecting the request. When the manager clicks Approve, the HR system is updated. Some of the scenarios in Duet (a product developed by SAP and Microsoft) are based on this pattern.
The key concept in this pattern is recognizing the context and data embedded in a task or an e-mail message. The content recognition can be enabled through a variety of methods: custom properties, Smart Tags, content parsing, and regular expressions. After the context (for example, a vacation request) and embedded data (for example, an employee ID) is recognized, the relevant LOB data and actions can be presented through the Outlook Ribbon and a custom task pane.
Form-Based Tasks & Notifications
This pattern is a variant of the Intelligent Tasks & Notification pattern described earlier. It uses an InfoPath form as an attachment to the e-mail message. The LOB system populates the InfoPath form template and e-mails the form to the user. The user opens the message and fills out the form. InfoPath facilitates data validation, custom calculations, and logic. In addition, an InfoPath custom task pane can present additional LOB data for context. The user submits the form data to the LOB system through a Web service call. For example, a project management system can e-mail a project time card form to a user at the end of the work week. Certain fields like employee information and project information are already populated in the form. The user opens the form in the message. He can view project details like estimated hours, remaining hours, and hours reported in the prior week in the InfoPath custom task pane. He enters the number of hours worked each day, the InfoPath form validates the input, and the form's data is submitted to the project management system.
This pattern benefits from the integration of Outlook 2007 and InfoPath 2007. An InfoPath 2007 form can be e-mailed without requiring a separate deployment of the form template because the template is embedded in the message. The attached form can be previewed in the Outlook reading pane. Also forms can be organized in Outlook folders and the form data can be presented as table columns of the folder view.
A variant of this pattern uses InfoPath Forms Services, which is part of Office SharePoint Server 2007, which allows filling out forms in a common web browser, even an HTML-enabled mobile device. In this pattern, the InfoPath form is posted to the forms library in the user's personal site, and the URL of the form is e-mailed to the user. The user then navigates to the form using a web browser, and the InfoPath form is rendered in a browser without requiring the InfoPath client on the user's computer. The user fills out the form in the browser and submits the data to the LOB system. This pattern extends the reach to users or organizations that do not use InfoPath 2007. So the project time card example can be extended to include partners and contractors. The project management system can post the time card InfoPath 2007 form to the forms library on a SharePoint extranet site and then a contractor can fill out and submit the time card using a web browser.
A common task within an enterprise is verifying budget status before approving a workflow-generated purchase order. A sales director and a merchandise planner can execute this process using a single underlying Excel document to store the data and Excel Services to maintain it. This way, they have a definitive version of the data, and the budget plan can be shared from the server to other people in the organization who need to review or use the information.
The Excel workbook file can be stored in a document library on a SharePoint site. A workflow can be associated with this document library, with custom business logic that executes whenever the workbook is saved. For example, the workflow could run validation rules on a worksheet; apply approval policies to the data; cleanse, validate or filter the data; or update an LOB or other back-end system. This example is intentionally simple for illustrative purposes. An OBA can range from solutions that require little or no coding to highly complex integration projects involving LOB systems and custom development that makes use of Office system API's. For example, the budgeting data could be exposed through the BDC and would then be available in all stages of the expense approval, to LOB system processes, and to the entire enterprise. The Open XML file format could provide document tagging and inspection, database integration, and content auditing. Adapters, hosted in either BizTalk Server or SharePoint Server 2007, could be used to abstract details from the LOB system and allow interaction with business data and processes.
Figure 1-25 illustrates the workflow and technologies involved in an OBA such as this example.
The following procedure shows you how to design and implement an OBA such as this one.
To design and implement an OBA
Create an Excel file that contains the budget numbers and PO information using the metadata.
Create a SharePoint portal and publish the file to Excel Services within the portal. The document will reside within a document library. Excel Services allows multi-level permissions to be applied to the file. For example, users can may be allowed to view the file's contents in a browser, but they are unable to open the file in their Excel client. Or, users will be able to see only the numbers in Excel client, but do not have access to any of the formulae being used in the document.
Create personalized sites for the Controller & Buyer within the portal and provide links to the Excel file on each of the sites. These users will see only those files they are interested in. Since the file is being hosted within Excel Services, all users receive the same copy of the file.
Using .NET Framework 3.0 and Visual Studio 2005, develop a workflow that takes the contents of the Excel file and saves it to a database. Use the OpenXml libraries (under System.IO.Packaging) available in .NET Framework 3.0 to get the Excel data. Since the workflow will be hosted in SharePoint, at runtime it has access to the attributes of the file, e.g. the stream for the file which has been modified, the user who last modified the file, or the library where the file resides. The workflow can also perform more complex functions like creating a SharePoint task for a set of users, sending an e-mail message to the users with the details of the task, and so on. Alternatively, in order to support communication across partners, the workflow could also send the data externally to a trading partner. As a final step, you can create a strong named assembly containing the workflow and install it in the local .NET Global Assembly Cache.
Create an association form using InfoPath. This form will be used to accept user data when the workflow is associated with the document library. Create an initiation form if required. The initiation form could be used to accept user data when the workflow starts execution. Install the workflow in the SharePoint portal as a feature and associate it with the document library that contains the Excel file. Configure the workflow such that whenever any changes are made to the file and saved, the workflow will be executed.
At the back-end, create a data warehouse based on the schema that matches the Excel spreadsheet metadata. Using SQL Server Integration Services, copy data from the database to the data warehouse in a scheduled or on-demand manner. Create an SQL Server Analysis Services cube using the warehouse.
Create a PivotChart in an Excel file and link it to the cube. Publish the Excel file to Excel Services. Finally, use the Excel Web Renderer Web Part to display the chart to users of the portal. Alternatively, using BDC metadata, declare an entity for each row in the database. Use BDC Web Parts to display lists of the entities, allow users to search the database etc. The specification can also be used to create a parent-child relationship between entities, e.g. a purchase order can contain line items. Since the metadata is in XML, it does not require users to be aware of any programming language in order to make changes.