Loading promotion folders and promotions into promotion folders

About this sample

When you run the Data Load utility to load this sample, the load operation first creates promotion folders for use in Management Center. Then, the load operation creates relationships between promotions and the folders that the promotions are going to be added within. The promotions are sorted based on the starting date time stamp of the promotions. This time stamp is retrieved from the STARTDATE column in the PX_PROMOTION database table. After the load operation completes, you can view the folders in the Promotions tool and browse through the folders to locate the promotions.

If you plan to create many promotion folders for your store or sort many promotions into folders, use the Data Load utility to create the folders and folder relationships. Copy the files that are provided with this sample and modify the files to help you create the input and configuration files for loading your store-specific data. If your store contains many promotions, you can create Java programs to generate the input files for creating your store promotion folders and for sorting promotions into these folders. For more information, see Java classes for generating data load input files for promotion folders and folder relationships.

You can load data for promotion folders and folder relationships in CSV or XML formatted input files. The following procedure loads the CSV input files. The sample CSV input files and data load configuration files for this sample are in the following directory:

utilities_root/samples/DataLoad/Promotion/Folder
WCDE_installdir\samples\DataLoad\Promotion\Folder

As an administrator, you can configure the Promotions tool so that only promotions that are not in a folder display in the main Promotions - List view. If your store has hundreds or thousands of promotions, preventing promotions that are sorted in folders from displaying in the Promotions - List view can make finding promotions easier. With this configuration enabled, business users can browse through folders to find sorted promotions and browse the main Promotions - List view to find unsorted promotions. For more information, see Organizing promotions with folders.

Sample input and configuration files

The following table lists the input files that you use to load the sample data. The table also identifies the business object mediators and business object configuration files that the Data Load utility uses to load the sample data.


Sample CSV input file	Description
PromotionFolders.csv For more information about folder data, see Promotion folder input file	The Data Load utility uses this sample input file to load the data to create promotion folders in the Promotions tool. The Data Load utility uses the following business object mediator and business object configuration file to load the data in this input file into the FOLDER database table. Business object mediator com.ibm.commerce.foundation.dataload.businessobjectbuilder.TableObjectBuilder Sample business object configuration file for loading this information: `utilities_root`/samples/DataLoad/Promotion/Folder/wc-loader-promotion-folder.xml `WCDE_installdir`\samples\DataLoad\Promotion\Folder\wc-loader-promotion-folder.xml
PromotionFolderItems.csv For more information about the data to sort promotions into promotion folders, see Promotion folder relationship input file	The Data Load utility uses this sample input file to sort promotions that exist in the main Promotions List in the Promotions tool into newly created promotion folders. The Data Load utility uses the following business object mediator and business object configuration file to load the data in this input file into the FOLDERITEM database table. Business object mediator com.ibm.commerce.foundation.dataload.businessobjectbuilder.TableObjectBuilder Sample business object configuration file for loading this information: `utilities_root/samples/DataLoad/Promotion/Folder/wc-loader-promotion-folderitem.xml` `WCDE_installdir\samples\DataLoad\Promotion/Folder\wc-loader-promotion-folderitem.xml` Note: This sample PromotionFolderItems.csv file contains promotion ID values that might not exist for the promotions in your store. Before you load this CSV input file, ensure that you update the input file to include ID values that exist for promotions in your store. The promotion ID values for promotions are stored in the PX_PROMOTION_ID column of the PX_PROMOTION database table.

The sample directory also includes the following Data Load utility configuration files:

wc-dataload.xml

The data load order configuration file, which identifies the order that the Data Load utility loads the sample CSV files. The load order file also identifies the appropriate business object configuration file to use to load each input file. For more information about load order configuration files, see Configuring the data load order.

wc-dataload-env.xml

The data load environment configuration file, which includes the environment variables for your HCL Commerce instance. These variables include the following information:

Business context variables, including the store identifier, catalog identifier, and the default language and currency for your store.
Database environment settings, including the database type, name, and schema.

wc-loader-promotion-folder.xml

The business object configuration file that defines how promotion folder data is loaded into the database. The file also defines the appropriate business object mediator to use to load the data.

wc-loader-promotion-folderitem.xml

The business object configuration file that defines how relationship data between a promotion folder and a promotion is loaded into the database. The file also defines the appropriate business object mediator to use to load the data.

