Configuring the Data Warehouse and Analysis Server

For the latest version of Commerce Server 2007 Help, see the Microsoft Web site.

The Data Warehouse and analysis server imports, transforms, and stores Web site data. You use the data that is stored in the Data Warehouse database to analyze user activity on your Web site.

Important Note:
Commerce Server 2007 does not support mixed deployments of SQL Server 2000 and SQL Server 2005. All SQL Server components that you install must be at the same version level as the SQL Server database that you installed.

The following table summarizes the required SQL Server components that must be installed depending on which SQL Server version you are using.

Note

Microsoft SQL Server Express does not support the Data Warehouse.

Component to install and configure	SQL Server 2000	SQL Server 2005
SQL Server 2000	X
SQL Server 2000 Service Pack 4	X
SQL Server 2005		X
SQL Server 2005 Service Pack 2		X
SQL Server 2000 Analysis Services	X
SQL Server 2005 Analysis Services		X
SQL Server 2000 Reporting Services	X
SQL Server 2005 Reporting Services		X
SQL Server 2000 DTS Designer Components		X

The Data Warehouse uses SQL Server Analysis Services to access the online analytical processing (OLAP) database. SQL Server Reporting Services is required for analysis reporting.

Note

Commerce Server 2007 Data Warehouse does not support SQL Server or SQL Server Analysis Services installed on a named instance.

To configure the Data Warehouse and analysis server, follow these steps:

1. Perform all pre-installation requirements:

   1. Install all prerequisite software. For more information, see What Are the Software Deployment Requirements?

   2. Enable Internet Information Services (IIS). IIS is required to support remote access to Commerce Server reports. Perform the same task as you do on the business management server. For more information, see How to Enable IIS on the Business Management Server.

   3. Configure the network adapters. For more information, see How to Configure the Network Adapter on the Data Warehouse Server.

   4. Join the Data Warehouse and analysis server to the domain. For more information, see the Windows Server 2003 Help or Windows Server 2008 Help.

   5. Create the DTSImport account on the data tier domain controller. For more information, see What Are the Accounts and Groups to Create? and How to Create a Domain Account in Active Directory.

2. Install SQL Server to support a non-clustered database. For more information, see How to Configure a Database Server. For SQL Server 2005, on the Components to Install page, select all these options:

   • SQL Server Database Services

   • Analysis Services

   • Reporting Services

   • Notification Services

   • Integration Services

   • Workstation components, Books Online, and development tools

3. Install the version of SQL Server Analysis Services that is appropriate for your platform:

   • For SQL Server 2000, install SQL Server 2000 Analysis Services. For more information, see How to Install SQL Server 2000 Analysis Services and https://go.microsoft.com/fwlink/?LinkId=7201.

   • For SQL Server 2005, install SQL Server 2005 Analysis Services. For more information, see https://go.microsoft.com/fwlink/?LinkId=7201.

4. Install and configure the version of SQL Server Reporting Services that is appropriate for your platform:

   1. For SQL Server 2000, install SQL Server 2000 Reporting Services. For more information, see https://go.microsoft.com/fwlink/?LinkId=7201.

      - or -

      For SQL Server 2005, install SQL Server 2005 Reporting Services. For more information, see https://go.microsoft.com/fwlink/?LinkId=7201.

   2. Configure the reporting services. For more information, see How to Configure Reporting Services.

5. For SQL Server 2005, install SQL Server 2000 DTS Designer Components. For more information, see How to Install SQL Server 2000 DTS Designer Components.

6. On the Business Management server, unpack the Data Warehouse resource and add it to the Web site that you previously unpacked. For more information, see How to Unpack the Data Warehouse Resource.

   For each Web site in which you want to mine data, repeat this step.

   Note

   When unpacking the Data Warehouse PuP files, the time zone is typically inherited from the SQL Server settings. However, if this information is not set correctly or is not correct for the Data Warehouse instance that you are installing, you must set the time zone manually. To set the time zone manually for Data Warehouse, open the Properties tab in the Data Warehouse Global Resource, and then click the Time tab. You can then set the time zone and starting day of the week manually. Any changes to the time zone will be in effect only for new imports. Existing data is not affected. To update existing data with the new time zone information, you must import the data again.

7. Install the Data Warehouse reports. For more information, see How to Install the Data Warehouse Reports.

8. On the Data Warehouse and analysis server, grant the necessary permissions:

   1. Add the DTSImport account to the Administrators group.

   2. Create a DTSImport account on the Data Warehouse database.

   3. Grant the DTSImport account access permissions to the following Data Warehouse databases:

      Database	Permissions
      Msdb	db_datareader, db_dtsadmin, db_dtsltduser , db_dtsoperator
      <sitename>_DataWarehouse	db_datareader, db_datawriter, db_owner, db_ddladmin

   4. Create a Data Warehouse role and grant it access permissions to the OLAP cubes.

   For more information, see How to Grant Permissions for Data Import and How to Grant Permissions on the Data Warehouse Server.

9. On the run-time production databases, grant the DTSImport account access to the Web site databases.

   1. Create a DTSImport account on the run-time production database.

   2. Grant the DTSImport account admin_reader_role permission for the MSCS_Admin database.

   3. For each Web site that you added the Data Warehouse resource to, grant the DTSImport account the following permissions to the associated databases:

      Database	Permissions
      <sitename>_DataWarehouse	db_datareader
      <sitename>_marketing	db_datareader
      <sitename>_marketing_lists	db_datareader
      <sitename>_productcatalog	db_datareader
      <sitename>_profiles	db_datareader, Profile_Schema_Reader
      <sitename>_transactionconfig	db_datareader
      <sitename>_transactions	db_datareader

      For more information, see How to Grant Permissions on the Runtime Site Databases.

In This Section

• How to Configure the Network Adapter on the Data Warehouse Server

• How to Install SQL Server 2000 Analysis Services

• How to Install SQL Server 2000 DTS Designer Components

• Installing and Configuring Reporting Services

• How to Grant Permissions for Data Import

• How to Grant Permissions on the Data Warehouse Server

• How to Grant Permissions on the Runtime Site Databases

Related Sections

• Configuring the Business Management Server

• How to Unpack the Data Warehouse Resource