Extending the Data Warehouse Database

The Data Warehouse import functions are designed to import data based on Commerce Server default settings. The method you use to extend the Data Warehouse database to accommodate importing new data depends on the type of data you want to import. For example, if you are adding user profile properties or product catalog properties, you can set the Export attribute in the Profile Definition Designer and the Store data for analysis attribute in the Catalog Definition Designer to tell the Data Warehouse to make the schema change required to accept the new data. Additionally, you can use custom attributes to designate the table and row in the Data Warehouse in which the data should be stored. Reusing existing columns in the Data Warehouse will present that data in an existing report. You can also import data into new tables and columns. If you import data into new columns, you will need to create new reports for that data.

If you want to import other types of data into the Data Warehouse, you will need to physically extend the Data Warehouse. For example, you would extend the Data Warehouse to import sales data from your brick-and-mortar store. Additionally, you would create a custom report to report on the new data that enables you to analyze both online sales and brick-and-mortar sales.

The following table describes the ways in which you can extend the Data Warehouse to accommodate new data.

Import data type	Data Warehouse extension method
New profile property	Select the Export attribute for the property in the profile definition. The Profile data import DTS task will automatically extend the schema of the Data Warehouse database to accommodate the new profile property. Alternatively, you can use custom attributes in the profile definition to designate a specific column in a specific table in the Data Warehouse in which the data will be stored. If you store the data in an existing column, the data will be included in an existing report. If you choose to store the data in a new column, the Data Warehouse will create the column during the import process, and you will need to create a new report to report on the data. For information about extending the Data Warehouse to import profile properties, see Selecting Profile Properties for Import. For information about designating an existing Data Warehouse column in which the imported profile data will be stored, see Mapping Profile Properties to Columns in the RegisteredUser Table. For information about designating a new Data Warehouse column in which the profile data will be stored, see Customizing the Name of the Data Warehouse Column for a Profile Property.
New transaction property	Use the TransDimension table in the Commerce database to select which transaction properties are imported into the Data Warehouse. The Transaction data import DTS task will automatically extend the schema of the Data Warehouse database to accommodate the new transaction property. Note Do not delete the default records from the TransDimension table. This action is untested and the resulting behavior is unknown. Only add new records to this table, and only delete those new records from this table. For information about extending the Data Warehouse to import transaction properties, see Selecting Transaction Properties for Import.
New catalog property	Select the Store data for analysis attribute for the property in the catalog definition. The Product catalog import DTS task will automatically extend the schema of the Data Warehouse database to accommodate the new catalog property. For information about extending the Data Warehouse to import catalog properties, see Selecting Catalog Properties for Import.

For more information about extending the Data Warehouse, see Extending the Data Warehouse. In addition, see the white paper "Extending the Commerce Server Data Warehouse / Analytics," located at the Development page on the Commerce Server Web site https://go.microsoft.com/fwlink/?LinkId=945. For samples that show how to extend the Data Warehouse, see Business Analytics Samples.

For information about the sample profile definitions included with Commerce Server, see Sample Profile Definitions.

Copyright © 2005 Microsoft Corporation.
All rights reserved.