TechNet Home > Product & Technologies > Servers > Commerce Server > Commerce Server 2002 > Deploy

Importing Non-Commerce Server Data into the Commerce Server 2002 Data Warehouse

	Executive Summary
	Introduction
	Preparing Non-Commerce Data for Import
	Importing Non-Commerce Data into the Data Warehouse
	Analyzing Data from Non-commerce Web Servers
	References
	Appendix A: Specifying a Web Server Log File Mask
	Appendix B: Web Log File Import Options
	Appendix C: Mapping Profile Properties
	Appendix D: Incrementally Importing Custom Data

Executive Summary

Microsoft® Commerce Server 2002 is the Microsoft Windows Server System platform for rapidly building next-generation online businesses. Commerce Server 2002 provides powerful features for user profiling, personalization, catalog management, order processing, globalization, and advanced online business analytics.

This white paper describes how to import Web log file data from a computer that is not running Commerce Server 2002, external catalog data, and external profile data into the Commerce Server Data Warehouse.

Top of page

Introduction

This white paper provides instructions for importing data not created with Commerce Server 2002 Business Desk, or from a Commerce Server 2002 site into the Commerce Server 2002 Data Warehouse. This white paper assumes that you are using Commerce Server 2002 Enterprise Edition and that you have access to Commerce Server 2002 Enterprise Edition Business Desk.

Commerce Server 2002 is designed to work with non-commerce applications and Web servers. These are defined as follows:

•	Non-commerce application. A container of non-commerce Web servers with the common properties for the application. It is analogous to a commerce application and its commerce Web server members.
•	Non-commerce Web server. A Web server on which Commerce Server has not been installed. It produces World Wide Web Consortium (W3C) format log files that can be imported into the Data Warehouse and used for analysis. A non-commerce Web server may or may not be running Microsoft® Internet Information Services (IIS).

This white paper provides instructions for importing the following types of data into the Commerce Server Data Warehouse and analyzing them with the Commerce Server Business Analytics System:

•	Non-commerce Web site log file data. Web site log files generated from sites on Web servers on which Commerce Server has not been installed.
•	External catalog data. Catalog data not generated with Commerce Server Business Desk.
•	External profile data. Profile data not generated with Commerce Server Business Desk or with a Commerce Server site.

Commerce Server Business Analytics System

The Commerce Server 2002 Business Analytics System provides complete decision support through data warehousing, prediction and data mining, and advanced analysis. You can install Microsoft Commerce Server 2002 to use the Business Analytics System as a stand-alone feature.

The Business Analytics System is divided into three main parts:

•

Data Warehouse. Describes a combination of a Microsoft® SQL Server™ database, an online analytical processing (OLAP) database, and a set of processes that a system administrator uses to import a large amount of operational data about site activity. The Commerce Server Data Warehouse stores and manages data in the database for the purpose of business analytics: prediction and data mining as well as analysis reporting.

•

Predictor. Describes a powerful data-mining tool that provides predictive capabilities for your Web site (for example, to display product recommendations). You can also use the Predictor to analyze the characteristics of the users visiting your site and discover relationships among the characteristics. You can then use this information to target content to users who have similar characteristics.

•

Analysis Reporting. Provides dozens of reports, enabling to you analyze product sales, Web usage, Web site diagnostics, and so on. It includes features that make reporting easier and faster, such as:

•	The drillthrough feature, so you can use the Pivot controls to expose underlying data.
•	The remote access feature, so business managers can access the OLAP database through firewalls or proxy servers and run reports.

Integrating External Data with Commerce Server

If you are planning to use external data with the Commerce Server 2002 Enterprise Edition Analytics System, you need to assess your existing systems and data to determine the most effective methods to either integrate the systems with Commerce Server 2002 components or convert the data to formats that can be imported into Commerce Server databases.

For example, do you want to integrate existing databases and database servers that contain such data as catalogs and user profiles or customers? If you maintain separate data sources for your offline and online businesses, you must establish a system for synchronizing the data. Consider integrating Microsoft Host Integration Server 2000 and Microsoft Application Center 2000 into your overall system design.

Additionally, what are your requirements for converting data? You can establish a system for transforming data from its existing format to a format you can use in Commerce Server. For example, you must convert existing catalog data in a tab-delimited format to comma-delimited or Extensible Markup Language (XML) before importing it into the Product Catalog System.

Top of page

Preparing Non-Commerce Data for Import

You must prepare external data before you can import it into the Commerce Server Data Warehouse.

Note: You must have an existing site before you can add non-commerce applications to it. In Commerce Server Manager, a site is a container for Commerce Server applications, as well as global and site-level resources. For example, the Retail site contains two applications: the business-to-consumer Web site and the Commerce Server Business Desk, and both share the same site-level resources. Instructions are provided in the topic "Preparing Non-Commerce Web Log File Data for Import" in this white paper for setting up the Data Warehouse resource, the Blank solution site, and a Business Desk to analyze Web log file data. For information about accessing and installing other Commerce Server sample sites, see the topic "Unpacking a Site" in Commerce Server 2002 Help.

Preparing Non-Commerce Web Log File Data for Import

You use the Data Warehouse Configurator (DWC) to configure your non-commerce applications and Web servers. The following three procedures take you through the configuration process.

To create the site and the Data Warehouse global resource

Click Start, point to Programs, point to Commerce Server, and then click Data Warehouse Configurator.

In the Data Warehouse Configurator dialog box, do the following:

Use this	To do this
Commerce Server site	Type the name of the site you want to create.
Data warehouse	Type the name of the Data Warehouse global resource you want to create.

To add a non-commerce application to a site

In the Data Warehouse Configurator dialog box, in the Non-Commerce applications section, click Configure.

The Non-Commerce applications and Web servers dialog box appears, which enables you to create multiple non-commerce applications and servers within those applications.

In the Non-Commerce applications and Web servers dialog box, in the Non-Commerce applications section, click New.

In the New Non-Commerce Application dialog box, on the General tab, do the following:

Use this	To do this
Web servers in this application are running IIS	Select this check box if the servers in the non-commerce application are running Internet Information Services (IIS) 5.0. Note: You cannot have both IIS and non-IIS servers in a non-commerce application.
Nonsecure host name	Type the Domain Name System (DNS) domain name used by nonsecured links in the application. Caution If you are using a port number other than 80, you must append that number to the end of the server name (for example, www.microsoft.com:81). To view or edit the port number, see the TCP Port box on the Identities tab in the Web Server Properties dialog box.
IIS application path or Application path	Type the path to the application. For example, for the application www.microsoft.com/msn, the path would be /msn. Caution If you change this property, you must change the location of the files on the Web server(s) to correspond to it. You must also configure IIS with the new application.
Enable HTTPS	Select this property if secured links are used in the application.
Secure host name	Type the DNS domain name used by secured links in the application. Caution If you are using a port number other than 443, you must append that number to the end of the server name (for example, www.microsoft.com:441). To view or edit the Secure Sockets Layer (SSL) port number, see the SSL Port box on the Identities tab in the Web Server Properties dialog box.

On the Web Servers tab, do the following:

Use this	To do this
IIS Web site	Type the name of the IIS Web site (for example, Default Web Site).
Local domain	Type the name of the local domain (for example, microsoft.com).

Click OK.

To add a non-commerce Web server to an application

In the Non-Commerce applications and Web servers dialog box, from the Non-Commerce applications list, select the name of the non-commerce application to which you want to add the Web server.

In the Non-Commerce Web servers for selected application section, click New.

Note: If a non-commerce application has not been created, the New button is disabled.

In the New Non-Commerce Web server dialog box, on the Identities tab, in the Server name box, type the name of the computer that hosts the non-commerce Web server.

In the Identities section, click Add.

In the Identity dialog box, do the following:

Use this	To do this
IP address	Type the IP address for the non-commerce Web server.
TCP port	Type the TCP port for the non-commerce Web server.
Host header name	Type the host header name for the non-commerce Web server.

Click OK.

If you are using a secure HTTP host (SSL) in your applications, in the SSL Identities box, click Add.

In the Identity dialog box, do the following:

Use this	To do this
IP address	Type the IP address for the non-commerce Web server.
SSL port	Type the SSL port for the non-commerce Web server.

Click OK.

10.

In the New Non-Commerce Web Server dialog box, on the Log Files tab, do the following:

Use this

To do this

New Log Time Period

Select the frequency that the Web site writes to a log file before the Web server creates a new file.

Log file mask

Type the log file rotation number, indicating how long the Web site writes to a log file before the Web server creates a new file. Use the following numbers to specify the log file rotation:

•	1=IIS daily rotation
•	2=IIS weekly rotation
•	3=IIS monthly rotation
•	4=IIS hourly rotation

For more information about specifying log file masks, see the topic "Specifying a Web Server Log File Mask" in Commerce Server 2002 Help.

Log file directory

Type the log file path to which the Web site writes events, for example, C:\Winnt\System32\LogFiles\Log1.

You may want to specify a different directory if you copy the Web log files from the Web server to a central location before you run the import Data Transformation Services (DTS) tasks.

Log file code page

Select from the drop-down list the code page used by the entire file.

URL encoding code page

Select from the drop-down list the code page used for all URLs.

11.

Click OK.

12.

After you create the non-commerce applications and Web servers, click Close.

In the Import Server section, the local computer on which Setup is running is displayed, which is a non-editable box.

13.

In the Commerce Server Data Warehouse Configurator wizard, on the Data Warehouse Configurator page, in the Analysis/Reporting Server section, click Configure.

