Import Product Catalog Data
You use the Product Catalog Import DTS task to import data about the products in your Commerce Server 2009 catalog database, including dimensional information such as product name, description, color, and size.
The Product Catalog Import DTS task imports data from catalogs, such as product names, colors, and sizes, into the Data Warehouse. After it is imported, the product data is used to generate reports that analyze product sales and perform other transaction analysis. This topic lists the transformations made by the Product Catalog Import DTS task on the data when it is imported into the Data Warehouse.
Imported Data
Use the Catalog Import Properties Dialog Box to specify the data to be imported from your catalog database(s) by the Catalog Import DTS task. Importing catalog data provides transaction reports that have more descriptive product information than is available in transaction data.
The following tables list the columns extracted from the source tables in the Site_productcatalog database, the transformation performed, and the tables in the Data Warehouse to which the data is saved.
Most Data Warehouse tables also contain a SiteName column. This column, like TableID and TableInternalFlag, is also not listed in the tables in the following sections because the SiteName is always extracted from the same location for all DTS tasks.
Source columns in the CatalogGlobal table of the Site_productcatalog database |
Transformation |
Target columns in the CatalogGlobal table of the Data Warehouse |
|---|---|---|
CatalogID |
Copy Column |
CatalogGlobalID |
CatalogName |
Copy Column |
CatalogName |
Currency |
Copy Column |
Currency |
EndDate |
Copy Column |
EndDate |
Locale |
Copy Column |
Locale |
ProductID |
Copy Column |
ProductID |
ReportingLanguage |
Copy Column |
ReportingLanguage |
StartDate |
Copy Column |
StartDate |
VariantID |
Copy Column |
VariantID |
WeightMeasure |
Copy Column |
WeightMeasure |
The I_ClassType column in the CatalogName_CatalogProducts table specifies whether a row is a product, variant, product family, or category. The following table lists the values associated with each classification.
Classification |
Value |
|---|---|
Category |
1 |
ProductVariantClass |
2 |
ProductClass |
4 |
ProductFamilyClass |
8 |
The process of transferring products has two steps. First, the task transfers each product and the category to which the product belongs. Then, it transfers all the product variants and the variant families together with their categories to the Data Warehouse product tables. The Data Warehouse Products table has a multivalue relation with the Category table. Based on the relation, the Data Warehouse provider writes the products to the Products table, the categories to the Category table, and populates the link table.
The Products table in the Data Warehouse does not contain all the properties associated with a product. Only product properties in the CatalogAttributes table in the Site_Commerce database that have the ExporttoDW property set to 1 are exported.
Source column from the CatalogName_Language View of the Site_productcatalog database |
Transformation |
Target columns from the Products table in the Data Warehouse |
|---|---|---|
CatalogName |
Copy Column |
CatalogName |
CatalogName and ProductID and VariantID (if available) |
Concatenation |
ProductNameCat |
cy_list_price |
Copy Column |
cy_list_price |
PrimaryParentCategory |
Copy Column |
PrimaryParentCategory |
ProductID |
Copy Column |
ProductID |
VariantID |
Copy Column |
VariantId |
N/A |
Generated |
Product, Unique |
Source columns from the CatalogName_Language view of the Site_productcatalog database |
Transformation |
Target columns from the Category table in the Data Warehouse |
|---|---|---|
CatalogName |
Copy Column |
CatalogName |
CatalogName and CategoryName |
Concatenation |
CategoryNameCat |
CategoryName |
Copy Column |
CategoryName |
Source columns from the CatalogName_CatalogHierarchy table of the Site_productcatalog database |
Transformation |
Target columns from the CatHierarchy table in the Data Warehouse |
|---|---|---|
CatalogName and CategoryName for parent categories from Catalog_CatalogProducts table If the ParentOID column is set to –1, the category is a parent category. |
Concatenation |
ParentCategoryCat |
child_CatalogName and CategoryName for child categories from CatalogProducts table. If the ParentOID column is set to the oid of a parent category, the category is a child category. |
Concatenation |
ChildCatalogCat |
Related Sections
How to Run the Catalog Data Import DTS Task