Integrating Word, Excel, and InfoPath with SQL Server 2000

Article
09/18/2009

Peter Kelly
3Sharp Consulting

April 2004

Applies to:
    Microsoft® Office System
    Microsoft Office Excel 2003
    Microsoft Office InfoPath™ 2003
    Microsoft Office Word 2003
    Microsoft SQL Server™ 2000

Summary: Too often, an organization's valuable data sits locked away, inaccessible to the people who could transform it into intelligence, insight, and, ultimately, competitive advantage. With the Microsoft Office System, you can use the data that resides in Microsoft SQL Server 2000 in ways that empower everyone in the organization. This article describes how new capabilities in Microsoft Office Excel 2003, Microsoft Office Word 2003, and Microsoft Office InfoPath 2003 provide a variety of ways to connect to and interact with SQL Server data. (10 printed pages)

Key Concepts and Definitions
Overview
Making SQL Relational Data More Valuable to Information Workers
Custom SQL Queries
Submitting to a Database
Best Practices for Making SQL Server Accessible
Conclusion
Find Additional Information

Key Concepts and Definitions

SQLXML 3.0. SQLXML enables eXtensible Markup Language (XML) support for your Microsoft® SQL Server™ Database. It allows developers to bridge the gap between XML and relational data. You can create an XML View of your existing relational data and work with it as if it was an XML file.

Analysis Services. SQL Server 2000 Analysis Services provides fast access to data warehouse data by creating multidimensional cubes from information in the data warehouse fact and dimension tables.

Data Transformation Services (DTS). The DTS feature makes it easy to extract, transform, and load heterogeneous data (such as data from IBM DB2 or Oracle) using OLE DB, Open Database Connectivity (ODBC), or text-only files into any supported OLE DB database or multidimensional store.

Overview

Among an organization's most valuable assets is the data stored in data warehouses and other repositories. Unfortunately, too often this data sits locked away, inaccessible to the people who could transform it into intelligence, insight, knowledge and, ultimately, competitive advantage.

For example, if a sales manager has access to up-to-date information about which products are selling well and about salesperson performance, she can respond with sales initiatives, directed assistance to those not performing well, and so on. It would be better yet if she could do this on her own from within the familiar environment of Microsoft Office Word 2003, Microsoft Office Excel 2003, or Microsoft Office InfoPath™ 2003, and if she could do it at any time. Compare this to the current status quo, in which most experience only indirect, limited access to databases, in the form of printouts or some rigid, disconnected report.

In keeping with the overall trend of democratization in computing, new Microsoft technologies enable access to back-end systems in ways that can empower all information workers. The sales manager in the example above, product managers, analysts (the list goes on) all benefit from being able to use information without having to worry about the technical details of how to connect to it. This article explains the specific ways in which the following key Microsoft technologies, working together, make it possible to provide this access:

SQL Server 2000 XML capabilities to generate XML from SQL Server data
Excel 2003 capabilities to read XML data and to connect directly to SQL (both OLAP and Relational)
InfoPath 2003 capabilities to connect to SQL Server (bi-directional)
Word 2003 capabilities to use XML as a native file format and support customer-defined XML schemas

Following the discussion of new possibilities, the article introduces some ways in which database administrators can achieve the best balance of enabling data access across the organization while maintaining good performance and security.

Making SQL Relational Data More Valuable to Information Workers

Information workers can use Microsoft Office programs to connect to SQL Server databases for more than a few years now. However, such usage was traditionally limited to power users. The rest receive, upon request, a report over which they have little control (that is, they cannot delve into the report because it is disconnected from the underlying data). In the best case, key information workers receive and use custom Excel-based or Microsoft Access-based solutions (with embedded Visual Basic® for Applications (VBA) code), but they do not necessarily know where the data comes from and they do not have the tools to make any modifications. The two key shortcomings of this state of things are:

Only a small number of users are capable of using the full richness of the corporate data cache (all warehouses and databases in an organization).
Only a small number of users are capable of changing the custom Excel or Access solution to reflect changing needs.

