Reuse attribute assigned values with the Data Load utility

We can use the Data Load utility to reuse assigned values for attributes when the same value is needed for multiple catalog entries. By reusing attribute assigned values across catalog entries, we can reduce the number of duplicate values that are created in the database.

If the site typically uses attributes with assigned values instead of attributes with predefined (allowed) values, many duplicate assigned values can exist for catalog entries since assigned values are not reused across catalog entries. The duplication of assigned values can cause the number of records in the ATTRVAL and ATTRVALDESC database tables to grow large, which can affect the performance of retrieving attribute information. For example, many catalog entries can have the same descriptive attribute Material with the same attribute value 100% Cotton. If the attribute uses assigned values, the value 100% Cotton is duplicated in the database for each catalog entry. If you use the Data Load utility to load the attribute values and reuse values, the utility creates only a single instance of the value. Alternatively to avoid duplicate assigned values and as a general best practice for using the attribute dictionary, use attributes with predefined values instead of attributes with assigned values whenever possible. Specifically, try to use attributes with predefined values when multiple catalog entries need the same attribute and value. If you do need to use attributes with assigned values, consider using the Data Load utility to load the catalog entry, attribute relationship, and assigned value information.

Note: If we use attributes with assigned values because attributes with predefined values display only 500 values in Management Center, as a best practice, use multiple attributes with predefined values instead. For instance, create separate attributes with predefined values for use within specific categories. For instance, we can create a Material attribute for each category. These attributes can all have the same name Material, but have different unique identifiers, such as material-shirts, material-shorts, and material-paints. By separating the predefined values into multiple instances of an attribute, the number of values for each attribute is reduced and business users can locate specific values easier. See Best practices for using the attribute dictionary.

Behavior changes when reusing assigned values

With the Data Load utility enabled to reuse assigned values, the behavior of the load process is different when we are creating, changing, or deleting assigned values.

• When we use the Data Load utility to reuse assigned values, specify the value identifier so that the load process uses the value identifier to identify multiple instances of the same assigned value in an input file. The utility creates the first instance of the value and reuses that value for all other instances of the same assigned value.

  To reuse values that are already in the database. we can include the value identifier for existing values as the identifier for assigned values in the input file.

• When you load data that changes a shared value by specifying the value identifier, the shared value is updated.

• When you attempt to delete an assigned value that is shared by another catalog entry, the assigned value is not deleted. The load process deletes only the relationship between the catalog entry and the attribute with assigned value. Other catalog entries that use the same shared value are not affected.

• When you load data to reuse assigned values, any assigned value that exists in the database before the load process runs is not affected. Duplicates that exist within the database are not updated to reuse values. To reuse assigned values and remove duplicate values, we can extract all assigned value data from the database and reload the data with the Data Load utility. If you do update all assigned value data to reuse values, avoid having business users use Management Center to manage attributes with assigned values. When a business user uses Management Center to create or change assigned values, duplicate records can still be created.

  • When a business user adds an attribute with assigned values to a catalog entry in Management Center, a new record for the assigned value creates in the database. This new record is created regardless of whether the assigned value is a duplicate of an existing value. To reuse a value, an administrator must load the relationships and value data with the Data Load utility.

  • When a business user edits an assigned value that is being reused by other catalog entries in Management Center, a new record for the assigned value is created in the database. The shared value is not updated, so other catalog entries that use the shared value are not affected.

  • When a business user deletes an assigned value that is being reused by other catalog entries in Management Center, the save process does not delete the assigned value. The save process deletes only the relationship between the catalog entry and the attribute with assigned value.

Configuration property to enable and disable reusing assigned values

To enable or disable the reuse of assigned values, a new configuration property, shareAssignedAttributeValues is available for use with the CatalogEntryAttributeDictionaryAttributeMediator business object mediator. We can set the following values for this property:

true

Assigned values for attributes are reused across catalog entries. This value is the default value.

false

Values are not reused across catalog entries. If you do not want to reuse assigned values, we can set this property value in the business object configuration file when loading catalog entry and attribute with assigned value relationships. When this property is set to false and the load process attempts to use an existing value identifier for another catalog entry, the load process throws an exception.

To help demonstrate how to configure this property, the sample wc-loader-catalog-entry-AD-attribute-relationship.xml configuration file in the following directory is updated to include the property. We can use this file when loading you own data.