14.

In the Data Link Properties dialog box, on the Connection tab, in the Data Source box, type the name of the computer that will host the Analysis and Reporting management modules.

If SQL Server was installed locally, the local computer name is displayed in the Data Source box.

15.

Click OK.

Note: You cannot change the name or location of the site, and Commerce Server and Commerce Server Business Desk must be installed on this computer.

16.

In the Database Server section, to configure the computer that will host the Data Warehouse relational database, click Configure.

17.

In the Data Link Properties dialog box, on the Connection tab, make the necessary changes, and then click OK.

Note: For information about how to complete the Data Link Properties dialog box, click Help in the Data Link Properties dialog box to open Microsoft Data Link Help.

18.

In the Analysis SQL Server section, to configure the computer that will host the Data Warehouse OLAP database, click Configure.

19.

In the Data Link Properties dialog box, on the Connection tab, make the necessary changes, and then click OK.

20.

In the Commerce Server Data Warehouse Configurator wizard, on the Data Warehouse Configurator page, click Next, and then click Finish to complete the wizard.

The Blank Solution Site package is unpacked, which contains the Data Warehouse, Predictor resource, and Business Desk Analysis modules.

Preparing External Catalog Data for Import

You can use Commerce Server Business Desk to import XML and comma-separated values (CSV) catalog data files. You will need to install a standard Business Desk that includes the catalog modules. For example, you can:

•	Export catalog data stored in an Oracle database to an XML or CSV file, and then import it into Commerce Server.
•	Import a catalog that is stored in a Microsoft Excel spreadsheet.

To import a catalog on a regularly scheduled basis, such as importing catalogs from trading partners, ask your developer to write the appropriate code. For information about using the CatalogManager application programming interface (API) to write the code for importing a catalog, see the topic "CatalogManager" in Commerce Server 2002 Help.

If Microsoft® BizTalk® Server is installed on the same computer as Commerce Server, you can use an Application Integration Component (AIC), as defined by BizTalk Server, to automate the process of importing XML catalogs into Commerce Server as they arrive from vendors through BizTalk Server. For more information about creating an AIC, see the BizTalk Server documentation.

Importing an XML Catalog File

A Commerce Server catalog in XML file format contains both catalog definitions and product data. You can import a Commerce Server catalog XML file, and then use the Catalog Editor module in Commerce Server Business Desk to make changes to the product data.

Note: Observe the following when importing catalogs into Business Desk:

•	Your system administrator must start the full-text indexing service (Microsoft Search service) before you can import a catalog into the catalog database.
•	Product definitions contain the properties that are available in a particular product type. Product definitions can be associated with multiple catalogs and are independent of the catalogs with which you associate them. If you attempt to import a catalog that contains a product definition that has the same name as an existing product definition, but different properties, the import fails.
•	Your catalog can include custom catalog attributes and property attributes.
•	If your catalog contains product variants, you must use XML to preserve your variants. CSV files do not support product variants.
•	After importing a catalog, you or your system administrator should check the Application Event Log file on the server for any errors.
•	To import catalog XML files that are larger than 1 GB, the server must have Microsoft® XML Core Services (MSXML) 4.0 and all current service packs installed.

To import an XML catalog file into Business Desk

In Commerce Server Business Desk, expand Catalogs, and then click Catalog Editor.

In the Catalogs screen, click on the toolbar, and then select Import XML from the drop-down list.

In the Import XML Catalog dialog box, do the following:

Use this	To do this
Specify an XML file on the server	Type the name of the XML catalog file that you want to import. This XML catalog file must be located on the same computer as Commerce Server.
Specify a locally accessible XML file	Click Browse to navigate to an XML catalog file located on your Business Desk client computer. If the catalog is large, the import process takes several minutes to complete. A catalog that is located on the server imports much more quickly.
Delete existing data in this catalog	If you are importing a catalog that already exists in your Commerce Server catalog database, select this check box to delete the catalog data from the existing catalog before importing the new version.
Validate XML file (do not import)	Select this check box to validate the data in the XML file before you import it. If the data is not valid, an error message appears and the catalog is not imported.

Click OK.

A message appears stating that the catalog import process has started. The catalog import process may take several minutes, depending on the size of the catalog. During this time, you can use Business Desk to perform other tasks or exit Business Desk.

If you receive a message that the XML file is not valid, contact your system administrator to determine the problem.

To see the catalog after you import it, click Catalog Editor. This step refreshes the Catalog Editor screen.

To publish the catalog to your Web site, click Catalog Editor, and then click on the toolbar.

For more information about publishing a catalog, see the topic "Refreshing and Publishing a Catalog" in Commerce Server 2002 Help.

Importing a CSV Catalog File

You may want to use CSV catalog files, for example, to import data from a Microsoft Excel spreadsheet. In Microsoft Excel, you can save a spreadsheet as a CSV file.

Note the following characteristics of a CSV file:

•	A CSV catalog file contains product data, but does not contain catalog definitions.
•	The products in the CSV file are not organized into categories.
•	The CSV catalog file must have a product identifier and a price column.
•	CSV catalog files contain only stand-alone products and do not support product variants.
•	The data type string is the only data type a CSV catalog file supports.

When imported, the products in a CSV catalog file are mapped to a single product definition.

When a CSV catalog file is imported, a product definition is automatically created, based on the columns listed in the file. Each product is imported as an instance of the product definition that was automatically created. The product definition is given the same name as the catalog being imported.

After importing a CSV catalog file, you can use the Catalog Editor module in Business Desk to create categories and add products to the categories.

Note: Observe the following when importing a CSV catalog into Business Desk:

•	Your system administrator must start the full-text indexing service (Microsoft Search service) before you can import a catalog into the catalog database.
•	To import a catalog with product variants, you must use XML to preserve your variants. CSV files do not support product variants.
•	Importing a catalog with the same name as a product definition overwrites the original product definition.
•	You cannot import a catalog that contains a product definition with different properties than an existing product definition of the same name.
•	After importing a catalog, you or your system administrator should check the Application Event Log file on the server for any errors.

To import a CSV catalog file into Business Desk

In Commerce Server Business Desk, expand Catalogs, and then click Catalog Editor.

In the Catalogs screen, click on the toolbar, and then select Import CSV from the drop-down list.

In the Import CSV Catalog dialog box, do the following:

Use this	To do this
Specify a CSV file on the server	Type the name of the CSV catalog file that you want to import. This file must be located on the same computer as Commerce Server.
Specify a locally accessible CSV file	Click Browse to navigate to a CSV catalog file located on your Business Desk client computer. If the catalog is large, the import process takes several minutes to complete. It is much faster to import a catalog that is located on the server.
Language	Select the language to be associated with the imported catalog.
Catalog name	Type a unique name for the imported catalog. This name will also be used for the product definition created during the import process.
Product identifier	Type the name of the identifier column in the CSV file.
Price column	Type the name of the price column in the CSV file.
Delete any existing data in this catalog	If you are importing a catalog that already exists in your Commerce Server catalog database, select this check box to delete the catalog data from the existing catalog before importing the new version.

Click OK.

If you receive a message that the CSV file is not valid, contact your system administrator to determine the problem.

To see the catalog after you import it, click Catalog Editor. This step refreshes the Catalog Editor screen.

To publish the catalog to your Web site, click Catalog Editor, and then click on the toolbar.

You can now import the catalog data into the Data Warehouse

Preparing External Profile Data for Import

You can import user profile data from external systems using the following methods:

•	Attach user profile information to existing data. This can be done using the aggregation feature of the Commerce Server Profiling System. Using this option, you would treat the existing data as any other data source.
•	Import the data into the Commerce Server Profiling System. The Profiling System provides an OLE DB interface that you can access using Microsoft ActiveX Data objects (ADO) and perform simple SQL queries using statements such as INSERT, UPDATE, DELETE, and SELECT.

For more information about preparing profile data from external systems for import into the Data Warehouse, see the topic "Profiling System and OLE DB Provider" in Commerce Server 2002 Help.

Top of page

Importing Non-Commerce Data into the Data Warehouse

You can import data into the Commerce Server Data Warehouse that was not originally created in Commerce Server Business Desk or with a Commerce Server site.

Importing Log Files from Non-Commerce Web Servers

To import log files from non-commerce Web servers into the Commerce Server Data Warehouse and make the data available to Commerce Server Analysis services, you must perform the following three steps:

•	Step 1: Reading the "Best Practices for Running the Web Server Log Import Data Transformation Services (DTS) Task" topic
•	Step 2: Configuring the Web Server Log Import DTS task
•	Step 3: Running the Web Server Log Import DTS Task

Step 1: Best Practices for Running the Web Server Log Import DTS Task

This topic lists best practices and important notes for running the Web server log import task:

•

If you have multiple applications and log files, use a script to configure the Web server log import DTS task instead of using the user interface. For instructions, see "Scripting the Web Server Log Import DTS Task" in Commerce Server 2002 Help.

•

If you are running the Web server log import DTS task by using a script, you must specify a 3-gigabyte (GB) switch in the Windows 2000 Boot.ini file on the computer hosting the Data Warehouse (SQL Server) and on the computer hosting the Analysis server. For instructions, see "Running the Web Server Log Import DTS Task for a Large Site" in Commerce Server 2002 Help.

•

The Web log file import process will not import log files while IIS is actively writing to them. Do not import your log file directly from your Web site. Instead, verify that the server is no longer accessing the file, and then copy the file to a different directory. You can then import it from the directory you selected.

