Site Server - Universal Studios Shopping Experience Deployment

August 1999 

By Steve Jacobs (Director, Universal Studios Online), Mark Kapczynski, Dan Sodhi and Rick Shahid (Microsoft Consulting Services, Southern California District)

Other contributions from: Roy Hirst, Mukesh Agarwal

Special Thanks to: Chris Cale, Beverly Jones

Version: Release 1.0

Introduction

Universal Studios' business is providing entertainment-oriented content to consumers. Universal is a major producer and distributor of films, videos, and television programs. The world's largest music company, it also operates theme parks, over 570 retail stores, and is part-owner of a chain of 440 theaters.

Merchandising is a rapidly growing part of this business. The online Universal Studios Shopping Experience ("the Store") features a wide selection of merchandise from Universal Studios entertainment properties. The Real Hollywood Online Auction ("the Auction") specializes in exclusive celebrity memorabilia and collectibles. Both the Store and Auction are located at https://store.universalstudios.com .

Universal has operated an online store since August, 1997. In 1998, a new Store was built to provide better shopper service, increase online sales, and offer a wider range of merchandise from Universal's theme parks, Home Video, Music Library, and Spencer Gifts operations. The new Store also integrates the online Auction, which had been operated as a separate site.

Universal divided Store development into three phases; Phase 1 built the new Store and Auction, Phase 2 will add shopper services such as parcel tracking and enhanced product search, and Phase 3 will implement personalization and an integrated accounting system. Universal worked with Microsoft Consulting Services to complete Phase 1 in two months. The Phase 1 Store uses Microsoft Site Server 3.0 Commerce Edition with Microsoft^® SQL Server^™ 6.5.

When all three phases of development are completed, the Store plans to reduce order turnround time and maintain inventory control by integrating with Great Plains Dynamics.Commerce, using SQL Server 7.0. It also will use CyberCash CashRegister 3.0 for payment authorizations, Live Picture Image Server 3.0 for image enhancement, and NetGravity Ad Server for Advertisements.

Goals for the New System

Compared with the previous online store, the new Store's primary goal is to increase the number of shopper visits and make it more likely that a visit results in a purchase. The greater range of entertainment merchandise on offer is expected to contribute to this.

The Store's second goal is automate the shopper's buying process from item selection through final shipping, replacing a "tear and fax" procedure that was in place before.

When all phases of implementing the new Store are complete, these goals will be met by:

• Better shopper service through real-time order fulfillment. Real-time queries will enable the shopper to check order status, query product inventory, or track a UPS shipment. 

• Expansion of the site to handle the expected growth in merchandising. 

• Security for sensitive information such as credit card data (using SSL when transmitting over the Web), together with site security that allows shoppers to administer their own profiles 

• Better web site analysis and reporting to improve the look-to-buy ratio, and to track the most popular stores and products visited. 

Application Overview

The Store home page at https://store.universalstudios.com welcomes all previously-registered members by name, or simply welcomes new shoppers as guests to the site. It offers a choice of:

• Member Login / Logout 

• Member Registration / Profile Management 

• Visiting one of the individual Universal divisional stores, e.g. at a theme park 

• Auction 

• Product Searches By Category 

• Net Gravity Advertisements 

Shopping at a divisional store: Once inside a divisional store, a shopper may search for a product in the Commerce database by navigating the divisional store's product catalog. The Commerce Product Pipeline determines the correct product selling or promotional price.

The shopper is given a unique identifier, and a basket is created in the Commerce database. As the basket is modified, the Basket Pipeline calculates line item and order totals and promotional discounts.

A shopper must be a registered member to purchase the contents of their basket. If a shopper is not yet registered when they begin the purchase process, they are sent to the online membership registration page.

The three steps in the purchase process are Shipping, Billing, and Purchase. An SSL-enabled wizard presents each step to the shopper as a page that pops up in a dedicated window. The Shipping and Billing steps collect shopper information using Microsoft Wallet for Microsoft Internet Explorer technologies 3.x and above. Standard HTML forms are used for browsers that do not support ActiveX® controls.

After shopper information is collected, the Purchase step completes the order by calculating line item and order totals, adding in charges for shipping, handling, and taxes, and applying discounts for volume sales or promotions. The shopper verifies the order before submitting it for processing. The Purchase step validates credit-card information via yberCash, generates a unique order identifier, and commits the transaction to the Commerce database. The order identifier and payment authorization code are displayed to the shopper for future reference.

A shopper can query their order status online, using the order identifier. For Phase 1 of the Store, an online query can determine if the order has been fulfilled. The final version of the Store will also provide inventory and delivery status information, based on the integration with the Great Plains Commerce accounting system and UPS shipment tracking.

Auction: The auction requires the shopper to be registered and hold a valid credit card before entering to bid. Credit card information is validated online with the issuer.

Auction rules and bidding logic support two types of bid. A bid for an item may be "absolute"–a one-off bid, or "proxy"–a pre-set ladder of bids to be used in succession as bidding escalates.

While bidding is open, the five most popular items (based on number of bids) are dynamically displayed on the site's homepage, as shown in the example below.

At the close of the Auction for an item, an Automatic Settlement process takes the item through the Order Processing Pipeline (OPP) check-out using the winning bidder's credit card information. If OPP check-out succeeds the winning bidder gets an email linked to the UPS tracking page.

If OPP checkout fails because of credit problems, an email is sent to the winning bidder, linked to his or her basket. When the shopper links to the site the basket is dynamically filled with the item won in the auction. The shopper is prompted to finish the checkout process manually.

Product Search: Clicking on the Search Icon lets a shopper search either alphabetically or by entering search criteria in an input box. The results are displayed in a pop-up window as shown below:

The search capability uses product attributes. Each product can be assigned an unlimited number of attributes, selected from a pre-defined list editable by a Store administrator. For example if the product is "Star Trek: Deep Space Nine Prop Tribble" the attributes assigned could be "Prop," "Star Trek," "Tribble," "Science Fiction," etc.

Shipping

Universal uses UPS to ship products. Integrating the UPS Shipping System with Universal's Store and Auction will involve applying Universal's negotiated UPS shipping rates, and providing a UPS Tracking Number to the shopper.

Charges for UPS Ground and UPS 2 Day Air will be based on Universal negotiated rates. Because Site Server Commerce's Pipeline uses standard UPS rates, a stored procedure was needed for the Pipeline in Phase 1 of the Store to accommodate Universal's special rates. When UPS shipping is integrated in Phase 2, these rates will be imported directly from UPS servers in the distribution centers.

To provide a UPS Tracking Number to the shopper, a flat file containing order and ship-to information will first be imported into the UPS Shipping System. UPS will produce a shipping label, and export the OrderID and UPS Tracking Number back to the Store database. The shopper will then be emailed with the UPS Tracking Number.

Billing

Universal intends to standardize all of the Store's back-end accounting on Great Plains Dynamics Commerce software. This will replace Universal's original own-developed accounting suite, and provide order acceptance, pricing, tax calculations, inventory management, and returns processing.

Personalization and Membership

Once a shopper has registered at the site they will be able to edit their own profile, view a history of their own transactions including current order status, and track packages shipped via UPS.

Forgotten passwords will be retrievable. An email will be sent to the shopper with their password if they can supply the correct 'Reminder Word' kept in their profile.

Online Agent "Woody Woodpecker"

For Phase 3, Universal intends to implement an online agent that offers advice to a shopper while browsing. The agent will be an animated cartoon character on-screen that gives its advice in text or speech. Universal intends to select a suitable cartoon character from their own assets, such as Woody Woodpecker or Crash Bandicoot.

Microsoft Agent already offers online agent functionality, for predefined Microsoft characters. Universal's implementation is expected to use similar technology.

Online Chat

Universal will enhance the shopper experience by implementing Microsoft Chat Server or iChat engine for live Shopper Support chats. This was implemented for the Auction in Phase 1, and is scheduled for the Store in Phase 3.

Project Overview

Universal envisions a next-generation online Store that highlights the products of multiple divisions. The Store is to be scalable to meet Universal's expected merchandising growth, and must increase the percentage of closed sales by improving the shopping experience and the quality of shopper service.

The implementation incorporates merchandise from the following Universal operations:

• Auction 

• Spencer Gifts 

• Universal Home Video 

• Universal Music Library 

• Universal Studios Florida 

• Universal Studios Hollywood 

• Universal Studios Store 

