Merisel Case Study: Building a State-of-the-art Electronic Commerce Application

By Mark Kapczynski, Brian Wren, Joseph Ruedlinger, and Rick Shahid (Microsoft Consulting Services, Southern California District)

Other contributions from: Michael Ohata, Lisa Wollin, Robert Haynie, Nathan Niederhausern, and Mukesh Agarwal

Special Thanks to: Chris Cale, Beverly Jones

Release Version: 2.0

August 1999 

Introduction

Merisel, one of the leading distributors of computer products in North America, moved more than $4 billion worth of computer hardware and software through a channel of 25,000 resellers to millions of customers in 1997. They were doing all of their business through phones, faxes, and electronic data interchange (EDI), with a proprietary Web-based solution. The company contracted their corporate Web site ( https://www.merisel.com ) with a third party and, aside from a file transfer protocol (FTP) server, had no in-house Internet applications.

They soon realized that contracting all electronic commerce applications and services put the company at a considerable competitive disadvantage in electronic commerce—enhancements and maintenance were costly, performance was slow, and Web usage reporting was non-existent. To regain a competitive advantage, Merisel decided to develop its own customized electronic commerce application and to purchase and configure the necessary host hardware.

Merisel worked with Microsoft Consulting Services to build a state-of-the-art electronic commerce application. This application is referred to as "Mer-EC-Soln" in this paper. The Mer-EC-Soln application was designed to improve and extend the existing system and develop a strong Internet connection with Merisel's customers. Their initial goals were to enable customers to perform quick searches of their online product catalog and encourage them to place orders over the Web.

Note The names of servers, applications, functions, Web pages, COM objects, referred to in this paper are fictitious in order to maintain the integrity of this solution.

Return on Investment

Four weeks after implementing the new application, Merisel averaged twice the number of orders received over the Web as the original system. Currently, the Web is still the smallest percentage of their business (as compared to phones, faxes, and EDI), but as they continue to develop the system, they anticipate it will become a larger portion of their business. As a result, customer service employees can focus on their intended purpose—selling.

Project Overview

Merisel's business requirements presented some challenges to a Web application. Merisel has two large redistribution operations: one for the United States and the other for Canada. Each operation maintains a separate order processing system—an IBM mainframe for the United States and SAP on HP UNIX for Canada. However, since the company plans to move the U.S. system to SAP by the end of 1998, long-term plans focused on this unification.

The two operations handle business processes and data management differently, but they each have 70,000 products, over 15,000 customers, and special customer pricing schemas based on customer classification and purchasing volume. While the obvious choice for an electronic commerce application was to leverage Web connectivity, the systems which contain the product and pricing data were never designed to handle the amount of traffic and processor demand that can occur when a system is accessible through the Web. The development of Mer-EC-Soln took into consideration the logic of the existing system but used none of its parts.

Timeline

There were two phases to this project, which are outlined below. The first phase took approximately 600 hours to complete. Partial completion of the second phase is expected in early December 1998.

Phase I

The first phase of development involved creating a base system that would provide customers with the functionality of Merisel's current system, but in a new environment. The following table describes the different projects involved in completing the various elements of Phase I, the estimated duration of each project, and the number of persons required to develop and maintain the new application.

Web Site Development 

Three persons worked on Web site development, including the following tasks:

• Setup Web servers 

• Install and configure Microsoft Internet Information Server, Windows NT® Server's built-in Web server 

• Setup appropriate security 

• Design directory and navigation structure

• Integrate pages from existing Merisel Web sites 

• Integrate with Microsoft SQL ServerTM 

• Outline procedures for maintaining site and updating/replication content 

• Testing and implementation 

Two persons are involved in maintenance.

Electronic Commerce 

Two and a half persons were involved, including the following tasks.

• Develop Functional Specification 

• Design store and product category structure 

• Design order templates and site navigation 

• Develop integration between Commerce Server and SAP/CAMBAR 

• Migrate data from existing systems 

• Testing and implementation 

One half person is responsible for maintenance.

Order Entry System 

Two persons worked on developing the order entry system, including the following tasks:

• Conduct study of current system and migration requirements 

• Develop Functional Specification 

• Develop database and back-end objects 

• Develop front-end, Web-based client application 

• Integrate with SAP

• End-user training 

• Testing and implementation 

Two persons are involved in maintenance.

Phase II

The second phase involved development of the Commerce Interchange Pipeline (CIP) and personalization, promoting functionality to their resellers in terms of the CIP, increasing real-time connectivity to the host, and additional personalization features. The elements regarding CIP are discussed in the section on the "Commerce Interchange Pipeline" later in this document.

Mer-EC-Soln allows customers to log onto a Web site, check product availability and pricing, place an order, and check the status of an order, all in real time. This functionality is currently being prototyped for Phase II.

System Requirements

One requirement of Mer-EC-Soln was to use all product, pricing, and customer information from the existing systems without altering or interrupting it to accommodate the new application. Further, orders taken by Mer-EC-Soln needed to be uploaded into the existing systems and filled as any other order.

Performance/Usage Requirements

• Broad Internet reach and browser support. Merisel did not want to lose current or potential customers who already had Internet browsers or operating systems in place. This application was designed to support Microsoft Internet Explorer 3.0x or later and Netscape Navigator 3.0 or later on Windows® 95, Windows 98, Windows NT, Macintosh, and Unix operating systems. 

  Easy-to-use, flexible, and intuitive product queries. Merisel's product catalog lists over 70,000 products, so customers must be able to find products, descriptions, availability, and pricing quickly and easily. Available searches include:

  • Manufacturer part number 

  • Merisel part number 

  • UPC code 

  • Product title 

  • Manufacturer 

  • Manufacturer and title 

  • Product category and subcategories 

• Responsive pages. Serve up page requests within 1-5 seconds with even the most complex queries. 

• Large queries. These must return partial results and be able to continue through one or more requests. (Full results would burden the data server and also clog a 28.8 modem.) 

• Expandable Web capacity. Initial capacity required 1000 concurrent users but could be expanded to 10,000. 

Order Placement Requirements

• Shopping basket metaphor for ordering. When customers find a product, they can simply point and click to add it to their basket. At any time, they can view the contents of the basket, update quantities, and continue shopping, purchase, or exit.

  Perpetual orders. Shopping baskets are perpetual—they would stay there until the order is completed and placed—so orders in progress can be continued through one or more logins (or purchasing sessions).

  • Customers can save an order and continue it the next time they login 

  • When an order is reopened, any pricing changes show automatically 

  • When an order is reopened, any products no longer offered are automatically removed

  • Product authorizations are rechecked automatically 

  Dynamic and customized purchasing. One goal of Mer-EC-Soln was to create a purchasing process based on a customer-host system profile, including:

  • Order terms (credit card, net 15, net 30, and so on) 

  • Shipping terms (Airborne, Federal Express, UPS, and so on) 

  • Customer contacts 

  • Ship-to locations 

  • Ship-from warehouse locations 

  • Drop shipment 

• Automatic authorizations. Automatic checking of orders for credit limit and product authorizations (some products require certified approval to resell). 

• Basic credit card validation. 

Additional Requirements

• Automatic uploading of placed orders to the host system for fulfillment. 

• Security for sensitive information such as credit card and drop-ship passwords (using SSL when transmitting over the Web). 

• Site security that allows customers to administer their own accounts. (See the section "User Authentication and Account Maintenance.") 

• Real-time queries to check placed order status and product inventory. 

• Variable privileges for users: product viewing, purchasing, and drop shipment. 

  Web site reporting, including:

  • Users/hour 

  • Visits/page

  • Logins/user 

  • Average logins/user 

  • User history 

System Architecture

Merisel chose a Windows-based Distributed Network Architecture (DNA) solution using Internet Information Server (IIS) and Active Server Pages (ASP) to provide dynamic content and user interface. Custom-built component object model (COM) objects were used to process and encapsulate Merisel business logic. Commerce Server objects were used to create and build customer order forms, add items to an order, and process orders. SQL Server was used to store product and customer information and process advanced queries. Distributed COM (DCOM) and Microsoft SNA Server were used to perform real-time requests against foreign systems.

Physical Architecture

The Merisel physical Web architecture consists of six main servers (two Web servers, two SQL servers, one membership server, and one host integration server). The hardware requirements and services used for these servers are explained in the following table.

Table 1 Physical Architecture 

Server	Qty	Services	Explanation
Web Servers	2	IIS 4.0 Microsoft Transaction Server (MTS) 2.0 Commerce Pipeline Objects Site Server Authentication Service Commerce Server	Contain the site's ASP files. Each server has an identical configuration with a single site for Mer-EC-Soln and separate virtual directories for the U.S. and Canadian sites.
SQL Servers	2	SQL Server	Provides all database services for the site including customer, product, and pricing information as well as the Commerce tables for shopping baskets and receipts. One server is dedicated to the U.S. site, the other to the Canadian site.
Membership Server	1	SQL Server Site Server Membership Directory Authentication Service LDAP Service MTS 2.0 Membership Objects	Provides all membership services including the Membership Directory and Membership COM objects.
Host Integration Server	1	SQL Server MTS 2.0 SAP DCOM objects SNA COMTI objects Site Server Usage Analyst SNA Server 4.0	Provides connectivity to mainframe and SAP systems by housing the appropriate objects, provides all batch processing for exchanging data between the production SQL Servers and host systems, and contains the SQL database used to import IIS logs and run Usage Analysis reports.
Staging Server	1	IIS 4.0 Site Server Publishing	Provides a staging location for testing programming changes, after which they can be moved to the production Web servers.

The only servers accessible to users are the Web servers. In order to have these servers accessible through the DNS name https://www.selline2.merisel.com , IP round-robining is used. With this technique, the DNS servers with the appropriate domain name rotate the returned IP address at regular intervals. When a user resolves the name to an address, they have an equal chance of receiving the address for either server. This effectively distributes the load between the two servers. Merisel's ISP, IKon, provides the DNS servers with the appropriate round-robin configuration.

Merisel is planning on purchasing a Cisco LocalDirector, which will remove both the need for DNS round-robining and the process for manually configuring the site for a failed server. This router will distribute all requests among available Web servers. If a server is not available, the router detects this and does not direct any traffic to it.

Logical Web Architecture

Merisel adopted a 3-tier approach to developing their business application. This is a logical division of the application and not a physical one. The following diagram shows the flow of information through these three tiers.

Figure 1 Logical Web Architecture 

Benefits of 3-tier Architecture

The logical architecture can map to multiple physical architectures by keeping objects stateless (or state-full only at the ASP file level). It can, therefore, easily be scaled up or down, from one machine for low load to as many machines as are required to balance the load.

• In a low-load or development environment, all the functional components can be hosted on a single server.

  In a high-load environment, the strain on the system can be measured, and each logical tier can be hosted on one or more physical servers running Windows NT. For example:

  • If the Web server is found to be a bottleneck, one or more dedicated Web servers can be added, making possible a round-robin scheme that balances the load across each of the servers.

  • If the business logic in the MTS packages strains performance, one or more middle-tier servers can be added, and the Web servers can remotely call the middle-tier MTS servers.

  • If product queries and pricing become load intensive, one or more servers can be added to host SQL Server and process the queries. 

Production Environment

An initial load study of 1000 concurrent users with typical usage led to this production implementation

Figure 2 Production Implementation 

Load Simulation

To validate the server capacity and configuration, the development team performed load simulation using InetMonitor 3.0, developing a script (SHOP.TXT) to simulate a user performing standard operations. In order to simplify the process, not all operations on the site were included in the simulation. For example, it was assumed that a quick product query would produce approximately the same server load as a detailed product query (based on development phase experience). In addition, several Membership operations were not included for general complexity in the script and because most internal operations are performed through a custom control. The goal of the simulation is not to test every aspect of the site, but to load the servers with sufficient traffic to simulate standard user traffic.

As the primary script, SHOP.TXT drives the simulation. The development team created it prior to significant actual user traffic and, upon further analysis of user tendencies, will modify it to more accurately reflect expected traffic and usage patterns. It performs the following core operations:

• Logs on to the site 

• Performs searches on the product database 

• Adds items to the shopping basket 

• Views the shopping basket 

• Finalizes a purchase 

The team created the following text files in order to randomize the data for user logons, queries, and products ordered:

• User Logon (SIGNONS.TXT) 

• Quick Search (QUICK_SEARCH.TXT) 

• Products Purchased (ADD_ITEMS.TXT) 

Although InetMonitor includes several counters for monitoring the Web servers, Performance Monitor was used to monitor key counters on both the Web and SQL Server-based servers and for creating logs and measuring performance during the load simulation. This was due to technical personnel's familiarity with the tool and its ability to measure performance on the SQL Server-based servers.

Security

Implementation of Site Server Membership for Mer-EC-Soln is based on the following application security requirements:

• Explicit session-based login process with a configurable session timeout property 

• Access to the U.S. (i.e., Mer-EC-Soln) site OR the Canadian (i.e., Mer-Can-EC-Soln) site, but not both sites 

• Access within a Web site granted (or restricted) based on a member's system privileges 

• Delegated administration to allow customers to manage their accounts/members remotely 

• Customizable membership administration utility for internal administrators 

• Online registration process and profile management interface 

• Simple procedure for extending the application security framework 

Certain user rights on each server have been restricted in order to provide extra security. The following user rights have been modified from the default setting.

Table 1

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

To minimize the potential for hacking, the following services have been disabled:

• Alerter 

• ClipBook Server 

• Computer Browser 

• Messenger 

• Net Logon 

• Network DDE 

• Network Monitor Agent 

• Simple TCP/IP Services 

• Spooler 

• NetBIOS Interface 

• TCP/IP NetBIOS Helper 

• WINS Client 

• NWLink NetBIOS 

Encrypting Data

Secure Sockets Layer (SSL) provides secure connections on the purchase authorizations and logon pages using a Verisign certificate loaded on each of the Web servers. Several pages on Mer-EC-Soln use SSL in order to encrypt sensitive information, such as user passwords. The following tasks were completed in order to use SSL:

• Certificate loaded on the Web server 

• SSL port specified on the Web site in IIS settings 

• Specific Web pages designated as requiring secure connection 

• SSL port opened on firewall 

The certificate may be loaded on any Web server but will only work properly if that server is accessed using the certificate's common name ( https://www.selline2.merisel.com ). If a server is to be accessed using another name, another certificate should be obtained.

With a certificate loaded on a Web server and the appropriate configuration in IIS, any page on the site may be accessed securely by simply specifying https:// instead of https:// in the browser address. In the case of connecting to a page from a link on the site, the link usually contains the specification to access the page securely.

Using the IIS Administration MMC, any page on a site may be specified to only allow secure access. In this case, if the page is not accessed via SSL, it will not be returned to the user. For Mer-EC-Soln, appropriate pages are specified as only allowing secure access. The user accesses these pages through links on the site that specify the appropriate security.

Database Access

The product table databases on both the U.S. and Canadian sites were designed to match (as closely as possible) the structure of the corresponding host data. This simplifies data transfer between platforms and makes it easier to replicate business logic from the host applications. The Commerce tables are independent of the host structures and are identical for both systems.

Since the design had to match as closely as possible the code at the U.S. and Canadian sites, the database structures had to be abstracted from the application so that the databases look identical to the application regardless of their very different structures. This was accomplished by accessing all product tables using stored procedures.

Equivalent stored procedures were created on each database with the same name, parameters, and return values. The details of equivalent procedures on each site are different due to the differences in database structure and, in some cases, business logic, but the Web application only needs access to the stored procedures—not information about the database structure.

This strategy provides an added benefit by separating programming tasks.

• The SQL developer responsible for database design and logic can make changes to the stored procedures as modifications are made to the database without affecting the application.

• The Web developer requires no knowledge of the unique structures between the two databases and is unaffected by any changes during development.

Connection pooling within MTS allows the application developers to generalize and centralize database access. All selections and updates are encapsulated within one Visual Basic® module, and when an object needs to perform database access, it simply sets up the string for calling the stored procedure and passes it to either DoSearch() to get a disconnected recordset, or to DoAppend to do an update.

An object running within MTS can easily set up a string representing a stored procedure call and invoke DoSearch(). An example call to Dosearch is:

vErrorCode = DoSearch(CStr(vDB), True, "EC-sp_ByMerPart" & vCustNo & ", " & 
vMerPartNo & "," & vMaxRows, vResultSet, vbErr, vbErrDescription)

In this case, the stored procedure EC-sp_ByMerPart is called with the parameters: Customer Number, Merisel Part Number, and the maximum number of rows to return. DoSearch returns a disconnected recordset, which allows objects running within MTS to remain stateless since a disconnected recordset copies its data to the caller. If the caller is a client in a separate process or another computer, the recordset marshals its data to the client's process. When going across a network, it compresses the data to use less bandwidth.

Performing Batch Transfers

Data services in Mer-EC-Soln (aside from the real-time connections to host systems) are provided by SQL Server. Product, pricing, and customer information from Merisel's hosts are moved at periodic intervals into SQL databases, while orders are uploaded back to the hosts. The following tables list the different types of data required by the system and their batch transfer requirements.

Table 2 Data Required by the System 

Data	Description
Product	Product information including vendor information and product descriptions.
Pricing	All tables required for calculation of prices.
Customer	Merisel reseller information.
Inventory Bulk	Inventory by warehouse for each product.
Inventory Delta	Inventory by warehouse for subsets of products. This information is updated throughout the day with only information for products whose inventory is modified.

Table 3 Batch Transfer Requirements 

Data	Direction	Frequency
Product Pricing Customer Inventory Bulk*	Host to SQL Server	Nightly
Inventory Delta*	Host to SQL Server	Hourly
Order Detail	SQL Server to Host	Every 15 minutes

A single database stores the tables specific to the Commerce Server (including the basket and receipts tables). Two identical databases, which are transferred regularly from the hosts, were created for the product, pricing, and customer tables. The data in these databases is read-only to the application. Each Web server has a separate DSN referring to each of the product databases.

A separate server is used to drive this process rather than incur processing on the Production Servers. In addition to processing advantages, this method allows Merisel to provide access between the DMZ (de-militarized zone) and corporate network only to the single Host Integration Server rather than to the Production Servers.

An application written with the Visual Basic 6.0 development system and that is scheduled to run through the SQL Server Executive on the Host Integration Server drives the data transfer process. This application:

• Transfers data files from the hosts and Windows NT-based servers using FTP. (Internet transfer control. Replication is another viable process.) 

• Uses bulk copy program (BCP) functions to transfer data from text files into SQL Server. (BCP taken from DB Library for Visual Basic.) 

• Launches SQL Server-stored procedures for data processing. 

• Makes appropriate modifications to GLOBAL.ASA once new product data has been moved into production. 

• Transfers order records from SQL Server to text files. 

• Transfers text files containing orders to host systems using FTP. (Internet transfer control. Replication is another viable process.) 

• Marks order records as uploaded when appropriate verification is complete. 

The data transfer application provides considerable error checking and data validation to ensure data integrity. FTP transfer functionality is provided by the Internet Transfer Control included in Visual Basic 5.0. DB-Library for Visual Basic was used to access BCP and SQL functions.

Accessing the Host in Real Time

Merisel required two pieces of information to be retrieved real-time from the host systems: product inventory and order status. During product queries, the system reports on inventory that has been in the SQL tables for up to an hour and determines whether an item is in stock. The user can request a detailed listing of how many items are in stock at each warehouse. This feature requires real-time information due to the volatile nature of product inventory. As for order status, users can track orders to see their status in real time.

Accessing the Mainframe

COM Transaction Integrator (COMTI) is shipped with SNA Server and allows a COM component to access an existing host transaction. An object written with COMTI for CICS (or IMS) allows real-time access to the Merisel IBM mainframe. The required transactions for order status and single product inventory already existed in the original system. The COM component accepts arguments from the Web application, passes them to the host transaction, retrieves the results, and passes them back to the application. The COM component resides on the Host Integration Server with SNA Server.

Accessing SAP

The SAP DCOM Component Connector, which may be downloaded from SAP's Web site ( https://www.sap.com ) allows writing a COM component to access existing Requests for Comments (RFC) in SAP R/3 on any platform. Using SAP DCOM Component Connector, the developers wrote a COM component that allows real-time access to SAP on HP UNIX. The original system already had the required RFCs for order status and inventory of a single product. The COM component accessing SAP resides on the Host Integration Server.

Implementation

To provide functionality via the Web without burdening the current systems, the designers built a loader that mirrors product and pricing information from the daily business systems over to high capacity Microsoft SQL Server-based computers. The design also includesseparate Web servers to respond to HTTP requests and serve up Web pages. The loader runs nightly during off-peak hours to keep product offerings and pricing information accurate. This basic strategy makes it possible to build a functionally rich application that adds neither risk nor burden to the current systems.

Tools Used

• Microsoft Visual Basic® development system 6.0 (COM Components) 

• Microsoft Visual C++® development system 6.0 (SAP DCOM Component) 

• Microsoft Visual InterDevTM Web development system 1.0 and SP3 (ASP and HTML) 

• Mircrosoft FrontPage® 98 Web site creation and management tool (HTML) 

• Mircrosoft SQL Server 6.5 and SP4 (Stored Procedures and databases) 

• Mircrosoft Visual SourceSafeTM version control system (Source Code Control) 

• COMTI Component Builder (Host Integration) 

• Web Cat (Performance and Load Testing) 

Technologies Used

• ActiveX® Data Objects (Data Access to SQL Server) 

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

• Client side ActiveX Controls (for Merisel internal pages only) 

• Remote Data Servers (RDS). Used to instantiate remote objects from Merisel internal clients running the Win 32® application programming interface and Internet Explorer 4.0over http using RDS.CreateObject(). 

• Windows NT Server 4.0 has several security patches that should be applied on top of Service Pack 3. Service Pack 4 provides the necessary patches, and a decision was made to wait for SP4 to be released before applying the patches. 

• SAP DCOM Connector (SAP Integration) 

• COM TI / SNA Server 4.0 (Host Integration) 

• Internet Information Server (IIS) 4.0 

• Microsoft Transaction Server (MTS) 2.0 

• Site Server 3.0 Commerce Edition 

Reporting

Reporting on Merisel's two sites (Mer-EC-Soln for the U.S., Mer-Can-EC-Soln for Canada) is accomplished with the analysis tools in Site Server. The system uses Host Integration Server both to import and store log information and to process reports. One complexity of reporting Web usage is the inability to distinguish user sessions from logs. Site Server Analysis performs analysis logic against log data to estimate usage, including separate user visits and page requests (ignoring all elements on the page) rather than the classic measure of raw hits.

Usage Import brings in log files from both Web servers on a weekly basis. A significant portion of analysis logic is performed at this level: Server Manager (in Usage Import) analyzes traffic between the two sites upon log import. Usage Import also imports batches of files and can backout entire batches as opposed to individual files. Merisel imports all files from a single week as a complete batch, archiving this data in one-week increments.

Creating Log Files

Log files are stored on the Web servers in the d:\weblogs directory. This directory is shared with the share name weblogs so that it may be accessed with the UNC \\servername\weblogs. The log files are imported into the Analysis database so that reports may be run against them. Once they are imported, they may be removed from the local file system. To make sure that data is not lost, log files should be removed from the Web servers approximately once a month to ensure that they have been properly imported into the database.

Importing Web Logs

Site Server Usage Analysis is an application that manages the process of importing Web log files into SQL Server for later access by Report Writer. It includes a process for creating a database, but this has already been done for Mer-EC-Soln.

Usage Analysis uses the following information on the SQL Server database. The database resides on the same server currently performing the bulk of import logs and reports. However, this does not have to be the case. Import and reporting processes simply need a valid ODBC connection to the server containing the database. The server simply needs to have SQL Server installed.

Table 4 Database Usage Analysis 

Server	DB02
Database Name	Mer-EC-SolnAnalysis
User Name	Sellanlaysis

A job scheduled on DB02 imports the log files each night. The scheduled job is configured using the Site Server Scheduler accessed from the Site Server Analysis Usage Import. This tool creates a batch file and schedules the job through the Scheduled Tasks in Windows NT.

Table 5 Sample Import Configuration 

Server	DB02
Scheduled Task	Daily Log Import
Batch File	D:\Microsoft Site Server\Data\Analysis\Schedule\Daily Log Import.bat
Log File	D:\Microsoft Site Server\Data\Analysis\Message\ReportUI.log
Schedule Time	Every day at 1:00 AM

Under normal operation, this imports the previous day's log files from both Web servers. The imported log may be verified either by checking the log file or by viewing the Import History in Usage Import. Should the automated process fail, the log files can be manually imported by starting Usage Analyst and selecting the appropriate log files to import. Should the automated process fail for several days, multiple days worth of files can be imported at one time.

The log files from both Web servers should be imported at the same time for a particular day (or set of days). However, a single user session may occur across both servers. In this case, the importing process analyzes all files from a particular day as one set of data. Should files from multiple servers be imported separately, the results may display an incorrect increase in the number of visits.

Generating Query Strings 

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

/Mer-EC-Soln/QSResults.asp QT=5&BPN=0&Crit1=microsoft+press&Crit2=&MR=25

QT	Quick Search Type
BPN	Beginning Part Number—Used
Crit1	First criteria specified by the user—as defined by the search type
Crit2	Second criteria specified by the user—may be empty depending on the search type
MR	Maximum Results—number of matching records to display

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

Table 6 Import Configuration 

File system paths (URIs) containing query strings to import:	/Mer-EC-Soln/QSResults.asp /Mer-EC-Soln/DSResults.asp /Mer-EC-Soln/confirmed.asp /Mer-EC-Soln/descinquire.asp
Names of single-value query parameters to parse:	CL CRIT1 CRIT2 ORDER_ID PARTNO QT

Deleting Data 

The initial two months of Mer-EC-Soln usage have not produced an unreasonable amount of data in the usage database (less than 100 MB per month). However, the database size should be monitored and data deleted at regular intervals to ensure that space does not run out. The process to remove data from the database can take significantly longer than adding data. See Site Server's online documentation for a description of this process.

Generating Reports

Report Writer in Site Server produces reports based on the Usage Import data. To make this data available to Merisel executives and other staff, the reports are configured to generate HTML files posted on an Intranet server, providing up-to-date statistics accessible from any corporate network client. The items in the weekly executive summary reports include:

• Number of requests/visits by date 

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

• Top users 

• Top pages 

• Number of orders by date 

• Number of new users registered 

Currently, commerce-specific data, including most popular products and vendors, is not included in the usage reports. Number of orders is calculated by counting the occurrences of the order confirmation page. The current method under consideration for capturing detailed information on product queries and purchases is to log this information.

Filters may be placed on a report or a section of a report using a relative date to allow running a report for a specified period without modification. For example, a report titled Weekly Mer-EC-Soln Usage uses a filter called LastWeek to report all usage from the previous week. This report is run every week and reports usage from the week prior to the date it is run.

Reports may be scheduled to run automatically using the Scheduler in Report Writer (this is the same scheduler used for import logs—it may be accessed either from Report Writer or Usage Import). For example, the Weekly Mer-EC-Soln Usage report has been scheduled to run every Sunday evening. This reports usage from the previous week and stores its results in an HTML file on the Intranet server. This allows any corporate network client to connect to the Intranet server and view usage statistics.

Publishing

Publishing refers to the act of moving content and applications from development into production. The content is first moved to a staging server where it is tested in a near production environment. Once it has been approved, it is moved into production.

The following table shows the servers involved in the Mer-EC-Soln publication process.

Server	Role	Directory
DEV1	Development	e:\inetpub\wwwroot\Mer-EC-Soln2
STAGE1	Staging	d:\inetpub\Mer-EC-Soln2
PROD02 PROD03	Production	e:\wwwroot\Mer-EC-Soln2

Content starts on the development server, DEV1, as shown, and is published out to the staging server STAGE1. STAGE1 is configured with the entire site and is accessible to the Merisel corporate network. Users connect to this server to test the new content.

When finished with a series of changes, a developer checks them into the development server for testing by another developer. This allows the developer freedom to make changes without destabilizing the system.

After the changes are passed, the application (ASP, HTML, COM objects, and stored procedures) is transferred to the staging server using a combination of CRS (Site Server Content Replication Services) and manually copying the DLLs and creating the MTS packages.

The QA team performs further testing and stress analysis against the staging server. When the new content is approved, it is moved to the production servers. These servers are not accessible from the corporate network.

This process is diagrammed below:

Understanding the Different Types of Accounts

In order for the publication process to work appropriately, a common account must be used throughout the process with sufficient rights to perform the necessary activities. The following three accounts have been set up at Merisel.

• Project Level Authentication Account. Optionally specified on each server for a specific project; overrides the Default Authentication Account. 

• MMC Authentication Account. Specified on one server to administer publishing on another server. 

The Site Server Content Deployment service runs under the Local System account on all servers including development, staging, and destination. An account called Mer-EC-Soln_Publish has been created on each server. This account has been specified as the Default Authentication Account. A Project Level Authentication Account was not used for Mer-EC-Soln.

Managing the Publishing Process

You can administer the publishing service from any server with a connection to the server that you want to administer. Management of the Mer-EC-Soln publishing process is most easily done by adding the four servers in the process (development, staging, two production) to a single MMC.

To connect to a server in MMC 

1. In the MMC, right-click Publishing, and then click Add Host. 

2. Type in the name or IP address of the server. 

3. Click Connect Using Authentication Account and type in the name and password of an account with Site Server Administrator privileges on the remote server. 

Initiating the Publishing Process

The Mer-EC-Soln publishing projects have been configured to operate manually, so an operator must initiate the process. Projects can also be configured to occur automatically according to a schedule, but no Mer-EC-Soln project was configured in this fashion.

The projects created to publish Mer-EC-Soln information are as follows.

Name	Origin	Destination
Mer-EC-Soln Content Staging	DEV1	STAGE1
Mer-EC-Soln Content Production	STAGE1	PROD02 PROD03

The project must be created with the same name on each server participating in the project. It may only be started from the origin server. Since you can connect to a server from another computer using the MMC, you can start a project while physically working at another computer.

To start a project 

1. Select the project in the MMC (again on the origin server). 

2. Click Action, and then Start.

   The job shows a status of Started and then Running. If it completes successfully, it will show a status of Complete. If not, it will show Error. If you do receive an error, check the Application Event Log using the Windows NT Event Viewer for more information. 

Code Components

Application functions involve all operations from initialization to order processing and real-time order status checks.

Initializing Mer-EC-Soln

Mer-EC-Soln initialization follows the basic rules of ASP. All application processing begins in the GLOBAL.ASA the first time a user visits the site. Application initialization includes:

• Setting up the site type (U.S. or Canada).

• Setting up the site dictionary. The site dictionary includes the hostname, SQL connection strings, and security settings. 

• Initializing global objects used frequently throughout the site. This includes an Error List (of type Commerce.SimpleList) used for error handling and reporting throughout the site. It also includes initializing lists used in the purchasing process: selWarehouse (warehouses), selTerms (shipping terms), (selShipVia) shipping options. Each purchasing list is read from the product database, then initialized and stored with the ASP Application object save on I/Os during customer purchases. 

• Initializing Commerce Server-specific objects, including the site dictionary, a global data functions object (Commerce.DataFunctions) to save on object initializations and tear downs on a page basis, and a query map (Commerce.Dictionary) used for queries in the Order Processing Pipeline. 

Upon completion of processing the GLOBAL.ASA, the ASP files have access to the Application object and the associated Mer-EC-Soln and Commerce objects.

Registering and Authenticating Users

The Merisel Mer-EC-Soln system provides an online registration process for new members. The process begins by explicitly requesting membership profile information from the current user. In addition, a user must provide the account number and access credentials to be enrolled as a member of that account.

The core Mer-EC-Soln Membership features include:

• Customized member profile and membership directory 

• Online member registration and profile management 

• Multiple membership sites with member-specific access 

• Distributed membership and account administration 

Figure 3 New Member Registration Page 

Once a user supplies all the requested information and submits it to the system, the following process flow takes place. This customized process flow represents the Mer-EC-Soln online registration process.

Figure 4 Registration and Authentication Process 

The application uses HTML Forms Authentication to authenticate a user's access to the site. Security is further enhanced within the .ASP pages where the .ASP scripts instantiate an Active User Object (AUO) to authenticate the user prior to instantiating objects or dynamically writing out privileged HTML options.

For example, during the order purchase process, the OrderFormCust.asp, which is used to dynamically write out the ordering terms, first instantiates the AU object, checks to see if this customer has drop-ship privileges and then optionally writes the HTML to allow the order to be drop-shipped. It is also important to note that the .ASP that checks the fields on the submission of this form also verifies the AU object for drop-ship since the sending HTML form could have been manually saved, altered, and then resubmitted.

Users authenticated through HTML Forms Authentication come into the ASP pages as the anonymous user IUSR_MACHINENAME. Since all privileged functionality is restricted within the .ASP pages, the objects within MTS run without any defined security or roles. Data access from the objects to SQL Server is done using the standard security model and ADO. A small set of restricted and reusable IDs were created in SQL Server. When an object needs to access the data server, it goes through the following steps:

1. Gets an ADO Connection using the user id 'webaccount' 

2. Performs the select, insert, or update (via a stored proc) 

3. Releases the connection 

Objects running within MTS and using ADO (ODBC 3.0 or higher) to SQL Server get connection pooling for free. That is, MTS pools the connections, automatically freeing the objects from having to implement a connection strategy.

Whenever unauthenticated users request a secure page, Site Server automatically redirects them to the following customized login page:

Figure 5 Membership Login Page 

Redirecting Unauthenticated Users 

Site Server automatically redirects an unauthenticated user to ../_mem_bin/formslogin.asp once the Web site is secured with HTML Forms Authentication. The Mer-EC-Soln system adds a second level of redirection from FORMSLOGIN.ASP to FORMSLOGIN_SSL.ASP for additional security. Site Server automatically sends the originally requested URL as the login query string.

1. Whenever an unauthenticated user requests a page from the Mer-EC-Soln Membership-enabled Web site, a series of validations and process redirections take place before the requested page is delivered to the end user. The steps below represent the Mer-EC-Soln Membership authentication process, which is a customized version of the HTML Forms Authentication process based on the custom AUTHENTICATE.ASP file. The diagram shows the flow of these steps.An unauthenticated user requests the https://www.selline2.merisel.com/ page, which is part of a Membership-enabled virtual directory called Selline. 

2. Site Server recognizes that an unauthenticated user has requested a secure page and redirects to https://www.selline2.merisel.com/_mem_bin/formslogin.asp?/Selline.default.asp . Notice that the page originally requested is passed into the FORMSLOGIN.ASP page as a query string. The page originally requested is referred to as the URLRequested. 

3. The Mer-EC-Soln implementation of FORMSLOGIN.ASP simply provides a Secure Sockets Layer (SSL) redirection to FORMSLOGIN_SSL.ASP. The URLRequested passed into FORMSLOGIN.ASP is also passed into the FORMSLOGIN_SSL.ASP page as a query string. 

4. The FORMSLOGIN_SSL.ASP page presents the user with a secure, encrypted channel to enter their personal Member ID and Password for authentication into Mer-EC-Soln. 

5. The entered Member ID and Password along with the passed-in URLRequested are then posted to the AUTHENTICATE.ASP page for online validation. 

   The AUTHENTICATE.ASP page performs this sequence:

   • Validates the Member ID and Password against the Membership Directory. 

   • Checks the Member status attribute to see if it is currently set to disabled. 

   • Validates the Member country/region attribute based on the virtual directory that is requesting authentication (that is, "US" for Mer-EC-Soln or "Canada" for Mer-Can-EC-Soln). 

   If the end user passes the validations above, then AUTHENTICATE.ASP generates the FormsAuth cookie and redirects the user to the page that was originally requested (URLRequested), which completes the Membership authentication process. 

Figure 6 

Site Server Membership uses the FormsAuth cookie to determine the scope and lifetime of an authenticated user's login session. Since the Mer-EC-Soln membership-enabled Web site consists of two independent IIS virtual directories (Mer-EC-Soln and Mer-Can-EC-Soln), the FormsAuth cookie has been customized to secure access to Mer-EC-Soln at the virtual directory level. Basically, the scope of the FormsAuth cookie has been limited to the virtual directory containing the AUTHENTICATE.ASP file in which the FormsAuth cookie is generated. If the FormsAuth cookie expires due to inactivity, the user is once again redirected to the FORMSLOGIN.ASP page and the authentication process is repeated.

Application Authentication and Database Access

The Member schema class is the fundamental structure that supports the Active User Object (AUO)— the object that contains information about the currently authenticated user. The Mer-EC-Soln implementation of the Member schema class consists of the following standard and custom member attributes.

Table 7 Member Schema Customizations 

Member Attribute	AUO Property	Custom	Data Type	Required
GUID	Guid	N	String	Y
Member ID	Cn	N	String	Y
Member Password	UserPassword	N	String	Y
Account ID	AccountID	Y	String	Y
Account Telephone	AccountTelephone	Y	String	Y
First Name	FirstName	N	String	Y
Last Name	SurName	N	String	Y
Company Name	CompanyName	N	String	Y
Job Title	JobTitle	N	String	N
Office Address	officePostalAddress	N	String	Y
City	l	N	String	Y
State / Province	st	N	String	Y
Postal Code	postalCode	N	String	Y
Country	c	N	String	Y
Office Phone	officePhone	N	String	Y
Office Extension	officeExtension	Y	String	N
Office Fax	officeFax	N	String	N
Additional Contact	additionalContact	Y	String	N
Email Address	mail	N	String	Y
Mailing List	mailerNoEmail	N	Integer (0-1)	Y
Place Orders	placeOrders	Y	Integer (0-1)	N
Drop Shipments	dropShipments	Y	Integer (0-1)	N
Account Admin	accountAdmin	Y	Integer (0-1)	N
Merisel User	meriselUser	Y	Integer (0-1)	N
Merisel Admin	meriselAdmin	Y	Integer (0-1)	N
Organization Name	ou	N	String	N
Accounts	groups	N	Distinguished Name (DN)	Y
Date Last Visited	LastVisit	N	Generalized Time	N
Member Status	accountStatus	N	Integer (1-4)	Y
DS Privileges	privileges	N	String	N

The customizations for the Account (mGroup) and the Organization (organizationalUnit) schema classes are noted in the following tables.

Table 8 Account Schema Customizations 

Account Attribute	AUO Property	Custom	Data Type	Required
Account ID	AccountID	Y	String	Y
Account Name	DisplayName	N	String	N
Description	Description	N	String	N

Table 9 Organization Schema Customizations 

Organization Attribute	AUO Property	Custom	Data Type	Required
Organization Name	ou	N	String	Y
Description	description	N	String	N

Implementing Functionality 

The set of files listed below are used to implement the core Mer-EC-Soln Membership functions. The table that follows identifies the function of each of the different Membership files. Some of fundamental relationships associated with the Membership files include:

• THE FORMSLOGIN.ASP, FORMSLOGIN_SSL.ASP, and AUTHENTICATE.ASP files implement the Mer-EC-Soln authentication process. (See the "Authentication Process" section for details.) 

• The PROFILE.ASP, SWITCHACCTS.ASP, ACCTADMIN.ASP, PRIVILEGES.ASP, MEMACCTS.ASP, and MDADMIN.ASP files logically constitute the functionality of the ACCTMAINT.ASP file. 

• The MDADMIN.ASP file is simply a deployment page for the MEMBERSHIP.OCX file. 

• The Membership.dll file represents the core API and underlying customized structure of the Membership Directory. It is a direct link into all of the membership data. 

Table 10 

File	Function
FORMSLOGIN.ASP	Redirects an unauthenticated user to the secure login page
FORMSLOGIN_SSL.ASP	Provides a secure login page against the Membership Directory
AGREEMENT.ASP	Standard membership agreement for online registration
REGISTRATION.ASP	New member online data entry, validation and registration
WELCOME.ASP	Successful online registration and welcome page
AUTHENTICATE.ASP	Customized member login/authentication process
ACCTMAINT.ASP	Online membership account access and maintenance
PROFILE.ASP	Online modification of a member's personal profile
SWITCHACCTS.ASP	Custom account switching process for privileged members
ACCTADMIN.ASP	Online account administration for privileged members
PRIVILEGES.ASP	Updates member privileges within the Membership Directory
MEMACCTS.ASP	Add/remove member from multiple membership accounts
MDADMIN.ASP	Membership Directory administration for privileged members
FORMembership.DLL	Custom Membership Directory API based on FastADS.dll
ForMEMBERSHIP.OCX	Custom Membership Directory administration control
ACTIVEUSER.INC	Standard interface for accessing the Active User Object (AUO)
DTCLIB.INC	Customized design-time control library for online registration
MEMBERSHIP.INC	Standard constants for accessing Membership Directory attributes

Navigating the Mer-EC-Soln Site

DEFAULT.ASP, the main navigation page, includes command buttons and hyperlinks that navigate to the page hosting the desired function: QuickSearch, DetailedSearch, View Current Order & Purchase, Check Order Status, Account Maintenance, and Help.

Figure 7 Mer-EC-Soln's Main Navigation Page 

Each functional page also includes two files, TOPNAV.ASP (at the top) and TEXTNAV.ASP (at the bottom) and HTML to provide hyperlinks to the other functional areas on the site.

Figure 8 TOPNAV.ASP and TEXTNAV.ASP 

Performing Product Queries

Users can perform quick product queries by navigating to the above page. The above form's Action attribute is set to QSRESULTS.ASP. Mer-EC-Soln processes the query submitted by the above form as follows:

1. QSRESULTS.ASP parses the form and instantiates a quicksearch object.

   Based on search criteria, it calls one of these QuickSearch methods:

   • PartSearch() 

   • MFCPartSearch () 

   • TitlSearch () 

   • MFCTitlSearch () 

   • MFCSearch() 

   • UPCSearch() 

2. The QuickSearch method opens an ActiveX Data Object (ADO) connection to the products database and invokes a stored procedure that queries the product table. 

3. The stored procedure passes a recordset back to the method. 

4. The method disconnects the resulting recordset and returns it to QSRESULTS.ASP. 

   QSRESULTS.ASP dynamically displays an HTML table to the user showing matching part numbers, descriptions, and a hyperlink for adding it to the shopping basket.

The following diagram illustrates this querying process:

Figure 9 Process for Performing Quick Product Queries 

Detailed product queries are similar in processing to the quick search queries, with the following exceptions:

• The user is given a set of hierarchical navigation pages to first select product category, multiple sub-categories, and then media format. 

• After selections are made, the form action invokes DSRESULTS.ASP. 

• DSRESULTS.ASP instantiates a DetailedSearch object, which calls a stored procedure and returns a disconnected recordset back to the page.

The page then writes the resultset dynamically into an HTML table and sends it back to the user.

Creating Orders and Checking Inventory and Order Status

Real-time inventory and order status checks are handled differently between the U.S. and Canada. The differences between these processes are described below and illustrated in the accompanying diagrams.

For Canadian customers:

• A check is made against the Canadian SAP system. 

• The page receiving the request, QTYCANINQUIRE.ASP, instantiates a COM object that issues the SAP RFC (remote function call) over the SAP DCOM connector. 

• Results are returned to the Web page in the form of a recordset, which is dynamically written back to the HTTP stream. 

Figure 10 Canadian Inventory and Order Status Checking Process 

For U.S. customers:

• The page receiving the request, QTYINQUIRE.ASP, executes a COMTI object. 

• The COMTI object issues a CICS request over SNA Server.

• The results are returned to the Web page in the form of a recordset, which is dynamically written back to the HTTP stream. 

Figure 11 U.S. Inventory and Order Status Checking Process 

In either case, an identical HTML page is returned to the user with the inventory and order status information.

Figure 12 Status Page Returned to the User 

The process for creating an order or checking on the status of orders is described below and illustrated in the diagram following:

• Users are authenticated and start at DEFAULT.ASP where they can submit product queries, check order status, update their own accounts, check Mer-EC-Soln news, or download price books. 

• Product query results contain a summary description, catalog price, and inventory status, and the ability to add the item to the order.

• From the query results, users can request a detailed product specification sheet, custom pricing, or real-time inventory from each of the warehouses. They can also add the item to their order. 

• The BASKET.ASP displays the order to the user. Each time the order is displayed, the Order Processing Pipeline (OPP) refreshes the pricing of the contents. 

Figure 13 Site Navigation for Creating an Order and Checking Status 

Processing Order Forms

Each product query page that returns a matching set of results includes a hyperlink to a page (ADDITEM_XT.ASP) that adds the product item to the customer's basket. This is implemented using the Commerce Server OrderForm (Commerce.OrderForm) and DBStorage (Commerce.DBStorage) objects.

When customers request to update their order form:

• Their order is retrieved from the Orders Database, instantiated into a Commerce order form and updated by running through the Order Processing Pipeline (OPP).

• The OPP calculates (or re-calculates) pricing information, checks (or re-checks) purchase authorization, and sub-totals the order.

• The updated order form is re-saved to the database and displayed to the user. 

Figure 14 Order Form Processing 

Prior to invoking the Order Processing Pipeline, BASKET.ASP invokes a Mer-EC-Soln object and calls the GetReceiptNum method to generate a Merisel receipt number. It then instantiates a MTS Pipeline object (Commerce.MtsPipeline) to execute each of the stages of order form preprocessing, which include:

• Refreshing product information and list prices against the product database 

• Rechecking product purchase authorization 

• Invoking a Visual Basic COM object which refreshes custom pricing 

• Subtotaling 

Placing an Order to Complete the Purchasing Process

The final stage of the ordering process allows the customer to purchase the items added to the order form. From the basket page (BASKET.ASP), a customer can submit the order and purchase the items selected. When customers select purchase, the BASKET.ASP directs them to ORDERFORMCUST.ASP, which invokes an OrderHelper object and also uses the global application objects to dynamically build an order page customized for each customer. This includes:

• Customer registered ship-to address 

• Ship-from warehouse 

• Shipping terms 

• Payment/ordering terms 

• Registered contact to send to 

• Optional drop-ship 

In the same fashion as the search objects, the OrderHelper object uses ADO to get a connection to the product database and invokes stored procedures that return recordsets back to the client (in this case ORDERFORMCUST.ASP), which dynamically writes HTML option boxes to the HTTP stream.

The action of ORDERFORMCUST.ASP directs the submission to PREPARE_XT.ASP, which checks each of the fields entered, updates changed fields, and re-saves the order form. Based upon the options specified, ORDERFORMCUST.ASP then either redirects to pages to get credit card and/or drop-ship information and/or it directs to a page (PURCHASE_XT) which invokes the order purchasing pipeline to complete the purchase.

After the customer reviews and accepts (submits) this page, they are directed to a page (PURCHASE_XT.asp) which invokes the order purchasing pipeline to complete the purchase.

The final purchase step invokes the OPP to check the credit card and make sure the information submitted is well formed, assigns credit for the order to the appropriate sales representative based on account identification, and moves the order within the Orders database from the basket table to the receipts table.

The navigation to purchase an order is described by the diagram below:

Figure 15 Merisel Site Navigation for Completing the Purchase of an Order 

Order Processing Pipelines

The application uses two different order processing pipelines: one for order preprocessing and subtotaling (plan.pcf) and the other to complete the purchase (purchase.pcf).

The preprocessing and subtotaling pipeline performs the following steps:

• Refreshes product information and list prices against the product database. (This is accomplished in the Product Info stage and the QueryProdInfoADO component.) In this component, a stored procedure refreshes the product information of items in the customer's basket and verifies that the items are still available. Any items that Merisel no longer carries are removed, and list prices are updated. This is an important stage since a customer can save and reopen order forms days or weeks later when the customer revisits the site.

• Rechecks product purchase authorization. (This is also accomplished within the QueryProdInfoADO ADO component.) The stored procedure completes a secondary check to determine whether the customer is approved to purchase each requested product. Some products offered through the application require certificates to purchase and resell.

• Refreshes custom pricing and gives customer rebates. (This is completed in the ItemPrice stage.) A custom Visual Basic COM pipeline object invokes stored procedures to execute an elaborate pricing schema based on customer contracts and purchase history to calculate a custom price.

• Subtotals the order. This is accomplished by invoking the default components: DefaultOrderSubTotal, DefaultShipping, DefaultHandling, DefaultTax, and DefaultTotal.

Upon completion of executing the preprocessing and subtotaling pipeline, the user can display the order form for review, continue shopping, or purchase. The purchase pipeline executes the following steps:

• Checks for a credit card number and validates that the number entered is well formed. (This is accomplished using the ValidateCCNumber component.) A credit check against the card is performed on the backend SAP or CAMBAR system. 

• Assigns credit to an account rep based on the account ID of the user. (This is accomplished through a custom built Visual Basic COM pipeline object.)

• Copies a snapshot of the order to the receipt table. All pricing included in the table represents the price frozen at the moment of purchase.

Custom-built pipeline components were built in Visual Basic as ActiveX-based DLLs and are hosted inside of MTS. The components are fairly standard and implement the IPipelineComponent interface.

Commerce Interchange Pipeline

In the follow-up release, Mer-EC-Soln will use the Commerce Interchange Pipeline (CIP) for remote connectivity from reseller e-commerce solutions back to Mer-EC-Soln's site. The system will provide the following advantages:

• Distributed host and SAP access through COM 

• Direct shipping from the nearest warehouse 

• Reseller order status via a Web interface 

• Resellers can hide stock shortages from consumers and automate replenishment (via CIP) 

• Resellers can place orders and check inventory directly from a reseller's Web site 

For an example, a consumer shops on a local reseller's Web site, such as PCMall.com. As the consumer checks the reseller's inventory, the reseller's system first checks their local inventory. If there is no inventory, the reseller's system connects back to Mer-EC-Soln using the CIP and checks Merisel's inventory. (The consumer doesn't see this process.) When the consumer places an order with the reseller site, the reseller commerce site commits the inventory and order with Merisel, again using the CIP. The following diagram shows how this process works.

