Saturday, 19 July 2014

Websphere Commerce Attribute Dictionary





The Attribute Dictionary tool is a new attribute management tool in Management Center to provide business users the ability to update,assign,and manage product attributes.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 "Adidas Shoes" and has the attributes of "Color" (Blue, Black) & "Size" (S, M). The combination of the Color and Size resolves to an item ie., for the Product "Adidas Shoes", we have 4 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" and "M".

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


Classic Attribute Model uses following tables

ATTRIBUTE Table
This table holds catalog entry attributes used for descriptive or SKU resolution purposes. An attribute stored in this table is for a single catalog entry and is not shared between catalog entries

ATTRVALUE  Table
This table holds the values assigned to product attributes and relates them back to catalog entries for SKU resolution.

There is redundancy in the data that is getting stored in Class Attribute Model tables. To avoid 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 Table  
Table used to store the attribute dictionaries configured for the site. Each attribute dictionary is owned by a store, but can be used by children stores through store relationships. There can be only one attribute dictionary owned by any given store.

CATENTRYATTR Table
Stores the catalog entry (CATENTRY) and attribute dictionary attribute (ATTR) relationship. For example, when assigning an attribute dictionary attribute to a catalog entry, a row is created in this table with the details of the relationship between the two objects.
 
 CATENTRYATTR Table



ATTR Table

Stores attribute dictionary attributes. These attributes can be used for descriptive or SKU resolution purposes, and can be shared by catalog entries. Non attribute dictionary attributes used for a single catalog entry are stored in the ATTRIBUTE table.

ATTR Table






ATTRDESC  Table
This table holds the language sensitive attribute description. Before loading attributes in multiple languages, the attributes must already exist in the database with default data.
 

ATTRVAL Table
This table holds an attribute value that can be a shareable global attribute value or a local attribute value defined for a catalog entry.


ATTRVAL Table



ATTRVALDESC Table
This table holds the language sensitive attribute value description.


Attribute Sequencing:
Dataload utility should update CATENTRYATTR.SEQUENCE for sequencing attribute names in store front pages and ATTRVALDESC.SEQUENCE for descriptive attribute values. ATTR Table SEQUENCE column  is used for organizing attributes in  Management Center .Storefront pages use CATENTRYATTR Table SEQUENCE column for attribute name sequencing .The sequence which you see in storefront are with respect to CATENTRYATTR table SEQUENCE column because attributes are associated with specific product.

Attribute value Sequencing:
For both defining and descriptive value ordering, ATTRVALDESC table SEQUENCE column  is used


Enable Attribute Dictionary  in Store

To enable the attribute dictionary and classic attribute functionality concurrently, switch the implementation of ResolveSkuCmd to ResolveSkuAllCmdImpl in CMDREG table

update cmdreg set classname='com.ibm.commerce.catalog.commands.ResolveSkuAllCmdImpl' where interfacename ='com.ibm.commerce.catalog.commands.ResolveSkuCmd';

To enable the attribute dictionary only, switch the implementation of ResolveSkuCmd to ResolveSkuNewCmdImpl in the CMDREG table:

1)update cmdreg set classname='com.ibm.commerce.catalog.commands.ResolveSkuNewCmdImpl'  where interfacename ='com.ibm.commerce.catalog.commands.ResolveSkuCmd';
2) Customize the Catalogs tool to hide the local attribute table to prevent users from using  the table to create a new attribute in the legacy table. 


There are two different types of attributes


Allowed value attributes

Assigned value attributes. 


Allowed value attributes enforce a set of common attribute values that can be reused by multiple products consistently. For example, you can assign small, medium, and large as allowed values for a size attribute and reuse the size attribute across multiple products instead of having to define sizes each time. 

Assigned values are specified individually for each product. An example is a length attribute where all your products have a unique length. You can use an assigned value attribute to specify unique lengths for each product. 

Attribute dictionary supports inheritance where you can add and remove attributes to products and Skus in asset stores and extended site stores. You can create local attributes and values in an extended sites attribute dictionary. Your site can have multiple attribute dictionaries, each associated to a particular store. Each store, however, can only have one attribute dictionary.Single product entry can only use either classic attribute or attribute dictionary, not both .

In order for the store front to show attribute dictionary attributes, you must change the store front JSP files to use the wcf:getData tag to fetch the attributes.
 

<wcf:getData type="com.ibm.commerce.catalog.facade.datatypes.CatalogEntryType" var="catalogEntryWithAttr"  expressionBuilder="getStoreCatalogEntryAttributesByID">
<wcf:contextData name="storeId" data="${param.storeId}"/>
<wcf:param name="catEntryId" value="${WCParam.productId}"/>
<wcf:param name="dataLanguageIds" value="${WCParam.langId}"/>
</wcf:getData>



<c:forEach var='attribute' items='${catalogEntryWithAttr.catalogEntryAttributes.attributes}'> 
<c:if test="${ attribute.usage=='Descriptive' }" >
<span class="strongtext" title="<c:out value="${attribute.description}" escapeXml="false" />">
<c:out value="${attribute.name}" escapeXml="false" />:</span> 
<c:choose> 
<c:when test="${ !empty attribute.extendedValue['Image1'] }" >
<c:out value="${attribute.value.value}" escapeXml="false" />
&nbsp;
<img src="<c:out value="${product.objectPath}${attribute.extendedValue['Image1']}" />" alt="<c:out value="${attribute.value.value}" />" border="0"/>
 <br/>