•

If you are importing Web log file data for multiple sites, and you perform a Data Warehouse level operation to import data for all sites associated with the Data Warehouse, the Web server log import DTS task imports all log files in the log file path depending on the criteria set for All logs since last import, or Logs by date/time range. The Web log import task does not recognize that you might have cleared the selection for some log files at the site level that you do not want to import.

•

If you are using the Campaign features of the Targeting System, the Web server log import task imports the URIs for campaign events, even if you do not run the Campaign data import task. The Web log files are the source of this data.

•

Commerce Server only supports files in W3C extended log file format. If you are importing Apache or Lotus log files, you must convert them to that format before you can import them.

•

Use the following performance counters when running the Web server log import DTS task:

•	UImport: Free hit buffers. The number of currently available empty hit buffers.
•	UImport: Used hit buffers. The number of currently filled hit buffers.
•	UImport: Hits per second. The rate per second at which the import task is processing hits.
•	UImport: Current visits. The total number of active visits recorded while the log files are processed.
•	UImport: Commerce events. The total number of Commerce Server events that occurred for the import task.
•	UImport: Decoded cookies. The total number of Commerce Server-decoded cookies encountered while the import task was running.
•	UImport: Query strings. The total number of query strings processed while the import task was running.

Step 2: Configuring the Web Server Log Import DTS Task

You can configure seven sets of properties to control and customize the data you import into the Data Warehouse from your Web log files. The settings that you select for these properties can improve your analysis of the data, as well as make your import processes faster and your Data Warehouse more manageable.

Note: Unless specified otherwise, these settings apply to all of the applications in the site and all sites associated with the Data Warehouse.

The following property sets control what data is imported from your Web log files into the Data Warehouse:

•	Setting Web Log Default Files. Ensure that the hit counts for unique visitors to your site default page are accurate, allowing for redirects and multiple versions of your URL.
•	Setting Web Log File Exclusions. Prevent the following data from being imported into the Data Warehouse: hits from certain hosts, requests for specific file types or expressions, and hits by crawlers.
•	Setting User Inferences. Customize the assumptions made during import about users and visits in order to meet the analysis needs of your organization.
•	Setting Visit Inferences. Customize the assumptions made during import about users and visits in order to meet the analysis needs of your organization.
•	Setting Time Overlap and Log File Rotation Properties. Customize the response to time overlaps in log files.
•	Setting Query String Import Properties for Web Log Files. Import Web site query strings so that the data associated with them can be analyzed.
•	Setting Web Log Import Restart Properties. Automatically restart log file imports after encountering an erroneous log file.

For more information about the configuration options for the Web server log import DTS task, see Appendix B, "Web Log File Import Options" in this white paper.

To set a directory index file

Click Start, point to Programs, point to Commerce Server, and then click Commerce Server Manager.

In the Commerce Server Manager window, expand Microsoft SQL Server, expand SQL Server Group, and then expand the server on which your Data Warehouse is installed.

Right-click Data Transformation Services, and then click New Package.

Alternatively, if you are changing an existing package, right-click Data Transformation Services, click All Tasks, and then select Open Package. In the Select File dialog box, click the package you want to change, and then click Open.

On the Task menu, click Web server log import (Commerce Server).

In the Import Web Server Logs dialog box, click Advanced.

In the Advanced Web Log Import Properties dialog box, on the Default Files tab, do the following:

Use this	To do this
Truncate top level directory from file system paths	Select this check box to display file name strings without their top-level directories.
Site uses .NET cookieless sessions	Select this check box if your site uses .NET-based cookieless sessions.
Directory index file	Type the name of the file you want to designate as a directory index file. Note: For non-IIS servers, the default settings are Index.html, Index.htm, and Default.htm. If these settings are not correct for your site, change them to ensure that your request count is accurate.
Add	Click to add the new directory index file to the default file list.

Click Apply, and then click OK.

The directory index files have been set for the Web log file import process.

After you perform this procedure, you must run the Configuration synchronization DTS task to synchronize your site configuration with the Data Warehouse before importing data into the Data Warehouse.

The default files are configured for the Web log file import process.

To add an import exclusion

Click Start, point to Programs, point to Commerce Server, and then click Commerce Server Manager.

In the Commerce Server Manager window, expand Microsoft SQL Server, expand SQL Server Group, and then expand the server on which your Data Warehouse is installed.

Right-click Data Transformation Services, and then click New Package.

On the Task menu, click Web server log import (Commerce Server).

In the Import Web Server Logs dialog box, click Advanced.

In the Advanced Web Log Import Properties dialog box, on the Excludes tab, do the following:

Use this	To do this
Include default crawler list for the exclude criteria	Select this check box to prevent crawlers on the default crawler list from being imported.
Add	Click to open the Excludes dialog box to create a new exclude item.

In the Excludes dialog box, do the following:

Use this	To do this
Select exclude category	Select from the drop-down list the type of exclusion you want to add.
Name	Type the string Commerce Server will search for in the log files. For example, type *.gif to exclude graphic files. Commerce Server will not import items that match the string you type. The string cannot contain spaces.
All applications within a site	Select this option if you want to exclude the item from all applications within a site.
Selected applications within a site	Select this option if you want to specify an application from which to exclude the item.
Select all	Select this option to exclude the data from all of the applications listed above the button.
Deselect all	Select this option to cancel the selection of all of the applications listed above the button.

Click OK to close the Excludes dialog box and return to the Excludes tab in the Advanced Web Log Import Properties dialog box.

Continue to add exclusions. When you are finished, click Apply, and then click OK to save your additions and close the dialog box.

The import exclusions are configured for the Web log file import process.

10.

After you perform this procedure, you must synchronize your site configuration with the Data Warehouse before importing data into the Data Warehouse.

The import exclusions are configured for the Web log file import process.

To delete an import exclusion

Click Start, point to Programs, point to Commerce Server, and then click Commerce Server Manager.

In the Commerce Server Manager window, expand Microsoft SQL Server, expand SQL Server Group, and then expand the server on which your Data Warehouse is installed.

Right-click Data Transformation Services, and then click New Package.

On the Task menu, click Web server log import (Commerce Server).

In the Import Web Server Logs dialog box, click Advanced.

In the Advanced Web Log Import Properties dialog box, on the Excludes tab, do the following:

Use this	To do this
Exclude Type	Select the exclude item you want to delete.
Remove	Remove the exclude item.

Click Apply, and then click OK to close the dialog box.

After you perform this procedure, you must synchronize your site configuration with the Data Warehouse before importing data into the Data Warehouse.

The import exclusions are configured for the Web log file import process.

To set an inference for users

Click Start, point to Programs, point to Commerce Server, and then click Commerce Server Manager.

In the Commerce Server Manager window, expand Microsoft SQL Server, expand SQL Server Group, and then expand the server on which your Data Warehouse is installed.

Right-click Data Transformation Services, and then click New Package.

On the Task menu, click Web server log import (Commerce Server).

In the Import Web Server Logs dialog box, click Advanced.

In the Advanced Web Log Import Properties dialog box, on the Inferences tab, do the following:

Use this	To do this
Use Commerce Server identification cookies for inferences	Select this check box if you want to use Commerce Server cookies for user inferences.
Use user names for inferences	Select this check box if you want to use unique user names for inferences.
Use custom cookie identifiers	Select this check box if you want to use custom cookies for inferences.
Enter new custom cookie identifier	Type the identifier for your custom cookie.
Add	Click to add the new custom cookie identifier.
Remove	Click to remove the selected custom cookie identifiers.
	Click to move the selected custom cookie identifier up in the list. Cookies are run in the order that they appear in this list.
	Click to move the selected custom cookie identifier down in the list. Cookies are run in the order that they appear in this list.

Click Apply, and then click OK.

After you perform this procedure, you must synchronize your site configuration with the Data Warehouse before importing data into the Data Warehouse.

The user inference is configured for the Web log file import process.

To set an inference for visits

1.	In the Commerce Server Manager window, expand Microsoft SQL Server, expand SQL Server Group, and then expand the server on which your Data Warehouse is installed.
2.	Right-click Data Transformation Services, and then click New Package. Alternatively, if you are changing an existing package, right-click Data Transformation Services, click All Tasks, and then select Open Package. In the Select File dialog box, click the package you want to change, and then click Open.
3.	On the Task menu, click Web server log import (Commerce Server).
4.	In the Import Web Server Logs dialog box, click Advanced.
5.	In the Advanced Web Log Import Properties dialog box, on the Inferences tab, in the Visit Inferences box, type a number to designate the minutes of user inactivity to allow before a visit ends.
6.	Click Apply, and then click OK. The visit inference is configured for the Web log file import process.
7.	After you perform this procedure, you must synchronize your site configuration with the Data Warehouse before importing data into the Data Warehouse.

The user inference is configured for the Web log file import process.

To configure Web log file time overlaps

In the Commerce Server Manager window, expand Microsoft SQL Server, expand SQL Server Group, and then expand the server on which your Data Warehouse is installed.

Right-click Data Transformation Services, and then click New Package.

On the Task menu, click Web server log import (Commerce Server).

In the Import Web Server Logs dialog box, click Advanced.

In the Advanced Web Log Import Properties dialog box, on the Log Files tab, do the following:

Use this

To do this

Number of minutes that records must overlap

Type the number of minutes that records must overlap.

If overlap is detected

Select an action to take if an overlap is detected:

