Site Server - Market Technical Deployment

August 1999 

By David B. Harding, Lisa Wollin, Mukesh Agarwal and the MS Market team

Other Contributions from: Michael Ohata, Vishal Nanda

Special Thanks to: Chris Cale, Beverly Jones

Introduction

Until 1996, Microsoft Corporation relied on dozens of paper forms and custom applications for procuring goods and services. These forms were especially cumbersome for the Corporate Procurement Group (CPG), which handled thousands of requests weekly for purchases under $1,000 each. The process was slow, costly, and prone to human data-entry errors. High-volume, low-dollar transactions represented 70 percent of Microsoft's corporate procurement, but only three percent of the total dollars flowing through the accounts payable department. Moreover, processing these forms was inefficient and confusing for both end users and vendors. CPG dedicated several resources to processing requisitions into purchase orders (POs), resolving problems, and clearly articulating business rules and processes for an increasingly complex and growing business. The lack of automated and standard procedures made it difficult to create and track these requisitions.

In July 1996, CPG implemented an innovative new application called MS Market. MS Market is an intranet-based procurement application that allows Microsoft employees to easily place orders for goods and services in less than three minutes without being burdened by the cumbersome process of completing paperwork. MS Market provides online forms used to create all purchase orders some of which include ordering office supplies, PCs, business cards, and catering, and for creating contracts to hire vendors. It validates pricing, links each order to their appropriate accounting codes, and routes messages to managers for notification or approval of purchase orders.

Within the first year of deploying MS Market, CPG was able to reassign 17 of the 19 employees responsible for processing purchase orders (POs) to more strategic functions, such as analyzing procurement data and negotiating volume discounts with vendors. In addition, several Microsoft subsidiaries have reassigned valuable resources to more important jobs.

In just over two years of operation, MS Market has:

• Saved Microsoft more than $6 million in human resources 

• Improved volume discounts on supplies by streamlining the supplier base 

• Reduced administrative costs from $60 to just $5 per order 

• Reduced the purchasing cycle from eight days to three days 

• Streamlined workflow by uploading transactions directly into SAP and to suppliers 

• Decreased the number of data-entry errors created while placing orders 

MS Market was initially deployed in the United States and Canada. Today it is available to Microsoft employees in 37 countries and handles well over 400,000 transactions a year. In the United States, employees use it to complete 99.8 percent of their requisitions. Employee response to the application has been very positive; they find it convenient and easy to use. And, since it is administered centrally, employees don't have to worry about upgrading to new versions of the application themselves; they simply use the most current version available on the Intranet.

Solution Overview

To reduce internal operating costs, Microsoft kicked off a data warehousing initiative in the mid 1990's. The project objective was to merge many disjointed SQL Server™ databases and streamline operations. To achieve this objective, Microsoft deployed the SAP/R3 application. Initially, MS Market was conceived as the purchasing front end to SAP/R3. Later, additional functionality allowed MS Market to integrate with systems external to Microsoft, as well as other internal applications.

Today, Microsoft employees use MS Market to create and submit requisitions for purchasing materials and services. The application strictly uses off-the-shelf technologies, including Microsoft® Internet Explorer web browser, a Windows NT® Server running Microsoft Internet Information Server and Microsoft Site Server 3.0 Commerce Edition, and a Windows NT Server running Microsoft SQL Server. It receives requisitions from Internet Explorer and submits them to a SQL Server database. It then sends out e-mail approval notifications to a Windows NT Server running Microsoft Exchange. Once requisitions have become approved, they are exported from the SQL Server database and submitted to Microsoft's SAP/R3 application to execute the purchase payment.

MS Market is updated quarterly. Each upgrade includes new functionality. Often the implementation of proposed features is based on their impact on the system and the Microsoft operating budget. Product and program managers are responsible for assessing and justifying the business need for requested features and upgrades.

Solution Specification

Microsoft created MS Market with the goal of providing an easy-to-use-tool for employees to purchase goods and services. From the beginning, the application was designed to use off-the-shelf technologies.

The client side of MS Market uses the Hypertext Markup Language (HTML), the Dynamic Hypertext Markup Language (DHTML), and the Microsoft Visual Basic® Script language (VB Script). However, because it is based on standard, off-the-shelf technology, it could have been written in any scripting language supported by Internet Explorer 4.0 and Internet Information Server.

It is hosted on a Windows NT Server running the Windows NT Internet Information Server (IIS). It reads and writes data within a Microsoft SQL Server database. Employees use the Microsoft Internet Explorer web browser to send queries to the web server in order to read from and write data to SQL Server. SQL Server stores catalogs that list Microsoft approved vendors and their offerings. Employees choose goods and services from these vendor catalogs and fill out a form.

Once employees submit their forms with Internet Explorer, the data is stored in a SQL Server database. At this point, it turns into a purchase order. Purchase orders require manager notification and, in cases over $1000, approval. For approvals, MS Market takes subscriptions from one of Microsoft's data warehousing solutions, called FeedStore. Feedstore provides information such as the name, location, and authority level of users and their approving managers. MS Market uses this information to pre-fill as many fields on the purchase requisition form as possible. Once purchase orders are approved, they are entered into the SAP/R3 application for fulfillment & payment.

System Requirements

MS Market serves over 22,000 employees in 37 countries. It handles well over 400,000 transactions a year. Yet its system requirements are simple. The application operates on one Microsoft Windows NT Server running Microsoft Internet Information Server and one Microsoft Windows NT Server running Microsoft SQL Server. Microsoft Internet Explorer must be used to access MS Market.

Application Requirements

MS Market was designed to be an easy-to-use application that supports and responds quickly to a variety of employee requisitions. In order to fulfill this design goal, the development team mapped out a number of functional requirements, which now serve as the basis to an extremely efficient application.

MS Market operates 24 hours a day, 7 days a week, with no more than half an hour of downtime each week. It supports virtually an unlimited number of currencies and vendors. The forms in MS Market are easy to fill out; the goal being that employees should not need to take more than three minutes to complete them.

Employees can choose from a list of commonly requested items provided by certified vendors. Once an employee sends a requisition to MS Market, SQL Server generates a purchase order (PO) number. MS Market records all requisitions entered into the system according to transaction level. POs are then sent automatically to the vendor for fulfillment. Vendors fulfill the orders within 24 hours, on average.

To ensure that the vendors who participate in MS Market receive purchase orders immediately, the application supports several mechanisms for exchanging data with them, including electronic mail, electronic data interchange (EDI), delimited text file, and SAP faxes. In addition, the application has a built-in capability for finding and selecting parts supplied by an approved vendor so employees know whether or not the product they want is currently available.

Validations of purchase-approval limits are performed against the Automatic Signature Authority Database (ASAD). In order for a requisition to be approved for payment through MS Market, it must meet the following requirements:

• Orders less than U.S. $1,000 receive tacit approval in the United States. The user's manager is notified via e-mail, but his approval is unnecessary.

• Orders equal to or greater than U.S. $1,000 in the United States require explicit approval. The user's manager receives e-mail requesting his approval, and the order is held until he responds via the Approvals page. 

• The user and the approval manager may not be the same person.

• Orders greater than U.S. $25,000 may have multiple approval managers. These managers are notified for approval sequentially and only the last one is subject to ASAD approval. 

Specification

MS Market was developed incrementally. Initially the application was intended to automate data entry into SAP/R3 by implementing basic requisition functionality and vendor catalogs for key suppliers. It took three programmers and two testers four months to develop and release the original version of MS Market. Over the next two years the application grew to support e-mail notifications, service contracts, part bundling, as well as a multilevel purchase-order approval process. New MS Market features are released on a quarterly basis.

Physical Architecture

MS Market's physical architecture is straightforward. It requires only two Windows NT Servers, one SQL Server, and one Internet Information Server with Site Server 3.0 Commerce Edition. Because the architecture is so simple, the application doesn't require the support overhead necessary to replicate data in SQL Server to other servers. The application also requires few resources to maintain it. No matter where in the world an order is placed, it is processed by MS Market running on a Web server located in Redmond, Washington, USA (see Figure 1).

Figure 1 Physical Architecture 

Application Architecture

MS Market is a centrally administered application, which reduces the demand upon users to upgrade components on their computers. As long as users have Microsoft Internet Explorer 4.0, or a later version, they can order supplies and services using MS Market.

Central administration also means that the team responsible for developing, upgrading, and maintaining the application can be a lean one. Today the team includes:

• Two testers who ensure that the product is stable before being released 

• Two program managers who write the product specifications 

• One product manager who interacts with business vendors and prioritizes projects 

• Two support analysts who support the product once it is implemented 

• Two developers who implement the specifications 

Because MS Market's web server – Internet Information Server (IIS) – transmits HTML pages, the only communication that needs to be optimized for speed over the WAN is between the web server and Microsoft Internet Explorer browser. Both MS Market's web server and its SQL Server database reside on an asynchronous transfer mode (ATM) backbone, so data transfers between them quickly. The MS Market development team optimized the speed between the web server and web browsers by allowing the user to navigate through the application with as little HTML code as possible.

MS Market provides support for Microsoft Windows® 95, Windows 98, and Windows NT–based clients. These clients use Internet Explorer version 4.0, to view MS Market's pages. MS Market's web server is set up to use a mixed security model. On pages not requiring client authentication, anonymous logon is permitted. Pages requiring client authentication use Windows NT challenge/Response (NTLM) authentication.

Microsoft Internet Explorer uses the hypertext transfer protocol (HTTP) to communicate with Active Server Pages (ASP). These are located on the MS Market web server running Microsoft Internet Information Server version 4.0 (see Figure 2). Using Internet Explorer 4.0, users create and submit requisitions by accessing the MS Market Intranet site. Once they have connected to the site, they enter the following information.

• Requested products/services. Users select from commonly requested goods and enter them into purchase requisitions. 

• Requester details. MS Market determines who the user is and enters the necessary details, such as user name, user e-mail name, user approval manager, etc. 

• Approval manager. A manager with the proper signing authority must approve requisitions over a certain amount. MS Market validates this information. 

• General Ledger coding details. MS Market validates the coding details, such as the internal account number and/or cost center coding, against SAP/R3. 

• Shipping information. MS Market automatically enters the user's name and location. If the delivery location is different, a user may enter the desired destination (i.e., building and room number). 

Active server pages running on MS Market's IIS server communicate with MS Market's SQL Server using ActiveX® Data Objects. As MS Market displays pages to the user, it accesses its database server and retrieves the user info (location, department, approving manager, etc.) and the items the user is ordering. Microsoft SQL Server version 6.5 implements MS Market's database.

Prior to submitting the order to SQL Server, MS Market uses Microsoft Exchange to send e-mail to the designated approval managers. If a requisition requires an approval, the e-mail will contain a link to a personalized approval web page. All purchase requisitions awaiting approval by a manager are listed on the approval page. The manager can specify which requisitions to approve. When the approval process is complete, requisitions are submitted to SQL Server.

Figure 2 Logical Application Architecture 

MS Market sends requisitions submitted to vendors to a Microsoft Exchange–based server. MS Market uses Collaboration Data Objects for Windows NT Server (CDONTS) to communicate with Microsoft Exchange Server. This is because CDONTS allows e-mail messages to queue even when an e-mail server is unavailable to transmit the messages.

Orders for goods and services from certified vendors are sent via e-mail, electronic data interchange, or delimited files. For general purchases, the requisition is transmitted directly into SAP, where a purchase order is generated and faxed to the vendor. However, out side of the US, a user must fax the order themselves.

MS Market's SQL Server database uses PCB_DASH. PCB_DASH is a Microsoft-written tool that schedules batch files to run at a specified time. It does this by scheduling a time to execute the files and then copying them to the destination server, which remotely runs them. When the job is complete, PCB_DASH copies back all log files and records a history and status of the run.

Subscriptions in FeedStore – a data repository within Microsoft's data warehouse -- are regularly imported into SQL Server by a batch file, such as MSOLOAD and MSPLOAD. Microsoft's internal vendors, like Microsoft Press®, provide updates to their catalog, which are then imported into SQL Server. Microsoft's external vendors provide part and price updates by uploading their catalogs to a file share within Microsoft's wide area network (WAN). These updates are also imported into SQL Server.

The User Interface

MS Market is designed to provide one-stop shopping to users. The web pages give users the ability to create and submit requisitions. Users can navigate dynamic HTML (DHTML) pages filled with vendor-part catalogs. If a user locates a part that he wishes to purchase, he can add that part to his order. DHTML forms collect the information the user has entered and, after client-side validation, pass validated data back to MS Market's Web server. This process, which uses HTTP POST, VB Script in ASP code, interprets the submitted information and communicates it to SQL Server using ADO. After the user has selected all the parts he wishes to purchase, he can submit his order. A submitted order initiates MS Market's approval process.

Stage 1: Home Page

The home page gives users a variety of available products or services. It includes a hierarchical menu, which details the categories from which users can buy.

All MS Market home pages are generated from a database so they can be localized for the different subsidiaries that use the application. If a user changes the country/region of origin on the home page, a new HTML home page will be sent to the user's browser. Home pages are created with static HTML code, using an internal tool that writes local versions. Each time a user visits MS Market, he is presented with the same static HTML home page he received at his last visit. This design reduces the amount of processing required by the web server.

Figure 3 Home Page 

A bar along the top of the Home page gives access to the Approvals page through the View Approvals button. In addition, anyone with access to the MS Market application can review their own – and other people's – orders. The Find/Change button allows users to locate both submitted and unsubmitted orders. The Home page links to related sites and will display "ads" for preferred and key alliance suppliers.

When a user selects a category, one of the following scenarios occurs:

• MS Market may not provide products from the selected category (e.g., subscriptions). In this case, MS provides instructions in the form of a telephone number or address. This ends MS Market's involvement in this type of purchase. 

• MS Market may not provide a product from the selected category, but the product may be purchased from another Microsoft group (e.g., telephones). In this case, a web link directs the user to the correct Intranet site. This ends MS Market's involvement in this type of purchase as well. 

• MS Market may determine a vendor for the user, depending upon the category and part the user selects. In this case, it skips Stage 2 (Vendor Selection) and goes directly to Stage 3 (Part Selection). 

• MS Market may determine that a key alliance vendor provides the product for the category selected by the user. In this case, it skips Stage 2 and goes directly to Stage 3. 

• MS Market provides a list of preferred vendors for the category the user has chosen. In this case, the user continues onto Stage 2 to select the vendor. 

• MS Market provides a complete list of vendors for general purchases and contracts. In this case, the user continues onto Stage 2 to select the vendor. 

The diagram in Figure 4 illustrates the logic in this process.

 

Figure 4 Home Page Logic 

Stage 2: Vendor Selection

MS Market displays the Vendor Selection screen in two cases: 1) when there is a list of preferred vendors for a selected category, or 2) when there is a complete list of vendors for a general purchase or contract purchase. In this second case, the user performs a search by vendor ID or name, views the details for a vendor, and makes a selection. Otherwise, the vendor is selected automatically for the user, depending on the part number or category that the user has selected.

After a user has selected the vendor from the list provided, the vendor information is saved to a Microsoft Commerce Server object. For contracts, MS Market requests additional information from the user so SAP/R3 can create a standardized contract that includes the necessary contract information.

MS Market allows one vendor per order and will prevent adding parts from other vendors once the vendor has been selected. If a user wants to purchase items from several vendors, he needs to fill out separate orders.

The diagram in Figure 5 illustrates the logic in this process.

Figure 5 Vendor Selection Logic 

Stage 3: Part Selection

When MS Market provides a list of parts, a user can narrow the selection by choosing a part category and subcategory, or he can perform a part search. When the desired part or parts are displayed, he can view detailed information for the part and make a selection. Some parts have web links to a vendor or manufacturer's site for additional information. If he requests nonstandard items or parts not shown on the list, he automatically continues onto Stage 4.

The diagram in Figure 6 illustrates the logic in this process.

Figure 6 Part Selection Logic 

Stage 4: Item Entry

MS Market requests the following line-item information about products and services from users. Some of the items are mandatory, however most are optional or depend on the category of the product or service. If a user selects parts from a part list, the data for some fields is automatically determined and is read-only to the user.

• Part Information (detailed if known). These are optional fields. However, 90 percent of all orders include part numbers. 

• Quantity. This is a mandatory field. All items require a quantity. 

• Asset Information and Usage. These are optional fields. 

• Accounting Information. These are mandatory fields. However, it will default to the accounting information entered from the previous item or order. 

• Show "Bundle" Options. These are optional fields when ordering PCs only. 

• Contract Line Information. These are optional fields when creating contracts only. 

• Business Card Information. These are optional fields when ordering business cards only. 

• Technical Details. These are optional fields when ordering PCs only. 

After a user enters all the information for a line item, MS Market validates the information by verifying that all mandatory fields contain valid data.

The diagram in Figure 7 illustrates the logic in this process.

Figure 7 Item Entry Logic 

Stage 5: Order Details

The Order Details stage uses the Microsoft Site Server Commerce Edition pipeline to calculate the total, set the default requisition information, and perform some validation to make sure the parts and the user are still valid. Then MS Market displays the collected order information, including the requisition details, approval details, shipping details, and contract information (contracts only). For PC orders, reviewers can enter a comment for configuration reviews.

Items are purchased in the currency specified by the vendor. However, MS Market displays orders in the currency rate of the user as well as the currency rate of the vendor. The purchase price displayed to the user in his currency is an estimated purchase price, which MS Market determines using currency exchange rates in MS Market's SQL-based database at the time of the requisition.

The order is saved as a Commerce Server object. Submitting the order performs the following final validation in case the order has changed since the user saved it.

• Verifies that a user is still a valid user 

• Verifies that the approval manager still has the approval limit 

• Verifies that the parts still exist in the database 

Once this final validation has been performed MS Market completes the purchase in one of two ways.

• Run Plan (Commerce Server Pipeline). Invoked every time that the purchase page comes up. This occurs when the user saves the order. 

• Run Purchase (Commerce Server Pipeline). Executes Run Plan and the final stage of the pipeline. This occurs when the user submits the order. 

The final stage of the pipeline is to actually process the purchase. MS Market takes the order out of the Order Form object and breaks up the order into a more traditional normalized database structure. MS Market then deletes the order from the Site Server Commerce object and sets the status of the order to either tacit approval or full manager approval.

The diagram in Figure 8 illustrates the logic in this process.

Figure 8 Order Details Logic 

Stage 6: PC Configuration Review

If a submitted order includes PC equipment, it may require a reviewer to verify that the selected PC components ordered are compatible and all necessary equipment has been ordered. For example, if a custom PC has been ordered, did the user include compatible RAM or other components necessary to operate the computer, and order the necessary cables? If the order needs to be changed, the configuration reviewer edits and resubmits it, or rejects it and returns it to the user to be edited.

Orders including bundled items do not require a configuration review. Bundled PC configurations are noted in the vendor catalogs. These bundles include everything that a user needs to set up a working workstation. Because they are bundled at the vendor site, the vendor verifies the compatibility of the components.

When a configuration reviewer makes a change to the order, MS Market flags the change as a deletion, modification, or addition of a line item. As the order is displayed to the user, the line items are color coded or struck through to show the modifications. When the user accepts the changes, the flags and any deleted lines are removed.

The diagram in Figure 9 illustrates the logic which MS Market uses while performing the configuration review process prior to an order being submitted to MS Market's approval pipeline.

Figure 9 Configuration Review Process 

Stage 7: Approval

When an order reaches the approval stage, MS Market first determines if it requires additional approval. MS Market tacitly approves orders under $1000. It begins the tacit approval pipeline by notifying the user's manager that a purchase has been completed and no further action is necessary.

Orders of $1000 and over require full-manager approval. MS Market begins the full-manager approval pipeline by sending e-mail to the first manager whose approval is required and then waiting for approval. MS Market will then sends e-mail to the next manager for approval and wait for approval. MS Market will continue to do this until all managers requiring approval have approved the purchase.

Tacit approval

Tacit approval is designed for mass movement of low-dollar purchases. MS Market tacitly approves an order by sending e-mail to the user's manager informing the manager that the order has been tacitly approved. It then sets the status of the purchase to approved, and sends the order to the vendor.

Full-manager approval

MS Market approves orders that require full-manager approval by sending an e-mail message to the first manager named within the approval table. It then sends the manager e-mail requesting him to access MS Market to view the order. Finally, MS market marks the status of the purchase as awaiting approval. (The purchase may await approval indefinitely, though the system will inform the original requestor by e-mail at regular intervals if the order is still awaiting approval.) Upon reading the e-mail, the manager accesses MS Market and is presented with a list of purchases that are awaiting approval (see Figure 10).

Figure 10 Approvals Page 

For each order the manager has four options available.

• Approve. Approving the order authorizes MS Market to send the order to the vendor. 

• Reject. If the manager rejects an order, MS Market sends e-mail notification to the requestor that the purchase was rejected. The order goes from being a submitted order to an unsubmitted order. 

• Hold. Holding an order is the default order status, giving the manager time to think about the purchase or wait for paperwork. 

• Reassign. Reassigning the order may be used if a manager would like other managers to see the purchase as well. When a manager reassigns the order, MS Market records that the original manager implicitly approved the order and requested that a second manager see the purchase. 

MS Market loops through the approval process for each remaining manager who is listed within the approval table of the purchase by sending out a new e-mail notification and changing the status of the order to awaiting approval. The diagram in Figure 11 illustrates the logic used to approve submitted purchases.

Figure 11 Approval Process 

After completing the approval process, the requisition class of the vendor determines how the order is transmitted. MS Market executes a stored procedure against the requisition class, which then copies the purchase order to one of the export tables within MS Market's SQL Server database and changes the status of the order to received by procurement. At this point, MS Market considers the purchase complete.

Approval E-mail

MS Market sends all approval e-mails to Microsoft Exchange, which in turn transmits the e-mail message using the MS Market e-mail alias.

Tables 1-4 describe the fields used in the e-mail messages:

Table 1 Database Fields Used in E-Mail Messages 

Data Field	Description
approveralias	The person whose approval is required
12345	The order number that requires approval
requestoralias	The person who is the requestor on the order
ccalias	The persons who are listed as cc recipients
vendorname	The vendor who is listed on the order
$XXX.XX	The total amount of the order
Z	The number of days to wait for approval before notifying requestor (configured by requisition class)

Table 2 Sample Content of an Automatic Approval Notification

To	Approveralias
Cc	Requestoralias, ccalias
Subject	Vendorname Order #12345 submitted by requestoralias Total Cost: $XXX.XX
Message Body	No Action Required. This MS Market Order has been automatically approved and submitted to the vendor. Click here to review the order.

Table 3 Sample Content of a Manager Approval Notification 

To	Approveralias
Cc	Ccalias
Subject	Vendorname Order #12345 submitted by requestoralias Total Cost: $XXX.XX
Message Body	Click here to review this order. If this is a general requisition order, add the text: This order was placed using the General Purchase Order section of MS Market. This means that even though a Purchase Order will be sent to this supplier, * you are responsible to contact the supplier directly to ensure your order gets fulfilled. *POs associated with contracts are NOT sent; the contract is the only document the supplier needs.

Table 4 Sample Content of a Manager Rejection Notification 

To	Requestoralias
Cc	Ccalias
Subject	Vendorname Order #12345 has been rejected by approveralias
Message Body	Click here to view details

Stage 8: Order Transmission

In the final stage of the ordering process, MS Market transmits the order to the vendor either directly (via e-mail, EDI, and delimited files) or through SAP, which faxes approved orders to vendors. Purchase orders are inserted into the MS Market SQL Server database via SQL stored procedures (covered in the Code Components section of this document, page 25).

Figure 12 illustrates each of database export tables within MS Market's database. Purchase orders are added to each export table, depending on the requisition class of the vendor. The right side of the diagram represents the PCB_DASH job, which will export each of the database tables. The exported tables are then transmitted to the vendor as a purchase order using the method appearing on the left side of the diagram.

Figure 12 Order Transmission 

Order Management Features

Find Order Screen

MS Market's Find Order screen allows the user to find submitted and saved orders for reference, submission, or deletion. The search may be done on the Order Number field or by a combination of user name, approval manager name, age of the order, and status.

Search Results Screen

MS Market's search results screen displays the results of all matching records found after having invoked the find-order pipeline. Saved orders displayed on the results screen can be submitted or deleted. Options, such as the ability to unsubmit, and copy orders are displayed when applicable.

The diagram in Figure 13 illustrates the logic MS Market uses within its search/find order pipeline.

Figure 13 Search Process 

View Order Screen

MS Market's view-order screen displays orders for users to review. Users can view the history of an order and any changes made by the configuration review pipeline. For foreign orders, the view order screen acts as the purchase order, in which case the screen would need to be manually printed and faxed to the vendor.

The diagram in Figure 14 demonstrates the logic used when the requestor, approving manager, or configuration reviewer is reviewing purchase orders.

Figure 14 View Orders

Security

User Authentication

Because the MS Market application is an Intranet application, users must log onto Microsoft's corporate network using a valid Windows NT account before they can make a purchase. Users may log on to the Microsoft corporate network using remote-access services (RAS), an integrated services digital network (ISDN), or the point-to-point tunneling protocol (PPTP), or directly from their workstation at Microsoft.

MS Market does not expose any file shares or allow any directory browsing, and it can only be accessed through HTTP. Once a user has logged on to the Microsoft corporate network and attempts to access a page that requires authentication, IIS challenges the user's credentials using Windows NT Challenge/Response before the user can log onto the application. After a user has been authenticated using Challenge/Response, the vendor catalog will be obtained from the Web server by invoking stored procedures on the database using ADO. Once a user has access to MS Market and is ready to place an order, MS Market verifies that the user's manager has an approval limit large enough to approve the order before the order can be submitted.

Data Integrity

The MS Market database is protected by Windows NT File System (NTFS) file and share permissions, and data is imported from the FeedStore via secured batch files. Only MS Market has access to these shares, and only a few support personnel have permission to modify the batch files and submit them to the scheduler for scheduled executions.

General user data access is performed via stored procedures. There are no read, write, or update access permissions on any of the tables within the MS Market database other than those granted to stored procedures. Because a user can access MS Market only from HTTP, the user has no way of knowing the name of the stored procedure required to update the database and does not have permission to run the stored procedure directly.

System Administration

Physical access to all servers on the MS Market system is limited to support and development personnel. These systems are secured in the Microsoft corporate data center. The MS Market SQL Server database is regularly backed up. Compaq Insight Manager is used to verify that the hardware used by MS Market has not degraded. Support personnel verify that all PCB_DASH jobs that run against MS Market's database complete successfully.

Only a few support personnel on these systems may start and stop services used to run MS Market. HTML and ASP content may only be modified by a limited number of support personnel. Monitoring of servers and services is performed using Windows NT Performance Monitor. When a server or service stops responding, a support analyst receives an administrative alert from Windows NT Performance Monitor.

Data Validation

Validation of data occurs in three instances:

• When the user types information: mandatory fails (i.e., is a field empty or not empty?) and formatting (numeric fields must contain numbers only; a quantity must be greater than zero) 

• When the web server does some validation, such as checking that an account code is valid and an approving manager has the right level of approval 

• When the database server double-checks that all information is valid 

Once validation is complete, unsubmitted orders are stored using the Microsoft Site Server Commerce Order Form object. Submitted orders are transferred into a table in the database.

Integration with Existing Systems (SAP, FeedStore)

Requisitions requiring purchase orders are forwarded to SAP via batch files. Every few minutes, a batch file calls a stored procedure that marks all of the order numbers in the export table with a "sent to supplier'" status and deletes the contents of the export table. The stored procedure then exports from the MS Market database all of the fields required by SAP to create a purchase order. The data is transferred to a location accessible only to SAP. A batch file on the SAP system detects the arrival of and processes new data. SAP then prepares the purchase orders and submits them to the vendor via fax. In the future, the MS Market team plans to use component object model (COM) interfaces to enter data directly into SAP.

MS Market uses a similar process to import data from the FeedStore via stored procedures. The FeedStore contains many data sets, including customer, finance, geographic, people, physical assets, and product data sets. Each of these data sets consists of other data sets, thereby making it possible to subscribe to small data sets. MS Market subscribes to several FeedStore data sets. The subscriptions ordered are delivered to a network share that only MS Market can access. Subscriptions are imported into the database using stored procedures. This process is performed daily to make sure that vendor pricing and availability of items are accurate and that the current exchange rate is as accurate as possible.

Implementation

Microsoft rolled out MS Market first in the United States and then to 37 subsidiaries around the world. The implementation followed on the heels of the SAP rollout.

Configuration

MS Market consists of two servers: one Web server and one SQL-based database server. The following are the configuration requirements for these servers.

Web Server

Hardware

• Dual-processor Compaq 3000

• 512 MB of memory

• 24 GB hard disk

Software

• Microsoft Windows NT Server network operating system version 4.0 (Service Pack 4) with Internet Information Server version 4.0 

• Microsoft Data Access Components (MDAC) version 1.5 (includes ADO version 1.5 and Open Database Connectivity) 

• CDONTS (shipped with Windows NT 4.0 Option Pack) to communicate with Microsoft Exchange Server 

• Microsoft Site Server version 3.0, Commerce Edition 

The primary function of the Web server is to process ASP and to create the DHTML user interface. Tables 5-6 describe the server details and settings.

Table 5 Web Server Platform Configuration 

Component	Version	Explanation
Microsoft Windows NT Server	4.0 (Service Pack 4)
Microsoft Internet Information Server (Option Pack)	4.0
Microsoft Transaction Server (Option Pack)	2.0
Microsoft Site Server, Commerce Edition	3.0	Admin Access only

Table 6 Internet Information Server Configuration 

Location	Setting	Value
Site Properties Web Site Tab	IP Address	All Unassigned
	TCP Port	80
	Connections	(All Unassigned)
	Enable Logging	Yes
	Active log format	W3C Extended Log File Format
Site Properties Home Directory Tab	Access Permissions	Read
	Content Control	Log Access
	Permissions	Script
	Enable session state	No
Site Properties Home Directory Tab Configuration Application Options Tab	Enable buffering	No
	ASP Script timeout	90 seconds
	Session Timeout	20 Minutes

Database Server

Hardware

• Three-processor Compaq 5000

• 512 MB of memory 

• 16 GB hard disk 

Software

• Microsoft Windows NT Server version 4.0 

• Microsoft SQL Server version 6.5 (Service Pack 4) 

• 2.2 GB for database data device 

• 500 MB for database log device 

• 1 GB for Tempdb device 

The database server contains the database tables used to populate the client browser with vendor, part, part description, and pricing data. It interacts with SAP and the Microsoft FeedStore to collect and transfer data. The database data device located on a separate physical drive, and transaction volumes expected to be reasonably light—less than one transaction per second.

Tables 7-8 outline the server details and settings

Table 7 Database Server Configuration 

Component	Version	Explanation
Microsoft Windows NT Server	4.0 (Service Pack 3)
Microsoft SQL Server	6.5 (Service Pack 4 HF297)

Table 8 SQL Server Configuration 

Location	Setting	Value
SQL Server Setup Network Support	Network Protocols	Named Pipes TCP/IP Sockets
	Port Number	Not 1433

Development Tools

• Microsoft Visual SourceSafe™ version control system version 6.0 

• Microsoft Visual InterDev™ Web development system version 6.0 

• Microsoft Visual Basic development system version 6.0 

• Microsoft Exchange Server version 5.5 

• Allaire Homesite version 3.0—HTML Editor 

Technologies Used

• Active server pages version 2.0

• ActiveX Data Objects version 1.5

• Dynamic hypertext markup language

• Component object model

• Microsoft Visual Basic Scripting Edition (VBScript) 

• Collaboration Data Objects for Windows NT Server 

Code Components

MS Market utilizes both client-side scripting and server-side scripting. The client-side scripts provide a feature-rich user interface, limit the amount of data processing the Web server has to perform, and reduce the frequency of HTTP requests across the Microsoft intranet. MS Market uses VBScript in ASP and DHTML pages.

VBScript in DHTML validates data prior to sending it, so further validation is reduced when the data is submitted to the Web server and the database. This process also helps eliminate erroneous data from being stored within the database.

To minimize network traffic, the MS Market team considers the size of every DHTML file sent to the client. Content in these DHTML files is determined by whether it serves a legitimate Microsoft business need. Animated graphics interchange format (GIF) files and complex artwork are used sparingly and only if they improve the usability of the application.

DHTML

MS Market uses HTML for displaying all of its pages but also takes advantage of the fact that Microsoft Internet Explorer 4.0 supports DHTML. MS Market uses DHTML in several ways:

• Shrinks large pages of text. MS Market uses this functionality for condensing instructions or Help text into headings that show or hide their content when clicked. This makes the initial view of the page less daunting for new users. 

• Dynamically displays only pertinent sections of Web forms. For example, an order is only considered to contain potential assets if the amount is over a certain threshold and the usage of the item indicates it will be used within Microsoft. Only when these conditions are met is the section of the form that collects asset information displayed. This means that the user is not confused by sections of forms that are not relevant to the current order. This is done on the client side rather than in ASP because MS Market cannot know in advance which sections will be required. 

• Changes the layout of the screen by showing, hiding, or resizing frames. 

Frames

MS Market uses frames to minimize the amount of HTML that is transmitted to the client when something on the screen changes. The frame set is created once when the Home page is created. Hyperlinks and VBScript update only the frames that are necessary, and show, hide, or resize others depending on the current stage of the order.

HeaderFrame. Used to display the menu bar. Always visible. Never refreshed. Contents changed using DHTML.

StatusFrame. Used to display order status and order manipulation buttons. Initially hidden; visible while ordering. Never refreshed. Contents updated by DHTML.

LeftFrame. Used to display menus for selecting commodities, part categories, and vendor search forms. Always visible. Contents refreshed by hyperlink or script from RightFrame.	RightFrame. Used for all main data entry and order processing. Always visible. Contents refreshed by hyperlink or script.
	SplitterFrame. Used to display information when RightFrame contains lists of information. For example, order details on the Find results screen or vendor details on the Vendor Search results screen. Initially hidden; only displayed when there is a need to display content. Contents refreshed by hyperlink or script from RightFrame.
HelpFrame. Mainly used for displaying Help text, although also used for part subcategories. Initially hidden; only displayed when there is a need to display content. Contents refreshed by hyperlink or script from RightFrame.

To avoid multiple frames updating each other, the main frame on the right side updates all MS Market frames. VBScripts are included in this frame to synchronize all other frames to it.

top.RFrameset.rows="*,0"
top.LFrameset.rows="160,*"

In addition, the outer frame is used as a storage area for often-used client-side VBScript routines. The Home page (default.htm) includes the following code:

<SCRIPT src="/default.vbs" LANGUAGE=VBSCRIPT></SCRIPT>

The Default.vbs file contains common routines such as the LoadFrame routine shown below:

Sub LoadFrame(objFrame, strURL)
'Check to see if this frame already contains the URL, case insensitive
if lcase(right(objFrame.document.location.href, len(strURL))) <> lcase(strURL) Then
'Different so update the frame - use replace so as not to fill up the history and break 'BACK'
objFrame.document.location.replace strURL
End If
End Sub 

VBScript (Client)

Common routines are part of the outer frame set. When another frame needs to use one of these routines, the code does not have to be included on the web page and downloaded because it is always on the client and can be called using the following code:

Top.LoadFrame top.LeftPage,"/store/partclassEnum.asp?reqclasscode=OP"
Top.LoadFrame top.TitlePage, "/static/title/OPTitle.htm?ReqClassCode=OP"

When the main MS Market frame needs to update a different frame, it uses the replace method of the Internet Explorer document object without updating the browser's history. This allows multiple frames to be updated without affecting the operation of the Back button.

ASP (VBScript Server)

MS Market uses ASP with VBScript on the Web server and uses Include files for two purposes:

• Sharing common code between multiple forms. For example, a library of database routines ease the use of ADO, and another database contains common routines to manipulate the HTML returned to the browser and to set default parameters faster than fetching them from the database. 

• To "black box" sections of the HTML code, enabling multiple developers to work on the same page simultaneously. In some cases, as new requisition classes are added, these sections can be added to different forms. 

Most of the ASP are huge sets of Case statements that change the HTML coming back depending upon the ReqClass, SubClass, and current state of the order.

To speed access to the database some rarely changed data is cached into Commerce Dictionary objects at application startup time. The following routine was written to update these caches at any time if the data is changed by support:

<OBJECT RUNAT=Server SCOPE=Application ID=Countries PROGID="Commerce.Dictionary"></OBJECT>
Sub InitialiseDataCache()
Dim Connection, SQLStmt, RSDataCache, Dict
Set Connection = Server.CreateObject("ADODB.Connection")
Connection.Open {DSN}
SQLStmt="Execute GetDataCache"
Set RSDataCache=Connection.Execute(SQLStmt)
'1st Record set is country
Do while Not RSDataCache.eof
set Dict=BuildDictionary(RSDataCache)
Set Countries(Dict.CountryShortCode)=Dict
RSDataCache.MoveNext
Loop
connection.close
End Sub
'--------------------------------------------------------
Function BuildDictionary(RS)
Dim Dict, i
'Create a new dictionary object and copy all the fields from the current record into it
Set Dict=Server.CreateObject("Commerce.Dictionary")
For i=0 to RS.Fields.count-1
Dict(RS(i).name)=Trim(RS(i).value)
Next
set BuildDictionary=Dict
End Function

All of the ASP that accepts user input contain forms that post the data back to themselves with an extra parameter on the uniform resource locator (URL) indicating that the page is being submitted. Therefore, if validation fails, MS Market can continue to display the page and return the fields to the values the user just entered without requiring any extra code to save these values. If the validation passes then the page is redirected to the next page in the sequence:

<%
If Request.QueryString("Action")="SAVE" Then
…..Validate data
…..If data is valid then
Response.Redirect "NextPage.asp"
%>
<HTML><BODY>
<FORM METHOD=POST ACTION="ThisPage.asp?Action=SAVE">
Enter Your Name <INPUT NAME=myName TYPE=TEXT VALUE="<%=Request.Form("myName")%>">
<INPUT TYPE=SUBMIT>
</FORM>
</BODY></HTML>

Data returned by stored procedures is filtered before being displayed in each of the frames. The usefulness of the data to the user determines how the data is filtered. The following pseudocode illustrates how this is accomplished.

If the REQ class equals PC 
Then display table columns one through 5 and 7
Else IF the REQ class equals CATERING 
Then display table columns 1 3 and 9

Commerce

All unsubmitted orders are stored in an Order Form object, which Site Server Commerce Edition saves to the database server as a binary object. Once the order is submitted, MS Market transfers the data from the Order Form object into normalized tables within the database server.

The Order Processing Pipeline is used to check the order before it is displayed or submitted, and transfer the final order into the MS Market database on order submission. The pipeline uses the components shown in Table 9 below.

Table 9 Order Processing Pipeline Components 

Stage	Component
Product Info	Scriptor—Custom component that validates that standard parts are still valid and fetches the current price from the database
Shopper Information	Scriptor—Custom component that validates user information and updates the current Order Form object with personal information
Shipping	Default Shipping—Provided and required by Commerce
Handling	Default Handling—Provided and required by Commerce
Order Total	Default Total—Provided and required by Commerce Scriptor—Custom component that calculates the total order, avoiding the upper 32-bit integer limitation of Commerce 3.0
Payment	Default Payment—Provided and required by Commerce
Accept	Scriptor—Custom component that transfers the data from the Order Form object into the MS Market database

Database

ASP connection pooling is used to speed up database access. For this to work, MS Market adheres to the following two rules:

• All pages use the same connection string to access the database.

• The connection object is created and closed on each page.

When MS Market requires multiple record sets that come at the same time, an extra stored procedure concatenates the record sets.

Database Input/Output

Batch files are used for executing stored procedures to import or export tables to or from the database; transfering data between servers; or invoking helper utilities. Invoking stored procedures from within batch files feeds the data into the database.

Data provided by vendors must be in a specified format, and vendors that have the capability to provide data files may do so at any time. A batch file monitors the arrival of new data files. The batch file performs a series of tests upon a new file to make sure the data is in a valid format and imports the data into the database.

MS Market subscribes to data feeds from the FeedStore. Each subscription arrives daily. A batch file for each subscription repeatedly executes a stored procedure to import a single subscription into the MS Market database.

Every few minutes, a batch file invokes a stored procedure that exports all database tables into SAP. The stored procedure exports all tables at once. Once this is done, the batch file will transfer the exported data to the SAP system. Data exported from the database is input into SAP by invoking a data- entry tool written using the SAP software development kit (SDK).

General Tables

Figure 15 and Figure 16 illustrate the relationship between the different database tables. Figure 15 illustrates the relationship between the vendor and commodity tables. All database tables contain InsertBy and InsertDate fields for tracking who made updates and when updates were made.

Table 10 Vendor and Commodity Tables 

Table Name	Description
SAPVendorMaster	Stores all vendors with which Microsoft can do business. MS Market pulls this data from the FeedStore. Important Fields SAPVendorNumber—Primary key; same value as in the SAP system VendorName—Used for searches. Stores two alternative Doing Business As (DBA) names Vendor addresses and phone numbers Vendor contacts Vendor e-mail addresses and Web sites
VendorGroup	Groups vendors into preferred supplier categories. Procurement updates this information via support. Important Fields VendorGroupID—Primary key; foreign into CommodityTable SAPVendorNumber—Foreign key from SAPVendorMasterTable
Commodity	Groups requisition classes, suppliers, and supplier groups into commodities. (A commodity is a single menu item from the Home page.) Procurement updates this information via support. Important Fields MenuTitle—Text that will appear on the Home page DisplayOrder—Primary key; defines the order in which menu items will appear Action—Defines what to do when this menu is selected ReqClass and SubClass—Identifies the ReqClass to enter if this menu item begins an order

Figure 15 Vendor and Commodity Table Relationships 

Table 11 Vendor and Commodity Interface Routines 

Stored Procedures	Description
CommodityEnum	Returns all commodities for the current country/company. Used to build the menu on the Home page.
VendorEnum	Returns a list of vendors that match the search criteria.
VendorEnumByGroup	Returns a list of vendors that are in a particular preferred vendor group.

Order Tables

The data in the Order database tables is updated when a requisition is submitted. Table 12 describes the order tables.

Table 12 Order Tables 

