Migrating existing attributes to the Management Center attribute dictionary

When the Management Center attribute dictionary is enabled, existing attributes are not migrated to the attribute dictionary by default. To replace existing attributes with attribute dictionary attributes, you must manually clean your attribute data.This data cleansing operation identifies common attributes and organizes these attributes into groups so that they can be assigned to catalog entries.

Before you begin

Migrating existing attributes to the attribute dictionary is a complicated process which requires business decisions. Before you manually clean your attribute data, ensure that you complete the following tasks:

Review the information in overview of migrating attributes to the attribute dictionary.
Install WebSphere Commerce Feature Pack 2 or later.
Enable Management Center for this feature pack.

Note: The SQL scripts included here are meant to serve as examples and as such, are not database-specific.

It is important to note that the steps outlined here builds the dictionary; they do not reassign the attributes and the applicable values in the dictionary back to the products. When you migrate your existing attributes the relationship between the attributes and your catalog entries is removed. You must assign the attributes in the dictionary to the catalog entries. To assign attributes to catalog entries, use Data Load or the Management Center Catalog Tools.

Procedure

Identify your existing attributes and the requirements for these attributes, and then organize similar attributes into groups. The grouping option you select can be different from the option suggested in this task; there are no restrictions on how to structure attribute groupings.
Note: All attributes contained within the sample SQL statements in this task are enabled by default to be displayable and searchable. To use different values, change the SQL statements or change their output. The sample SQL statements migrate attributes that are only in a single language. For more information about handling multiple language attributes, see uploading catalogs.
Optional: If you are working with multiple stores, complete the following updates to each of the SQL statements to restrict the results to a specific store:
1. Add STORECENT to the SQL from clause.
2. Add "and STORECENT.CATENTRY_ID = R.CATENTRY_ID and STORECENT.STOREENT_ID = yourstoreid" to the SQL where clause.

Extract existing predefined (allowed) values with SQL statements:

Option Description

Option	Description
Manage attributes at the category level (share attributes across catalog entries in a category)	Extract the existing predefined (allowed) values with SQL statements. For example: 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; Determine whether you want to convert existing assigned values into predefined values and then extract the existing assigned values: To convert existing assigned values into predefined values, extract the assigned values with SQL statements. For example: 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 A.USAGE='2' and A.LANGUAGE_ID =-1 and AV.LANGUAGE_ID=-1 order by G.IDENTIFIER, A.NAME; To extract the existing assigned values without converting them into predefined values, extract the assigned values with SQL statements. For example: `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;` Note: You must reenter the value when the attribute is assigned to a catalog entry.
Manage attributes at the catalog level (share attributes across all catalog entries in a catalog)	Use the SQL statements shown in the previous table row, with the following changes: Change the first column from `G.IDENTIFIER\|\|'-'\|\|A.NAME` to `A.NAME` Remove `G.IDENTIFIER` from the order by clause

Manage attributes at the category level

(share attributes across catalog entries in a category)

Extract the existing predefined (allowed) values with SQL statements. For example:

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;

Determine whether you want to convert existing assigned values into predefined values and then extract the existing assigned values:

To convert existing assigned values into predefined values, extract the assigned values with SQL statements. For example:


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 A.USAGE='2'

and A.LANGUAGE_ID =-1 and AV.LANGUAGE_ID=-1 

order by G.IDENTIFIER, A.NAME;

To extract the existing assigned values without converting them into predefined values, extract the assigned values with SQL statements. For example:

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;

Note: You must reenter the value when the attribute is assigned to a catalog entry.

Manage attributes at the catalog level

(share attributes across all catalog entries in a catalog)

Use the SQL statements shown in the previous table row, with the following changes:

Change the first column from G.IDENTIFIER||'-'||A.NAME to A.NAME
Remove G.IDENTIFIER from the order by clause

Note: In the sample SQL statements, the sequence number is hardcoded to 1.0. This number does not cause issues when using Catalog Upload. You can change the sequence number before you load the attribute data into the attribute dictionary so that the values appear in the order you require.

Save the output of the SQL statements as a Comma-Separated Values (CSV) file. Manually add the catalog upload template name (AttributeDictionaryAttributeAndAllowedValues) to the beginning of the CSV file. The following code snippet shows sample output of the SQL statements with the catalog upload template name added:
```
AttributeDictionaryAttributeAndAllowedValues
Identifier,Type,AttributeType,Sequence,Displayable,Searchable,Comparable,Name,AllowedValue1,AllowedValue2,AllowedValue3,AllowedValue4,AllowedValue5,Delete
CopperPipeDiameter,FLOAT,AllowedValues,12.0,true,true,true,Diameter,0.5,0.75,1.0,1.25,1.5,
PantsWaist,INTEGER,AllowedValues,1.0,true,true,true,Waist,30,32,34,36,38,
BlouseMaterial,STRING,AllowedValues,1.0,true,true,true,Material,Cotton,Nylon,Lace,Rayon,Polyester,
```
The output format of the sample SQL statements matches the format expected by the catalog upload utility for the AttributeDictionaryAttributeAndAllowedValues template, with one predefined value (AllowedValue1). The provided SQL statements do not combine multiple attribute values together for an attribute. When the catalog upload utility encounters additional values for an attribute, it combines them with the existing values for the named attribute. The general format of the AttributeDictionaryAttributeAndAllowedValues template allows for up to nine values in a single CSV row. You can manually combine the values based on your requirements.
Optional: You can further change attributes in the attribute dictionary. When changing your attributes, you can change or create values for predefined value attributes.
Use Data Load or the Management Center Catalogs Tool to assign attributes to catalog entries.