•	Discard records and proceed. (Default setting) Discards all overlap requests and imports the remaining requests.
•	Import all records. Imports all requests, including overlap requests.
•	Stop the import. Does not import the log file containing overlap requests. All other log files are imported.
•	Stop all imports. Stops all log file imports. You must delete the imported log file if you do not want to keep the data imported before the overlap request was detected.

Note: Select Stop the import or Stop all imports to troubleshoot problems you might experience with importing.

Click Apply, and then click OK.

The time overlap property is now configured for the Web log file import process.

After you perform this procedure, you must synchronize your site configuration with the Data Warehouse before importing data into the Data Warehouse.

The time overlap property is configured for the Web log file import process.

To set log file rotation

In the Commerce Server Manager window, expand Microsoft SQL Server, expand SQL Server Group, and then expand the server on which your Data Warehouse is installed.

Right-click Data Transformation Services, and then click New Package.

On the Task menu, click Web server log import (Commerce Server).

In the Import Web Server Logs dialog box, click Advanced.

In the Advanced Web Log Import Properties dialog box, on the Log Files tab, do the following:

Use this

To do this

When import is completed

Select an action to take with the open visits at the end of your import process:

•	Store open visits for the next import. (Default setting) Reconstructs the actual visit as if there were one seamless log file. This option takes slightly more time because the open visits must be called up from the cache at each new import. You must import log files in chronological order for this option to work correctly.
•	Commit all open visits to database. The visit count is higher at the opening and closing of the log file period because visits that span those two periods are counted twice.
•	Discard open visits. The report count is lower at the closing of the log file period, because visits that have not been closed are dropped.

Click Apply, and then click OK.

After you perform this procedure, you must synchronize your site configuration with the Data Warehouse before importing data into the Data Warehouse.

The log file rotation property is now configured for the Web log file import process.

To configure query strings for import

In the Commerce Server Manager window, expand Microsoft SQL Server, expand SQL Server Group, and then expand the server on which your Data Warehouse is installed.

Right-click Data Transformation Services, and then click New Package.

On the Task menu, click Web server log import (Commerce Server).

In the Import Web Server Logs dialog box, click Advanced.

In the Advanced Web Log Import Properties dialog box, on the Query Strings tab, do the following:

Use this	To do this
Save query strings with URI	Select this check box if you want to store your query strings together with their associated URIs in the Data Warehouse.
Add	Click to open the Query Strings dialog box.

In the Query Strings dialog box, do the following:

Use this	To do this
Name	Type the name of the query string exactly as it appears in your log files. The name cannot match any of the default Data Warehouse columns; it also cannot contain "CEVT" (this grouping of letters is reserved for Commerce Server query strings). In addition, the name cannot contain spaces or include the characters: ''', '"', '+', '&', '^', ''', '"', '{','}', '<', '>', or '='.
All applications within a site	Select this option to import query strings from all applications within the site.
Selected applications within a site	Select this option to select the applications for which you want to import query strings.
Select data type	Select from the drop-down list the data type of your query string. Four data types are possible: integer, string, date/time, or real. The maximum number of characters for the data type string is 255.
Multi-value data type	Select this check box if your query string has a multi-value data type.

Click OK to close the Query Strings dialog box.

Click Apply, and then click OK.

After you perform this procedure, you must synchronize your site configuration with the Data Warehouse before importing data into the Data Warehouse.

Query strings are configured for the Web log file import process.

To remove a query string from import

1.	In the Commerce Server Manager window, expand Microsoft SQL Server, expand SQL Server Group, and then expand the server on which your Data Warehouse is installed.
2.	Right-click Data Transformation Services, and then click New Package. Alternatively, if you are changing an existing package, right-click Data Transformation Services, click All Tasks, and then select Open Package. In the Select File dialog box, click the package you want to change, and then click Open.
3.	On the Task menu, click Web server log import (Commerce Server).
4.	In the Import Web Server Logs dialog box, click Advanced.
5.	In the Advanced Web Log Import Properties dialog box, on the Query Strings tab, select the query string you want to remove, and then click Remove.
6.	Click Apply, and then click OK.
7.	After you perform this procedure, you must synchronize your site configuration with the Data Warehouse before importing data into the Data Warehouse.

The query string is removed from the Web log file import process.

To view the properties of a query string configuration

1.	In the Commerce Server Manager window, expand Microsoft SQL Server, expand SQL Server Group, and then expand the server on which your Data Warehouse is installed.
2.	Right-click Data Transformation Services, and then click New Package. Alternatively, if you are changing an existing package, right-click Data Transformation Services, click All Tasks, and then select Open Package. In the Select File dialog box, click the package you want to change, and then click Open.
3.	On the Task menu, click Web server log import (Commerce Server).
4.	In the Import Web Server Logs dialog box, click Advanced.
5.	In the Advanced Web Log Import Properties dialog box, on the Query Strings tab, click Properties to open the Query Strings dialog box. (You cannot change any of the properties shown.) Note: To change properties, you must first remove the query string for import and then add it again with the new properties you want.
6.	Click OK, and then click OK again.

To set Web log file import restart options

Click Start, point to Programs, point to Commerce Server, and then click Commerce Server Manager.

In the Commerce Server Manager window, expand Microsoft SQL Server, expand SQL Server Group, and then expand the server on which your Data Warehouse is installed.

Right-click Data Transformation Services, and then click New Package.

On the Task menu, click Web server log import (Commerce Server).

In the Import Web Server Logs dialog box, click Advanced.

In the Advanced Web Log Import Properties dialog box, on the Restart tab, in the Restart section, do the following:

Use this	To do this
Retry exhaust action	Select either Stop the import at the end of retries or Delete the problem log import and continue from the drop-down list.
Number of restart attempts	Specify the number of times Commerce Server should attempt to restart an import process that has failed.

Click OK, and then click OK again.

The restart options are set for Web log imports.

You can now run the Commerce Server import DTS tasks to import data into the Data Warehouse.

Step 3: Running the Web Server Log Import DTS Task

This topic explains how to run the Web server log import DTS task. Before running this task, it is recommended that you read the topic "Step 1: Best Practices for Running the Web Server Log Import DTS Task."

Web server log files store data that is obtained when users visit your site and click links to site pages. This data includes the length of time a user spends visiting your site, the referring site, ad clicks, ad reach, click frequency, and the path the user takes through the site, including entry and exit pages.

You use the Web server log import DTS task to specify which log files to import into the Data Warehouse and where they are located. For information about configuring this DTS task, see Appendix B, "Web Log File Import Options."

The Web server log file import task looks for the log files in the specified location, parses them, and applies the properties that you configured.

You can import log files from Web sites that are not running IIS 5.0 (and do not include Commerce Server) as long as they are in the W3C extended log file format. For more information about adding applications and Web servers that do not use Commerce Server components, see "Step 1: Add a Non-Commerce Application to a Site" in this white paper.

If your site has more than one Web server, the Web server log file import process combines the files from the different Web servers in memory and organizes the log file data chronologically. The Web server log file import process filters the Web log file data by using the import properties you set. The process applies a user visit inferential algorithm to transform click data into distinct user and visit counts.

Additionally, if you select to import query strings associated with the Web site, the Web log file import process imports them.

For information about setting up import properties for Web log file data, see the topic "Step 2: Configuring the Web Server Log Import DTS Task" in this white paper.

To import Web log files from a non-commerce Web site into your Data Warehouse

Click Start, point to Programs, point to Commerce Server, and then click Commerce Server Manager.

In the Commerce Server Manager window, expand Microsoft SQL Server, expand SQL Server Group, and then expand the server on which your Data Warehouse is installed.

Right-click Data Transformation Services, and then click New Package.

On the Task menu, click Web server log import (Commerce Server).

In the Import Web Server Logs dialog box, do the following:

Use this	To do this
Description	Type a description for the import task.
Operation level	Select Site level from the drop-down list.
Site	Select from the drop-down list the name of the Web site that contains the Web log file you are importing.
Log file path	Type the path of the Web log file you want to import, or click Log Files to select the Web log files you want to import from the Web Server Log Files list.
All logs since last import	Select this option to specify that all log files created since the last import will be imported.
Logs by data/time range	Select this option to specify that only log files created between the Start date/time and End date/time will be imported.
Start date/time	Type the date and time for the beginning of the range from which log files will be imported. This option is available only if you select the Logs by date/time range option. The value for this option is stored in the Sitesummary table in the Data Warehouse.
End date/time	Type the date and time for the end of the range from which log files are imported. This option is available only if you select the Logs by date/time range option. The value for this option is stored in the Sitesummary table in the Data Warehouse.

In the Retry properties section, do the following:

Use this

To do this

Number of retries

Type the number of times you want the task to retry connection failures to the Data Warehouse. The default number of retry attempts is 10.

Note: To specify the number of times the Web log file import process is to be restarted before proceeding to the next import process, click Advanced, and then click the Restart tab.

Seconds between retries

Type the number of seconds you want the task to wait between retry attempts. The default time interval is 30 seconds.

Click OK.

On the Package menu, click Execute.

The Executing Package dialog box appears, showing the progress of your import. When the import process is complete, the Package Execution Results dialog box informs you that it completed successfully.

Note: If the Web log file import process fails, run the Data deletion DTS task to delete the data associated with that Web log file, and then run the Report preparation DTS task to refresh the OLAP cubes.

Click OK, and then click Done.

The data from the Web log file is imported into the Data Warehouse.