New capabilities in the Microsoft Office System make it possible to get a lot more value out of the corporate SQL Server data warehouse. With a variety of connection methods, programs in the Office 2003 Editions can use and interact with the data in different and complementary ways.

With Excel, information workers can perform their own analysis and business intelligence, without code. Using InfoPath, form developers can create rich, dynamic forms that are based on structured SQL Server data, which allow information workers to interact bi-directionally with that data.

Even Word, with its support for customer defined XML schemas, can be a powerful contributor to the corporate data warehouse; organizations can consume Word documents through Data Transformation Services and harvest the important information that resides there.

The following sections describe these possibilities in detail.

How Excel 2003 Can Use SQL Server Data

Excel provides default functionality allowing it to connect to SQL Server 2000 in a wide variety of ways. Information workers can use the "old standby" ADO (ActiveX® Data Objects) connection through VBA or newer Visual Studio® 2003 Tools for Office projects, which allow complete access to SQL Server data programmatically.

However, information workers also have a rich set of options to consume SQL Server data out of the box without writing any code. Much of this functionality is an improvement over what was available in Excel 2002. For example, XML support in Excel 2003 is much richer and more conducive to working with SQL Server data.

Excel 2003, Business Intelligence and SQL Server Analysis Services

It is quite simple to create a PivotTable® or PivotChart® Report tied directly to a SQL Server Analysis Services Cube. The steps are simply to create a report with an external data source. If the data source is not created on the computer already, one simply has to know the name of the server running Analysis Services and the cube that they want to access:

Figure 1. Connecting to the SQL Server database

Once the data source has been created or selected it is simply a matter of dragging the dimensions into the PivotTable, providing easy access to the rich functionality of the PivotTable.

Figure 2. Creating a PivotTable directly from SQL Server data (Click picture to view larger image)

Using the Excel 2003 PivotTable, information workers can analyze the data within the cube on a very detailed scale. In previous example, with our sales cube, we can look at a summary of sales data across all of the years and store locations, or view the individual transactions of a specific store on a specific date. This ability to "slice" the data within the Analysis Service's Cube across any set of dimensions that the information worker chooses gets ever closer to the goal of business intelligence for the masses.

Excel 2003 and XML

Excel 2003 enables users to work with other XML schema. The new XML feature set enables users to add arbitrary XML schemas to a workbook, and gives users a visual tool to select, drag and drop XML elements onto the sheets of a workbook, creating a custom layout and view of their data. You can then use these mappings to import data files conforming to that XML vocabulary (or schema), and to write the data back out into that XML vocabulary (or schema).

This support includes any XML served through SQLXML. Without any code, users can access and report off of richly described hierarchical data. The figure below shows what the XML-formatted data from the FoodMart database looks like.

Figure 3. XML-formatted data directly from SQL Server (Click picture to view larger image)

An information worker simply has to indicate to Excel 2003 the SQL Server XML data for Excel to infer the schema automatically and import the data. Once you map and import the data, the information worker has full control over which data to map to the spreadsheet and in what order and location. The figure below shows how simply dragging the elements from the XML Source task pane positions the live SQL Server data in the spreadsheet.

Figure 4. An Excel 2003 list based on the XML map (Click picture to view larger image)

In addition, Excel remembers where to retrieve the XML data. At any point while working with the data, the information worker can refresh the data from SQL server. To do so, right-click and choose XML, Refresh XML Data.

Figure 5. Refreshing XML data directly

This is a live connection to the richly structured SQL Server data without ever having to write a line of code. Of course, all of this functionality and more is available through code, either by writing macros or by creating a custom application using Microsoft Visual Studio 2003 Tools for the Microsoft Office System.

How Word 2003 Can Work With SQL Server

Microsoft Word 2003 includes extensive XML support. In fact, XML is now a native file storage format for Word documents. Further, Word supports custom XML schemas, such that you can tag information within Word documents with an organization's custom schema. As a result, a few powerful scenarios are now possible:

Working with SQLXML and Extensible Stylesheet Language Transformations (XSLTs), you can easily generate Word documents server-side.
Word 2003 can consume XML through SQLXML and apply an XSLT to make is usable.
Data Transformation Services can load Word 2003 documents saved in XML format and interpret them based on your organization's XML schema as it applied to the document.

InfoPath and SQL Server

InfoPath allows solution developers to create rich dynamic forms based on a variety of data sources. InfoPath has built-in support for connecting to SQL Server over ADO or through Web services. The following sections describe some of the compelling means of giving information workers interactive, yet controlled, access to corporate data stored in SQL Server.

Building a Form based on a Database

An InfoPath form can draw its data directly from a SQL Server (Access as well) database table or query and display it with the same forms engine used for XML data files. You can also submit changes to the data back to the database.

To create a form from a database, a form designer clicks New from Data Source in the Design a Form task pane. A wizard guides the form developer through the process of connecting to SQL, and choosing the database, tables, and fields to include in the form.

As shown in the figure below, the form developer can choose tables and fields from the database. .

Figure 6. Data Source from database

Based on this information, InfoPath creates a form that contains a data source representing the structure of this data. You can then complete the form design by dragging and dropping fields from the Data Source task pane onto the form's design surface. You can use the resulting form to query, view, and edit data retrieved from this database.

Custom SQL Queries

In some cases, you may need to customize the query InfoPath uses to retrieve the data. To do this click Edit SQL when setting up the database using the Data Source Setup Wizard (see Figure 6 above). This opens the Edit SQL dialog box and displays the SQL statement that InfoPath constructed from the selection of tables and fields in the wizard. The form designer can make modifications and then click Test SQL Statement to make sure the SQL statement is functional and provides enough information for InfoPath to extrapolate an XML Schema for the data.

Figure 7. Edit the SQL statement InfoPath uses to query the database

Note that the SQL statements used by InfoPath are data shaping queries. Data shaping queries allow the building of hierarchical relationships between two or more logical entities in a query. For more information about data shaping queries, see Data Shaping Overview.

Submitting to a Database

In addition to receiving data from a database, InfoPath is capable of submitting new or changed data back to a database. To track changes to data in the form, InfoPath adds a special hidden xdado attribute to each field bound to a database element. It stores information about the changes to the field in this attribute. When you use the Submit command to submit changes to the database, InfoPath uses ADO to update the records in the database.

Best Practices for Making SQL Server Accessible

Part of the point of this article is to demonstrate that with the Microsoft Office System, information workers are empowered to use SQL Server data without the traditional interface of the database administrator. Of course, for performance and security reasons, an organization should not simply expose SQL Server for everyone to use in an unrestricted manner. One best practice may be to provide non-production copies of SQL Server databases for certain broad uses, such as business intelligence for information workers.

In review, using Excel, Word, and InfoPath, you can connect to SQL Server data either directly (through ADO), or through a Web service. Usually, you want to provide access to some combination of these methods.

For more information about setting up Web services, see the SQL Server 2000 Web Services Toolkit. The toolkit contains code samples, white papers, and SQLXML 3.0—everything you need to build XML Web services and Web applications with SQL Server 2000.

For more information about securing SQL Server, see SQL Server 2000 Operations Guide: Security Administration.

Conclusion

You can use the Microsoft Office System to work with SQL Server in ways that profoundly affect the usefulness of the data stored there. When an organization also takes advantage of data transformation capabilities of SQL Server, which can take data from many sources (including IMS, DB2, Oracle, and Sybase) and consolidate them into SQL Server on a regularly scheduled basis, the ability to provide broad access through programs in the Office 2003 Editions becomes even more compelling.

When administrators take the appropriate steps, SQL Server data can reach every desktop in the organization. As information workers use these new capabilities, corporate data can become corporate intelligence.

Find Additional Information

For more information, see the following: