Executive Summary: Dan Holme teamed up with NBC television as the Microsoft Technologies Consultant to help bring the 2008 Beijing Olympic Games to television and the Internet. Microsoft SharePoint was a major part of the platform used to manage the operations that bring the events to the public. He discusses four real-life tasks that he solved by using Microsoft Office SharePoint Server (MOSS) 2007: a custom document management application, a Help desk application, a forms-based content delivery solution, and a transportation management system.

I’m honored to work with some of the greatest IT teams on the planet, including the awe-inspiring folks at NBC Olympics, who play a pivotal role in bringing the Olympic Games to the air and to the Internet every two years or so. This summer, in Beijing, we’re making more use of SharePoint than ever before, and I want to share with you four of the tasks we’ve conquered by using SharePoint. I’ll explain how to tie existing applications into SharePoint; replicate documents between document libraries; create intelligent, form-based applications; and develop multiuser applications that provide rich client and Webbased interfaces. I hope to give you insight into real-world uses of SharePoint, teach you what we’ve learned along the way, and inspire you to use SharePoint to provide new types of business solutions.

Custom Document Management
I’m often asked, “How can I hook my existing applications into SharePoint to leverage SharePoint’s collaboration features?” One of our applications is a custom Microsoft .NET application that—put simply—generates specifications about venues so that engineering and production staff know what types of situations they’ll be dealing with. One of the outputs of the application is a PDF file about the venue. In this Games, the application was modified so that instead of storing the PDFs in a traditional file share, the PDFs are saved to a SharePoint document library.

You have several options for saving PDFs to a document library. The most complex and rich method is to use code (or a third-party tool) to generate the PDF on the SharePoint server, then develop custom code using the SharePoint object model to manage document metadata (columns) and manipulate the document library. But there are easier methods. You can email-enable a document library so that email messages with an attached document can be sent to the library, and the library will receive that message and automatically store the document—and, optionally, the message as well. This is one of the easiest ways to extend existing applications: just have the applications email the document to the address of the library. Note that this method effectively populates the document library, but you would need a workflow or other programmatic or manual method to configure document metadata.

You can also use Web Distributed Authoring and Versioning (WebDAV) to interact with SharePoint document libraries. Our custom application saves its PDFs to the document library using the URL of the library. WebDAV lets you use standard Windows methods to retrieve documents from a library—you can even map a drive to a document library. Therefore, we also used WebDAV to implement simple document library replication. The PDFs generated by the custom application need to be available on servers in both Beijing and New York. We needed a lowoverhead way to get documents from Beijing, where the documents are generated, to New York, where they are available in a read-only library. We opted to use a scheduled task to launch Robocopy to mirror the contents of the document library in Beijing to the document library in New York.

When you use WebDAV to interact with a document library, you lose metadata richness. So, for example, we can’t mirror metadata about a document in Beijing to New York. But metadata replication wasn’t a requirement, so by focusing on the core requirement, we found a solution that the team liked and that took less than an hour to implement. If our requirements were any deeper, we would have needed a third-party content replication application.

Help Desk Application
The site templates available out of the box with SharePoint are generic at best. If you need a SharePoint application that supports a specific business task, function, or department, you’re out of luck. Out of luck, that is, unless you turn to the Fabulous Forty application templates that Microsoft has developed. Application templates provide a lot of cool, out-of-the-box functionality through custom lists, libraries, workflows, content types, and Web parts. The Fabulous Forty includes templates for dozens of scenarios, including a Help Desk template that we’ll be customizing to support the Help desk for NBC during the Games. You can download the templates at www.microsoft.com/downloads/info.aspx?na=22&p=1 &SrcDisplayLang=en&SrcCategoryId=& SrcFamilyId=&u=%2fdownloads%2fdetails .aspx%3fFamilyID%3d5807b5ef-57a1-47cb- 8666-78c1363f127d%26DisplayLang%3den.