This vision includes the total redesign of the old online store, the incorporation of the previously separate Auction site, and integration with payment systems, the back-end UPS shipping system and the Great Plains accounting package.

Timeline

The project began in August 1998 and was divided into 3 phases.

Phase 1 took two months from design to the launch of the new store. It focused on store layout and the front-end shopper experience. Included were a new Auction capability, and the integration of the other Universal stores, with a cross-store search capability and "buy now" checkout functions.

Phase 2, still under development at time of writing, includes cards, and gift certificates, and extend cross-store searching capabilities. Shopper service is to be further improved by integrating the UPS shipment tracking system.

Phase 3 will further improve shopper service by integrating the Great Plains Accounting financial package. The site will be made more appealing and accessible to shoppers by adding a cartoon online agent, personalizing the shopper's home page, and adding support for WebTV™ service.

Development Resources

Universal followed the Microsoft Solutions Framework (MSF) development process for the Store and Auction. Development resources were organized along MSF lines, i.e.

• Product Management–liaises with business partners and prioritizes projects. 

• Program Management–writes the product specifications. 

• Development–implements the specifications. 

• Testing–ensures the product is stable before being released. 

The following resources were used in Phase 1 of the Store and Auction development.

Phase 1	Man-months	Resource	Source
Store	2	Product Manager	Universal Studios
	2	Development Lead / Program Manager	Microsoft Consulting Services (MCS)
	P/T	Development	MCS
	2	Development	Comsys (solution provider/contractor)
	2	Development	Universal
	P/T	Testing	Universal (as required)
Auction	P/T	Program Manager / Development	MCS
	2	Development	Comsys

System Requirements

The Store operates on a single Web server and a single database server. The application is scalable in line with the expected future growth in Store sales. It operates 24 hours a day, 7 days a week with less than a half-hour of down time each week.

Web site reports monitor shopper demographics, browser use, and site navigation, to ensure that the site is as accessible and as easy to navigate as possible. The aim is to increase the proportion of shoppers who buy by 100% compared with the old Universal online store. Web site reporting includes:

• Shoppers/hour 

• Page views and visits/page

• Logins/shopper 

• Shopper history 

• Site usage profile reporting–which divisional stores and products were accessed. 

Using the Great Plains system, order fulfillment, order status queries, and product inventory queries are to be real-time.

Security Requirements

The Store requires the following security:

• An online registration process and profile management interface. Once a shopper registers, their profile passes into membership along with shopper attributes. 

• Delegated administration to allow shoppers to manage their own profiles remotely. 

• Configurable session timeout after login. 

• Access within the site granted based on a member's system privileges—by default anonymous access is supported and tracked.

• Systems security for safe operation of Commerce Store on the Internet. 

• Security for sensitive information such as credit card (using SSL when transmitting over the Web). 

Proposed Solution

Logical Architecture

The Store's supported range of Web technologies includes WebTV, Internet Explorer 3.0/4.0, and Netscape 3.0/4.0. Pages are presented in HTML or ASP depending on the browser detected. ASP pages running on an IIS server communicate with the database using ActiveX Data Objects (ADO).

Business logic is implemented through Site Server Commerce's Order Processing Pipeline and Auction components.

Phase 1 of the Store uses SQL Server 6.5 for catalog and web services, and for personalization and membership. The site will migrate to SQL Server 7.0 when Great Plains Dynamics is integrated in Phase 3.

Logical Architecture (Phase 1) 

Orders received from shoppers are confirmed directly from the Web Server via the Exchange Server. The Store uses Collaboration Data Objects for Windows NT^® Server (CDONTS) to communicate with the Exchange Server rather than CDO. CDONTS allows asynchronous messaging that does not depend on the Exchange Server being available.

Physical Architecture

The Store and Auction use four servers (Web Server, SQL Server, Membership Server, and Great Plains Integration Server). See Configuration on page 28 for detailed configurations.

The Web Server contains the site's ASP files, product images, Live Picture files, and admin pages for both the Store and Auction applications. The developers made every effort to move functionality from IIS to the other servers.

The Database Server provides shopper, product, and pricing information as well as the Commerce tables for shopping baskets and receipts.

The Web Server and the Database Server share the same hardware unit to reduce processing delays.

The Membership Server provides the Membership Directory and Membership COM objects. Most requests to the Personalization & Membership database happen when the shopper first hits the site.

The Great Plains Integration Server will house (Phase 3) the connectivity objects for the Great Plains Software (GPS) system, allow batch data exchange between GPS and the Database Server, and contain the SQL Server database used to import IIS logs and run Usage Analysis reports. It will be the only production server communicating from the DMZ to the corporate network.

The production Web site is summarized in the diagram below.

Shown outside the production site are the Development and Intranet servers, used to make programming changes and provide a staging location for testing them, and the back-end Great Plains accounting system.

Not shown is the server used for backup/redundancy purposes. Data is replicated to this server from the Web Server and Database Server.

Implementation

Application implementation was in three phases. At the time of writing this whitepaper, Phase 1 had been completed, and Phases 2 and 3 were in planning and development. Except for the Auction, the same development team will implement all three Phases.

Phase 1

• Implemented the Store, with the capability to handle multiple divisional stores. Additional stores can be added without changes to code. 

• Recognized past shoppers and welcomed them with a personal message. 

• Provided a mechanism to migrate the registered shoppers from the old store. Used existing product data stored in SQL Server 6.5. 

Divided products into categories searchable from the home page across all stores. Added capability for unlimited attributes per product.

• Implemented a secure checkout page. 

• Integrated the Microsoft Wallet. 

• Enabled emails to shoppers for on-order and shipping confirmation. 

• Created a 'Buy Now' feature, with links from other Universal sites. Collects the shopper's address and payment information. Allows shoppers to buy a product without forcing them to register with the site. 

• Allowed shoppers to edit their own profile. Differentiated between multiple registered shoppers sharing the same computer at home. 

• Created a Site Usage Analysis capability using tools provided by Site Server 3.0. 

• Built the Auction Store using the Auction Component of Site Server 3.0.

• Implemented Online Chat for Auction shoppers. 

Phase 2 will, when completed:

• Allow shoppers to request their password over email if they forget it. 

• Introduce shopper service options such as gift-wrap, personal greeting option, and online cards. 

• Enhance Search so a shopper can use multiple divisional stores, categories and prices as search criteria. 

• Implement a promotions capability. 

• Integrate with UPS shipment tracking. 

• Offer parental advisories for music and video titles. 

• Allow the shopper to view order history and order status. 

• Add support for WebTV. 

Phase 3 will:

• Implement personalized home pages based on known shopper preferences. 

• Add the ability to handle split orders in case the items ordered are out of stock. 

• Implement online chat for the Store (online chat for the Auction was part of Phase 1). 

• Implement online coupons, gift certificates, and their redemption. 

• Extend shopper help using a Universal property such as Woody Woodpecker or Crash Bandicoot as an on-screen agent. 

Include the capability for employee and vendor discounts.

• Integrate with Great Plains accounting software. 

Application Architecture

Key application features are the Order Processing Pipeline, the Auction, Product Search, the integration of other technologies such as UPS shipping and Great Plains accounting, Membership, and Security.

The implementation of the functions is described in detail below.

Key application features 

Data Access

The operation of the Store and Auction is controlled by 41 tables:

• Auction tables define auction features, and include auction item details, bid information, credit card data, and consignor details. 

• Fulfillment tables contain shipping rates and methods, state and country/region lists, and order and receipt information. 

• Product tables contain product and product category information, search attributes, and promotion details. 

• Store tables include store and division information, product categories held by a store, and the shopping basket. 

• System tables define frame types, message codes, and image files. 

Divisional stores send data, such as product images, prices, and descriptions, offline to the Store. Table data is entered online and managed by Store administrators.

Store And Auction tables 

	Table Name	Purpose
Auction	AuctionItem	Auction Item details.
	AuctionBid	Auction Table. A record is created in this table with the bid information when a registered user bids for the first time in an auction. The bid information is then updated for any subsequent bids by the same user for this auction.
	AuctionPing	Contains the bidder's credit card information. The bidding process checks to see if an entry exists for the current bidder and the credit card information shown in his profile. If an entry is not found, the credit card is validated and after successful validation, an entry is created in the table.
	AuctionProperties	Auction specific information e.g. charity, feature products and preview details.
	Consignor	Auction consignor information.
	MiscAuctionInfo	Stores miscellaneous auction product information e.g. Consignor Percentage, Charity Percentage and Buyer Premium.
Fulfillment	Country	Contains country/region names, their respective two character and three character codes, and UPSZone codes used to calculate international shipping charges.
	DistributionCenters	Contains Universal distribution centers that ship products to customers
	Receipt	Stores the order information after a successful purchase is made. This table has the subtotal, shipping & handling charge, tax amount and other order level information.
	ReceiptBillTo	Stores the bill-to address for each order in the orders table.
	ReceiptItem	Stores line item data for each order in the orders table.
	ReceiptShipTo	Stores the ship-to address for each order in the orders table.
	ShippingFlatFee	Contains products that have flat fee shipping rates.
	ShippingHandling	Populated by the Fulfillment Pipeline with the line item information for the current order being placed. The Store Procedures used to calculate the Shipping and Handling rates for the order read this table.
	ShippingMethod	This table contains the shipping methods supported, e.g. ground & 2-day air.
	UPSShippingRates	Domestic shipping. Contains UPS shipping rates based on zone, product, and service type.
	UPSZones	Domestic shipping. Contains UPS zone codes for a combination of origin zip code and destination zip code.
	USStates	Contains state descriptions and codes.
Product	Category	Master table holding Category information.
	Product	Contains Store and Auction products for all the divisional stores. Products can be made active or inactive.
	Product_Variant	Contains all product variants. There should be at least one variant record in this table for each product in the product table.
	ProductCategory	All products in the Store are categorized. This table is used to retrieve products in a category. A product may appear in multiple categories.
	ProductSearchAttributes	This table contains the search attribute values for a product used by the search routines. Any number of attribute values can be associated to a product.
	Promotion	Site Server 3.0 table to set up and configure promotions on the site. Promotions can also be turned off and turned on any time.
	PromotionType	This table stores all promotion types supported by the site, e.g. sale promotion, price promotion, cross-sell.
Store	Basket	This table stores items added to the cart by a user.
	Division	Contains division information. Every product is associated with a division and its distribution center.
	SearchCriteria	The advanced search page displays these criteria to allow a more refined search. Criteria can be added dynamically.
	Store	Contains all the stores information.
	StoreCategory	Contains all the categories of products a store carries.
	StoreContentFrameAsset	This table contains all the assets that go in the content frame for an active store template.
	StoreContentTemplate	This table contains all the active content frame templates for a store. When a user navigates between different stores in the tree, the content frame changes depending on the template setup.
	SuperStore	This table holds all Superstore information. Superstores is the highest in the hierarchy of Superstores, Stores, Categories, Products.
	SuperStore_Store	This table contains all the stores under a Superstore.
	SuperStoreTemplate	This table stores a Superstore's active template.
System	Asset	All images used in the site are stored as assets. The asset table contains e.g. name, size, and the actual physical location.
	AssetType	Stores different asset types e.g image, text etc.
	Counter	Table containing the next id values to be used when inserting data in a table. The Counter table holds the table name and the next id value to be used.
	FrameType	This table contains the different frame types within a template. There are four main types of frames. Logo, Navigation, Tree and Content Frame.
	Message	Stores the Pipeline error message codes and descriptions.
	TemplateFrames	There are multiple templates for a frame setup, only one is active at a time. This table stores template data e.g. template name, stylesheets used for a frame.

Order Processing Pipeline

The Order Processing Pipeline is implemented using a custom blend of 5 pipelines: Product, Basket, Fulfillment, Register, and Purchase. The traditional Plan pipeline has been split into Basket and Fulfillment pipelines for better performance and more granular maintenance.

The 5 pipeline files are:

• Product.pcf–computes unit and sale prices for individual products offered to the shopper. 

• Basket.pcf–computes purchase subtotal, which includes product promotions. 

• Fulfillment.pcf–computes purchase total, which includes shipping, handling, and tax totals. 

• Register.pcf–provides payment authorization. 

• Purchase.pcf–manages payments, receipts, and transactional integrity. 

Stages and components for the 5 pipeline files 

Stage	Product.pcf	Basket.pcf	Fulfillment.pcf	Register.pcf	Purchase.pcf
Component
Shopper Information
DefaultShopperInfo	X	X
Product Information
QueryProdInfoADO (g_Product)	X	X
RequiredProdInfo	*X1	*X
Order Initialization
RequiredOrderInit		*X
Order Check
RequiredOrderCheck		*X
Item Price
DefaultItemPrice	X	X
RequiredItemPrice	*X	*X
Item Adjust Price
SaleAdjust	X	X
RequiredItemAdjustPrice	*X	*X
Order Adjust Price
DBOrderPromoADO (g_Promotions)		X
RequiredOrderAdjustPrice		*X
Order Subtotal
DefaultOrderSubTotal		X
RequiredOrderSubTotal		*X
Shipping
SQLItemADO (i_ShippingHandling)			X
TableShippingADO (g_Shipping)			X
Scriptor (_shipping_total)			X
RequiredShipping			*X
Handling
TableHandlingADO (g_Handling)			X
SQLOrderADO (d_ShippingHandling)			X
Scriptor (_handling_total)			X
RequiredHandling			*X
Tax
Scriptor (_tax_total)			X
RequiredTax			*X
Order Total
DefaultTotal			X
RequiredTotal			*X
Purchase Check
ValidateCCNumber				X	X
Payment
CyberCash MtsDirectCreditPayment				X	X
RequiredPayment				*X	*X
Accept
SaveReceipt					X
SQLItemADO (i_ReceiptItem)					X
SQLOrderADO (i_ReceiptShipTo)					X
SQLOrderADO (i_ReceiptBillTo)					X
SQLOrderADO (d_Basket)					X

¹ X = hidden pipeline component that is only viewable when the pipeline editor is in enhanced mode (pipeeditor –e).

Implementing the Auction

The Auction is based on the Auction Component of Microsoft Site Server Commerce Edition. For business reasons, implementation includes enhancements to the Auction Component's bidding rules, and a custom Automatic Settlement process.

The Auction Component offers two bid types, but all bids for an item must be of the same type. From Phase 3, the Auction will offer shoppers more than one bid type per item.

A custom Automatic Settlement process was developed to track the final highest bidder and get them to pay promptly for what they had won. Some winning bids may not result in sales because of credit card charge failures, even though credit cards are checked online when shoppers register to bid.

The Automatic Settlement process is written in the Visual Basic® development system using the Internet Transfer Control. Initiated via the Task Scheduler on the IIS server, it calls an ASP page that checks the database for closed Auctions and processes them in turn.

The Automatic Settlement process sends losing bidders a "you are outbid" e-mail with a link back to the item they've been outbid on.

It sends a "you are the winner" e-mail to the final highest bidder, and checks out a shopping basket containing the winner's item. Basket checkout follows the same Product Pipeline as a Store purchase, using the Auction bid price instead of the Store price. The Auction shopper is unable to modify the bid quantity shown in the basket.

Serttlement completes automatically if there are no payment problems. The winner's credit card is charged for the amount of the winning bid, the item is shipped to the winner, and an email confirming shipment is sent. If the charge to the credit card fails after two attempts, an email with a shopping basket link is sent requesting action to settle the account. A week's grace period is allowed before the winner is contacted by phone.

All bidders are imported into the Auction database, which is shared with the Store. Mass emails to registered shoppers are sent directly from the Auction and do not have to be ported to another device (such as List Serv) for distribution .

Implementing Enhanced Product Search

The Store will offer three types of search based on unlimited search attributes, e.g. a Babe Plush Toy could have the attributes Toy, Plush, Babe, Soft, Collectible, etc.

The three search types will be implemented in SQL Server 6.5, and available from any shopper page. Search results are displayed in a new pop-up window.

• Search Type 1 searches all the divisional stores from the Home Page, as shown below.

• Search Type 2 searches a divisional store using the item description or the Alphabet Bar.

• Search Type 3 searches one or more divisional stores by category and price range. This advanced search will use table-driven parameters stored in SQL Server 6.5. The site administrator will be provided with a tool to manage these parameters. 

Integrating Other Technologies

The Store integrates six technologies with Site Server Commerce:

• CyberCash 

• Net Gravity 

• Live Picture 

• UPS Shipping 

• Microsoft Wallet 

• Great Plains Dynamics.Commerce 

CyberCash

CyberCash provides online credit card processing via the Internet. The Store uses an integrated Order Processing Pipeline (OPP) component, supplied by CyberCash's Cash Register 3, in the Purchase pipeline to provide payment authorization.

Care had to be taken when implementing CyberCash to select a version that would participate in the transactional pipeline. The version 1.0 first implemented for the Store did not always allow credit card charges to be backed out if a purchase was cancelled late in the Pipeline.

Net Gravity

Net Gravity provides advertisements from a standard Net Gravity server, based on parameters provided by the Store. No customization was required.

Live Picture

Live Picture provides zoom in/out for the Store's online product images. Universal intends to develop for the future a viewer with 360º rotation capability. The intent is to provide this viewer for browsers with JAVA or HTML capability.

UPS Shipping

Integrating the UPS system is intended to reduce clerical errors in writing shipping labels, and will allow UPS domestic and international shipping rates, codes, and parcel tracking numbers to be imported back to the Store.

This is expected to reduce the number of returns due to bad shipping address. Allowing the shopper to track their own packages should reduce the number of calls to the shopper service department.

UPS systems in the distribution warehouse will be connected to Universal Studios via an ISDN link. Every hour, an extended stored procedure, written in Microsoft Visual C++® 6.0, will create a delimited flat file containing order and ship-to information.

Order and ship-to flat file contents 

OrderID	ShiptoName
BilltoName	ShiptoAddress
BilltoAddress	ShiptoState
BilltoState	ShiptoZip
BilltoZip	ShiptoPhone
BilltoPhone

This flat file will be exported to the UPS shipment system that will generate and print the address labels, and assign a shipping tracking number to each OrderID. The tracking number and OrderID will be exported back. To confirm shipment, an email will be sent to the shopper containing a link to the shipping tracking page of the UPS website.

Microsoft Wallet

Microsoft Wallet provides a shopper-friendly and secure interface for storing credit card and address information for shoppers with browsers that support ActiveX controls. An alternative HTML-based interface was provided for shoppers with browsers that do not support ActiveX controls, such as WebTV, or for shoppers not wishing to use Microsoft Wallet.

California state tax applies to purchases by California residents at the Store. Since Microsoft Wallet does not provide a drop-down list for the state field, a surprisingly large number of different abbreviations for "California" were used by different shoppers. It proved easier and more accurate to determine California residence from the supplied ZIP code rather than from the state field.

Universal will ship internationally to selected countries serviced by UPS. This list of selected countries differs from the drop-down list offered by Microsoft Wallet, and the version of Microsoft Wallet available for the Store does not permit modifications to its country/region list. The integration with UPS will allow the Store to import an up-to-date list of UPS countries, and reduce the number of shipments delayed because of invalid country/region names.

Great Plains Dynamics.Commerce

Great Plains Dynamics.Commerce is a set of Internet technologies that provides connectivity between Great Plains Dynamics C/S+ for Microsoft SQL Server 7.0 and Microsoft Site Server Commerce Edition.

The integration of Great Plains Dynamics.Commerce will take place in Phase 3 of the Store, and so will require interfacing with the OPP components that went live in Phase 1.

Because of the high functionality of these Phase 1 components, there are implementation differences between Site Server Commerce and Great Plains, especially for the OPP product catalog, and membership and user profiles.

Great Plains Dynamics C/S+ software has specific pipeline components that plug into the OPP to aid integration with Great Plains Dynamics.Commerce. Additional RPM objects are planned by Universal to complete the integration.

Personalization and Membership

The Store's implementation of personalization and membership allows a registered shopper to:

• Receive a customized home page. 

• Edit their own profile. 

• View a history of their own transactions. 

• Review their current order status and track their UPS packages. 

• Retrieve a forgotten password.

• Receive e-mail about their Auction bids. 

Personalization is based on a Site Vocabulary that defines shopper and content attributes. The Site Vocabulary is stored in the Membership Directory and can be used by the Personalization and Membership Rule Builder, Knowledge Manager, and Tag Tool.

The Membership Directory uses Site Server's Membership Authentication and Automatic Cookie Identification. No modifications were made to the directory structure (the Directory Information Tree). Thus, all anonymous users are maintained within the o=USOnline, ou=Members, ou=AnonymousUsers container. All registered members are maintained within the o=USOnline, ou=Members container.

The Store's membership extensions are based on:

• Site Vocabulary–contains categories of member attributes. 

• Online Registration–the membership registration process includes customizations for personalization, automatic cookie identification, and multiple membership levels. 

• Profile Management–each member can directly update his or her membership profile online. 

• Login/Logout–a registered member's profile identification cookie can be regenerated from the server and placed on any client. It can also be deactivated from the server. 

Site Vocabulary

The Site Vocabulary is a hierarchical set of shopper and content attributes, such as favorite characters, leisure time, and gift buying. Member attributes are constrained to values in the Site Vocabulary at registration.

Online Registration

Registration builds a membership profile from shopper-provided information such as identifier, password, favorite merchandise characters and buying habits. The profile is stored in the Membership Directory. A sample Membership Registration Form is shown below.

Membership profile information is used widely within the Store, for example to pre-populate shipping and billing information during the online purchase process. In addition, each member's optional personal preferences, such as favorite characters, will be used to construct a personalized home page. Currently, each member gets a 'Hello <First Name>!' message to show the membership account is active.

An unregistered shopper gets a client-side cookie that includes a unique anonymous identifier. The Store can use this anonymous identifier to track use of that client workstation. When a shopper registers with the Store, their membership account is upgraded from anonymous to registered. The anonymous identifier still on the client can be used to retrieve a profile.

Registration for the Auction requires additional validated credit card information. This is required before a member can place an auction bid, rather than at the time of purchase.

Profile Management

A registered member can update their profile online. This reduces the maintenance cost and routine administration effort needed to change a shopper's password, address, etc.

Both the online registration and profile management sections of the Store are based on Site Server's Design Time Controls (DTC) for membership (i.e., the Membership Header, Membership Attribute, and Membership Footer controls).

Explicit Login / Logout

By implementing explicit login/logout, the Store offers shoppers personal membership, rather than membership based on a client workstation cookie. This enables, for example, household members sharing a workstation to have separate Store memberships.

A member can explicitly log into the web site, which recreates his or her original anonymous identifier from the required security credentials and sends it to the client within a cookie.

A member can explicitly log out to protect their membership account. This destroys the cookies. All membership information is securely maintained on the server ready for the next login request.

Interface for Explicit Login 

Member Login ASP Code Example

The following code fragment, taken from login.asp, is used to log a member into the system with MemberID and Password supplied via the login interface, above. The SetUserName method on the Active User Object (AUO) is used to attempt the login process

Dim MSCSMember
Set MSCSMember = Server.CreateObject("Membership.UserObjects")
Call MSCSMember.SetUserName(MemberID)
If Not IsEmpty(MSCSMember.Get("userPassword")) Then
Set MSCSMembership = Server.CreateObject("Membership.VerifUsr")
If MSCSMembership.VerifyPassword(MemberID, Password) Then
Call MSCSMembership.IssueCookie("SITESERVER", "GUID=" & MSCSMember.Get("guid"))
Call MSCSMembership.IssueCookie("MEMUSER", MemberID)
Call MSCSMember.Put("lastVisit", GetGeneralizedDateTime())
Call MSCSMember.SetInfo()
Set MSCSMember = Nothing
End If
Set MSCSMembership = Nothing
End If

A successful login sends a new cookie back to the member. The new cookie contains the GUID and MemberID associate for that Member within cookies called SITESERVER and MEMUSER. At the server, the member's last successful login timestamp is updated with the current date and time.

Member Logout ASP Code Example

Registered members who explicitly logout get their client-side cookies deleted to protect their membership account.

The Logout routine below removes the cookies associated with the currently active shopper. These include the SITESERVER and MEMUSER cookies created during the login process, and the NGUserID and MemRightsChanged cookies used by Site Server.

