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
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:
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.
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.
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.
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.
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:
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.
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
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:
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.
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.
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.
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 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:
The diagram in Figure 4 illustrates the logic in this process.
Figure 4: Home Page Logic
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
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
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.
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
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.
Once this final validation has been performed MS Market completes the purchase in one of two ways.
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
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
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 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.
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.
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.
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 |
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
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.
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
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
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.
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.
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.
Validation of data occurs in three instances:
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.
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.
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.
MS Market consists of two servers: one Web server and one SQL-based database server. The following are the configuration requirements for these servers.
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 |
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 |
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.
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:
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 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.
MS Market uses ASP with VBScript on the Web server and uses Include files for two purposes:
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
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 |
ASP connection pooling is used to speed up database access. For this to work, MS Market adheres to the following two rules:
When MS Market requires multiple record sets that come at the same time, an extra stored procedure concatenates the record sets.
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).
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 |
| VendorGroup | Groups vendors into preferred supplier categories. Procurement updates this information via support.
Important Fields VendorGroupID—Primary key; foreign into CommodityTable |
| 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 |
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. |
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 |
| LineItemDetail | Stores line item level information for submitted orders.
Important Fields Part Information—Part number, description, price (may be a copy from Part Table) |
| 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 |
| 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. |
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. |
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. |
MS Market provides help to the users in several ways:
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.
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:
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.
© 1999-2000 Microsoft Corporation. All rights reserved.
The information contained in this document represents the current view of Microsoft Corporation on the issues discussed as of the date of publication. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft and Microsoft cannot guarantee the accuracy of any information presented after the date of publication.
This document is for informational purposes only.
MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, IN THIS DOCUMENT.
Microsoft, ActiveX, the Internet Explorer Logo, Microsoft Press, Visual Basic, Visual InterDev, Visual SourceSafe, Windows, Windows NT and the Windows Logo, are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries/regions.
The example companies organizations, products, people and events depicted herein are fictitious. No association with any real company, organization, product, person or event is intended or should be inferred.
Other product or company names may be the trademarks of their respective owners.