• (Linux) utilities_root/samples/DataLoad/Catalog/AttributeDictionaryAttribute

• (Developer) WCDE_installdir\samples\DataLoad\Catalog\AttributeDictionaryAttribute

The following code demonstrates how to configure this property.

<_config:BusinessObjectMediator 
  className="com.ibm.commerce.catalog.dataload.mediator.CatalogEntryAttributeDictionaryAttributeMediator" 
  componentId="com.ibm.commerce.catalog" >
  <_config:property name="supportMultipleValuesForADAttributes" value="true" />
  <_config:property name="attributeValueValidation" value="false" />
  <_config:property name="shareAssignedAttributeValues" value="true" />
</_config:BusinessObjectMediator>

Another new configuration property is available to control the behavior when a business user updates or deletes an assigned value that is reused in Management Center. This KeepAssignedAttributeValueLocal configuration property is enabled by default and ensures that when business users update or delete an assigned value in Management Center, it does not affect other catalog entries that reuse the same assigned value. This KeepAssignedAttributeValueLocal configuration property is defined within the wc-business-object-mediator.xml file for configuring the business object mediators.

• (Linux) utilities_root/ts.ear/xml/config/com.ibm.commerce.catalog

• (Developer) utilities_rootts.ear/xml/config/com.ibm.commerce.catalog

The following code shows this configuration, which is set for the CatalogEntryAttributeDictionaryAttributeMediator business object mediator.

<_config:mediator
  interfaceName="com.ibm.commerce.foundation.server.services.dataaccess.bom.mediator.
  ChangeBusinessObjectMediator"
  className="com.ibm.commerce.catalog.facade.server.services.dataaccess.bom.mediator.
  ChangeCatalogEntryMediator" 
  updateAccessProfile="IBM_Admin_CatalogEntryUpdate">
  <_config:mediator-properties>
    <_config:mediator-property name="Attributes/SupportMultipleValuesForADAttributes" 
     value="true"/>
  <_config:mediator-property name="Attributes/KeepAssignedAttributeValueLocal" 
     value="true"/>
  </_config:mediator-properties>
  <_config:part-mediator interfaceName="com.ibm.commerce.foundation.server.services.
     dataaccess.bom.mediator.ChangeBusinessObjectPartMediator">
  <_config:part-mediator-implementation 
    className="com.ibm.commerce.catalog.facade.server.services.dataaccess.bom.
    mediator.ChangeCatalogEntrySEOPropertiesMediator" />
  <_config:part-mediator-implementation 
    className="com.ibm.commerce.catalog.facade.server.services.dataaccess.bom.mediator.
    ChangeCatalogEntrySEOURLMediator" />
  <_config:part-mediator-implementation 
    className="com.ibm.commerce.catalog.facade.server.services.dataaccess.bom.mediator.
    ChangeCatalogEntryDescriptionOverrideMediator" 
    updateAccessProfile="IBM_Admin_CatalogEntryDescriptionOverrideUpdate" />
  </_config:part-mediator>
</_config:mediator>

We can set the following values for the property:

true

When a business user updates an assigned value for a catalog entry in Management Center, the save process creates a new value in the database for the catalog entry. Catalog entries that use the initial assigned value are not affected. When a business user deletes an assigned value from a catalog entry, the user deletes only the relationship between the assigned value and catalog entry. If the assigned value is used by other catalog entries, the value is not deleted and the other catalog entries that use the value are not affected. This value is the default value.

false

When a business user updates an assigned value for a catalog entry in Management Center, the save process updates the assigned value. The value updates for all catalog entries that share the value. When a business user deletes an assigned value from a catalog entry, the user deletes the value from all catalog entries that shared the value.

Note: Before you change the value of this property to false, consider the following limitations for when the KeepAssignedAttributeValueLocal is set to "false":

• There is no visual indicator in Management Center that an assigned value is being shared by other catalog entries.

• Changes might not display immediately for all catalog entries that share a value when a business user updates an assigned value for a catalog entry in Management Center. Business users might need to reload any open catalog entry properties views to view the changes.

• The WebSphere Commerce search delta index rebuilds the index for only the catalog entry that the user updates when a business user updates the assigned value for a catalog entry. An administrator needs to run the full index to rebuild the index for the other catalog entries that use the updated assigned value so that the changes display in the storefront.

