How to optimize your B2C Commerce product data

HOTSPOT
Products and their custom attributes are organized within two tables on the database. The product table is used to store all the system data such as product IDs, validForm, validTo, etc and custom attributes are stored separately. In the Business Manager, you can visit the following page to view the list of product system attributes (with lock icon) and product custom attributes (without lock icon) - Administration > Site Development > System Object Types > Product - Attribute Definitions.

The reasons behind a high amount of custom product attribute is mainly because of:

• The amount of sites and the amount of locales (languages and countries)
• The amount of localized and specific custom attributes
• High usage of Set and Enum attributes
• Redundant usage/storage of localized and/or site-specific values, no use of an optimized default value

Hence, the total number of product custom attributes in the database depends on:

• the number of products
• the number of locales for localizable attributes
• the number of sites for site-specific attributes

Each translation and site-value get a separate row in the database but only attributes which actually have values take up space (in general or for a specific value / locale).

There are possibilities while proceeding imports or live modifications via the BM that some of the product custom attribute values are not removed correctly from the database, no longer being referenced by a product, they become orphans.


ACTION

The following best practices should be performed when storing and maintaining product and catalog data on the Commerce Cloud platform:

• Remove orphan products and catalog data if they are not needed any longer.
• Remove custom attributes at product and category levels completely if they are not needed any longer. This will remove all appropriate stored values as well.
• Avoid as much as possible overrides for the default value of a site/locale specific attribute and try to use an optimized default.
• Only use country code values for locale specific attributes if needed, instead consolidate locale specific values to the language representation
• Only use set-of or Enum-of custom attributes when the extended functionalities of such data types are leveraged within the customization.

EXAMPLE – How to avoid overrides for the default value of a site/locale specific attribute
An instance has 1- sites and has a catalog with 100k products

• The products using a Boolean custom attribute (true or false) such 'isCoolProduct'
• The default value (all sites) for this attribute is set to false (100k products implying 100k rows for this custom attribute)
• The default value gets overwritten for all sites to true for all products via an import (100k products x 10 sites = 1.1 M rows for this single custom attribute)
• Solution: Changing the default value for all sites to true and remove the overrides via another catalog import

The import of empty values for custom and system objects is different:

• Custom attribute values can be deleted by importing empty values

<?xml version="1.0" encoding="UTF-8"?>
<catalog xmlns="http://www.demandware.com/xml/impex/catalog/2006-10-31"" catalog-id="catalog">
<header><header>
    <product product-id="product1">
        <custom-attribute attribute-id="defaultColor" site-id="DE"/>
    </product>
</catalog>

The default value of a custom attribute should always be considered as the needed fallback option. The first xml snippet below shows how an import file wrongly configured could unnecessarily lead to data duplication.  

<?xml version="1.0" encoding="UTF-8"?>
<catalog xmlns="http://www.demandware.com/xml/impex/catalog/2006-10-31"" catalog-id="electronics-catalog">
<header></header>
<product product-id="myProductID">
<custom-attributes>
<custom-attribute attribute-id="department" xml:lang="de-DE">Clothing</custom-attribute>
<custom-attribute attribute-id="department" xml:lang="fr_FR">Clothing</custom-attribute>
<custom-attribute attribute-id="department" xml:lang="it-IT">Clothing</custom-attribute>
<custom-attribute attribute-id="department" xml:lang="es-ES">Clothing</custom-attribute>
</custom-attributes>
</product>
</catalog>

Instead, the default value of the custom attribute can be filled using x-default preventing any additional row to be unnecessarily created for the department custom attribute.  

<?xml version="1.0" encoding="UTF-8"?>
<catalog xmlns="http://www.demandware.com/xml/impex/catalog/2006-10-31"" catalog-id="electronics-catalog">
<header></header>
<product product-id="myProductID">
<custom-attributes>
<custom-attribute attribute-id="department" xml:lang="x-default">Clothing</custom-attribute>
</custom-attributes>
</product>
</catalog>

• System attribute values can be deleted (if the schema allows it) by using

xsi:nil="true”

it also requires to add the following to the catalog tag

<?xml version="1.0" encoding="UTF-8"?>
<catalog xmlns="http://www.demandware.com/xml/impex/catalog/2006-10-31"" xsi:noNamespaceSchemaLocation="catalog.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"" catalog-id="catalog">
<header></header>
     <product product-id="product1">
        <online-flag site-id=“DE" xsi:nil="true”/>
     </product>
</catalog>

The default value of a system attribute should always be considered as the needed fallback option. The first xml snippet below shows how an import file wrongly configured could unnecessarily lead to data duplication.

<?xml version="1.0" encoding="UTF-8"?>
<catalog xmlns="http://www.demandware.com/xml/impex/catalog/2006-10-31"" catalog-id="electronics-catalog">
<header></header>
<product product-id="myProductID">
<online-flag>false</online-flag>
         <online-flag site-id="SiteGenesis">true</online-flag>
         <online-flag site-id="SiteGenesisGlobal">true</online-flag>
         <online-flag site-id="SiteTraining">true</online-flag>
</product>
</catalog>

Instead, the default value of the online-flat can be set to true avoiding duplicate of the system attribute value for a specific site.  

<?xml version="1.0" encoding="UTF-8"?>
<catalog xmlns="http://www.demandware.com/xml/impex/catalog/2006-10-31"" catalog-id="electronics-catalog">
<header></header>
<product product-id="myProductID">
<online-flag>true</online-flag>
</product>
</catalog>

Additional Info : MORE INFORMATION
Master / Variant attributes: Having identical values in an attribute across all variants instead of storing it only on the master leads to data duplication and could be one of the biggest chances for improvement. You can automatically fallback to the master if the variant has no value for an attribute. It is also not a problem for a part of the variants to fall back to the master (since attribute is empty for them) and a part having specific values (which will then have priority of the master value). This potentially could apply to many attributes (including lots of indexed attributes and some with lots of data in it, e.g. the descriptions, which would only increase the benefit).

Localisable attributes: Each translated value gets a separate row in the database. It will make quite a difference if identical values for country-specific locales are only stored once on the fallback locale instead of each country-specific locale (e.g. German with only being stored once on “de” instead three times on “de-DE”, “de-AT”, “de-CH”; for English it could be even more important that “x-default” is used as much as possible and all the “en-X” locales are left empty unless they are really different- Like for the masters this can be done in two phases: Quick win: hard-code per locale or per attribute or per locale/attribute where stuff goes, e.g. if you now that all three Germans are always the same always use only the “de-DE” values and store into “de” while ignoring the other two versions and leaving “de-DE”, “de-AT”, “de-CH” empty.

Site-specific attributes: Pretty much the same as with the localisable attributes applies, only that we have one less layer of fallback (i.e. they all fallback to default and not e.g. to “de” and after that to default).

Set of String attributes: Each string in a set of string uses a separate row in the database. Therefore, a set-of-string attribute with four values is like having four separate string attributes. For some information currently represented by a set-of-string, it may help to use a flat string instead and use a separator to indicate the individual values. There is a small code change necessary where this information is displayed to not simply iterate over the values but split the value instead and only afterwards iterate over the parts. Effort for this however is very low but the change then also needs to be included in a deployment, tested and rolled out. The attribute will not be able to be used as a refinement / filter afterwards anymore (also not for implicit refinements). All values joined together plus the separator still need to fit the maximum length of a single string for all languages. As long as the set-of-string only has 1 value set, it is the same (from a row count point of view) as having a value set for a string type attribute.