ETL Process for the Product Catalog Import DTS Task

Article
11/12/2009

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.

Ee797081.note(en-US,CS.20).gif Note

The Product Catalog Import DTS task imports only base catalogs. If the IsVirtualCatalog property of the CatalogGlobal site table is set to 0, the catalog is a base catalog.

The following tables list the columns extracted from the source tables in the Site_commerce database, the transformation performed, and the tables in the Data Warehouse to which the data is saved.

Source columns in the CatalogGlobal table of the Site_commerce 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 along with their categories to the Data Warehouse product tables. The Data Warehouse Products table has a multi-value 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 of 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_commerce 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 identifier for table.`

Source columns from the CatalogName_Language view of the Site_commerce 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_commerce database Transformation Target columns from the CatHierarchy table in the Data Warehouse

Source columns from the CatalogName_CatalogHierarchy table of the Site_commerce 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`

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

ETL Process for the Product Catalog Import DTS Task

See Also

Additional resources