Run the Report preparation DTS task to organize the imported data into the designated OLAP cubes. For instructions about running the Report preparation DTS task, see the topic "Running the Report Preparation DTS Task" in this white paper. After you run the Report preparation DTS task, the data is available for reports.

Importing Catalog Data into the Data Warehouse

You use the Product catalog import DTS task to import data about the products in your Commerce Server catalog database, including dimensional information such as product name, description, color, and size into your Data Warehouse.

For reports such as Product Analysis and Transaction Analysis to display properly, they must have catalog data. If you do not import catalog data into the Data Warehouse, products are identified in reports by their stock-keeping unit (SKU) numbers only.

Note: Observe the following when importing catalog data into the Data Warehouse:

•	You must use the full load option and perform a full import the first time you import catalog data into the Data Warehouse. After you have imported catalog data, use the incremental load option to import only new data that was created since you last imported catalog data.
•	If one Data Warehouse is supporting multiple sites, the names of the catalogs you are importing data for must be unique.
•	If there are two sites that have the same catalog name and the same products, but the catalog properties and product definitions are different, then data of the catalog that is imported first is overwritten when data for the second catalog is imported.
•	Do not create catalog properties that contain special characters. Special characters cause the Product catalog data import task to fail. For example:
•	Catalog properties should not include the following characters: '!', '"', '%', '&', '(', ')', '*', '+', ',', '-', '.', '/', ':', ';', '<', '=', '>', '?', '\\', ']', '^', '`', '{', '\|', '}', '~'.
•	Catalog properties should not contain any control characters, for example, 0x00 /* NULL /, 0x01, 0x02, 0x03, 0x04, 0x05, 0x06, 0x07 / , CONTROL-G <BEL> /, 0x08 / CONTROL-H <BS> /, 0x09 / CONTROL-I <HT> /, 0x0a / CONTROL-J <LF> /, 0x0b / CONTROL-K <VT> /, x0c / CONTROL-J <FF> /, 0x0d / CONTROL-M <PRS_CR> /, 0x0e / , CONTROL-N <SO> /, 0x0f / CONTROL-O <SI> */, 1x10, 0x11, 0x12, and so on.
•	Use the following performance counters for this DTS task: CatImport: Total Catalogs imported CatImport: Total Categories imported CatImport: Total Products imported

To import external catalog data into your Data Warehouse

Click Start, point to Programs, point to Commerce Server, and then click Commerce Server Manager.

In the Commerce Server Manager window, expand Microsoft SQL Server, expand SQL Server Group, and then expand the server on which your Data Warehouse is installed.

Right-click Data Transformation Services, and then click New Package.

On the Task menu, click Product catalog import (Commerce Server).

In the Catalog Import Properties dialog box, do the following:

Use this	To do this
Description	Type a description for this import task.
Operation level	Select Site level from the drop-down list.
Sitename	Select from the drop-down list the site that contains the data you want to import.
Incremental load	Select this option to import only new catalog data based on a previous import.
Full load	Select this option to import all catalog data.

In the Retry Properties section, do the following:

Use this	To do this
Number of retry attempts	Type the number of times you want the task to retry connection failures to the Data Warehouse. The default number of retry attempts is 10.
Time between attempts (seconds)	Type the amount of time you want the task to wait between retry attempts. The default time interval is 30 seconds.

Click OK.

Note: You can add multiple DTS tasks to a package, add sequencing or workflow information, and then run all the tasks in the package.

On the Package menu, click Execute.

Click OK, and then click Done.

Catalog data is imported into the Data Warehouse.

10.

Run the Report preparation DTS task to organize the imported data into the designated OLAP cubes. For instructions about running the Report preparation task, see "Running the Report Preparation DTS Task" in this white paper.

This data is now available for reports.

Importing Profile Data into the Data Warehouse

When you import profile data into the Data Warehouse, the Profile data import DTS task extends the schema of the Data Warehouse to accommodate any new profile definition properties marked as exported. For information about mapping profile properties to columns in the RegisteredUser table, see Appendix C, "Mapping Profile Properties" in this white paper.

To designate which profile properties are imported into the Data Warehouse, you use the Profile Definition Designer module in Commerce Server Business Desk. For information about specifying profile data for import into the Data Warehouse, see Appendix C, "Mapping Profile Properties" in this white paper.

Note: Observe the following when importing profile data into the Data Warehouse:

•	You must use the full load option and perform a full import the first time you import profile data into the Data Warehouse. After you have imported profile data, use the incremental load option to import only new data that was created since you last imported profile data.
•	If the Profiles server is clustered and a failover occurs while the Profile data import DTS task is running, you might experience a data loss. You must rerun the Profile data import DTS task in full load for the last increment by changing the import start date to the increment before the failover occurred. Rerunning the Profile data import DTS task in full load restores any lost data.
•	This DTS task uses the maximum length of the string type when creating tables in the Data Warehouse. Because profiles can have strings that are a maximum of 4000 characters long, the table generated by the DTS task converts each char and varchar into a varchar(4000). It converts char to varchar because SQL Server optimizes on space for the varchar data type.
•	Use the Query Interval property to specify the time intervals in which profile data is imported into the Data Warehouse. This setting prevents the data source's locking for the import. For example, if you plan to import data for 10,000 users, you can specify 15-minute intervals for user data import, so that your server is not overloaded.
•	If you have a large number of user profiles and you are performing a full import, you can set the import start date in the DTS task to import only part of the user profiles. When you import profile data in full load, the start date is 1/1/2000.
•	Oracle uses a different date format than SQL Server. If user profile data is stored in an Oracle database, you must set the following environment variable before running the Profile data import task: Set NLS_DATE_FORMAT = MM/DD/YYYY HH24:MI:SS
•	Use the following performance counters for this DTS task: UPM: DTS Records Exported/sec UPM: DTS Records Exported UPM: Exported Errors

To import external profile data into your Data Warehouse

Click Start, point to Programs, point to Commerce Server, and then click Commerce Server Manager.

In the Commerce Server Manager window, expand Microsoft SQL Server, expand SQL Server Group, and then expand the server on which your Data Warehouse is installed.

Right-click Data Transformation Services, and then click New Package.

On the Task menu, click Profile data import (Commerce Server).

In the Profile Import Properties dialog box, do the following:

Use this	To do this
Description	Type a description for this import task.
Operation level	Select Site level from the drop-down list.
Site name	Select from the drop-down list the site that contains the data you want to import.
Query interval (mins.)	Type the time interval for the import. User data is imported in time intervals to prevent locking the database table. For example, a Web site with 10,000 users would import user data in 15-minute intervals. Setting a smaller value locks the table for a shorter duration, but increases the number of queries against the table and therefore the time the task takes to run. Setting the value to ZERO runs a single query, locking the table for the entire duration of import, but might degrade site performance.
Import start date	Select from the drop-down calendar a date on which to begin your import.
Incremental load	Select this option to import only new users (based on a previous import).
Full load	Select this option to import all user data.

In the Retry Properties section, do the following:

Use this	To do this
Number of retry attempts	Type the number of times you want the task to retry connection failures to the Data Warehouse. The default number of retry attempts is 10.
Time between attempts (seconds)	Type the amount of time you want the task to wait between retry attempts. The default time interval is 30 seconds.

Click OK.

Note: You can add multiple DTS tasks to a package, add sequencing or workflow information, and then run all the tasks in the package.

On the Package menu, click Execute.

Click OK, and then click Done.

Profile data is imported into the Data Warehouse.

Running the Report Preparation DTS Task

You use the Report preparation DTS task to populate the online analytical processing (OLAP) cubes with the data in the Data Warehouse. Commerce Server 2002 uses OLAP cubes to organize the summarized data in the Data Warehouse so that the reports run in the Commerce Server Business Desk Analysis modules complete faster. The Report preparation DTS task must be run every time data is imported into or deleted from the Data Warehouse. For information about OLAP cubes, see SQL Server Books Online.

Note: Observe the following when running the Report preparation DTS task:

•

If multiple sites are sharing a Data Warehouse, run the Report preparation DTS task once to process data for all the sites.

When run, the Report preparation DTS task processes all the sites in a Data Warehouse. Do not run the DTS task separately for each site; it affects performance of the Data Warehouse.

•

The first time you run the Report preparation DTS task to populate the OLAP cubes in the Data Warehouse, you must select full cube processing. After you have run the Report preparation DTS task, you can perform incremental cube processing to load only new data into the OLAP cubes.

•

You must also select full cube processing after you delete all data from the Data Warehouse. For information about deleting data from the Data Warehouse, see "Running the Data Deletion DTS Task" in Commerce Server 2002 Help.

•

Even if you run the Report preparation DTS task in incremental mode, the task might still display an increasing time-to-completion because it must perform full processing on the following cubes:

Cube	Processing mode considerations
Page Usage	Contains the URI dimension, which is an always full process dimension.
Entry Pages	Contains the URI dimension, which is an always full process dimension.
Exit Pages	Contains the URI dimension, which is an always full process dimension.
MscsSales	Contains facts, which can change; therefore, the cube cannot be incrementally processed.
Basket Events	Contains facts, which can change; therefore, the cube cannot be incrementally processed.
Buyer Visits	Contains facts, which can change; therefore, the cube cannot be incrementally processed.

•

The Report preparation DTS task does not work in a clustered OLAP environment, especially if a "retryable" error or a failover occurs. The Report preparation DTS task reattempts processing if it encounters certain error conditions that are identified as "retryable" errors. If it encounters one of these errors, the Report preparation task waits for the configured retry interval and reattempts the processing operation until the configured number of retries is exhausted.

