Technical Overview of the Fabrikam Office Web Services Integration Sample Solution

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

Chris Kunicki
OfficeZealot.com

July 2002

Applies to:
     Microsoft® Office XP

Summary: Technical overview of the Fabrikam Office Web Services Integration Sample Solution and the various components it incorporates.

Note   If you have not installed the Fabrikam office solution, methods and worksheets that are referenced at http://localhost/Fabrikam, such as http://localhost/Fabrikam/fabPurchaseOrders/wsFabPurchaseOrders/wsFabPurchaseOrder.asmx?wsdl, will not work.

Solution Objectives

The Fabrikam Office Web Services Integration (OWSI) Sample solution demonstrates how the fictional company Fabrikam Interiors has integrated XML Web services into Microsoft Office XP to provide their users with instant access to enterprise data that helps them to do their jobs more effectively.

The international company, Fabrikam Interiors, is a high-end office design firm that thrives in its field, helping customers with every aspect of office layout, design, and furnishing. Fabrikam assists customers in determining how to use their office space, to manufacturing, installing, and supporting custom furniture. With customers around the world, Fabrikam has field offices in several countries, multiple custom furniture production facilities, and a central headquarters.

Why did Fabrikam Interiors choose to combine Microsoft Office XP and XML Web services? First, Microsoft Office XP is the place Fabrikam employees spend most of their day. Fabrikam kicked off a corporate-wide initiative to improve the personal productivity of its employees. This project will help Fabrikam employees with their day-to-day tasks, provide easy access to corporate information they need, and improve communication with customers, partners, suppliers, and other Fabrikam employees. By combining the rich in-house data exposed via Web Services and the natural and familiar environment of Microsoft Office XP, Fabrikam employees have been better enabled to meet the daily demands of their job.

Technical Overview

The Fabrikam OWSI Sample solution is multifaceted in that it incorporates numerous components to provide an integrated interface for Fabrikam users to access corporate data exposed as XML Web services. These components include:

• Over 50 XML Web services that expose corporate information in the following categories: customers, human resources, products, materials inventory, suppliers, and orders. These XML Web services were developed with Microsoft Visual Studio® .NET and interface with a Microsoft SQL ServerTM 2000 database.
• A client object library for developers that encapsulates and simplifies the logic needed to connect to the Fabrikam Web Services. This object library, which is a compiled dynamic-link library (DLL) that runs on the client computer, centralizes the necessary intelligence for making the connection to the Fabrikam Web Service server, executing an XML Web service, and parsing the response from the XML Web service into a useful format for the Microsoft Office developer. This library is referred to as the Fabrikam OWSI Client Object Library.
• Custom add-ins for Microsoft Word and Microsoft Excel that provide a Microsoft Office XP user with a TreeView representation of the Fabrikam Web Services (see Figure 1). This TreeView interface categorizes XML Web services into business related topics and actions that can be performed. These add-ins address a critical factor in helping users to adopt XML Web services by simplifying the presentation user interface into business terms and by removing XML Web service related technical jargon. For more information, see the accompanying document Fabrikam Office XP Web Service Connector Add-In Templates.
• Custom templates for Microsoft Word and Microsoft Excel that demonstrate how the Fabrikam OWSI Client Object Library can be used by a Microsoft Visual Basic® for Applications (VBA) developer to easily connect Fabrikam Web Services with standard corporate Microsoft Office templates. For more information on these templates see the accompanying two documents Fabrikam Account Explorer Template for Excel 2002 and Fabrikam Account Executive Performance Appraisal for Word 2002.

Figure 1. Microsoft Word add-in that provides a TreeView representation of the XML Web services available at Fabrikam Interiors (click thumbnail for larger image).

A few other objectives are noteworthy:

• For the user, one goal was to maximize access to critical corporate information for users via Microsoft Office XP, while at the same time removing the need for the user to know where the data is stored or how to access it. The information from various XML Web services are all presented in a simple TreeView interface that takes away all the guess work of finding data that will be useful to the user.
• For the developer, another goal was to minimize the complexities for the Microsoft Visual Basic® or VBA developer in connecting to and fully utilizing corporate data exposed by internal Fabrikam Web Services. The following section details how some of these complexities were minimized.

Fabrikam OWSI Client Library

XML Web services are rapidly being adopted by numerous enterprises. With their broad adoption and tools like Visual Studio .NET that simplifies the development of highly robust and reliable XML Web services, it is no surprise that information technology organizations are looking for ways to make these XML Web services available to users.

From the perspective of the Microsoft Visual Basic and Microsoft Office developer, connecting to XML Web services is a relatively simple process. The Microsoft SOAP Toolkit 3.0 provides a COM library that allows developers to easily interact with XML Web services. Key features provided by the Microsoft SOAP Toolkit 3.0 include:

• Connecting to XML Web services and retrieving results with support for both simple type and complex type data.
• Sending and receiving attachments via XML Web services.
• Ability to parse Web Service Description Language (WSDL) files and return them as a programmable object model.
• Communicating through proxy servers, including authentication through firewalls.
• User authentication when accessing XML Web services.
• Industry standards support:
  • http://www.w3.org/TR/soap
  • http://www.w3.org/TR/wsdl
