Migrate > Take the first steps after WebSphere Commerce migration > Migrating attribute data



Migrate 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, manually perform a data cleansing operation on the 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

Before you perform a manual data cleansing on the attribute data, ensure that you complete the following tasks:


Procedure

  1. Identify the existing attributes and the requirements for these attributes, and then organize similar attributes into groups. The grouping option you select can be different than those suggested in this task; there are no restrictions on how to structure attribute groupings.

    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 Upload catalogs.

  2. 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 from clause.

    2. Add "and STORECENT.CATENTRY_ID = R.CATENTRY_ID and STORECENT.STOREENT_ID = yourstoreid" to the where clause.

  3. Extract existing allowed values using SQL statements:

    Option Description
    Manage attributes at the category level

    (share attributes across catalog entries in a category)

    1. Extract the existing predefined allowed values using 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;

    2. Determine whether to convert existing assigned values into predefined allowed values and then extract the existing assigned values:

      • To convert existing assigned values into predefined allowed values, extract the assigned values using 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 allowed values, extract the assigned values using 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;

        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

    In the sample SQL statements, the sequence number is hardcoded to 1.0. Although this 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.

  4. Save the output of the SQL statements as a Comma-Separated Values (CSV) file and then manually add the catalog upload template name (AttributeDictionaryAttributeAndAllowedValues) to the beginning of the 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, using one allowed 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 the requirements.

  5. Optional: You can further change or create values for predefined allowed value attributes in the Management Center attribute dictionary.


Related concepts

Overview of migrating attributes to the attribute dictionary


+

Search Tips   |   Advanced Search