For information about "retryable errors," see the topic "ETL Process for the Report Preparation DTS Task" in Commerce Server 2002 Help.

•

The Report preparation DTS task might report errors when processing OLAP dimensions and cubes if the information necessary to populate those objects has not been imported into the Data Warehouse. The error message is displayed in the following format:

Processing error [Source data contains no rows] <object name>

If you are importing a subset of data into the Data Warehouse (for example, you are not running the Product Catalog data import task), you might want to disable the processing of objects for which there will be no data. This disabling prevents the [Source data contains no rows] error messages.

To disable processing of Analysis Services objects, set the ProcessingEnabled flag to 0 (zero) in the CubeProcInfo and DimProcInfo tables for the cubes and dimensions you do not want to process.

To populate OLAP cubes with data

Expand Microsoft SQL Server, expand SQL Server Group, and then expand the server on which your Data Warehouse is installed.

Right-click Data Transformation Services, and then click New Package.

On the Task menu, click Report preparation (Commerce Server).

In the Prepare Reports Properties dialog box, do the following:

Use this	To do this
Description	Type a description of the Report preparation DTS task. The maximum number of characters for the description is 255.
Site name	Select the site for which you are populating the OLAP cubes.
Incremental	Select to process the OLAP cubes incrementally.
Full	Select to process the OLAP cubes in their entirety.

Click OK.

On the Package menu, click Execute.

Click OK, and then click Done.

The data is loaded into the OLAP cubes. You can now use the Commerce Server Business Analytics System, including Commerce Server reports, to analyze the data in the Data Warehouse. For more information about running reports, see the topic "Business Desk Analysis" in Commerce Server 2002 Help.

Top of page

Analyzing Data from Non-commerce Web Servers

You use the Reports module in Commerce Server Business Desk Analysis to run reports against the data stored in the Commerce Server Data Warehouse.

The following is a list of built-in reports that work when only Web log files are imported:

•	Bandwidth Summary (dynamic)
•	Bandwidth Trends (dynamic)
•	Directories (dynamic)
•	Entry pages (dynamic)
•	Entry path analysis (static)
•	Exit pages (dynamic)
•	General activity statistics (dynamic)
•	Hits by HTTP status (dynamic)
•	Hits by Win32 status (dynamic)
•	Query strings (muti value) (static)
•	Query strings (single value) (static)
•	Top referring domains by request (dynamic)
•	Top requested pages (dynamic)
•	User trends (dynamic) Note: The UserType dimension for the User trends report counts all site users as anonymous unless you also select the UserType dimension from the Profile Data Import Data DTS task field chooser and run the task.
•	Usage summary by day of week (dynamic)
•	Usage summary by hour of day (dynamic)
•	Usage summary by week of year (dynamic)
•	Usage trends (dynamic)
•	User visit trends (dynamic) Note: The UserType dimension for the User visit trends report counts all site users as anonymous unless you also select the UserType dimension from the Profile Data Import Data DTS task field chooser and run the task.
•	Visits by Browser, Version, and OS (Dynamic)

If you import catalog and Web log file data only, you can run the following two reports:

Buyer Browse to Purchase (Dynamic)

•	Order Events (Dynamic)

If you import log file data and profile data only, and map the imported data to the existing columns in the RegisteredUser table, you can run the following reports:

•	Distinct Users by Time (dynamic)
•	New Registered Users (static)
•	Registered User Properties (static)
•	Registered Users by Date Registered (dynamic)
•	User Days to Register (dynamic)
•	User Registration Rate (dynamic)
•	User Trends (dynamic)

For information about mapping data to columns in the RegisteredUser table, see Appendix C, "Mapping Profile Properties" in this white paper.

For information about the dimensions and measures in these reports, see the topic "Commerce Server 2002 Reports" in Commerce Server 2002 Help.