• W3C XML Schema recommendations:
  • http://www.w3.org/TR/xmlschema-1/
  • http://www.w3.org/TR/xmlschema-2/

As you can see, the Microsoft SOAP Toolkit 3.0 provides most of the key functionality needed in utilizing XML Web Services from Microsoft Office. Yet, there are a number of repetitive programming tasks that a developer must do when integrating XML Web services with Microsoft Office functionality. Therefore, the Fabrikam OWSI Client Library encapsulates these repeatable Office-related Web Services tasks by extending the Microsoft SOAP Toolkit 3.0.

The Fabrikam OWSI Client Library is a COM DLL written in Microsoft Visual Basic 6.0 and is compiled as an ActiveX® dynamic-link library (DLL). You can find the source code to the DLL by looking in the <<Install Directory>>\Source\Client Objects directory.

The project name for the Fabrikam OWSI Client Library is FabrikamOWSICO.vbp. The project is made up of nine class modules and four standard modules. Table 1 lists the various modules and their description.

Table 1. Module breakdown for the Fabrikam OWSI Client Library.

• To use the library in your solution, set a reference in the Visual Basic Editor to the Fabrikam Office Web Services Integration Client Object. To use the object from code, it is first necessary to create a WebServicesConnector object. For example:

Dim fabWS As FabrikamOWSICO.WebServicesConnector
Set fabWS = New FabrikamOWSICO.WebServicesConnector

• This line of code instantiates an object named fabWS as type FabrikamOWSICO.WebServicesConnector. This object can now be used to access the Fabrikam Web Services. The following code demonstrates using one of the CustomersWS Web Services:

Debug.Print fabWS.CustomerWS.CustomerName(205000)

• This will output the Fabrikam customer name to the Debug window for customer ID 205000. Table 1 includes a list of all the public objects that are callable from a WebServiceConnector object: CustomerWS, EmployeeWS, MaterialWs, OrdersWS, ProductsWs and SuppliersWS.

**Note   **To see a list of the available XML Web services and their corresponding Web methods, see the Web page http://localhost/FabrikamOWSISamples, which is configured as part of the standard installation of the Fabrikam Office Web Services Integration Sample solution.

In addition to using strongly typed names for object calls as just demonstrated, it is possible to call an XML Web service dynamically using a parameter based approach. The preceding line of code can be rewritten using the InvokeSimpleTypeWebMethod method as shown with the following line of code:

fabWS.InvokeSimpleTypeWebMethod("CustomerWS", "CustomerName", Array(205000))

The same fabWS object is used to call InvokeSimpleTypeWebMethod. However, this time, three parameters are passed to the method. The first is the name of the XML Web service, the second is the name of the Web method to be called, and the final is an array that contains the required parameters for the specific method of the XML Web service.

At this point, we have only looked at two lines of code that return simple types from an XML Web service. Simple types are just as they sound, simple data types that are easy to represent in Visual Basic. Examples of simple types are Integer, Double, String, and Date, among others. XML Web services also support the notion of complex types. A complex type is a structured XML document that contains one or more simple types. An example of a complex type would be an ADO.NET DataSet serialized as XML or a Visual Basic Array serialized as XML.

The Fabrikam OWSI Client Library also includes support for complex types in the form of XML serialized ADO.NET DataSet returned by XML Web services written with the .NET Framework. The Fabrikam OWSI Client Library returns the ADO.NET DataSet to the Visual Basic or VBA developer as a two-dimensional array. For example, consider the following:

Dim vnt As Variant
vnt = fabWS.CustomerWS.CustomersByRegion("US-East", True)

First, a Variant object variable is created. Then the CustomersByRegion Web method of the CustomersWS XML Web service is called and assigned to the Variant object. CustomerByRegion accepts two parameters: the region of interest and the final parameter, which indicates if column names from the XML serialized ADO.NET DataSet object should be returned in the array. The response from the Web method is an array that is assigned to the Variant object which Visual Basic and VBA refers as a Variant array. A Variant array is simply an array, except that its dimensions do not need to be known when the Variant array is populated. This allows our ADO.NET DataSet object to be of any size in columns and rows.

Another benefit realized by the Visual Basic or VBA developer using the Fabrikam OWSI Client Library is they do not have to figure out the complexities of parsing XML from an ADO.NET DataSet object. The library has been optimized to parse the returned XML and to retain the data types of the data returned. Additionally, great effort has been placed on performance of parsing this information. A typical DataSet object of about 20,000 rows by about 10 columns can be parsed by the library in under a second. What about a DataSet object that is larger? One thing to keep in mind is that larger DataSet objects take longer to download and there are practical limitations about how much data a user will want to store in a Microsoft Office document; think 65,536 rows in Excel and realize that even coming close to that is impractical. The library can handle much larger DataSet objects, but it's important to remember bandwidth constraints and practical limitations on the amount of data you return to the client from an XML Web service.

Just as we could dynamically call simple types using InvokeSimpleTypeWebMethod, the Fabrikam OWSI Client Library provides a means to return a complex type by using the InvokeComplexTypeWebMethod method. For example, to return the CustomersByRegion as previously demonstrated with InvokeComplexTypeWebMethod:

vnt = fabWS.InvokeComplexTypeWebMethod("CustomerWS", "CustomersByRegion", True, Array("US-East"))

Why provide two approaches to access the same XML Web service? The first example is strongly typed and guarantees an XML Web service will execute. This approach is ideally suited for times when XML Web service calls are know at development time. The second method allows the programmer to dynamically call XML Web services and Web methods by name in real time. The latter approach is more dynamic but can also be more error prone, as the method call will fail if the XML Web service name or Web method name are incorrect.

Error Handling

Responsible error handling is a concern of every developer. When it comes to error handling XML Web services, there can be many unexpected issues that arise. For example, consider just a few reasons an XML Web service may fail:

• The Web server is unavailable.
• The user's network connection has been dropped.
• The XML Web service itself is failing.
• The user is unable to authenticate to an XML Web service.

The Fabrikam OWSI Client Library simplifies error handling by encapsulating common errors into just three types of errors. The errors returned by Fabrikam OWSI Client Library are defined in a simple enumeration:

Public Enum OWSICOErrorCodes
    FailedToInitializeEnvironment 
    WebServiceCallFailure 
    WebServiceReturnedNoData 
End Enum

A developer can test for these conditions with the following:

If Err.Number = OWSICOErrorCodes.WebServiceCallFailure ThenEnd If

By simplifying the number of possible errors, a developer focuses not on handling all the possible conditions that might arise, but on addressing how to respond to these generalized error conditions.

The following list summarizes what these errors mean:

• FailedToInitializeEnvironment – This error only occurs when creating the FabrikamOWSICO.WebServicesConnector object and represents the fact that the user will not be able to interface with the Fabrikam Web Services at all. This error could be a result of the user not having a network connection or the Web server is unavailable.
• WebServiceCallFailure – This is a general error that can occur during the execution of Web methods. It represents a failure to properly connect to a specific XML Web service. This can be triggered on the server for a number of various reasons, such as passing a parameter with an incorrect data type to a method or receiving the wrong number of parameters. Since it is a server error, the calling client does not have any control over it, but can handle the outcome by trapping this error.
• WebServiceReturnedNoData – If a method is called, but no data is returned, this error will fire. It provides a consistent way of telling the developer that the call was properly executed but the result from the XML Web service call returned no data. This can happen for example when a XML serialized ADO.NET DataSet object returns no records.

Caching

A challenge presented by using XML Web services in Office is the fact that the XML Web services communication has to go across a wire in an XML format. Depending on the reliability and bandwidth of a user's connection, XML Web services can be slower than other forms of data access. However, the simplicity of XML Web services in development and the broad adoption by the industry make them an ideal data access technology for Microsoft Office solutions.

To reduce the areas where XML Web services impact the performance of the Fabrikam solution, a caching mechanism has been incorporated into the Fabrikam OWSI Client Library. Caching is performed in two ways:

• The WSDL files used by the Fabrikam OWSI Client Library are cached at the client during regular intervals. This allows the client objects to load quicker and to use local information when determining how to connect to the Fabrikam Web Services server.
• The Fabrikam OWSI Client Library allows for certain XML Web services that require no input parameters to be marked for caching. When accessed, the response for these XML Web services is cached to the client's machine for a period of time. Further requests of these marked XML Web services are pulled from the local cache until they are required to be refreshed.

The Fabrikam OWSI Client Library uses a XML based configuration file to define which WSDL files are used and to define which Web Services should be cached locally. This file can be found at <<Install Directory>>\Source\Client Objects\owsico.config.

The owsic.config file also stores the BaseServerPath parameter. By default this parameter is set to BaseServerPath="http://localhost/fabrikamowsisamples/".

The BaseServerPath parameter is used by the Fabrikam OWSI Client Library to know the location of the Fabrikam Web server. As a note, if you decide to run the Fabrikam Web Services on a remote server, the BaseServerPath parameter must be modified.

You may be wondering why Fabrikam does not cache every call to their XML Web services. The reason is simple; most of the data requests are invoked by users when they need the data. Therefore, the data should be as current as possible. Also, once the data is retrieved, it is stored in the Office document and thus is persisted until they choose to update the data. Caching is beneficial in a few cases where there may be large list of information being returned by the XML Web service. Another case where caching is useful is where a specific XML Web service is the basis of a lookup list and is used frequently.

Object Model Access

The Fabrikam OWSI Client Library provides a convenient object model for accessing Fabrikam Web Services. This object model reduces the developers need to understand the plumbing logistics of connecting to XML Web services, and also eliminates the need to remember which services are available since they can simply browse the object model straight from Visual Basic or VBA. When a developer makes a reference to the Fabrikam OWSI Client Library in the Visual Basic or VBA integrated development environment, they also benefit from IntelliSense® to all the XML Web services and their accompanying Web methods. Above all, it enables them to focus on the business problems at hand and not the technical ones.