ETL Process for the Product Catalog Import DTS Task
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.
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 |
---|---|---|
|
Copy Column |
|
|
Copy Column |
|
|
Copy Column |
|
|
Copy Column |
|
|
Copy Column |
|
|
Copy Column |
|
|
Copy Column |
|
|
Copy Column |
|
|
Copy Column |
|
|
Copy Column |
|
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 |
---|---|
|
1 |
|
2 |
|
4 |
|
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 |
---|---|---|
|
Copy Column |
|
|
Concatenation |
|
|
Copy Column |
|
|
Copy Column |
|
|
Copy Column |
|
|
Copy Column |
|
|
Generated |
|
Source columns from the CatalogName_Language view of the Site_commerce database | Transformation | Target columns from the Category table in the Data Warehouse |
---|---|---|
|
Copy Column |
|
|
Concatenation |
|
|
Copy Column |
|
Source columns from the CatalogName_CatalogHierarchy table of the Site_commerce database | Transformation | Target columns from the CatHierarchy table in the Data Warehouse |
---|---|---|
|
Concatenation |
|
|
Concatenation |
|
See Also
Running the Product Catalog Data Import DTS Task
Scripting the Product Catalog Import DTS Task
Workflow for Running DTS Tasks
Copyright © 2005 Microsoft Corporation.
All rights reserved.