Managing the Data Warehouse

You use the Commerce Server Data Warehouse to import, transform, and store Web site data. You import data into your Data Warehouse from Web log files, Commerce Server databases, and other data sources that you specify. You use the data stored in the Data Warehouse to analyze user activity on your Web site. Commerce Server 2009 Enterprise Edition provides several business analytic features, such as reports, segment viewer, and affinity lists, through SQL Server Reporting Services. These reports show data from the Data Warehouse formatted in various ways to answer specific business questions about your Web site.

You use Commerce Server Data Transformation Services (DTS) tasks to import and change data in the Data Warehouse. The Commerce Server tasks are loaded into SQL Server when you unpack a site. You add the Commerce Server tasks to a SQL Server package and then run that package to make changes to the Data Warehouse. You can manually configure the Commerce Server DTS tasks to import data, or you can use the Data Warehouse Import Wizard.

You can perform the following tasks using the Data Warehouse:

  • Import Web log file, user profile, campaign, transaction, and catalog data from your Commerce Server site. You can also import data from other data stores, such as non-Commerce Server Web servers.

  • Resolve Internet Protocol (IP) addresses imported for client hosts that have connected to your site for use in custom reports.

  • Prepare the data in your Data Warehouse for analysis by populating the online analytical processing (OLAP) cubes, which organize the data for retrieval during report execution.

  • Delete imported log file data to reduce the amount of storage space in the SQL Server database required by Commerce Server.

  • Delete all data from the Data Warehouse to remove existing test or archived data.

  • Automatically rebuild analysis models to show current data for prediction, and archive existing analysis models.

The Data Warehouse storage engine provides the following storage processes for loading data into the Data Warehouse:

  • Summarization - You use summarization to summarize user, marketing, transaction, and catalog data from the Commerce Server database when you run the Web server log import DTS task and the Report preparation DTS task. Summarizations transform raw data in the Commerce Server database into information in the Data Warehouse that you use to analyze user activity on your Web site. This is performed by totaling and counting the related raw data.

    For example, 1,000 records of single hits on a particular page, on a particular day, are summarized into the information that the page had 1,000 hits on that day. You can use this information to make decisions that will increase traffic on your Web site.

  • Schema Management - You use schema management to modify the Data Warehouse schema as needed during the import process. For example, if you add a new property to the User Object profile definition and mark it as exported, the Profile data import DTS task adds a new column to the Data Warehouse schema when user profile data that contains that property is imported.

    The following table lists the import process and describes the schema changes the Data Warehouse storage engine makes to the Data Warehouse schema.

    Import process

    Data Warehouse schema change

    Web log file

    Query strings are added to the schema.


    Profile properties are added to the schema.

    Product catalog

    Product catalog dimensions are added to the schema.


    Transaction properties are added to the schema.

  • Data Loading - You use data loading to support the loading of high volumes of data into the Data Warehouse by using parallel reads and writes. The storage engine performs writes, high-speed parallel foreign key lookups, surrogate key generation, logical to physical mapping, and with the SQL Server Bulk Copy Program (BCP), high-speed parallel bulk copies.

For more information about how to configure the Data Warehouse, see How to Unpack the Data Warehouse Resource and Configuring the Data Warehouse and Analysis Server.

In This Section