Table Name	Description
CommerceRequisition	Used by Commerce to store unsubmitted orders.
Requisition	Stores order level information for submitted orders. Important Fields RequisitionID—Primary key; used for tracking all orders ReqStatus—Stores the current status of the order; defines workflow Requisitioner details—Name, location, and phone number Shipping Details—Internal or full address SAP Fields—Material Group code and Tax code General Use Fields—Used differently by each ReqClass
LineItemDetail	Stores line item level information for submitted orders. Important Fields Part Information—Part number, description, price (may be a copy from Part Table) Line Details—Quantity Asset Information—Usage Accounting Fields—Cost Center, Internal Order Number, Account Code, Asset Tag General Usage Fields—Used differently by each ReqClass
RequisitionVendorInfo	Stores vendor information. Important Fields A copy of the fields from the SAPVendorMaster table at the time the order was created, because SAPVendorMaster changes each night.
ApprovalManager	Stores approval manager information. Important Fields ApprovalManagerEmail—Identifies to whom to send the e-mail Approval Status—Sets a flag to check if manager has approved order Approval Comment—Stores comments made by approval managers
CONTRequisition	Stores extra information for contract requisition classes.
BusinessCardLayout	Stores extra information for business card requisition classes.

Figure 16 depicts the relationships between the order tables, and Table 13 describes the order table interface routines.

Figure 16 Order Table Relationships 

Table 13 Order Interface Routines 

Stored Procedures	Description
ReqGetHeader	Returns order-level information.
ReqGetDetails	Returns line item information.
RequisitionSave	Updates order-level information; performs validation before actually modifying the database.
LineItemInsert	Updates/adds line-item information.
RequisitionSubmit	Performs final order validation on the entire order and sets the status of the order to submitted.
ReqStatusSet	Changes the status of an order.

Part Tables

The data in the part tables is updated by PARTLOAD batch jobs. Table 14 describes the part tables.

Table 14 Part Tables 

Table Name	Description
PartClass	Stores groupings of parts.
Part	Stores part catalog information on individual parts.
BundlePart	Stores lists of parts that make up a bundle. The bundle name is also a part in the Part table, so to create a bundle, the Part table is joined to itself using the BundlePart table

Figure 17 depicts the relationship between the part tables and Table 15 describes the stored procedures used with the parts tables.

 

Figure 17 Part Table Relationships 

Table 15 Part Interface Routines 

Stored Procedures	Description
PartClassEnum	Returns a list of classes for a given ReqClass.
PartEnum	Returns a list of parts for a given part class. –or– Returns a list of parts that match a search term in the description.
PartGet	Returns all information about a particular part.
BundlePartEnum	Returns all the parts that make up a bundle.

Export Tables

The Export tables are temporary stores where the data for requisitions is held until batch files transfer the data to SAP or submit it to vendors. All of the tables contain a subset of the fields from the Requisition and Line Item tables. The subset will be slightly different for each Export table depending upon what is required by each business partner.

Table 16 Export Tables 

Table Name	Description
ExportSAP	Exports tables to SAP for requisitions requiring a purchase order.
ExportBIZ	Exports tables for business card requisitions.
ExportGMBHBIZ	Exports tables for German business card requisitions.
ExportMSO	Exports tables for Microsoft software requisitions.
ExportBAN	Exports tables for Marketing software requisitions (demos, etc.).
ExportBOOK	Exports tables for book orders.
ExportCAT	Exports tables for catering service requisitions.
ExportMSP	Exports tables for Microsoft Press requisitions.
ExportEDIPC	Exports tables for PC requisitions.
ExportEDI850	Exports tables for office supply requisitions.

Table 17 Export Interface Routines 

Stored Procedures	Description
ExportXXXInsert	Each Export table has an insert routine associated with it. This routine transfers all of the data required and simply inserts that data into the correct table.
ExportXXXDelete	Each Export table has a delete routine associated with it. This routine is called by the Export batch job after the data has been transferred to the output file.

Help System

MS Market provides help to the users in several ways:

• A hierarchical list of answers to frequently asked questions (FAQ) is maintained and updated regularly by the production support team. 

• Each requisition class has a home page to display information on the ordering process for that area. 

• Some screens have help text that is automatically displayed in the lower left frame whenever that screen appears. 

• Many labels within the applications are hyperlinks to help for that particular field. Help appears in the lower left frame. 

Performance Monitoring

The Microsoft Information Technology (IT) group monitors the internal network and the computer systems running on it on a regular basis. Therefore, additional regular performance monitoring was not deemed necessary for MS Market. However, Windows NT Performance Monitor is used to troubleshoot and resolve problems when they occur. A small amount of logging has been built into the system to check that users accessing the system are being validated correctly and to monitor the company code they are using.

Lessons Learned

The MS Market team gained a lot of knowledge about developing Intranet applications from working on this project. These are some of the key observations they walked away with:

• All pages were written as ASP and, as a result, IIS had to be configured to limit the number of concurrent users because too many concurrent sessions overloaded the web server and made the system unstable. IIS was reconfigured to allow an unlimited number of concurrent connections, and many of the ASP pages were rewritten as HTML pages or DHTML pages. 

• Little emphasis was placed on how often users would access the web server. The web server performed all validation of data. This has been reworked so that Internet Explorer 4.0 using VBScript validates much of the data prior to being submitted to the Web server. 

• Dynamic pages containing data that rarely changes (such as those based on the company code) are generated once into static HTML and can be regenerated at any time from the database when and if the data changes. This is especially useful for the Home page because many users access this page as their default Home page. 

Optimal System Settings

Windows NT

• The page file size is set to the size of system memory plus 50 MB. 

• The minimum page file size is set to equal the maximum page file size. 

• The server is configured for maximum throughput for network application. 

IIS

• The service is configured to allow an unlimited number of users. 

Technical Challenges

One of the greatest technical challenges has been to minimize the amount of DHTML code that is sent to the client's browser and to minimize the frequency that the client browser must communicate with the Web server. Following are guidelines for overcoming these challenges.

Do

• Have the Web browser validate data entered by the user. 

• Limit the amount of DHTML code sent to the client browser. 

• Write pages as ASP only when the Web server must obtain information. 

• Refresh data in frames that require refreshing only. 

• Access the Web database by way of the Web server rather than allowing the Web browser to communicate directly using data binding. 

• Add features only when there is a business need for them. 

• Create a formal process, which includes program management, development, and testing. 

• Use DHTML to hide and display data within frames.

• Use radio buttons and drop-down menus to simplify data entry. 

• Pre-fill fields wherever possible to save the user time. 

Don't

• Allow clients to log on to the Web database directly. 

• Write every Web page as an ASP page. 

• Have the Web server send large animated GIF files to the user. 

• Have the Web server validate all data entered by the user. 

• Add features just because they are easy to add. 

• Refresh the entire client area of the browser unnecessarily.