If you want to generate new reports from your non-commerce data, see the "Extending the Commerce Server 2000 Data Warehouse/Analytics" white paper for information about extending the Data Warehouse and Analytics (http://go.microsoft.com/fwlink/?LinkId=11952).

Running Reports

You use the Reports module in Commerce Server 2002 Business Desk to run both dynamic and static reports.

Before you can run reports, you must import data into the Data Warehouse and run the Report preparation DTS task, ensuring that the reports you run contain the most current data. (You must have the appropriate permissions in Commerce Server Business Desk to save and view a report.)

To run a dynamic report

1.	Import data into the Data Warehouse and run the Report preparation DTS task to make the data available for reports.
2.	In Commerce Server 2002 Business Desk Analysis, click Reports. In the list of available reports, select the dynamic report definition you want to run, and then click on the toolbar.

The completed dynamic report appears as a PivotTable® in a new browser window.

Note: Observe the following when running dynamic reports:

•	If your PivotTable is not visible, click on the toolbar.
•	Commerce Server 2002 supports up to 250,000 members per dimension in dynamic reports. If the data set for the report has more than 250,000 members in a dimension, the report appears in the browser window without data.

To run a static report

Import data into the Data Warehouse and run the Report Preparation DTS task to make the data available for reports.

In Commerce Server 2002 Business Desk Analysis, click Reports. In the list of available reports, select the static report you want to run, and then click on the toolbar.

In the Run a report dialog box, in the Site Name section, do the following:

Use this	To do this
Operand	From the drop-down list, select an operand (All, Equals, Not equal to, Like, Not Like) to determine for which site the report will run.
Parameter value	By default, this box contains the current site name. If you want to run the report for a different site, type the name of the site for which you want the report to run. If the name that you type does not match a Commerce Server 2002 site name, the report will not contain data.

In the Run a report dialog box, adjust the parameters to meet your report needs. All report parameters are report-specific.

Note: You can view the report query, parameter tags, and field name tags by clicking Advanced in the Run a report dialog box.

Click OK.

Caution Too many static reports running concurrently can slow processing time or possibly cause Business Desk to not respond.

The static report runs and, when completed, is available for viewing in the Completed Reports module.

Top of page

References

For more information, see the following:

•	"Extending the Commerce Server 2000 Data Warehouse/Analytics" white paper located at http://go.microsoft.com/fwlink/?LinkId=11952

Top of page

Appendix A: Specifying a Web Server Log File Mask

A log file mask is a property that specifies what a Web server log file name will be as it is rotated over time, or based on file size. For example, a daily IIS log file rotation is specified as exyymmdd.log: the log file name has a prefix of "ex," a two-digit year, a two-digit month, and a two-digit day. You set the log file mask when you use Commerce Server Manager to configure a Web server.

You should append an extension to the end of the file mask; in most cases add ".log" at the end of the mask.

The following rules apply to Log file masks:

•

Fixed strings can be appended anywhere in the log file mask.

•

Log file masks support directory-level masking.

•

The following characters cannot be used in the log file mask: : | * ? < > / "

•

Log file rotation can be specified in the mask. The following table lists log file mask values that specify rotation.

Log file mask	Description	Example
0	Extended file names
1	IIS daily rotation	ex$y$m$d.log
2	IIS weekly rotation	ex$y$m$w.log
3	IIS monthly rotation	ex$y$m.log
4	IIS hourly rotation	ex$y$m$d$h.log

The following table lists the characters to use when you specify the log file mask.

Character	Description
$M	Full month name (Only English names are supported.)
$m	Month 01-12
$d	Day of the month 01-31
$h	Hour 00-23
$y	Two-digit year 00-99
$Y	Four-digit year
$w	Week of the month 01-05
$n	For sequential numbering to support IIS extended log file names (only single-digit numbers).
$N	Same as $n but for all numbers with one or more digits. This character should be the last mask before the file extension. Strings not starting with numerals can proceed $N.

Sample Log File Masks

Sample 1

If you rename the IIS W3C extended log files so that the server names are prefixed to the log file name, the log file names may look like the following for daily rotation:

Server1_ex010810.log

Server1_ex010811.log

To retrieve these log files for import, type Server1_ex$y$m$d.log in the Log file mask box.

Sample 2

If you select the Unlimited file size or when file size reaches certain size option in IIS logging properties, then the file names are in the format extend#.log, that is, extend1.log, extend2.log, and so on.

To retrieve these log files for log import, type extend$n.log in the Log file mask box.

Sample 3

The Log file mask box can also include a directory. For example, to pick up log files from the folder e:\logs\server1\extend1.log, you can specify one of the following in the Log file mask box:

•	If the log path is e:\logs\server1\, type extend$n.log in the Log file mask box.
•	If the log path is e:\logs\, type server1\extend$n.log in the Log file mask box.
•	If the log path is e:\logs\, type server$n\extend$n.log in the Log file mask box.

Top of page

Appendix B: Web Log File Import Options

This section contains detailed information about Web log file import options.

Web Log Default Files

A default file, also known as a directory index file or home page, is the Web page that users reach when they type a site URL that does not contain a specific file name in the Address box of their browser. For example, users reach the www.microsoft.com/default.htm page when they type either www.microsoft.com or www.microsoft.com/default.htm in their browser. Web log files, however, count the two URLs as hits to two different pages.

You can set the Default Files option in Commerce Server 2002 to designate multiple URLs to be counted as hits to the same page. You can use this option to create redirects and multiple paths into your site without decreasing the accuracy of your total site user statistics.

In addition, you can display file name strings without their top-level directories before they are imported. This practice makes it easier to work with your files if your site directories are stored in a subdirectory that you do not need to view in your reports.

For IIS 5.0, this setting is synchronized automatically during setup with the base URL directory index files that are listed in IIS.

Web Log File Exclusions

You can exclude certain types of data from your imports. By excluding certain data types, you keep the size of your Data Warehouse manageable, and page requests are more accurately defined in your reports. You can exclude the following types of data:

•

Hosts. You can exclude data from users who originate from certain hosts (for example, employees or software testers). This feature is typically used to exclude internal hosts. However, you can specify any host for exclusion.

The Data Warehouse does not import hits associated with hosts that you exclude. The hits associated with the excluded hosts are, however, included in some aggregation-only reports, such as Summary Hit Counts and Bandwidth Data.

When you enter a host name to exclude, you must type a string that Commerce Server 2002 can use to search the log files. For example, "www.microsoft.com" excludes information from users whose host is www.microsoft.com. Do not include spaces in the name you type.

•

File types. Although one page in your Web site can include several files, you want to ensure that only one page request is counted rather than a hit for each file in a page. For example, if a page includes graphics that are stored in separate files but are logically part of the page, exclude the graphics files from the import for more accurate hit counts. You can set the Excludes property to exclude files by specifying the file name extensions of the types of files you want to exclude.

By default, files with the extensions .gif, .jpg, .jpeg, .css, and .cdf are excluded from Commerce Server imports. You can also remove file extensions from the exclude list. The name you type is the string that Commerce Server searches for in the log files. You must type the extension exactly.

•

File expressions. In addition to excluding a file from being imported by its file extension, you can exclude a file by its file path. You can provide a complete file expression for an individual file, or you can use wildcard characters to exclude a group of files that share similar characters in their expressions.

File expressions are not case sensitive. Do not include an underscore (_) or spaces. You must type the exact string for which you want Commerce Server to search.

The following table lists the wildcard characters you can use in file expressions.

Character	Description	Example
*	Matches any number of characters in a character string	t* matches Test, total, and Terrific
?	Matches any single alphanumeric character	Te?t matches Test and text

•

Crawlers. Search engines rely on crawlers that pace through the Internet checking for certain content, titles, key words, and so on. By default, crawlers are excluded from import to prevent hits by Internet search engines, robots, and any other user agent from being imported into the Data Warehouse. (These hits, however, are used in reports such as Summary Hit Counts and Bandwidth Data.)

You can find a list of crawlers that are excluded by default in the Commerce Server root directory, in the file Crawler.ini. The default list includes the crawlers that are excluded by the major Web auditing organizations. You can add new crawlers to the exclude list or restore crawlers for import; however, restoring crawlers might reduce your overall system performance.

The Excludes tab in the Advanced Web Log Import Properties dialog box provides a drop-down list from which you can choose the type of data you want to work with. Select the All option from the list if you want to see a list of all the exclusions.

By default, excluding data from import excludes that data for all applications running on your site. You can also exclude data from specific applications, while including similar data from other applications on your site.

For example, if you have two site applications, Application1 and Application2, and each application contains a directory named /Examples, you can choose to exclude the content in the /Examples directory for both applications or you can specify that data should be excluded only from the /Examples directory of Application1. The content in the Application2 /Examples directory continues to be imported.

Accuracy of Inferences

Commerce Server 2002 uses the concept of user visits to understand Web site traffic. A visit can be defined as a set of requests from the same user within a specified time span. However, the identification of users is not completely accurate because the Web client computers send only a limited amount of data to the Web server computer. Commerce Server attempts to identify users based on the properties in the Web log file. "Inferences" refer to the process of identifying unique users based on the user and visit data in the Web log file.

The accuracy of inferences depends on the data that is available. To maximize accuracy, Commerce Server uses a user inferential algorithm on each hit in a priority order:

•

If the hits include Commerce Server or customer-defined cookies, or user names, the accuracy is quite good because the users are being identified explicitly: they have enabled cookies, they are within a domain, or your site is using AuthManager, which supports cookieless shopping.

•

If the hits do not include cookies or user names, Commerce Server uses a combination of the client IP address and client browser agents to identify the users. In this scenario, the accuracy cannot be measured.

•

If client IP addresses are translated to a single IP address, use either cookies or user names in the Web server log import for inferring users.

Two DTS tasks are affected by IP translations:

•

Web server log import task. When inferring users, if the IP addresses of some users are translated to a single IP and the users all use the same browser, then they are treated as the same user.

For example, if ten users whose IP addresses are translated to a single IP address, and five of those users use browserA and the remaining five use browserB, then all ten users are treated as two users. This is the result if the IP address and browser type (instead of cookies or user names) are used for inferring users.

•

IP resolution task. When some IP addresses are translated to a single IP address, you may not get accurate domain information on all of the users visiting the site because the IP resolution task performs a look up on only one IP address.

You can set user and visit inferences in the Data Warehouse to customize the assumptions made during import about users and visits to best meet the analysis needs.

Note: After you perform these procedures, you must synchronize your site configuration with the Data Warehouse before importing data into the Data Warehouse.

For information about analyzing data from cookies, see the topic "Analyzing Data from Cookies" in Commerce Server 2002 Help.

Setting User Inferences

You might want to identify users as individuals whose browsing or buying patterns are of interest.

Commerce Server offers three options for identifying users:

•

Use Commerce Server identification cookies for inferences. You can automatically set Commerce Server to issue encrypted cookies that identify distinct users and associate them with user profiles in the Commerce Server database.

•

Use custom cookie identifiers. You can add a custom cookie identifier.

•

Use user names for inferences. You can set user names as the values of the cs-username field in the W3C log files.

Note: If you previously used Microsoft Site Server 3.0 Commerce Edition (SSCE), the Site Server cookies that were in use at that time are now treated as custom cookies. They appear in the list of custom cookies on the Inferences tab, in the Advanced Web Log Import Properties dialog box, just beneath the box labeled Enter new custom cookie identifier.

By default, all three options are enabled for Commerce Server. Unless you disable one or more of these options, they are used in the following order to identify users:

•	MSCSProfile in the cs(Cookie) field. Profile cookie issued by Commerce Server, stored in the W3C log file.
•	MSCSProfile in the cs-uri-query field. Profile cookie issued by Commerce Server, passed in the URL query string if the site is in cookieless mode.
•	MSCSAuth in the cs(Cookie) field. Authentication cookie issued by Commerce Server, stored in the W3C log file.
•	MSCSAuth in the cs-uri-query field. Authentication cookie issued by Commerce Server, passed in the URL query string if the site is in cookieless mode.
•	Custom cookie in the cs(Cookie) field. Cookie that a user might have chosen to accept, stored in the W3C log file.
•	Username. Field in the W3C log file (cs-username) as defined by the HTTP specification. The HTTP server populates this field with whatever user name is available. In most Internet scenarios, this field is not populated because the users are anonymous.
•	Client IP and User Agent. Client IP address and client browser string as defined by the HTTP specification.

Setting Visit Inferences

A visit to a Web site is considered the length of time a unique user spends interacting with that Web site. Typically, a visit begins when a user accesses a site either by clicking a link to the site or by typing the site URL into the Address box of the browser. Determining when a visit ends is more difficult. If the user closes the browser in the middle of a session, minimizes the browser, or types a new URL into the Address box of the browser, no log entry indicates that the visit has ended.

To compensate for the lack of a clear indication that a visit has ended, Commerce Server sets a default visit time-out of 30 minutes of user inactivity to declare that a visit has ended. If you choose, you can change the length of the visit time-out to meet the needs of your Web site. If the visit time-out elapses, ending the visit, and the user returns to the site a moment later, the report shows two visits.

Note: In some cases, your server might not log user visit activity chronologically, which can cause data for a visit to be imported out of chronological order. The Data Warehouse can adjust for this up to the specified length of the visit time-out. However, if a visit exceeds the visit time-out, the accuracy of your data is impacted.

Setting Time Overlap and Log File Rotation Properties

You use the time overlap property to eliminate redundancy that can occur when log files contain requests that overlap in time. You use the log file rotation property to eliminate redundancy or the loss of data that can occur when log files are rotated among servers to keep the log files from getting too large.

Without the time overlap and log file rotation settings, it is impossible to import log files into the Data Warehouse without losing activity data because log files are continually created as your site operates. Your files might contain data for overlapping time periods if you run log files on separate servers, interrupt and resume logging on a single server, accidentally re-import a log file, or concatenate distinct log files.

Note: After you perform these procedures, you must synchronize your site configuration with the Data Warehouse before importing data into the Data Warehouse.

Time Overlaps

Time overlaps can occur if you have multiple servers associated with a site, interrupt and resume logging on a single server, accidentally re-import a log file, or concatenate distinct log files. (Concatenation makes tracking time overlaps extremely difficult and therefore should be avoided.) The default time overlap period for Commerce Server is 30 minutes.

When a time overlap is detected during import, by default, the overlapping requests are discarded, and the remaining requests are imported. You can change both the time overlap period and the action you want to occur when an overlap is detected.

If you import a log file accidentally, you can delete the log file by using the Data Deletion task. For information about deleting log files, see the topic "Running the Data Deletion DTS Task" in Commerce Server 2002 Help.

Log File Rotation

Log file rotations cause an arbitrary cutoff of data in log files because data for some visits are divided between the end of one log file and the beginning of the next. You can choose to store, commit, or disregard the open visits at the end of your import process.

Setting Query String Import Properties for Web Log Files

If your site uses query strings, you can record the query strings in log files and import them into the Data Warehouse. Query strings can be used in your site for searching or running executable files. If you have a list of query strings that your site uses, you can enable reporting on the content associated with those query strings.

By default, query strings are stored with the Uniform Resource Identifier (URI) in the Data Warehouse. Using query strings, you can perform HTML title lookups for the pages on your site that use query strings. However, if there are many different combinations of parameter names and values for the query strings in your log files, the number of imported requests could increase substantially, which can reduce system performance. If this occurs, you might want to discontinue storing query strings with the URI.

Note: Observe the following when configuring query string properties:

•	After you perform these procedures, you must synchronize your site configuration with the Data Warehouse before importing data into the Data Warehouse.
•	The Data Warehouse can import a combined maximum of 256 single- and multi-valued query strings.
•	Imported query strings can consist of a maximum of 150 single-valued query strings or a maximum of 256 multi-valued query strings. Importing too many query strings can cause the Web log import process to fail.
•	The Data Warehouse limits the length of imported multi-valued strings (including query strings) to 512 characters. Any multi-valued query string consisting of more than 512 characters is truncated to 512 characters when it is imported.

Setting Web Log Import Restart Properties

You use the Restart tab of the Import Web Server Logs dialog box to configure your restart options. If the Data Warehouse encounters an erroneous import log, the restart option can either automatically stop the entire import process or delete the erroneous log and continue after all the retry attempts have failed.

Note: After you perform this procedure, you must synchronize your site configuration with the Data Warehouse before importing data into the Data Warehouse.

Top of page

Appendix C: Mapping Profile Properties

Commerce Server 2002 ships with several profile definitions that you can use for your site. If you choose to create custom profile definitions and still want to use the Commerce Server 2002 analysis reports, you must map the properties of your custom profile definitions to the tables and columns in the Data Warehouse that provide the data for analysis reports. If you choose to store your data in custom tables, you must create new reports that access the data in those tables.

Each exported profile property must be mapped to a profile data source for storage before being imported into the Data Warehouse.

If you create a custom profile definition to collect user data on your Web site, and you want to use the data collected with it in Commerce Server profile reports, you must store the information in the existing columns in the RegisteredUser table in the Data Warehouse.

If the data is not needed for reports or if you are writing custom reports to use the data, running the Profile data import DTS task creates a new class (table) and columns in the Data Warehouse.

To map the data collected with your user profile definition to the RegisteredUser table in the Data Warehouse, you must add the following to your user profile definition:

•

DWClassName. A profile definition custom attribute for defining the name of the object (in this case the RegisteredUser table) in the Data Warehouse to which the profile exports data. If the class (table) does not exist in the Data Warehouse, the DTS task creates it.

•

DWMemberName. A profile property custom attribute for defining the name of the data member (in this case, the column in the RegisteredUser table) in the Data Warehouse where the profile property is stored. If the member name (column) does not exist in the specified Data Warehouse class (table), the DTS task creates it. For a list of data members (columns) in the RegisteredUser table, see the topic "RegisteredUser" in Commerce Server 2002 Help.

Note: If you choose to store the data in a new column, assign the name of the new column as the value of DWMemberName for the property to be stored in that column. The DTS task adds the column to the RegisteredUser table when it runs. The data in the new column, however, is not used in any Commerce Server reports. If you want to run reports on data in new tables and columns in the Data Warehouse, you must create new reports. For information about creating new reports, see the topic "Creating Custom Reports" in Commerce Server 2002 Help.

•

Date_Last_Changed a profile property of type DATETIME that the DTS task needs to perform incremental updates. This property is included in the sample profile definitions that ship with Commerce Server 2002. For information about adding a property to a profile definition, see the topic "Adding Properties to Profile Definitions in the Profiles Resource" in Commerce Server 2002 Help. For information about the sample profile definitions that ship with Commerce Server 2002, see the topic "Sample Profile Definitions" in Commerce Server 2002 Help.

For more information about Data Warehouse classes, see the topic "Dividing the Data Warehouse into Primary Business Categories" in Commerce Server 2002 Help. For more information about Profile classes in the Data Warehouse, see the topic "Profile Management Classes in the Data Warehouse" in Commerce Server 2002 Help.

To add the DWClassName custom attribute to a profile definition that maps to the RegisteredUser table in the Data Warehouse

Click Start, point to Programs, point to Commerce Server, and then click Commerce Server Manager.

Expand Commerce Server Manager, expand Global Resources, expand Profiles, expand Profile Catalog, expand Profile Definitions, right-click your user object profile definition, and then click Properties.

In the Properties dialog box, on the Custom Attributes tab, click New.

In the Custom Attribute dialog box, do the following:

Use this	To do this
Name	Type DWClassName.
Display name	Type Data Warehouse class name.
Description	Type Class name in Data Warehouse.
Value	Type RegisteredUser.

Click OK to close the Custom Attribute dialog box.

Click OK to close the Properties dialog box.

After you have added the DWClassName custom attribute to the user profile definition, add the DWMemberName custom attribute to each property in the user profile definition that is exported to the Data Warehouse. The DWMemberName custom attribute is used to map profile definition properties to the columns in the table specified with the DWClassName custom attribute.

To add the DWMemberName custom attribute to a profile property that maps to a column in the RegisteredUser table in the Data Warehouse

Click Start, point to Programs, point to Commerce Server, and then click Commerce Server Manager.

Expand Commerce Server Manager, expand Global Resources, expand Profiles, expand Profile Catalog, expand Profile Definitions, and then click the profile definition that contains the property to which you want to add the custom attribute.

In the Profile Designer screen, in the Properties list, select the property to which you want to add the custom attribute.

In the Profile Designer screen, in the Advanced Attributes section, in the Map to Data box, click to open the Data Source Picker dialog box.

In the Data Source Picker dialog box, expand the data source that contains the data object that contains the data member with which you want to map the property.

Expand the data object that contains the data member with which you want to map the property, select the data member, and then click OK.

In the Profile Designer screen, in the Advanced Attributes section, select Exported to specify that this property will be imported into the Commerce Server Data Warehouse. You can export only mapped properties.

Note: You cannot export properties that are multi-valued and of the type Boolean or properties of type binary, long string, or password.

In the Profile Designer screen, in the Custom Attributes section, click New.

In the Profile Designer screen, in the Custom Attributes section, do the following:

Use this	To do this
Name	Type DWMemberName.
Display Name	Type DWMemberName.
Description	Type Data Warehouse member name.
Value	Type the name of the column in the RegisteredUser table where the data should be stored.

10.

Click Accept, and then click Apply.

11.

To save the profile definition, click on the toolbar.

The profile definition is saved with the custom attribute.

Customizing the Name of the Data Warehouse Column for a Profile Property

For profiles, you can customize the name of the column in the Data Warehouse for the new profile property. In addition, you can export the property to a particular column that already exists in the Data Warehouse.

To customize the name of the column for a profile property

In Commerce Server Business Desk, click Profiles, and then click Profile Definition Designer.

In the Profiles screen, select the profile definition that contains the property you want to import into the Data Warehouse, and then click on the toolbar.

In the Profile: <profile definition> screen, in the Properties list, select the property or property group that you want to import into the Data Warehouse.

In the Detailed Information section, click Custom Attributes.

In the Custom Attributes section, click New, and then do the following:

Use this	To do this
Name	Type DWMemberName.
Value	Type a name for the column.

Click Accept, click Apply, and then click on the toolbar.

Click Publish Profiles.

In the Publish Profiles screen, select the site to which you want to publish profile definition changes, and then click on the toolbar.

A column with the name you specified is added to the Data Warehouse the next time you run the Profile data import task.

Top of page

Appendix D: Incrementally Importing Custom Data

The Data Warehouse DTS tasks provide the option of importing data incrementally to prevent source tables from locking during the import process. With incremental importing, you can also import data that is new since the last time you imported the data into the Data Warehouse.

If you are importing non-commerce data into a custom class in the Data Warehouse, you cannot use a Commerce Server DTS task to perform the import, but you may still be able to perform the import incrementally.

To import non-commerce source data incrementally, the source data must have a timestamp as a dimension so you can record the last timestamp imported in a separate Data Warehouse table. In this way, you can qualify a select statement, with a where clause restricting the import by date.

If the source data has any other numerically incremented data (preferably with some kind of index built on it) then you can use that as a bookmark in the source data.

Top of page

Importing Non-Commerce Server Data into the Commerce Server 2002 Data Warehouse

On This Page

Executive Summary

Introduction

Commerce Server Business Analytics System

Integrating External Data with Commerce Server

Preparing Non-Commerce Data for Import

Preparing Non-Commerce Web Log File Data for Import

Preparing External Catalog Data for Import

Preparing External Profile Data for Import

Importing Non-Commerce Data into the Data Warehouse

Importing Log Files from Non-Commerce Web Servers

Importing Catalog Data into the Data Warehouse

Importing Profile Data into the Data Warehouse

Running the Report Preparation DTS Task

Analyzing Data from Non-commerce Web Servers

Running Reports

References

Appendix A: Specifying a Web Server Log File Mask

Sample Log File Masks

Appendix B: Web Log File Import Options

Web Log Default Files

Web Log File Exclusions

Accuracy of Inferences

Setting User Inferences

Setting Visit Inferences

Setting Time Overlap and Log File Rotation Properties

Setting Query String Import Properties for Web Log Files

Setting Web Log Import Restart Properties

Appendix C: Mapping Profile Properties

Customizing the Name of the Data Warehouse Column for a Profile Property

Appendix D: Incrementally Importing Custom Data