ETL Process for the Catalog Data Import DTS Task
The Catalog Import DTS task imports data, such as product names, colors, and sizes, from catalogs 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 Catalog Import DTS task on the data when it is imported into the Data Warehouse.
Importing product data provides your transaction reports with more descriptive product information than is available in transaction data. You can also run reports to analyze the sale of products.
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 <SiteName>_ProductCatalog 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 <SiteName>_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 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 the properties associated with a product. Only product properties in the CatalogAttributes table in the <SiteName>_ProductCatalog database that have the ExporttoDW property set to 1 are exported.
Source column from the CatalogName_Language View of the <SiteName>_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, Uniqueidentifier for table. |
Source columns from the CatalogName_Language view of the <SiteName>_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 <SiteName>_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 tableIf the ParentOID column is set to the object identifier of a parent category, the category is a child category. |
Concatenation |
ChildCatalogCat |