Configuring the Data Import Handler mapping

In this lesson, you configure the Data Import Handler mapping to extract data from the relational table. You configure the mapping for data extraction with predefined SQL query lines that extract WebSphere Commerce data.

To extend the extraction scope, this lesson appends the columns and tables for cost price to your search queries. The cost price itself is not indexed because you do not directly use it during this tutorial. You define the query to calculate the profit margin that is based on offer price and cost price. You then map the column to index field in the configuration file.

Note: During this task, you are adding fields for only the Master Catalog in United States English. To add fields for more catalogs or languages, you must repeat this lesson for the additional catalogs and languages.

About this task

Procedure

Navigate to the following directory:
- WC_installdir/instances/instance_name/search/solr/home/MC_10001
- WCDE_installdir/search/solr/home/MC_10001
This directory contains the Master Catalog folder, in which there are the configurations files for each language.
Open the en_US\CatalogEntry\conf\wc-data-config.xml file
Locate the code <entity name="Product".

Add the columns and tables for profit margin into the SELECT clauses for both the query and deltaImportQuery:

Note: The customization that you perform in this tutorial modifies only one catalog in one language. During this task, you are adding fields for only Master Catalog 10001 in United States English. To add fields for more catalogs or languages, you must repeat this lesson for the configuration files of the additional catalogs and languages.

This customization handles both full indexing and delta indexing.

The following code should be added to SELECT clause:

CASE
WHEN TI_OFFERPRICE.PRICE_USD &lt;&gt; 0 
THEN 
(TI_OFFERPRICE.PRICE_USD - COSTPRICE.PRICE) / 
TI_OFFERPRICE.PRICE_USD * 100 
ELSE NULL 
END AS PROFIT_MARGIN

The following code should be added to FROM clause:

LEFT OUTER JOIN
(SELECT OFFER.CATENTRY_ID, OFFERPRICE.PRICE
FROM OFFER
INNER JOIN OFFERPRICE ON (OFFER.OFFER_ID = OFFERPRICE.OFFER_ID AND OFFERPRICE.CURRENCY = 'USD')
INNER JOIN TRADEPOSCN ON (OFFER.TRADEPOSCN_ID = TRADEPOSCN.TRADEPOSCN_ID AND TRADEPOSCN.NAME = 'My Company Cost Price List' )
) COSTPRICE
ON (TI_OFFERPRICE.CATENTRY_ID = COSTPRICE.CATENTRY_ID)

Your code should resemble

SELECT 
CATENTRY.CATENTRY_ID,CATENTRY.MEMBER_ID,CATENTRY.CATENTTYPE_ID,CATENTRY.PARTNUMBER,CATENTRY.MFPARTNUMBER,
CATENTRY.MFNAME, CATENTRY.BUYABLE, CATENTRY.STARTDATE, CATENTRY.ENDDATE, STORECENT.STOREENT_ID, CATENTDESC.NAME,
CATENTDESC.SHORTDESCRIPTION,CATENTDESC.LONGDESCRIPTION,CATENTDESC.THUMBNAIL,CATENTDESC.FULLIMAGE, CATENTDESC.KEYWORD, 
CATENTDESC.PUBLISHED,	CATENTSUBS.SUBSCPTYPE_ID,CATENTSUBS.DISALLOW_REC_ORDER, 
TI_DPGROUP.CATGROUP DPCATGROUP, TI_APGROUP.CATGROUPS APCATGROUP, TI_APGROUP.CATPATHS APCATPATH, TI_PRODUCTSET.PRODUCTSET,
TI_OFFERPRICE.PRICE_USD, TI_OFFERPRICE.PRICE_EUR, TI_OFFERPRICE.PRICE_CAD, TI_OFFERPRICE.PRICE_CNY,
TI_OFFERPRICE.PRICE_JPY, TI_OFFERPRICE.PRICE_KRW, TI_OFFERPRICE.PRICE_BRL, TI_OFFERPRICE.PRICE_TWD,
TI_OFFERPRICE.PRICE_PLN, TI_OFFERPRICE.PRICE_RUB, TI_OFFERPRICE.PRICE_RON, TI_OFFERPRICE.PRICE_EGP,
TI_OFFERPRICE.PRICE_GBP,

CASE
WHEN TI_OFFERPRICE.PRICE_USD &lt;&gt; 0 
THEN							
(TI_OFFERPRICE.PRICE_USD - COSTPRICE.PRICE) / TI_OFFERPRICE.PRICE_USD * 100 
ELSE NULL
END AS PROFIT_MARGIN