Procedure

On a command line, go to the WCDE_installdir\bin directory.
Open a command line in the . Change the directory to utilities_root/bin directory. For information about entering and leaving containers, see Running utilities from the Utility server Docker container.
Enter the following command to run the sample data load order file and load the information that included in this sample:
- ./dataload.sh ../samples/DataLoad/Promotion/Folder/wc-dataload.xml
- dataload ..\samples\DataLoad\Promotion\Folder\wc-dataload.xml

Verifying results

Verify that the promotion folder and relationship data are loaded by reviewing the data load summary report. For more information about the location and contents of this summary report, see Verifying the results of the data load.
You can also verify that the sample data is loaded by running the following SQL statements against your HCL Commerce database:
```
select * from FOLDER;  
select * from FOLDERITEM;
```
Ensure that the data within the sample input files exists within the appropriate database tables.
In Management Center, verify that you can view the promotion folders and the appropriate promotions in the folders in the Promotions tool.

Removing the sample data from the HCL Commerce database

Edit the wc-dataload.xml load order configuration file to change the value of the dataLoadMode attribute from "Replace" to "Delete" to run the Data Load utility in delete mode. When you run the utility in delete mode, the utility deletes the data in your database that matches the rows in your CSV file. You do not need to edit the value for the Delete column in your input CSV file.

Verify that the data is removed from your database by running the following SQL statements:

select * from FOLDER;  
select * from FOLDERITEM;

The data that you loaded with this sample should no longer exist in your database.

In Management Center, verify that you can no longer view the promotion folders and the appropriate promotions in the folders in the Promotions tool.

Limitation

Folder and FolderItem were introduced solely as a convenience for Business Users who want to keep track of a large number of objects, such as Promotions or Marketing Activities. Introduced in HCL Commerce Version 7, Feature Pack 6, this feature only applies to Staging or Authoring and does not get propagated to the production database. Folders are not restricted to any one datatype.

Promotions are often re-used as a model for future promotions, or used for retrospective views of sales volumes. This makes them prime candidates for folders, as folders give the organization a means to keep them for quick discovery. Marketing activities also tend to be repeated over time.

Support was included to be able to use Dataload to create folder structures. Additional support was added to enable using Dataload to populate folders for Promotions. There are two example CSVs in the product for this purpose: PromotionFolders.csv and PromotionFolderItems.csv{{ }}. This can be customized to load other datatypes.

Later, support was added for Promotions Export that did not include the folders themselves. There were two main factors:

Folders can contain multiple data objects
- Exporting folder structure and membership would be incomplete if multiple datatypes are contained within the folder
Folders have Member owners
- FOLDERITEM entries do not have direct owners - they are children of FOLDER entries.
- The FOLDER table contains a field MEMBER_ID which is the owner of the folder. That field can be NULL to indicate that it is not owned by anyone, or is for a specific business user that would not exist outside of this Commerce instance.

Workaround

Dataload assumes that both the folder and the promotion exist in the system and that they have a known or knowable relationship. The input file uses the promotion id and the folder name. You can extract the promotion name and the folder name from the source database. The challenge is knowing the id for that promotion in the destination database, because they are likely to be different.

To determine the correct id in the new database based on the promotion's name, you can execute a sql query to list the id and name of all promotions. A matching operation needs to be performed to find the corresponding name in the source database.
Example: Promotion Register and Save is in folder NewCustomers. The promotion's id in the source database is 10012, but it will be different in the target database, perhaps 123456. You need a way to associate the target id with the folder name. You can do this using the source database folder name.
The list of all promotion folders in the source database can be found with this SQL: P.NAME, F.IDENTIFIER from FOLDER F, PX_PROMOTION P, FOLDERITEM FI where FI.FOLDER_ID=F.FOLDER_ID and P.PX_PROMOTION_ID=FI.REFERENCE_ID and F.TYPE='IBM_PromotionFolder';
The list of all promotion names and ids in the target database can be found with this SQL: select PX_PROMOTION_ID,NAME from PX_PROMOTION;
By combining the output of these two operations, matching the NAME from the promotion in the two databases, the target database's px_promotion_id can be associated to the folder from the source database to produce the CSV for the dataload operation.

Another alternative is to use a Java program to make use of jdbc queries to generate the same CSV. See Java classes for generating data load input files for promotion folders and folder relationships.