Figure 16 The CIP Process 

Application Data Objects

The Mer-EC-Soln object model is flat, and objects can be allocated, called, and de-allocated without any side effects or the expectation that state needs to be held between calls. To allow high scalability and performance when being run locally or remotely, objects are kept stateless and return either simple standard Visual Basic automation types or ADO disconnected recordsets. For transactional support, process isolation, and remote capabilities, business objects are placed into packages to run under MTS.

The philosophy used in building the application is to:

• Incorporate as much of the data logic as possible into SQL Server stored procedures. 

• Incorporate the business logic into COM objects written in Visual Basic (primarily). This yields better performance as these objects are precompiled and callable from ASP, Visual Basic, Visual C++, and Visual Basic for Applications-enabled applications. 

• Limit as much as possible the use of ASP and HTML to UI logic. All data access is done exclusively through business objects. ASP never directly connects to the data tier.

The Mer-EC-Soln objects fall into five categories: shop, pipeline, formatting, membership, and real-time.

Shop Objects

Each of the shop objects is implemented as COM objects written as a Visual Basic-based ActiveX DLL. The objects are all contained physically in the same DLL (Mer-EC-Soln.dll).

Customer 

CustCheck—Checks to see that the passed in AccountID is a valid Merisel account number.

GetCommissionAcc—Based on the passed in AccountID, returns the ID of the Merisel account representative who receives credit for the order.