Due to the preceding limitations, we are recommended to keep the KeepAssignedAttributeValueLocal property set to "true". If an assigned value that is shared by multiple catalog entries needs to be updated, use the Data Load utility to load the changes. An Administrator needs to update the value for only a single catalog entry to update the value for all catalog entries that use the value. For example, the following CSV file contents update an assigned value for multiple catalog entries. If the initial assigned value "100% Cotton" is shared by multiple catalog entries, the following example updates the value to be "90% Cotton" for the specified catalog entry and all other catalog entries that use the value.

PartNumber	AttributeIdentifier	ValueIdentifier	Value	Usage	Sequence
Shirt	Material	Cotton	90% Cotton	Descriptive	1

Example

Before you enable the Data Load utility to reuse assigned values, loading the following catalog entry and attribute with assigned value relationships loads duplicates of assigned values.

PartNumber	AttributeIdentifier	ValueIdentifier	Value	Usage	Sequence
Shirt	Material		100% Cotton	Descriptive	1
Pants	Material		100% Cotton	Descriptive	1
Shorts	Material		100% Cotton	Descriptive	1

When you load this data into the database, each catalog entry uses the same attribute and attribute value information, but different attribute value records must be created for each value. Before you enable the reuse of values, you normally do not include the ValueIdentifier since the load process creates a new assigned value for each row. We can see these records by running the following SQL statement.

select catentry_id,attr_id,attrval_id,usage,sequence from catentryattr 
where attr_id in (select attr_id from attr where identifier='Material')

Where Material is the value of the AttributeIdentifier column. The following table shows the information that returns for the catalog entry and attribute relationship. These records include a different ATTRVAL_ID for each catalog entry and attribute relationship even though the actual value, 100% Cotton is the same.

CATENTRY_ID	ATTR_ID	ATTRVAL_ID	Usage	Sequence
19801	7000000000000000451	7000000000000008301	'2'	1
19802	7000000000000000451	7000000000000008302	'2'	1
19803	7000000000000000451	7000000000000008303	'2'	1

The different records for the same attribute and assigned value information includes duplicate attribute and assigned value relationship records in the ATTRVAL table. We can see these records by running the following SQL statement:

select attrval_id, attr_id, identifier, valusage from attrval 
where attr_id in (select attr_id from attr where identifier='Material')

The following table shows the information that returns for these duplicate relationships.

ATTRVAL_ID	ATTR_ID	IDENTIFIER	VALUSAGE
7000000000000008301	7000000000000000451	Cotton	NULL
7000000000000008302	7000000000000000451	Cotton1	NULL
7000000000000008303	7000000000000000451	Cotton2	NULL

The duplicate assigned value records are stored in the ATTRVALDESC tables, which we can see by running the following SQL.

select attrval_id, attr_Id, value from attrvaldesc 
where attr_id in (select attr_id from attr where identifier='attribute_identifier')

ATTRVAL_ID	ATTR_ID	Value
7000000000000008301	7000000000000000451	100% Cotton
7000000000000008302	7000000000000000451	100% Cotton
7000000000000008303	7000000000000000451	100% Cotton

When the Data Load utility is enabled to reuse assigned values, the same data is stored in the database differently. In your input file, we can include the same value identifier for different catalog entries without encountering an exception.

PartNumber	AttributeIdentifier	ValueIdentifier	Value	Usage	Sequence
Shirt	Material	Cotton	100% Cotton	Descriptive	1
Pants	Material	Cotton	100% Cotton	Descriptive	1
Shorts	Material	Cotton	100% Cotton	Descriptive	1

When the data loads, the catalog entry and attribute relationship can point to the same assigned value. We can see this change by running the following SQL statement.

select catentry_id,attr_id,attrval_id,usage,sequence from catentryattr 
where attr_id in (select attr_id from attr where identifier='AssignedValueAttr')

CATENTRY_ID	ATTR_ID	ATTRVAL_ID	Usage	Sequence
19801	7000000000000000451	7000000000000008301	'2'	1
19802	7000000000000000451	7000000000000008301	'2'	1
19803	7000000000000000451	7000000000000008301	'2'	1

The ATTRVAL table now includes only a single record for the attribute and attribute value relationship and the ATTRVALDESC table includes only a single record for the assigned value.