Dim MSCSMembership
Set MSCSMembership = Server.CreateObject("Membership.VerifUsr")
Call MSCSMembership.CancelCookie("SITESERVER")
Call MSCSMembership.CancelCookie("NGUserID")
Call MSCSMembership.CancelCookie("MEMUSER")
Call MSCSMembership.CancelCookie("MemRightsChanged")
Set MSCSMembership = Nothing
Call Response.Write("<SCRIPT LANGUAGE=""JavaScript"">window.top.location.href=""" & ClientURL & """;</SCRIPT>")

Security

The Store's security features were based on Site Server's Active User Object (AUO).

Each server is configured to allow only anonymous access over HTTP. Site Server Membership stores shopper credentials and authenticates each shopper's login. Once inside the Store, shoppers can access resources under the Windows NT operating system machine accounts. Local machine accounts offer more security than the domain model by limiting the security contexts between servers.

When an unregistered shopper accesses an IIS-based server anonymously (i.e., with no Windows NT shopper credentials), they access the IIS anonymous account. When a shopper logs in through Site Server Membership, they are mapped to a different account on the server.

Shopper rights on each server have been restricted to provide extra security, as shown here.

Right	Grant To
Access this computer from network	Administrators Authenticated Shoppers Web users Site Server Admins
Force shutdown from a remote system	<Empty>
Log on locally	Administrators Authenticated Shoppers Backup Operators Web users Site Server Admins

Firewall

All Store servers are member servers in the DMZ (de-militarized zone), separated from the Universal corporate domain by a firewall. Administrative user accounts are located on the individual servers. The developers chose not to use a DMZ-based domain model because of security requirements.

The firewall separating the DMZ from the Internet is summarized below. It allows inbound access only on ports 80, 443 (SSL), and 8081 (Live Picture) and only to the IP addresses owned by the Web servers. This blocks shoppers from accessing any other computers on the DMZ.

In order to limit the possibility of a security breach on the corporate network, the firewall separating the DMZ from Universal's corporate network allows outbound access only from the Great Plains Software (GPS) Integration server.

This server controls GPS connections by housing objects that speak directly to the GPS system, and by running the batch processes that move data between systems. DCOM allows the Web servers to invoke objects on this server and retrieve their results. The GPS Server also collects IIS logs and imports them into SQL Server using the Usage Analyst.

Only the Staging server, which moves approved modifications to the Web servers, allows inbound access from the corporate network to the DMZ. The Staging server uses port 507 and is given access to the DMZ through the firewall.

Data Encryption

The Store implements a secure checkout page via a pop-up window or a direct-URL, using VeriSign's Secure Sockets Layer (SSL) technology.

SSL provides secure connection for purchase authorizations and logon pages using a Verisign certificate loaded on each of the Web servers. SSL is used to encrypt shopper passwords and credit card information. The following tasks were completed in order to implement SSL:

• Certificate loaded on the Web server. 

• SSL Port specified on the Web site in IIS settings. 

• Specific pages designated as requiring secure connection. 

• SSL port opened on firewall. 

The certificate may be loaded on any web server but will only work properly if that server is accessed using the certificate's common name (Store2.universalstudios.com). If a server is to be accessed using another name, another certificate is needed.

With a certificate loaded on a web server and the appropriate configuration in IIS, any page on the site may be accessed securely by simply specifying https:// instead of https:// in the browser address. Using the IIS Administration MMC, any page on a site may be set to allow only secure access.. If the page is not accessed via SSL, it will not be returned to the shopper.

Security for Administrative Users

For restricted areas of the site (e.g., administration) that require a higher level of application security, a combination of Site Server Membership security and Windows NT directory security was implemented for the Store.

Administrative users are presented with the following Sign In screen, part of Internet Explorer's Distributed Password Authentication feature. The supplied Member ID and Password must exist in both the Membership and the Windows NT Directory.

The following diagram summarizes how Membership Authentication, Windows NT Authentication and Windows NT Groups are implemented in the Store.

Services Disabled

To minimize the potential for hacking, the following services were either disabled or not installed:

Alerter	NetBIOS Interface	Spooler
ClipBook Server	Network DDE	TCP/IP NetBIOS Helper
Computer Browser	Network Monitor Agent	WINS Client
Messenger	NWLink NetBIOS
Net Logon	Simple TCP/IP Services—not installed

Application Objects

Application objects form a standard framework throughout the web site for individual pages to use. For example, a custom data recaching mechanism was developed to dynamically refresh the contents of several application objects based on data within files, databases, etc. A specific URL query string argument is used as the trigger.

The following application objects are initialized in global.asa upon system startup. Each object is used across the web site.

MSCSSite contains the overall site configuration for Universal's online store. It is initialized from the site.csc file during system startup and subsequently modified and extended during system operation. Properties of the MSCSSite application object are used to differentiate between development, testing, and production modes of Universal's online store based on the local server currently hosting the web site.

MSCSMessageManager contains the localized collection of application and system messages retrieved from the database during system startup.

MSCSDataFunctions performs localized data-type handling and value-range checking functions across the Store.

MSCSQueryMap contains the stored procedure interfaces used by the Order Processing Pipeline (OPP) and across the web site. A standardized function is used to execute any stored procedure within the MSCSQueryMap object, based on ActiveX Data Objects (ADO).

MSCSReceiptStorage determines how purchase order receipts are stored within the database.

MSCSPipeConfig contains the cached representations of all of the Order Processing Pipeline (OPP) configuration files.

MSCSPipeContext: The Order Processing Pipeline (OPP) uses the stored procedure interfaces defined in MSCSQueryMap to communicate directly with the commerce database. The MSCSQueryMap object is passed into the OPP as an attribute of the MSCSPipeContext object. The MSCSPipeContext object references application object and active shopper information. In addition to direct data access, all of this information is then available within the OPP via the MSCSPipeContext.

MSCSPredictor provides intelligent cross-selling functionality by suggesting complementary products to a shopper during their shopping process.

ExecuteQuery Function: The MSCSQueryMap application object defines the stored procedure interfaces used by the Order Processing Pipeline. For stored procedures executed within ASP pages, a global function called ExecuteQuery is used.

This extracts the requested stored procedure interface from the MSCSQueryMap object, populates its parameter list, executes the stored procedure using ActiveX Data Objects (ADO), and then returns the resultant recordset.

Listing of the Global Gunction ExecuteQuery

FUNCTION ExecuteQuery(Query, Parameters)
Dim ADOConnection, ADOCommand, ADORecordset, SQLCommand, Parameter
Set ADOConnection = Server.CreateObject("ADODB.Connection")
Set ADOCommand = Server.CreateObject("ADODB.Command")
Set ADORecordset = Server.CreateObject("ADODB.Recordset")
SQLCommand = MSCSQueryMap(Query).SQLCommand
For Each Parameter In Parameters
If IsNumeric(Parameter) Or Left(Parameter, 1) = "'" Then
SQLCommand = Replace(SQLCommand, "?", Parameter, 1, 1)
Else
SQLCommand = Replace(SQLCommand, "?", "'" & Parameter & "'", 1, 1)
End If
Next ' Parameter
ADOCommand.CommandText = SQLCommand
Call ADOConnection.Open(MSCSSite.DefaultConnectionString)
Set ADOCommand.ActiveConnection = ADOConnection
Call ADORecordset.Open(ADOCommand)
Set ADOConnection = Nothing: Set ADOCommand = Nothing
Set ExecuteQuery = ADORecordset
END FUNCTION

User Interface Components

The Store's user interface components include the Home Page and divisional store pages.

The Home Page links to each divisional store. It also links to six product categories–Gifts, Clothing, Collectibles, Toys & Stuffed Animals, Software and Theme Park Tickets. Selecting a category launches a product listing window. The Store Home Page does not use frames.

Divisional store pages use frame sets to minimize the amount of HTML transmitted to the shopper when a page changes. The frame set, created when the shopper selects a divisional store, includes logo, navigation, content, and tree frames:

Logo Frame: Displays the Store logo.	Navigation Frame: Includes the buttons for Home, Help, Cart, Search, and Check Out. A drop-down enables switching between divisional stores. User Identification is also in this frame.
Tree Frame: Displays links to all the Stores, Categories and Products. Clicking an item in this frame changes product details in the Content Frame.	Content Frame: Displays product details based on the Tree Frame selection. If a shopper selects a Store or a Category in the Tree Frame then four products from that Store or Category are randomly displayed . If a shopper selects a product, its details are shown here.

The Tree Frame was orginally implemented with a Java Tree Control populated with Stores, Categories and Products. This Java Tree Control has since been replaced by an HTML-based Tree Control written in ASP, mainly to support browsers that lack Java.

The Auction differs from a divisional store because of the need to register the shopper before bidding. In the Auction frame set, the Content Frame shows a list of the five currently most popular auction items. The shopper may bid on an item selected from this list, on a specially featured item, or on an item selected from the Tree Frame. The shopper is then presented with the screen where bidding takes place.

Development Tools and Resources

The following tools and resources were used to implement the Store. Since a highly aggressive deployment schedule was required by Universal to accommodate the holiday shopping season, Universal decided not to incorporate custom COM components into the initial Store design. Integrating custom COM components is considered a system enhancement at a later stage.

Tools

• Visual Basic development system 6.0 and SP1 (COM Components) 

• Visual InterDev™ web development system 6.0 and SP1 (ASP and HTML) 

• Visual C++ 6.0 with SP1 for the development of extended stored procedures. 

• Visual J++® 6.0 development system with SP! For the Navigation Component 

• FrontPage® 98 site creation and management tool 

• SQL Server 6.5 and SP4 + Hotfix 297(Stored Procedures and databases) 

• Visual SourceSafeTM version control system (Source Code Control) 

• Homer and Internet Monitor v3.0 (Performance and Load Testing) 

• ActiveX Data Objects (Data Access to SQL Server) 

• Dynamic web pages (ASP, HTML, & Visual Basic, Scripting Edition) 

• Client side Java Controls (for building the tree control) 

• Windows NT Server 4.0 with Service Pack 4, including MDAC 2.0SP1, Internet Explorer 4.01 SP1, WBEM, and the Security Configuration Editor (SCE) 

• GPS Dynamics.Commerce (GPS Integration) 

• CyberCash (CC validation) 

• LivePicture (Image manipulation) 

• Internet Information Server (IIS) 4.0 with Service Pack 1 

• Microsoft Transaction Server (MTS) 2.0 

• Site Server 3.0, Commerce Edition with Service Pack1 and Auction Objects 

Configuration

Software

Web Server Components

The primary function of the Web server is to process ASP pages for the shopper interface.

Web Server Components 

Component	Version	Explanation
Windows NT Server	4.0 SP4	With SCE, MDAC 2.0 SP1, and WBEM
Internet Information Server (Option Pack)	4.0	With SP1 for the option pack
Transaction Server (Option Pack)	2.0
Site Serve Membership	3.0	Required for mapping of IIS to Membership. All Membership components and services are on the Membership server.
Commerce Server	3.0	Commerce provides necessary components as well extensions to the Site Server administration MMC specific to Commerce Server.
Universal ASP pages

Web Server Configuration 

Location	Setting	Value
Server Properties	Max ASP Files Cached	1,200
Site (USI) Properties Web Site Tab	IP Address	192.251.67.187(USINTAP37LAX)
	TCP Port	80
	SSL Port	443
	Connections	Unlimited
	Enable Logging	Yes
	Active log format	W3C Extended Log File Format
Site (USI) Properties ISAPI Filters Tab	Name:	Auth Filter
	Executable:	Authfltr.dll
	Priority:	Low
Site (USI) Properties Home Directory Tab	Local Path
	Access Permissions	Read
	Content Control	Log Access
	Permissions	Script
Site (USI) Properties Home Directory Tab Configuration Application Options Tab	Enable session state	No
	Enable buffering	Yes
	ASP Script timeout	180 seconds
Virtual (USI) Directory Properties Virtual Directory Tab	Access Permissions	Read
	Content Control	Log Access
	Permissions	Script
	Name	<blank>
Virtual (_mem_bin) Directory Properties Virtual Directory Tab	PermissionsContent ControlAccess PermissionsContent Control	ExecuteLog AccessReadLog Access
	Content Control	Log Access

Database server components

The server running SQL Server contains the databases for interaction with the Store.

Database Server Components 

Component	Version	Explanation
Windows NT Server	4.0 SP4	With SCE, MDAC 2.0 SP1, and WBEM
SQL Server	6.5 SP4 HF297	SP4 and hotfix 297 are required for and included with Site Server.
Transaction Server (Option Pack)	2.0	There are no MTS components on the servers running SQL Server, but this provides the updated version of MSDTC.

Database Server Configuration 

Location	Setting	Value
SQL Server Setup Network Support	Network Protocols	Named Pipes TCP/IP Sockets
	Port Number	Not 1433
SQL Enterprise Manager Configuration	Locaks	15000
	Memory
	User Connections	10

Membership Server Components

The Membership server includes the membership directory (SQL Server database) and all COM objects supporting membership activities (in MTS).

Membership server components 

Component	Version	Explanation
Windows NT Server	4.0 SP4	With SCE, MDAC 2.0 SP1, and WBEM
SQL Server	6.5 SP4 HF297	SP4 and hotfix 297 are included with Site Server.
Microsoft Transaction Server (Option Pack)	2.0	Contains all Membership objects called by the Web servers.
Site Server, Membership	3.0	All Membership components and services.
Membership Directory		A SQL Server database containing user credentials and accessed through the LDAP service.

Great Plains Integration Server Components

The Great Plains Integration server executes COM objects for Great Plains Software (GPS) connections. This includes the Dynamics.Commerce objects for communication with GPS. This server is the only one in Universal allowed to communicate from the DMZ to the corporate network. The Web server calls MTS objects on this server to access host information.

This server also drives the batch process for data transfer with GPS. SQL Server is installed locally in order to perform any required processing before being placed into production. It contains the usage database and runs scheduled imports and reports.

Great Plains Integration Server components 

Component	Version	Explanation
Windows NT Server	4.0 SP4	With SCE, MDAC 2.0 SP1, and WBEM
SQL Server	6.5 SP4 HF297	SP4 and hotfix 297 are included with Site Server.
Internet Information Server	4.0	With Service Pack 1
Transaction Server (Option Pack)	2.0
SQL Server	6.5 SP4 HF297
Site Server, Usage Analysis	3.0
GPS Dynamics.Commerce		Required for communication with GPS.

Hardware

Web and Database servers: Both servers share the same hardware unit to reduce processing delays and bandwidth requirements. The unit is a Compaq Proliant 7000 with the Pentium II Xeon Processors. Quad Pentium Pro 400Mhz, Memory: 1 Gigabytes, HD: SCSI-Raid Array 28 Gigabytes. The Database server has SQL Server 6.5 configured to use two processors and 512 MB of RAM.

The Membership server runs on a Compaq Proliant 2500 Pro 200Mhz Processors. Dual Pentium Pro 200Mhz, Memory: 512 megabytes, HD: SCSI-Raid Array 12 Gigabytes

An additional backup server runs SQL Server 6.5 and IIS 4.0 on a Compaq Proliant 2500 Pro 200Mhz Processors. Dual Pentium Pro 200Mhz, Memory: 296 megabytes, HD: SCSI-Raid Array 12 Gigabytes. All the Store and Membership data is replicated to this box.

Operations

Startup

A configuration file site.csc is used for system startup and site recovery. It contains site configuration information such as data source and host names. On system startup or application restart, the site.csc file is read into memory and provides the basis for all data access as well as all secure and non-secure URL references.

Backup/Restore

The servers dump to disk on a daily basis. Seagate Backup Exec is used from a remote DLT Tape system to back up all specified files from the servers. This includes all html, graphics, asp, dll's, and dumps.

Administrative Tools

Administrative tools are provided for:

• Merchandising–lot creation, categorization, start/end dates.

• Bidder–bidder history, unsettled items, editable address/e-mail information. 

• Transactions–unsettled items, orders, registered bidders. 

• System–open/close site, edit pipeline 

E-mail reminders sent to bidders are editable by the administrator and sent from the bidder area of the admin.

Staging

After testing on the development server, an application (ASP, HTML, COM objects, and stored procedures) is transferred to the staging server using CRS (Site Server Content Replication Services). Staging also involves manually copying the DLLs, creating the MTS packages, and transferring updates of stored procs and tables.

A QA team performs further testing and stress analysis using the staging server. When the new content is approved, it is moved to the production servers. The following table shows the servers involved in the Universal Store publication process.

Server	Role	Directory
NEWSTORE	Development	d:\inetpub\wwwroot\
USINTAP36LAX	Staging	d:\inetpub\wwwroot\
USINTAP37LAX	Production	f:\wwwroot\

The staging server USINTAP36LAX is configured as part of the Store and is also accessible to the Universal corporate network. The production servers are not accessible from the corporate network. The router separating these networks has been configured to allow USINTAP36LAX to communicate with the production servers over port 507 which is used for Site Server Publishing.

Usage Analysis

Site Server Usage Analysis imports web log files into SQL Server for access by Report Writer. It also creates a usage database, implemented as part of the previous online store.

Importing Log Files

Log files are stored on the Web server in the f:\weblogs directory. This directory is shared with the FTP share name weblogs so that it may be accessed with the FTP ftp://usintap37lax*/weblogs*. The log files are imported into the Analysis usage database, which keeps logs for a month.

Log files from a single day are processed as one batch. A job scheduled on USINTAP40 imports the log files each night at 2am. This must be run as an AT command on Windows NT. If the batch file is set up under the Site Server Scheduler of the Windows NT task scheduler, the batch process may fail due to a security context issue. The scheduled job was created by hand instead of using the automated tools to ensure the process is logged.

The imported log may be verified either by checking the log file or by viewing the Import History in Usage Import. If the automated process fails, log files can be manually imported using Usage Analysis. Multiple days of log files can be imported at one time.

Parameters for the daily import job are shown below:

Sample Log Import Parameters 

Server	USINTAP40LAX
Scheduled Task	Daily Log Import
Batch File	F:\weblogs\analysis\DailyImport.bat
Log File	F:\weblogs\analysis\messages\ReportUI$1.log
Schedule Time	Every day at 2:00 AM

Log files are removed from the Web servers monthly to ensure that they have been properly imported into the Analysis database. They are archived to tape.

Deleting Usage Data

The initial two months of Universal usage produced 200 MB/day of data in the usage database. Database size is monitored and data deleted at regular intervals to ensure that space does not run out. Removing data from the database can take significantly longer than adding data. Site Server's online documentation describes this process.

Generating Usage Reports

Much of the log analysis is done by Usage Import as it brings in log files. Usage Import also imports batches of files and can backout entire batches as opposed to individual files.

Report Writer in Site Server produces weekly executive summary reports based on the Usage Import data. The reports generate HTML files posted on an Intranet server, providing up-to-date statistics accessible from any corporate network client. The reports include:

• Number of requests/visits by date. 

• Number of requests/visits by hour (average for entire reporting period). 

• Top shoppers. 

• Top pages. 

• Number of orders by date. 

• Number of new shoppers registered. 

• Page views. 

• Site usage profile reporting—which stores and products were accessed. 

Since a single shopper session may occur across both servers, the importing process analyzes all files from a particular day as one set of data. This avoids over-counting shopper visits.

Filters may be placed on a report or a section of a report using a relative date to allow running a report for a specified period without modification. For example, a report titled Weekly Universal Usage report uses a filter called LastWeek to report all usage from the previous week. It is run every Sunday evening.

Reports may be scheduled to run automatically using the Scheduler in Report Writer (this scheduler may be accessed either from Report Writer or Usage Import).

Currently, Commerce-specific data, including most popular products and vendors, is not included in the usage reports. Number of orders is calculated by counting orders stored within the Commerce Server orders table. A way is being explored of logging detailed information on product queries and purchases.

Analyzing Shopper Searches

When a shopper fills out a search form, a query string is generated that passes the shopper's information to the appropriate page. This information, stored in the log files, is valuable for reporting. For example, a sample query string generated from a quick search is as follows:

/navigation.asp?SSID=5&SID=1&PID=4000

where SSID=Super Store ID, SID=Store ID, and PID=Product ID.

In order to use query string information in reports, Usage Import must be configured in the following order:

Import Configuration 

URLs containing query strings to import	Query parameters to parse
/navigation.asp /superstore.asp /productdetail.asp /content.asp	SSID SID PID

Performance Monitoring/Tuning

Microsoft Homer was the primary tool used for load and performance testing. Homer scripts are auto-generated from actual IIS site logs. Universal's primary focus was on:

• Capacity–defined as the number of shoppers that can be serviced at any one time. 

• Scalability–the ability to grow capacity by adding another processor or server. 

• Stress–how many shoppers can be handled before things break. 

• Performance–how fast a server or application runs. 

Sample Homer script generated from IIS logs 

Sample Homer Output Report

==========================================================================
Report name: 12/20/98 4:34:29 PM
Run on: 12/20/98 4:34:29 PM
Run length: 00:00:46

Homer Version: 0.90.0.116

Number of test clients: 1

Number of hits: 4
Requests per Second: 0.80

Socket Errors
--------------------------------------------------------------------------
Connect: 2
Send: 0
Recv: 0
Timeouts: 0

Script Settings
==========================================================================
Server: localhost
Number of threads: 2
Sockets per thread: 1

Test length: 00:15:00
Warmup: 00:00:00
Cooldown: 00:00:00

Use Random Delay: Yes
Min Delay Time: 20
Max Delay Time: 40

Clients used in test
==========================================================================
localhost
Clients not used in test
==========================================================================
Result Codes
Code Description Count 
==========================================================================
404 Not Found 4 
Page Summary
Page # Hits TTFB Avg TTLB Avg 
==========================================================================
POST /homersamples/asp_post_te 0 0.00 0.00 
GET /homersamples/browser_test 0 0.00 0.00 
GET /homersamples/fileacc_test 0 0.00 0.00 
GET /homersamples/html_test.ht 0 0.00 0.00 
GET /homersamples/asp_cookie_t 0 0.00 0.00 
GET /homersamples/ad_test.asp 2 336.00 336.50 
GET /homersamples/Homer_BlackL 2 68.50 68.50 

Load Simulation

To validate the server capacity and configuration, the development team performed load simulation using InetMonitor 3.0, developing a script (see sample Test.txt below) to simulate a shopper performing common operations (based on log file data).

Loads simulated include logging on to the site, searching the product database, adding items to the shopping basket, viewing the basket, and finalizing a purchase. Although Homer/InetMonitor include several counters for monitoring the servers, Performance Monitor was used to monitor key counters on both the Web and SQL Server-based servers and for creating logs and measuring performance during the load simulation. This was due to technical personnel's familiarity with the tool and its ability to measure performance on the SQL Server-based servers.

Sample TEST.txt Script

REM InetMon3 Script Generator v1.0
GET url:/store2.universalstudios.com/
GET url:/store2.universalstudios.com/superstore.asp?SSID=5
GET url:/store2.universalstudios.com/Logo.asp?SSID=5&SID=0&CID=0&PID=0&Recache=0
&StyleSheet=%2FStyles%5Clogo%2Ecss&Tab=Default
GET url:/store2.universalstudios.com/Navigation.asp?SSID=5&SID=0&CID=0&PID=0&
Recache=0&StyleSheet=%2FStyles%5Cnavigation%2Ecss&Tab=Default
GET url:/store2.universalstudios.com/TreeList/Tree.asp?SSID=5&SID=0&CID=0&PID=0&
Recache=0&StyleSheet=%2FStyles%5Ctreestyle%2Ecss&Tab=Default
GET url:/store2.universalstudios.com/Content.asp?SSID=5&SID=0&CID=0&PID=0&
Recache=0&StyleSheet=%2FStyles%5CUniversalContentStyle%2Ecss&Tab=Default
GET url:/store2.universalstudios.com/navigation.asp?SSID=5&SID=121&StyleSheet=
/Styles/navigation.css
GET url:/store2.universalstudios.com/content.asp?SSID=5&SID=121&PID=0
GET url:/ipt:PopupExpressWindow('https://store2.universalstudios.com/xt_orderfor
m_additem.asp?redirectURL=shipping.asp&sku=1020900&quantity=1&price=300000')
;_https://store2.universalstudios.com/content.asp?SSID=5&SID=121&PID=0
GET url:/store2.universalstudios.com/xt_orderform_additem.asp?sku=1020900
&quantity=1&price=300000
POST url:/store2.universalstudios.com/xt_orderform_refresh.asp?PROPERTIES:0=1&Q1
=1&R1.x=54&R1.y=12
POST url:/ipt:OpenWindow('https://store2.universalstudios.com/shipping.asp?Remote
BuyNow=0')_https://store2.universalstudios.com/basket.asp?PROPERTIES:x=21&y=13
GET url:/store2.universalstudios.com/navigation.asp?Tab=Checkout&StyleSheet=
/Styles/navigation.css&SSID=5&SID=121&PID=0
GET url:/store2.universalstudios.com/basket.asp?SSID=5&SID=121&PID=0

Performance Counters

The following counters are used as key indicators of the health of the web application.

Object	Counter	Web Server	SQL Server	Other	Purpose
Processor	% Total Processor Time	X	X	X	Shows the time spent processing threads by all CPU's. If consistently above 90% indicates the test is too intense for the hardware. Add the 0 -x instances of this counter for multi-processor machines.
Physical Disk	% Disk Time	X	X		Requires "diskperf -y" at the command prompt. Shows the percentage of elapsed time that the disk is busy with read/write activity. If consistently above 80% may indicate a memory leak. Add the 0-x instances of this counter for multi-disk machines.
Physical Disk	Disk Queue Length		X		Shows the number of outstanding requests on the disk. Sustained queue lengths on one disk that are greater than 3 indicate a disk or memory problem, or that a SQL-based server is not set up correctly.
Memory	Page Faults/sec	X			Shows the number of times a virtual page was not found in memory. If consistently above 0 it indicates that too much memory has been allocated to an application, and not enough to Windows NT.
Memory	Available Bytes	X	X	X	Shows the total bytes of real memory available to the computer, less the bytes being used by running applications. Add to Memory:Committed Bytes to get the total amount of memory on the machine.
Memory	Pool Nonpaged Bytes	X	X		Shows pages used by the operating system that do not leave memory. This will increase with each process, but watch for gradual growth in this counter over a test run. This would indicate an application's repeated inadvertent opening of a file or some other object. Performance will suffer if this gets within 4 meg of Memory:Available Bytes.
Memory	Pages/sec	X	X		Shows how many pages are being moved to and from the disk to satisfy virtual memory requirements. If the server does not have enough memory to handle its workload, this number will be consistently high.
Memory	Committed Bytes	X	X	X	Shows the size of virtual memory that has been committed to the running applications. Add this number to Memory:Available Bytes to compute the total amount of memory on the machine. Committed bytes should increase as the test is ramping up, but it should remain fairly constant after that.
Server	Bytes Total/sec	X			Shows network activity, and how close the server's adapters are to being fully utilized. This is particularly useful for watching multi-homed servers.
System	Total Interrupts/sec	X	X		Shows the frequency with which this computer is handling hardware interrupts. This gives you a good idea of how busy the whole system is.
Object	Threads	X	X	X	Threads are the basic executable entity that can execute instructions in a processor. If this number continues to rise over time, open the Process:Thread Count counter and see which instance is creating all of the threads.
Process	Private Bytes - _Total	X	X		Shows the current number of bytes that all instances have allocated that cannot be shared with other processes. Make sure you select the _Total instance from the list. Select whatever other instances you suspect may be eating up memory.

Web Server Only 

Object	Counter	Purpose
HTTP Service	Current Connections	Shows the current number of connections to the service. This is the sum of both anonymous and non-anonymous shoppers. If this number is at or near 3000, this indicates that a web service is at full capacity.
HTTP Service	Maximum Connections	The maximum number of simultaneous connections to the HTTP Server. Use this number to determine the maximum level of stress a server has experienced during a test run. If this number is at or near 3000, the server experienced peak load at some time during the test.
HTTP Service	Bytes Total/sec	Shows the sum of bytes sent and received by the HTTP server. If this number is low it means that IIS is not transferring data at a reliable rate.
HTTP Service	Current NonAnonymous Users	The number of authenticated users currently connected to the HTTP Server. Use this number to determine how many membership broker connections the server is seeing. If this drops to 0 during an authenticated stress run, check for Sicily problems or missing permissions on the content.
HTTP Service	Not Found Errors	Shows the number of requests that could not be satisfied by service because requested document could not be found; typically reported as HTTP 404 error code to client. If greater than 0, check the stager for missing files or the server replication project.
Active Server Pages	Request Errors/Sec	The number of errors per second, including connection errors, compile errors, and runtime errors. If this number is ever greater than 0, something is wrong with the test scripts, server configuration, or ASP scripts.
Active Server Pages	Requests/Sec	The number of ASP page requests executed per second. This should have a 1-to-1 ratio with HTTP Service:Connections/sec. Use to provide an indication of how heavy the stress on the server is. Anything over 85 connections/sec is very heavy stress.
Active Server Pages	Not Found Errors	The total number of requests that occurred since the service was started. Use this number to determine a total of the hits the server experienced during a test.
Active Server Pages	Requests Rejected	The total number of requests not executed because the queue was full or there were insufficient resources to meet the number of hits that the server is getting. If this number is consistently above 0, the stress test is too heavy.
Active Server Pages	Memory Allocated	The total amount of memory currently allocated by Active Server Pages. Compare this number to Memory:Available Bytes and Memory:Committed Bytes to determine what percentage ASP is using. A ratio of greater than 50% during the test would indicate a memory leak in a Server Side Object.
Active Server Pages	Requests Queued	This should remain close to 0. If it ever reaches 460, clients are receiving the "server too busy" error. If this queue continues to grow as more stress is applied, the ASP page should be redesigned because it is too complicated for the given stress level.
Process	Private Bytes - inetinfo	Private Bytes - inetinfo is the current number of bytes the HTTP/ASP service has allocated that cannot be shared with other processes. If this number is consistently large, and growing, there is probably a leak in a Server Side Object. Compare with Process: Private Bytes - _Total.

SQL Server Only 

Object	Counter	Purpose
SQLServer	Cache Hit Ratio	Shows the hit rate that data is found in the cache. If data is not in the cache, the server will have to be read from the disk. This shows memory allocation and therefore indicates whether the server has sufficient memory for the task. A number consistently less than 85% indicates a memory problem.
SQLServer	User Connections	Shows the number of active users of SQL Server. Each connection uses 37K of memory. Compare this number to the Active Server Pages:Requests/Sec counter to get an idea of how much the scripts are really working the SQL Server-based server. A large difference may indicate that the test script is not a valid stress of SQL Server.
SQLServer	Net - Network Reads/sec	Shows the number of data packets read from the network. An extremely high value for an extended time indicates that either the network card has a problem, or more likely, the application is not using enough stored procedures and is written improperly.
SQLServer	I/O - Lazy Writes/sec	The number of flushed pages per second by the lazy writer. A number consistently above 0 indicates that the lazy writer is constantly working to flush buffers to disk. This means that the application either has a memory leak or SQL Server requires more memory for normal operation.
SQLServer - Locks	Total Blocking Locks	Shows a lock that forces another process to wait until the current process is complete. An occasional block is normal. If this number is consistently greater than 0 it indicates transaction problems. Some of the basic causes are inefficient query design, poor table design, or slow throughput due to inadequate hardware.

Note The overhead incurred from Performance Monitor is 5% or less on single processor machines and insignificant on multiple processor machines.

Lessons Learned

Integrating the UPS shipping system

The main problem was getting details from UPS on the data formats for the shipping system. Just finding the correct person to talk to took around three to four weeks. Once this was resolved and Universal had the data formats, everything else was quite easy.

Technical Challenges

Migrating existing catalog and shopper data from the previous Universal store site was a significant technical challenge, requiring custom code.

Site Server Commerce's Auction component supports the two types of bidding that Universal need, but allows only one type of bid per auction item. Custom code is being considered to allow multiple bid types per item.

Adopting the Great Plains accounting system will require a great deal of interfacing work. The Great Plains catalog, for example, will differ in functionality from the Site Server Commerce version already implemented.

Microsoft Wallet's list of countries is different from the list the Store will use for UPS shipping. The initial Store implementation used separate country/region tables as a way round this. A way of integrating with the Universal/UPS country/region list is under review.

Future Requirements

Any decision to accept non-U.S. currencies in the future will significantly affect product pricing and the order processing and acceptance pipelines.

As the site continues to grow, internationalization of the site for local languages may be considered.

Do's

Take advantage of Commerce Server objects, such as SimpleList, Dictionary, Page, Datafunctions, DBStorage, etc. These objects are included in Site Server (non-Commerce edition) and make life easier.

Take advantage of the connection pooling provided by using MTS and ODBC 3.0 or higher. It has better performance and greatly simplifies database access programming, because it removes the need to manage connections.

Return disconnected recordsets from MTS components back to ASP. When returning values from a function, the recordset copies its data to the caller. If the caller is a client in a separate process, the recordset marshals its data. When going across a network, it compresses the data to use less bandwidth.

Mark the ADO component as free-threaded when accessing data from SQL Server. Do this through a registry setting or the utility makfre15.bat. Apartment-threaded is the default.

Get and use the IIS 4.0 resource kit. This is invaluable.

Don'ts

Don't assume all browsers are alike. Make sure from the beginning that your testing covers all the browser types that your site is supposed to support. This will save you a lot of time and headache during the later part of the project. Each browser implementation is slightly different and causes different types of behaviors.