GetDropAddShip—Based on the passed in AccountID, returns a disconnected recordset of drop-ship addresses previously shipped to for this customer. Order is based on most recently used.

PutDropAddShip—Saves the passed in drop-ship address based on account identification. Does not save duplicates or number of times used.

DetailedSearch 

CanSrchExt—Passed in the search Criteria for a detailed Canadian search along with the beginning part number and the maximum matches to return, returns a disconnected recordset of matches. When called with a beginning part number of 0, also returns the maximum number of matches. This function can be called iteratively, bumping up the beginning part number each time to return large sets, one piece at a time.

SrchExt—Passed in the search Criteria for a detailed U.S. search along with the beginning part number and the maximum matches to return, returns a disconnected recordset of matches. When called with a beginning part number of 0, also returns the maximum number of matches. This function can be called iteratively, bumping up the beginning part number each time to return large sets, one piece at a time.

DownloadLib 

GetProdImg—Passed in a Merisel PartNumber, returns the location on disk of the corresponding image file.

GetProdDesc—Passed in a Merisel PartNumber, returns a detailed description of the product.

GetMetalLvl—Passed in a customer ID, checks the customer's retailer class and returns the ftp location of a downloadable price book.

DumpForm

BasketDump—Used for debugging. Passed in an AccountID, ShopperID and a file name, checks the basket table to see if the user has a current order and if so, dumps the contents to the specified file.

ReceiptDump—Used for debugging. Passed in an OrderID and a file name, checks the receipt table to see if the order exists and if so, dumps the contents to the specified file.

Inventory 

GetInvLocal—Passed in a part number, checks against the local SQL databases for inventory levels at each of the Merisel warehouses. Inventory information from this call is not real-time and is up to 24-hours old. This call is used if access to the real-time systems is not available.

MerPricing 

GetCstmPrice—Passed in a part number, a customer number, and a quantity, calls stored procedures to run through an elaborate pricing algorithm and returns both the list price and the custom price.

GetCstmPrice_Rebate—The same as above, but also returns any rebate amount the customer may receive for the product.

OrderHelper 

CheckShipDrop—Passed in a an account identification, verifies that the customer has drop-ship capability.

CheckShipDropCd—Passed in an account identification, and a drop-ship password, authenticates the correctness of the password.

GetBill_To—Passed in an account identification, returns a disconnected recordset of registered bill-to addresses.

GetContact_info—Passed in an account identification, returns a disconnected recordset of registered contacts.

GetCustDiv—Passed in an account identification, returns the division of the customer (Canada only).

GetCustOrdr—Passed in an account identification and a shopper identification, checks the current basket table and returns the unique order identification for that account identification/shopper identification.

GetCustOrdrTrms—Passed in an account identification, returns a disconnected recordset of the registered and approved ordering terms for that customer (e.g. net 15, net 30, credit card, and so on).

GetCustViaShip—Passed in an account identification, returns the preferred shipping vendors used by that customer.

GetCustWrhs—Passed in an account identification, returns the preferred Merisel warehouses which the customer prefers to ship from.

GetBill_To—Passed in an account identification, returns the set of registered bill-to addresses for the customer.

ProductInfo 

GetProdDesc—Passed in a Merisel Part Number, returns a detailed product description in a string suitable for display in html.

GetProdPromoDesc—Passed in a Merisel Part Number, returns a detailed promotion description running for that product in a string suitable for display in html.

QuickSearch 

SrchMFCExt—Passed in an account identification, manufacturer criteria, along with the beginning part number and the maximum matches to return, returns a disconnected recordset of matches. When called with a beginning part number of 0, also returns the maximum number of matches. This function can be called iteratively, bumping up the beginning part number each time to return large sets, one piece at a time.

SrchMFCPartExt—Same as above, but uses a Manufacturers part number as the search criteria.

SrchPromotionExt—Same as above, but uses promotion criteria as the search criteria.

SrchTitlExt—Same as above, but uses a partial title as the search criteria.

SrchUPCCode—Passed in an account identification, and a UPC code, returns a single row disconnected recordset matching the request.

SrchMerPartNo—Passed in an account identification, and a Merisel part number, returns a single row disconnected recordset matching the request.

ReceiptSearch 

GetReceipts_Date—Passed in an account identification, a beginning date, and an end date, returns a set of matching OrderIDs, amount totals, and submitted dates, which were submitted by that customer within the specified period.

GetReceipt_PONum—Passed in an account identification and a customer PO Number, returns a matching OrderID, amount total, and submitted date.

GetReceipts_Amt—Passed in an account identification, a beginning amount, and an end amount, returns a set of OrderIDs, amount totals, and submitted dates which were submitted by that customer within the specified range.

GetReceipt_OID—Passed in an account identification and an order identification, returns a matching OrderID,amount total, and submitted date.

Scalars 

GetReceiptNum—Returns a unique ID which can be used for an order identification. Uses transactions to handle concurrency.

Pipeline Objects

Each of the Mer-EC-Soln pipeline objects are implemented as COM objects written as a Visual Basic ActiveX DLL. They implement the interface IpipelineComponent. Within the IpipelineComponent_Execute() method, the objects retrieve data from the passed in OrderForm and PipeContext and call the Mer-EC-Soln shop objects to perform the actual work. The objects are all contained physically in the same DLL as the shop objects (Mer-EC-Soln.dll).

PipelinePricing 

IpipelineComponentExecute—Retrieves the customer number from the order form header and then iterates through the item list calling the MerPricing object with each of the items (quantity and part number). Currently this component is not called in any Merisel pipelines.

PipelinePriceAndRebate 

IpipelineComponentExecute—Retrieves the customer number from the order form header and then iterates through the item list calling the MerPricing object with each of the items (quantity and part number) to get the custom price and rebate for each item. Called in the plan pipeline.

PipelineCommission 

IpipelineComponentExecute—Retrieves the customer number from the order form header and then calls the Customer object to retrieve the customer's account representative in order to give that representative credit for the order. Places the account representative identification in the order form header.

Formatting Object

Currently there is only one formatting object: SelPerf.Format. This object has a single method, VBFormat(). which takes an input string and a format string and returns an output string. It is simply used as a wrapper to the Format function of Visual Basic. This is done to work-around the painful limitation of formatting functions available to ASP from VBScript.

This object is placed in its own DLL (selperf.dll) and is run within MTS as its own library package. This allows for faster (in-process) calls to the object for formatting.

Configuration

The Mer-EC-Soln application consists of six servers: two Web servers, two SQL Server-based servers, one membership server, and one host integration server. The hardware requirements for these servers are discussed in detail in the section on "Physical Architecture." The configuration requirements for these servers are listed below.

Web Servers

The two Web servers in the Mer-EC-Soln environment have been configured identically. The primary function of the Web servers is to process ASP pages for the user interface. The developers made every effort to move functionality, aside from IIS, to the other servers.

Table 11 Platform 

Component	Version	Explanation
Windows NT Server	4.0 SP3
Internet Information Server (Option Pack)	4.0
Transaction Server (Option Pack)	2.0
Site Server Membership	3.0	Required for mapping of IIS to Membership. All Membership components and services are on the Membership server.
Commerce Server	3.0	Commerce provides the necessary components, as well as extensions to the Site Server administration MMC, specific to Commerce Server.
Mer-EC-Soln ASP pages

Table 12 Internet Information Server Configuration 

Location	Setting	Value
Server Properties	Max ASP Files Cached	1,200
Site Properties Web Site Tab	IP Address	Specific IP address for PROD01, PROD02
	Connections	Unlimited
	Enable Logging	Yes
	Active log format	W3C Extended Log File Format
Site Properties ISAPI Filters Tab	Name: Auth Filter Executable: Authfltr.dll Priority: Low
Site Properties Home Directory Tab	Local Path
	Access Permissions	Read
	Content Control	Log Access
Site Properties Home Directory Tab Configuration Application Options Tab	Enable session state	No
	Enable buffering	Yes
	ASP Script timeout	180 seconds
Virtual Directory Properties Virtual Directory Tab	Access Permissions	Read
	Content Control	Log Access
	Name	<blank>

SQL Servers

The servers running SQL Server contain the databases for interaction with Mer-EC-Soln. There are two servers, one each for the U.S. and Canada. Each database is significantly different and is designed to match, as closely as possible, the structure of the corresponding host data.

Table 13 Platform 

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

Table 14 SQL Server Configuration 

Location	Setting	Value
SQL Server Setup Network Support	Network Protocols	Named Pipes TCP/IP Sockets
SQL Enterprise Manager Configuration	Locks	15000
	Memory
	User Connections	10

Membership Server

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

Table 15 Platform 

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

Note The SQL Server configuration is the similar to above.

Host Integration Server

The Host Integration Server provides several functions.

• Executes COM objects for host connections. This includes SNA Server and COMTI as well as the SAP DCOM Connector to objects for communication with the respective systems. This server is the only server in Mer-EC-Soln allowed to communicate from the DMZ to the corporate network to access either host. The Web servers call MTS objects on the Host Integration Server in order to access host information. 

• Drives the batch process for data transfer with the hosts. SQL Server is installed locally in order to perform any required processing before being placed into production. A Visual Basic application that drives the batch processes is called from SQL Executive on this server. 

• Contains the usage database and runs scheduled imports and reports. 

Table 16 Platform 

Component	Version	Explanation
Windows NT Server	4.0 SP4
SQL Server	6.5 SP4 HF297	SP4 and hotfix 297 are required for and included with Site Server.
Internet Information Server (Option Pack)	4.0
Transaction Server (Option Pack)	2.0
SQL Server	6.5 SP4 HF297
Site Server, Usage Analysis	3.0
SNA Server / COMTI	4.0	Required for communication with mainframe.
SAP DCOM Connector	1.0	Required for communication with SAP

Configuration 

SNA/COMTI. An SDLC card is resident in the Host Integration Server for communication with the mainframe in Boulder.

Supplemental Information

Do's

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

• Take advantage of connection pooling provided by using MTS and ODBC 3.0 or higher. This yields performance gains in three tier architectures and dramatically simplifies database access programming by eliminating the need to manage connections. 