In Beijing, we need an application to support the Help desk, which exists for about four weeks and is crucial to operations. But the limited lifetime of the application means that we need an inexpensive, easy-to-useand- maintain solution. We used the Windows SharePoint Services (WSS) 2.0 Help Desk application template in Torino, and we’ll be making even more use of the WSS 3.0 version in Beijing.

The template provides a ticketing system that lets our Help desk staff enter tickets, update and track issues, and assign tickets and tasks, which is important because a ticket might cross from one shift to another. The template also lets our Help desk manager monitor progress on tickets and pull reports. I’ll be monitoring the template to look for common concerns that we can address with training or through configuration changes to clients and applications.

Because you can extend these templates, we’ll add a Calendar list to incorporate our Help desk staff scheduling so that we always know who is on duty and which escalation points are on call. We’ll use a Contacts list to store the contact information, and we’ll have a mobile access page for that list so that we can get to contact information using a PDA from anywhere on our network.

Content Delivery
If you’ve paid any attention to the preparations for the broadcast of this Olympics, you’ll know that a huge amount of content will be made available over the Web and to your mobile devices. For the first time, you’ll be able to watch events from any device and be able to see events and competitors you’ve never had the chance to see before. In Salt Lake City and then Torino, where NBC expanded its coverage to a broad range of broadcast and cable channels, the sport of curling caught fire. I’m excited to see which sports, countries, and athletes capture our imagination this summer!

To get all that content to the right media outlets, a dedicated video distribution application takes video packages and delivers or streams them. A large amount of metadata accompanies each package—metadata that varies per outlet. For example, the “Title” field can be long on Amazon.com, but must be short for mobile Multimedia Messaging Service (MMS) delivery.

Additionally, editors must be assigned to create the packages. To make it possible for mere mortals to enter all this data, preferably before the Games begin, we need a userfriendly interface that exposes just the right data, performs data validation, ensures that required fields are completed, then transforms all this information into the XML file required by the video distribution application. Once again, SharePoint came to the rescue.

Continue on Page 2

We built the UI—the form—by using InfoPath 2007, which allows us to embed the business logic into the form so that when an editor works on metadata for a specific package, the only fields that appear are those needed by the appropriate outlets. The form also makes sure the data complies with the datatype and content requirements of the video distribution application.

So that we don’t have to deploy the InfoPath client on our systems, we’re using InfoPath Forms Services, a feature of Microsoft Office SharePoint Server (MOSS) 2007 Enterprise Edition, to render the forms in users’ browsers. When a form is saved, the data is stored in a SharePoint list, and a custom application page uses a workflow to publish the data to the XML format that the video distribution application requires. This application is the most heavily coded application of the four SharePoint solutions I’m describing in this article, and even this application took two smart folks from Net- Fusion, a Microsoft Gold Partner, less than two weeks to create. It would have taken months without the functionality that Info- Path and MOSS provided.

I’ll create custom views of the SharePoint list, add alerts, and create Microsoft Excel reports linked to data in the list. These tools will allow editors, producers, and management to perform their responsibilities related to the process. So if you watch any of the Games on a device other than a television, know that SharePoint helped it get to you.

Transportation Management
Speaking of applications that could have taken months to create, let’s talk about the transportation management application we developed in just a few days, without ever opening Visual Studio. NBC’s thousands of employees, contractors, and vendors need to travel all over Beijing. And you’ll know when watching the Games what a masterful feat it is to move team members from point A to point B. The transportation group consists of a handful of talented folks who must coordinate hundreds of rides every day for more than three months and oversee as many as 200 vehicles and drivers. We had to provide a way to make it all easier to manage.

The transportation management application needed to allow any transportation team member to enter information about a ride request, assign a vehicle and driver (a “transfer”) to fulfill the request, and enable monitoring and reporting of the transportation group’s activities. In previous Games, all ride requests were submitted by phone. This time, we wanted to let users submit requests online. Because SharePoint can use alerts and workflows to notify users when data changes, I wanted the application to notify a user when his or her ride request had been assigned a transfer, so that the user would know which vehicle to look for, when the ride would depart, and who else was going on the same ride.