FROM CATENTRY
INNER JOIN TI_CATENTRY_0 TI_CATENTRY ON (CATENTRY.CATENTRY_ID=TI_CATENTRY.CATENTRY_ID)
LEFT OUTER JOIN STORECENT ON (CATENTRY.CATENTRY_ID=STORECENT.CATENTRY_ID)
LEFT OUTER JOIN CATENTDESC ON (CATENTDESC.CATENTRY_ID=CATENTRY.CATENTRY_ID AND CATENTDESC.LANGUAGE_ID=-1)
LEFT OUTER JOIN CATENTSUBS ON (CATENTSUBS.CATENTRY_ID=CATENTRY.CATENTRY_ID)
LEFT OUTER JOIN TI_DPGROUP_0 TI_DPGROUP ON (CATENTRY.CATENTRY_ID=TI_DPGROUP.CATENTRY_ID)
LEFT OUTER JOIN TI_DPGRPNAME_0_1 TI_DPGRPNAME ON (CATENTRY.CATENTRY_ID=TI_DPGRPNAME.CATENTRY_ID)
LEFT OUTER JOIN TI_APGROUP_0 TI_APGROUP ON (CATENTRY.CATENTRY_ID=TI_APGROUP.CATENTRY_ID)
LEFT OUTER JOIN TI_PRODUCTSET_0 TI_PRODUCTSET ON (CATENTRY.CATENTRY_ID=TI_PRODUCTSET.CATENTRY_ID)
LEFT OUTER JOIN TI_OFFERPRICE_0 TI_OFFERPRICE ON (CATENTRY.CATENTRY_ID=TI_OFFERPRICE.CATENTRY_ID)
LEFT OUTER JOIN TI_DPCATENTRY_0 TI_DPCATENTRY ON (CATENTRY.CATENTRY_ID=TI_DPCATENTRY.CATENTRY_ID)
LEFT OUTER JOIN TI_DCCATENTRY_0 TI_DCCATENTRY ON (CATENTRY.CATENTRY_ID=TI_DCCATENTRY.CATENTRY_ID)
LEFT OUTER JOIN TI_CATALOG_0 TI_CATALOG ON (CATENTRY.CATENTRY_ID=TI_CATALOG.CATENTRY_ID)
LEFT OUTER JOIN TI_CATGPENREL_0 TI_CATGPENREL ON (CATENTRY.CATENTRY_ID=TI_CATGPENREL.CATENTRY_ID)
LEFT OUTER JOIN TI_SEOURL_0_1 TI_SEOURL ON (CATENTRY.CATENTRY_ID=TI_SEOURL.CATENTRY_ID)
LEFT OUTER JOIN TI_CEDSOVR_0_1 TI_CATENTDESCOVR ON (CATENTRY.CATENTRY_ID=TI_CATENTDESCOVR.CATENTRY_ID)

LEFT OUTER JOIN TI_CASTB1_0_1 TI_CASTB1 ON (CATENTRY.CATENTRY_ID=TI_CASTB1.CATENTRY_ID)
LEFT OUTER JOIN TI_CAITB1_0_1 TI_CAITB1 ON (CATENTRY.CATENTRY_ID=TI_CAITB1.CATENTRY_ID)
LEFT OUTER JOIN TI_CAFTB1_0_1 TI_CAFTB1 ON (CATENTRY.CATENTRY_ID=TI_CAFTB1.CATENTRY_ID)

LEFT OUTER JOIN TI_ATTR_0_1 TI_ATTR ON (CATENTRY.CATENTRY_ID=TI_ATTR.CATENTRY_ID)

LEFT OUTER JOIN 
(SELECT OFFER.CATENTRY_ID, OFFERPRICE.PRICE
FROM OFFER
INNER JOIN OFFERPRICE ON (OFFER.OFFER_ID = OFFERPRICE.OFFER_ID AND OFFERPRICE.CURRENCY = 'USD')
INNER JOIN TRADEPOSCN ON (OFFER.TRADEPOSCN_ID = TRADEPOSCN.TRADEPOSCN_ID AND TRADEPOSCN.NAME = 'My Company Cost Price List' ) 
) COSTPRICE
ON (TI_OFFERPRICE.CATENTRY_ID = COSTPRICE.CATENTRY_ID)

WHERE  CATENTRY.CATENTTYPE_ID in ('ProductBean', 'ItemBean', 'PackageBean')"

After the code above, add the following mapping:
```
<field column="PROFIT_MARGIN" name="profitMargin"/>
```
Where
- PROFIT_MARGIN is the column alias in the above select clause
- profitMargin is the index field that is defined in the previous lesson
Note: profitMargin is the example index field name that is used in this tutorial. Use the same index field name that you defined when you updated your search schema.xml file.
Save your changes and close the file.
Restart the WebSphere Commerce search server.

Results

Note: In total, five changes are made in this configuration file.