</c:when> 
<c:otherwise >
<c:out value="${attribute.value.value}" escapeXml="false" />
</c:otherwise> 
</c:choose> 
</c:if> 
</c:forEach>


Facet management Tables

The database tables used to store facet information are listed below. The attribute table has been updated with one new column to represent the facetable attribute flag. The search attributes and search attribute properties tables continue to be used to store information about attributes to be included in the search index. These include both searchable and facetable attributes. Three new tables have been added to support facet management. The facet table stores all of the facet display properties. The facet description table is only used for non-attribute based facets to store a language sensitive display name and description for the storefront. The facet to catalog group mapping table tracks category level facet settings such as whether to display the facet and the display sequence. 

Existing tables 


ATTR  Table
Stores attribute dictionary attributes. These attributes can be used for descriptive or SKU resolution purposes, and can be shared by catalog entries. Non attribute dictionary attributes used for a single catalog entry are stored in the ATTRIBUTE table. 
SRCHATTR Table
Each row represents the logical name of a catalog attribute that is defined in the search schema.
SRCHATTRPROP  Table
This table stores the properties that are defined for each search index attribute. For e.g: the properties are rank, sort, catalogFilter, display, facet, filter and search.




insert into SRCHATTR (SRCHATTR_ID, INDEXSCOPE, INDEXTYPE, IDENTIFIER) values (primary_key, 'master_catalog_id', 'CatalogEntry', 'attr_identifier');

primary_key
An unused key in the SRCHATTR_ID column
master_catalog_id
The ID of the master catalog that the attribute belongs to.
attr_identifier
The column value in the ATTR.IDENTIFIER prefixed with _cat. For example: _cat.Size.

For eg:

Insert into SRCHATTR (SRCHATTR_ID, INDEXSCOPE, INDEXTYPE, IDENTIFIER) values (1000, '10101', 'CatalogEntry', '_cat.Size');

Insert into SRCHATTRPROP (SRCHATTR_ID, PROPERTYNAME, PROPERTYVALUE) values (srchattr_id, 'facet', 'solr_index_column_name');

srchattr_id
The primary key used in the previous query.

solr_index_column_name
The corresponding value in the ATTRDICTSRCHCONF.SRCHFIELDNAME suffixed with _ntk_cs. For example, if the ATTRDICTSRCHCONF.SRCHFIELDNAME column has a value of cas_f1, the value used for this table is cas_f1_ntk_cs.

Insert into SRCHATTRPROP (SRCHATTR_ID, PROPERTYNAME, PROPERTYVALUE) values (1000, 'facet', '
Propertynames   can be facet, rank, sort, catalogFilter, display, filter and search.
cas_f1_ntk_cs');


ntk stands for non tokenized search.

Tokenizing a field enables full text search, i.e. finding any word that occurs anywhere in the field. An Untokenized field will be found only when you have a complete and exact match, e.g. if the field's content is "black pen" then it will only be found when you search for "black pen", not when you search only for "black".


New tables introduced from WCS 7 Feature Pack 5 for facet management from management center are listed below.

FACET  Table
Store facet configurations including attribute based and non-attribute based facets.

FACETDESC  Table
Stores language sensitive name and description information for non-attribute based facets.
FACETCATGRP Table
Stores the facet IDs related to a given catalog group.


Making a Attribute as Non facetable in WCS 7 (WCS 7 feature pack 5)


UPDATE ATTR SET searchable=0 WHERE attr_id =? (This step is what we usually do  from management center but this alone is not sufficient)
DELETE FROM ATTRDICTSRCHCONF WHERE ATTR_ID = ?(The mapping between attribute and search schema)
DELETE FROM SRCHATTRPROP WHERE SRCHATTR_ID in (SELECT  SRCHATTR_ID FROM SRCHATTR WHERE IDENTIFIER LIKE ?(Table defining the search index attribute like facet, rank, sort etc..)
DELETE FROM SRCHATTR WHERE IDENTIFIER = ? 
UPDATE ATTR SET facetable=0 WHERE attr_id=?
DELETE FROM SRCHATTR WHERE SRCHATTR_ID IN (SELECT SRCHATTR_ID FROM FACET WHERE ATTR_ID =? )
DELETE FROM FACET WHERE ATTR_ID = ?

Making a Attribute as Non Searchable in WCS 7 (WCS7 feature pack 5)

SELECT ATTR_ID FROM ATTR WHERE IDENTIFIER ='';
(This step is what we do from management center but this alone is not sufficient)
UPDATE ATTR SET SEARCHABLE =0 WHERE IDENTIFIER = ' '
(The mapping between attribute and search schema is store  in ATTRDICTSRCHCONF )
SELECT * FROM ATTRDICTSRCHCONF WHERE ATTR_ID =''
DELETE FROM ATTRDICTSRCHCONF WHERE ATTR_ID = ''
(Table defining the search index attribute like facet, rank, sort etc..)
DELETE FROM SRCHATTRPROP WHERE SRCHATTR_ID in (SELECT SRCHATTR_ID FROM SRCHATTR WHERE IDENTIFIER ='')
DELETE FROM SRCHATTR WHERE IDENTIFIER =''
 

 

1 comment: