WCS | Migrating Classic Attribute Model table data to Attribute Dictionary tables

In Aurora store, if you wanted to enable facets in the Category landing pages and Product Listing pages, the attributes for the products/items should be available in Attribute Dictionary  tables and not in the Classic Attribute model tables. If you already published the information to the Class Attribute tables and if you wanted to enable the facets, these attribute data in Classic Attribute tables needs to be migrated to Attribute Dictionary tables. I am not sure how to enable the facets for Classic Attribute Model data and I couldn't able to get appropriate support for the same. So, I tried to migrate the this Class Attribute data to Attribute Dictionary table.

Here are the steps, I have followed as part of migration,

1. Query the Classic Attribute Tables for Descriptive and Defining Attributes for all the products in the Catalog and import the data as comma separated files, add "AttributeDictionaryAttributeAndAllowedValues" as the first row in the CSV file and do the dataload of these CSV files,

          1.1. Defining Attributes: You can maintain the Defining Attributes at Catalog level or Category level          1.1.1. Query to generate Defining Attributes at the Catalog level,        
select G.IDENTIFIER||'-'||A.NAME "Identifier", AV.ATTRTYPE_ID Type", 'AllowedValues' "AttributeType", '1.0' "Sequence", 'true' "Displayable",'true' "Searchable",'true' "Comparable", A.NAME "Name",  COALESCE(AV.STRINGVALUE, CHAR(AV.INTEGERVALUE),CHAR(AV.FLOATVALUE)) "AllowedValue1"
from ATTRIBUTE as A, CATGPENREL as R, CATGROUP as G, ATTRVALUE as AV
where A.CATENTRY_ID = R.CATENTRY_ID and R.CATGROUP_ID = G.CATGROUP_ID and A.ATTRIBUTE_ID = AV.ATTRIBUTE_ID and AV.CATENTRY_ID = 0 and (A.USAGE is NULL or A.USAGE = '1') and A.LANGUAGE_ID =-1 and AV.LANGUAGE_ID=-1 order by G.IDENTIFIER, A.NAME;
       
        1.1.2. Query to generate Defining Attributes at the Category level,         
select A.NAME, AV.ATTRTYPE_ID "Type", 'AllowedValues' "AttributeType",'1.0' "Sequence", 'true' "Displayable", 'true' "Searchable", 'true' "Comparable", A.NAME "Name",
COALESCE(AV.STRINGVALUE, CHAR(AV.INTEGERVALUE),CHAR(AV.FLOATVALUE)) "AllowedValue1" 
from ATTRIBUTE as A, CATGPENREL as R, CATGROUP as G, ATTRVALUE as AV
where A.CATENTRY_ID = R.CATENTRY_ID and R.CATGROUP_ID = G.CATGROUP_ID and A.ATTRIBUTE_ID = AV.ATTRIBUTE_ID and AV.CATENTRY_ID = 0 and (A.USAGE is NULL or A.USAGE = '1') and A.LANGUAGE_ID =-1 and AV.LANGUAGE_ID = -1 
order by A.NAME;

      1.2. Descriptive Attributes: You can maintain the Defining Attributes at Catalog level or Category level
       1.2.1. Query to generate Descriptive Attributes at the Catalog level,              
select G.IDENTIFIER||'-'||A.NAME "Identifier", AV.ATTRTYPE_ID "Type", 'AssignedValues' "AttributeType", '1.0' "Sequence", 'true' "Displayable", 'true' "Searchable",  'true' "Comparable", A.NAME "Name" 
from ATTRIBUTE as A, CATGPENREL as R, CATGROUP as G, ATTRVALUE as AV
where A.CATENTRY_ID = R.CATENTRY_ID and R.CATGROUP_ID = G.CATGROUP_ID and A.ATTRIBUTE_ID = AV.ATTRIBUTE_ID and A.USAGE='2'and A.LANGUAGE_ID =-1 and AV.LANGUAGE_ID=-1 
order by G.IDENTIFIER, A.NAME;
       
        1.2.2. Query to generate Descriptive Attributes at the Category level,   
 select A.NAME "Identifier", AV.ATTRTYPE_ID "Type", 'AssignedValues' "AttributeType", '1.0' "Sequence", 'true' "Displayable", 'true' "Searchable",  'true' "Comparable", A.NAME "Name"
 from ATTRIBUTE as A, CATGPENREL as R, CATGROUP as G, ATTRVALUE as AV
 where A.CATENTRY_ID = R.CATENTRY_ID and R.CATGROUP_ID =   G.CATGROUP_ID and A.ATTRIBUTE_ID = AV.ATTRIBUTE_ID and A.USAGE='2'and A.LANGUAGE_ID =-1 and AV.LANGUAGE_ID=-1 
order by A.NAME;       

    The output CSV file will be of the following format,
AttributeDictionaryAttributeAndAllowedValues    Identifier,Type,AttributeType,Sequence,Displayable,Searchable,Comparable,Name
operating_system,STRING,AssignedValues,1.0,true,true,true,windows8
chipset,STRING,AssignedValues,1.0,true,true,true,intel
mouse,STRING,AssignedValues,1.0,true,true,true,build_in_mouse
 

WCS | Attributes of the Product/Item | Classic Attribute Model Vs Attribute Dictionary

Attributes of the product/item are of the following types,
1. Descriptive Attributes
2. Defining Attributes

Defining Attributes: It is the attribute used for SKU resolution and on combination of multiple attributes, it resolves to an item
 Eg: If we have a product "Nike Shoes" and has the attributes of "Color" (Blue, Black) & "Size" (S, M, L). The combination of the Color and Size resolves to an item ie., for the Product "Nike Shoes", we have 6 items. So, these "Color" and "Size" has to be declared as Defining Attributes. For the "Color" attributes, the allowed values will be "Blue" & "Black" and for "Size" attribute, the allowed values will be "S", "M" & "L"

Descriptive Attributes: This attributes are not intended for any SKU Resolution. These Descriptive Attributes only provides more information about the product.
 Eg: For the HP Laptop, the Descriptive Attributes can be "Operating System Installed", "Chitset" and "Processor Speed". And the Assigned values for "Operating System Installed" might be "Windows 7" or "Windows 8" etc.

In earlier versions of WebSphere Commerce, for storing the Attributes of the products/items the following set of tables are used, which are called as "Classic Attribute Model" tables,
 - Attribute
 - Attrvalue

There are redundancy in the data that is getting stored in Class Attribute Model tables. To overcome that, IBM came up with new setup of tables and model called as "Attribute Dictionary", which will have a different normalization to avoid the redundancy of data. Here are the tables associated with Attribute Dictionary,
 - AttrDict 
 - CatentryAttr
 - Attr
 - AttrDesc
 - AttrVal
 - AttrValDesc

Here is the nice tutorial, which explains the difference between Classic Attribute Model and Attribute Dictionary,
http://publib.boulder.ibm.com/infocenter/ieduasst/v1r1m0/topic/com.ibm.iea.wcs/wcs/6.0.0.4/Customization/WCS6004_CatalogAttributeDictionary.pdf