Data Warehouse Configuration Best Practices
The following configuration best practices apply to the Data Warehouse.
When you change your site configuration settings, run the Configuration synchronization DTS task.
You use the Configuration synchronization DTS task to import the site configuration data from the Administration database to the Data Warehouse. This differs from the Synchronize Values button on the Application Properties dialog box. When you click this button, Commerce Server 2009 reads the values from the Internet Information Services (IIS) metabase to the Administration database, and then from the Administration database to the Data Warehouse.
For performance purposes, the Data Warehouse stores the site configuration data, which includes site topology settings, instead of reading it from the Administration database every time that it is needed.
You can decide to synchronize the Data Warehouse with a single site or with all the sites that are associated with the Data Warehouse. You must run the synchronize task before you run DTS tasks that import or delete data. For instructions about how to run the Configuration synchronization DTS task, see How to Run the Configuration Synchronization DTS Task.
Consider Using a Script
If you have multiple applications and log files, use a script to configure the Web server log import DTS (Data Transformation Services) task instead of using the user interface.
For the Profile data import DTS task, review the default value of the Query Interval property and the Import Start Date property.
The Query Interval property controls how many import queries are sent to the online data source to retrieve data. You use the Query Interval property to split into several intervals the time between the time that you last ran the Profile data import task and the current time. The default value is 15 minutes. This means that the DTS task imports 15 minutes of data at a time, locking fewer records in the data source.
If you set a smaller value, it will lock a smaller section of the table but will increase the number of queries against the table and therefore the time that the task takes to run. If you set the value to zero, it will run a single query. This will lock a significant section of the table for the duration of the import and may decrease site performance.
The Import Start Date property determines the starting date of the import. By default, it is set to the current date. The first time that you run the task you may have to change this value.
If you configure the Profile data import task to run in incremental mode, the time that the last run completed is used as the starting time for the next import. If you configure the Profile data import task to run in bulk mode, the value of the Import Start Date property is used as the starting time.
Review the default value of the FirstDayOfWeek property in your language version of SQL Server to determine the start day of the week for reports.
For weekly aggregations of analysis reports, Commerce Server 2009 uses the default first day of the week for the given language version of the SQL Server product that is used. For the U.S. English version of SQL Server, the default first day of the week is Sunday.
Analysis reports do not use the Start day of the week property specified in the Commerce Server Manager.
If you use SQL Server authentication and you change the sa password for the SQL Server that hosts the Data Warehouse database, you must re-create the Transaction data import DTS task and the Product catalog import DTS task.
These DTS tasks persist the sa password in them.
Always accept the default IIS option to log data in Greenwich Time. This is the W3C standard.
The Web server log import DTS task assumes that the data to be imported is in Greenwich Time and processes the translation accordingly. Never select the IIS option to log data in local time, as that can cause problems with using the Date/Time Range property to import Web server log files.
If you configure the Web server log import DTS task to import log files that are based on the Date/Time Range property, specify the date/time in Greenwich Time.
Set the IncrementalOverlap value to the difference in time zones plus two hours.
When the Transaction data import and Profile data import DTS tasks perform incremental imports, use the last modified date field in the runtime tables to detect when a record has been changed and must be imported into the Data Warehouse. As this field is a date/time value written by applications, the time zones and clocks of the systems writing the value may not be synchronized. This could lead to variations in the last modified date field and can potentially cause records to be skipped during the import process.
You should increase this interval for systems that operate in different time zones so that the processing window covers the difference in time zones plus two hours.
To override the default of two hours, add the following values to the Windows registry:
DWORD : HEKY_LOCAL_MACHINE\Software\Microsoft\Commerce Server\TransactionImportDTS\IncrementalOverlap
DWORD : HKEY_LOCAL_MACHINE\ Software\Microsoft\Commerce Server\ProfileDataImportDTS\IncrementalOverlap
If the registry value is present, the Data Warehouse will use it as the number of hours subtracted from the start time of the last successful import instead of using the IncrementalOverlap value.
If you have a situation in which the Web server log import DTS task is not importing a file that you want, or is importing one that you do not want, check the configuration of the "Import all log files since last import" option.
The Sitesummary table in the Data Warehouse stores the start date/time and end date/time data that you specify in the Import Web Server Logs dialog box.
The Import all log files since last import option compares the end time value in the Sitesummary table with the date on the Web log file. This value is recorded in the local time. Therefore, you may have to adjust it because the Web log files are logged in Greenwich Time.
If client IP addresses are translated to a single IP address, use either cookies or user names in the Web server log import DTS task for inferring users.
IP translations affect two DTS tasks. These tasks are as follows:
Web server log import task. When the Web server log import DTS task infers users, if the IP addresses of some users are translated to a single IP address, and if the users all use the same browser, they will be treated as the same user.
For example, if there are ten users whose IP address is translated to a single IP address, and five of those users use browser "A" and the remaining five users use browser "B", all ten users will be 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 receive accurate domain information about all the users who visit the site. This is because the IP resolution task performs a look-up on only one IP address.