The challenge with this application began with the data tables. To properly support the application, we needed tables with vehicle information, ride requests, and locations. The transportation team needed to be able to easily pull up information such as addresses, maps, and even photos of buildings. SharePoint isn’t built to create or support relational databases out of the box. However, with Microsoft Access as a client, you can create an application that provides, through queries, the ability to relate data in various lists and delivers a rich, form-based interaction with that data. Traditional Access applications had data in tables within the database (.mdb) itself. Many IT pros have learned that you can gain a lot by putting the data on SQL Server or, now, a SharePoint server instead, and use Access only as the front-end application.

There are several ways to build Access applications that are front ends to Share- Point lists. You can build an Access database and use the migration wizard in Access 2007 to move tables to a SharePoint site as lists. Or you can create the lists in SharePoint and pull them into an Access database application as linked tables. I chose the latter method.

Access then lets you create queries and rich forms; any additional functionality you need becomes an Access programming task rather than a SharePoint programming task. For example, I wanted to give the transportation team a way to identify which of the 200 vehicles were available at a specific place and time to fulfill a ride request. This required a fairly sophisticated SQL query, which I built in Access with help I found via my favorite Internet search engine. I also wanted fields to autopopulate data to reduce data entry for users and transportation managers. I accomplished this with simple Visual Basic for Applications (VBA) code behind the Access forms. In less than three days, I had a rich application that will support our complex transportation management needs.

But I also wanted to provide some of the “nice to have” functionality for this application. First on my list was a Web form for users to submit ride requests. This was easy thanks to SharePoint Designer. I created a new Web form, dragged my SharePoint list onto the form to create the data connection, then modified the Insert template, which is the form rendered by the server when a user creates a new record. Within an hour, I had the form I wanted.

The next step was to create alerts for users. I created a custom view of the ride request list, called My Rides, which filters the list to look for items for which the Created By field was equal to [Me], a special token in SharePoint that translates to the current user. So when I go to the list, I see only my rides, and when another user goes to the list, she sees only hers.

One of the lesser-known features of Share- Point alerts is that when you create a custom view, you can generate alerts, based on changes to data, that appear in that view. So your view can be a filter for your alerts. This feature isn’t well known because the option for creating a view-based alert appears only after you create a custom view, so you might not have ever noticed that option. After I created the My Rides view, I assigned the view to all users. Now, when anything changes about a ride for a user, the user will receive an alert with the appropriate information. We’ll be testing the alert-based notification for a few weeks. If it doesn’t meet our needs, I’ll use SharePoint Designer to create a custom workflow to achieve the same email notification.

I learned some important lessons from building this application. Because SharePoint doesn’t support relational data very well, I needed to denormalize some data points. That is, I have some redundant information across tables. For example, when a user’s ride request is fulfilled as a transfer, the vehicle number is entered into the transfer table and into the ride request table. Code within the Access form enters the data automatically, but it’s redundant. That was necessary to give the correct experience and information when a user visits the site online or receives an email notification. SharePoint has lookup fields, but working with them can be difficult (e.g., there are places where doing searches, sorts, and queries based on their content isn’t so easy). So, I cut my development time significantly by entering information into two fields—a lookup field, which lets users jump to related items via a link when visiting the application online, and a normal text field, which is easier to manipulate programmatically and to generate views and queries.

Using Access as a front end to a Share- Point application isn’t the right solution for every challenge, but since we had a limited number of internal transportation managers who need to interact with the data in a rich way, we can deploy Access to their systems. All other users will be interacting with the ride request form and with the ride list online, and with email notifications. We could have created part of the solution using SharePoint Designer and its powerful Data View Web part, or we could have opened Visual Studio. But we met our requirements with an easy solution within which the only custom code is related to automating the process and reducing data entry.

On to the Games
These four applications demonstrate some interesting uses of SharePoint to solve problems. I’m writing this article a few months before the Games, and by the time you read this, we’ll be running full speed toward the Opening Ceremony on August 8. We’ll have learned even more by then, and you can learn about these applications at my blog, share.intelliem.com/cs/blogs/danholme.