• Return disconnected recordsets from MTS components back to ASP. When returning disconnected recordsets from a function, either as the return value, or as an out parameter, the recordset copies its data to the caller. If the caller is a client in a separate process or another computer, the recordset marshals its data to the client's process. When going across a network, it compresses the data to use less bandwidth. 

• Mark the ADO component as free threaded when accessing data from SQL Server. Apartment threaded is the default model used. This can be accomplished via a registry setting or the utility makfre15.bat. 

• If developing with Visual Basic 5.0, be sure to apply SP 2 or higher.

• Get the IIS 4.0 resource kit. This is invaluable. 

• Review Nancy Clut's white paper on ASP performance on Microsoft.com and "TechNet: 15 ASP Tips to Improve Performance and Style." 

Don'ts

• Performance degraded when using Visual Basic apartment threaded objects with the ASP Application object 

Technologies Avoided

• Client-side Java applets—Largely due to heavy download time, inconsistent performance, and Java virtual machine implementation differences. 

• CGI—Performance inefficiencies. 

• Microsoft FrontPage Server Extensions—Not required. 

Lessons Learned

• Do not re-invent the wheel. 

• Custom code only as required for product tables and links to legacy systems. 

• Leverage ERP systems as much as possible (tax, shipping, credit card authorization). 

• Emphasize importance of code management, of scope management, and of setting expectations for deliverables. 

Be Aware

• Use IP communication protocol to connect from MTS objects to SQL Server when using connection pooling. The default communication protocol for SQL Server is Named Pipes. Experience showed the entire platform became unstable when using anything other than IP. 

• Calling Remote Objects from an ASP file poses security issues that are difficult to predict. The requirements are different based on the authentication method being used in IIS. Review the Microsoft Knowledge Base article 159311 available at https://support.microsoft.com/default.aspx?scid=kb;en-us;159311&sd=tech . 

• If using Visual Basic 5.0, watch out for Error 48 coming from components hosted in MTS components. This is suspected to be due to For Each iterations through Commerce objects, which are unavoidable. This is corrected in Visual Basic 6.0. For Visual Basic 5.0 it requires a coding patch which can be obtained from Premier Support, located at https://msdn.microsoft.com/vbasic/default.asp . 

• Visual Basic error code gets removed from MTS component when returned to the client. This happens because the MTS component is running in a separate process space from the client. 

• A common MTS instantiation problem is due to creating a new package and allowing the package identity to default to 'Interactive User.' This is fine for initial testing and instantiating objects while the machine is logged in, but will error out when attempting to instantiate an object when the machine is logged out. When calling your object from a remote client, the call only works with this setting if the machine is physically logged on. You need to create a user identification that the MTS package can run under and change the package identity setting to this user. 

• Remotely instantiating MTS objects that live on a multi-homed server can result in delays. 

• A special tool (MetaEdit) is needed to completely remove a site from IIS 4.0. Removing the files is not enough; it leaves garbage in the IIS metabase and confuses both Visual InterDev and the developer. MetaEdit information is available from "Using the Metabase Editor (MetaEdit)" in the Site Server 3.0 Commerce Edition Resource Kit..

• Differences in document models between browsers requires client-side testing for each targeted browser. While Internet Explorer and Netscape Navigator share common features, there are substantial amounts of differences in their document object models. 

• Functional authentication has to occur on the server side due to inherent transparency in HTML. While client-side validation can save on processing required by the server, it is also not very secure. HTML forms can be easily saved and modified and then resubmitted to the server by malicious users. This vulnerability requires server validation on any critical or sensitive data. 

• Pipeline logging is a powerful technique for debugging the Commerce Order Processing and Interchange Pipelines. This technique, however, should only be used in unit testing as the log file writes are not thread safe. This generates errors and should never be left on in a multi-user test environment or production environment. 

• Session variables can affect scalability when moving from a single server configuration to a Web farm. Web farms balance the load of user requests among a number of Web servers. In order to use ASP session management, the same Web server must handle all requests from a user for the life of a session. This requires programmatic solutions such as writing your session management or using a third-party solution, such as Cisco's LocalDirector. 

Appendix A: Code Fragments for Generalized Database Access

Option Explicit

Private Const bConnectLocal = False
Private Const MER_COMMAND_TIMEOUT = 150
Private Const MER_CONNECTION_TIMEOUT = 30

Public Function GetADOConnection(vDB As String, bProductDB As Boolean, 
adoConn As ADODB.Connection, vbErr As Variant, vbErrDescription) As Long
Dim lngErrorCode As Long
Dim strConn As String
Dim str As String
Dim bSet48Ret As Boolean
Dim lng48Value As Long

On Error Resume Next
' Open a connection without using a Data Source Name (DSN).

Set adoConn = New ADODB.Connection
If Err Then
lngErrorCode = ERROR_CANT_CREATE_CONNECTION
GoTo GetADOConnectionExit
End If

Call GetADOConnString(vDB, bProductDB, strConn)
adoConn.ConnectionString = strConn

adoConn.CommandTimeout = MER_COMMAND_TIMEOUT
adoConn.ConnectionTimeout = MER_CONNECTION_TIMEOUT

adoConn.Open
If Err Then
lngErrorCode = ERROR_CANT_DBCONNECT
GoTo GetADOConnectionExit
End If

GetADOConnectionExit:
If Err And (lngErrorCode = 0) Then
lngErrorCode = ERROR_UNEXPECTED_GETCONN
End If
If Err Then
vbErr = Err.Number
vbErrDescription = Err.Description & " " & Err.LastDllError
End If
GetADOConnection = lngErrorCode
End Function

Public Function DoSearch(vDB As String, bProductDB As Boolean, strProcCall 
As Variant, vResultSet As Variant, vbErr As Variant, vbErrDescription) As Long
Dim lngErrorCode As Long
Dim rstResultSet As ADODB.Recordset
Dim adoConn As ADODB.Connection

On Error Resume Next

lngErrorCode = GetADOConnection(vDB, bProductDB, adoConn, vbErr, vbErrDescription)
If lngErrorCode Then GoTo DoSearchExit

Set rstResultSet = New ADODB.Recordset
If Err Then
vbErr = Err.Number
vbErrDescription = Err.Description & " " & Err.LastDllError & " " & Err.Source
lngErrorCode = ERROR_CANT_CREATE_RECORDSET
GoTo DoSearchExit
End If

Rem See KB Article 169210
Rem rstResultSet.CursorType = adOpenStatic
Rem rstResultSet.CursorType = adOpenKeyset
' The forward only cursor is most optimal

rstResultSet.CursorType = adOpenForwardOnly

Rem Use ReadOnly Recordsets
rstResultSet.LockType = adLockReadOnly
Rem rstResultSet.LockType = adLockBatchOptimistic
'Allows for a disconnected recordset
rstResultSet.CursorLocation = adUseClientBatch
rstResultSet.Open strProcCall, adoConn, , , adCmdText
If Err Then
vbErr = Err.Number
vbErrDescription = Err.Description & " " & Err.LastDllError
lngErrorCode = ERROR_CANT_EXEC_STOREDPROC
GoTo DoSearchExit
End If

Set rstResultSet.ActiveConnection = Nothing

Set vResultSet = rstResultSet

DoSearchExit:
If Err And (lngErrorCode = 0) Then
vbErr = Err.Number
vbErrDescription = Err.Description & " " & Err.LastDllError
lngErrorCode = ERROR_UNEXPECTED_DOSEARCH
End If
Set rstResultSet = Nothing
Set adoConn = Nothing
DoSearch = lngErrorCode
End Function

Public Function DoAppend(vDB As String, bProductDB As Boolean, strProcCall 
As Variant, vbErr As Variant, vbErrDescription) As Long
Dim lngErrorCode As Long
Dim cmdCommand As ADODB.Command
Dim adoConn As ADODB.Connection

On Error Resume Next

lngErrorCode = GetADOConnection(vDB, bProductDB, adoConn, vbErr, vbErrDescription)
If lngErrorCode Then GoTo DoAppendExit

Set cmdCommand = New ADODB.Command
If Err Then
vbErr = Err.Number
vbErrDescription = Err.Description & " " & Err.LastDllError & " " & Err.Source
lngErrorCode = ERROR_CANT_CREATE_COMMAND
GoTo DoAppendExit
End If

cmdCommand.ActiveConnection = adoConn
cmdCommand.CommandText = strProcCall
cmdCommand.Execute
If Err Then
vbErr = Err.Number
vbErrDescription = Err.Description & " " & Err.LastDllError
lngErrorCode = ERROR_CANT_EXEC_STOREDPROC
GoTo DoAppendExit
End If

Set cmdCommand.ActiveConnection = Nothing

DoAppendExit:
If Err And (lngErrorCode = 0) Then
vbErr = Err.Number
vbErrDescription = Err.Description & " " & Err.LastDllError
lngErrorCode = ERROR_UNEXPECTED_DOAPPEND
End If

Set adoConn = Nothing
Set cmdCommand = Nothing
DoAppend = lngErrorCode
End Function